from spire.xls import *
from spire.xls.common import *

from spire.xls import *
from spire.xls.common import *

# 創(chuàng)建 Workbook 對(duì)象并加載 Excel 文件
workbook = Workbook()
workbook.LoadFromFile("/input/示例.xlsx")

# 設(shè)置拆分后文件的保存路徑
output_path = "/output/按表拆分/"

# 遍歷工作簿中的所有工作表
for sheet in workbook.Worksheets:

    # 創(chuàng)建新的 Workbook 對(duì)象，并刪除默認(rèn)工作表
    new_workbook = Workbook()
    new_workbook.Worksheets.Clear()

    # 將當(dāng)前工作表復(fù)制到新的工作簿中
    new_workbook.Worksheets.AddCopy(sheet)

    # 以當(dāng)前工作表名稱(chēng)命名，并保存為獨(dú)立的 Excel 文件
    new_workbook.SaveToFile(
        output_path + sheet.Name + ".xlsx",
        FileFormat.Version2016
    )

    # 釋放資源
    new_workbook.Dispose()

workbook.Dispose()

from spire.xls import *
from spire.xls.common import *


# 創(chuàng)建 Workbook 對(duì)象并加載 Excel 文件
workbook = Workbook()
workbook.LoadFromFile(
    "/input/示例.xlsx"
)

# 獲取第一個(gè)工作表
worksheet = workbook.Worksheets[0]

# 設(shè)置文件保存路徑
folderPath = "/output/按行拆分/"

# 將第 1-5 行拆分到新文件

# 創(chuàng)建新的 Workbook，并刪除默認(rèn)工作表
newWorkbook1 = Workbook()
newWorkbook1.Worksheets.Clear()

# 添加新的工作表
newWorksheet1 = newWorkbook1.Worksheets.Add("Sheet1")

# 復(fù)制第 1-5 行數(shù)據(jù)
for i in range(1, 6):
    worksheet.CopyRow(
        worksheet.Rows[i - 1],
        newWorksheet1,
        newWorksheet1.LastDataRow + 1,
        CopyRangeOptions.All
    )

# 復(fù)制列寬
for i in range(worksheet.Columns.Count):
    newWorksheet1.SetColumnWidth(
        i + 1,
        worksheet.GetColumnWidth(i + 1)
    )

# 保存文件
newWorkbook1.SaveToFile(
    folderPath + "1-5行.xlsx",
    FileFormat.Version2016
)

newWorkbook1.Dispose()


# 將第 6-11 行拆分到新文件

# 創(chuàng)建新的 Workbook，并清除默認(rèn)工作表
newWorkbook2 = Workbook()
newWorkbook2.Worksheets.Clear()

# 添加新的工作表
newWorksheet2 = newWorkbook2.Worksheets.Add("Sheet1")

# 復(fù)制表頭（第1行）
worksheet.CopyRow(
    worksheet.Rows[0],
    newWorksheet2,
    newWorksheet2.LastDataRow + 1,
    CopyRangeOptions.All
)

# 復(fù)制第6-11行數(shù)據(jù)
for i in range(6, 12):
    worksheet.CopyRow(
        worksheet.Rows[i - 1],
        newWorksheet2,
        newWorksheet2.LastDataRow + 1,
        CopyRangeOptions.All
    )

# 復(fù)制列寬
for i in range(worksheet.Columns.Count):
    newWorksheet2.SetColumnWidth(
        i + 1,
        worksheet.GetColumnWidth(i + 1)
    )

# 保存文件
newWorkbook2.SaveToFile(
    folderPath + "6-11行.xlsx",
    FileFormat.Version2016
)

newWorkbook2.Dispose()
workbook.Dispose()

from spire.xls import *
from spire.xls.common import *

# 創(chuàng)建 Workbook 對(duì)象并加載 Excel 文件
workbook = Workbook()
workbook.LoadFromFile(
    "/input/示例.xlsx"
)

# 獲取第一個(gè)工作表
worksheet = workbook.Worksheets[0]
# 設(shè)置輸出路徑
folderPath = "/output/按列拆分/"


# 拆分第 1-2 列（A-B列）

# 創(chuàng)建新的 Workbook，并刪除默認(rèn)工作表
newWorkbook1 = Workbook()
newWorkbook1.Worksheets.Clear()

# 添加新的工作表
newWorksheet1 = newWorkbook1.Worksheets.Add("Sheet1")

# 復(fù)制第1-2列
for i in range(1, 3):
    worksheet.CopyColumn(
        worksheet.Columns[i - 1],
        newWorksheet1,
        newWorksheet1.LastDataColumn + 1,
        CopyRangeOptions.All
    )

# 復(fù)制行高
for i in range(worksheet.Rows.Count):
    newWorksheet1.SetRowHeight(
        i + 1,
        worksheet.GetRowHeight(i + 1)
    )

# 保存文件
newWorkbook1.SaveToFile(
    folderPath + "AB.xlsx",
    FileFormat.Version2016
)

newWorkbook1.Dispose()


# 拆分第 3-5 列（C-E列）

# 創(chuàng)建一個(gè) Workbook，并刪除默認(rèn)工作表
newWorkbook2 = Workbook()
newWorkbook2.Worksheets.Clear()

# 添加新的工作表
newWorksheet2 = newWorkbook2.Worksheets.Add("Sheet1")

# 復(fù)制第3-5列
for i in range(3, 6):
    worksheet.CopyColumn(
        worksheet.Columns[i - 1],
        newWorksheet2,
        newWorksheet2.LastDataColumn + 1,
        CopyRangeOptions.All
    )

# 復(fù)制行高
for i in range(worksheet.Rows.Count):
    newWorksheet2.SetRowHeight(
        i + 1,
        worksheet.GetRowHeight(i + 1)
    )

# 保存文件
newWorkbook2.SaveToFile(
    folderPath + "CDE.xlsx",
    FileFormat.Version2016
)

newWorkbook2.Dispose()

# 拆分第 6 列（F列）

# 創(chuàng)建一個(gè) Workbook，并刪除默認(rèn)工作表
newWorkbook3 = Workbook()
newWorkbook3.Worksheets.Clear()
# 添加新的工作表
newWorksheet3 = newWorkbook3.Worksheets.Add("Sheet1")

# 復(fù)制第6列
worksheet.CopyColumn(
    worksheet.Columns[5],
    newWorksheet3,
    newWorksheet3.LastDataColumn + 1,
    CopyRangeOptions.All
)

# 復(fù)制行高
for i in range(worksheet.Rows.Count):
    newWorksheet3.SetRowHeight(
        i + 1,
        worksheet.GetRowHeight(i + 1)
    )

# 保存文件
newWorkbook3.SaveToFile(
    folderPath + "F.xlsx",
    FileFormat.Version2016
)

newWorkbook3.Dispose()
workbook.Dispose()

from spire.xls import *
from spire.xls.common import *

# 加載 Excel 文件
workbook = Workbook()
workbook.LoadFromFile("/員工信息.xlsx")

# 獲取第一個(gè)工作表
worksheet = workbook.Worksheets[0]

# 輸出目錄
folderPath = "/output/按條件拆分/"

# 用于保存不同部門(mén)對(duì)應(yīng)的 Workbook
department_books = {}

# 獲取總行數(shù)
row_count = worksheet.Rows.Count

# 設(shè)置按部門(mén)拆分
department_column = 3

# 遍歷數(shù)據(jù)行（跳過(guò)表頭）
for i in range(1, row_count):
    # 獲取部門(mén)名稱(chēng)
    department = worksheet.Range[i + 1, department_column].Value

    if department not in department_books:
        # 創(chuàng)建新的 Workbook
        newWorkbook = Workbook()
        newWorkbook.Worksheets.Clear()

        newWorksheet = newWorkbook.Worksheets.Add("Sheet1")

        # 復(fù)制表頭
        worksheet.CopyRow(
            worksheet.Rows[0],
            newWorksheet,
            newWorksheet.LastDataRow + 1,
            CopyRangeOptions.All
        )

        department_books[department] = newWorkbook

    # 獲取目標(biāo) Sheet
    newWorksheet = department_books[department].Worksheets[0]

    # 復(fù)制當(dāng)前行
    worksheet.CopyRow(
        worksheet.Rows[i],
        newWorksheet,
        newWorksheet.LastDataRow + 1,
        CopyRangeOptions.All
    )

# 保存文件
for department, wb in department_books.items():

    wb.SaveToFile(
        folderPath + department + ".xlsx",
        FileFormat.Version2016
    )
    wb.Dispose()

workbook.Dispose()