天天看點
傳回

超簡單的方法完整保留原有所有樣式拆分Excel表

本文作為一篇原始雛形已經過時，新版本的文章請移步到：

深度剖析Excel表拆分的三項技術（已實作純Openpyxl保留全部樣式拆分，自适應單檔案和多檔案拆分等）

本文目錄：

文章目錄

  • ​​需求描述​​
  • ​​實作過程​​
  • ​​純VBA實作​​
  • ​​更新：能指定起始行的帶格式拆分​​
  • ​​VBA代碼翻譯成Python調用示例​​
  • ​​使用Pandas實作Excel拆分​​
  • ​​使用openpyxl保留表頭樣式拆分Excel表​​
  • ​​總結​​

透過本文你能夠學到：

  1. 通過VBA複制粘貼全部樣式進行單檔案表拆分
  2. 純Pandas拆分表，無樣式保留
  3. openpyxl模闆法拆分表保留表頭樣式
作者：小小明，高階資料處理玩家，幫助各行資料從業者解決各類資料處理難題。

需求描述

有一個Excel表格：

超簡單的方法完整保留原有所有樣式拆分Excel表

我們希望将其按照指定的字段拆分為多個表格。如果直接用pandas，代碼很簡單卻隻能保留資料；如果使用openpyxl，也無法直接設定原有的樣式，需要逐個設定會非常麻煩。下面我将使用Excel自帶的篩選功能，篩選出指定的值，然後複制粘貼到一張新的工作表中。唯一值不多的時候我們人工操作也可以，但資料量大唯一值多的時候，人工操作就耗時很久了。

如何使用Python實作這個自動化操作呢？那就是通過pywin32調用VBA。

下面我們開始操作吧：

實作過程

首先，用pywin32打開目标檔案：

import win32com.client as win32  # 導入子產品
import os

excel_app = win32.gencache.EnsureDispatch('Excel.Application')

filename = "資料源.xlsx"
filename = os.path.abspath(filename)

wb = excel_app.Workbooks.Open(filename)
sheet = wb.ActiveSheet
max_rows = sheet.UsedRange.Rows.Count
max_cols = sheet.UsedRange.Columns.Count
max_rows, max_cols      
(3216, 9)

可以看到源資料有3216行，9列。

擷取資料範圍，并設定自動列寬調整：

rng = sheet.Range(sheet.Cells(1, 1), sheet.Cells(max_rows, max_cols))
# 設定自動列寬
rng.EntireColumn.AutoFit()

設定後的效果：

超簡單的方法完整保留原有所有樣式拆分Excel表

建構一個拆分函數：

def split_excel(num):
    """num示被拆分的列号"""
    names = set(sheet.Range(sheet.Cells(2, num), sheet.Cells(max_rows, num)).Value)

    # 禁用自動更新加快執行速度
    excel_app.ScreenUpdating = False
    for name, in names:
        sheet.Activate()
        rng.AutoFilter(Field=num, Criteria1=name)
        rng.Select()
        excel_app.Selection.Copy()
        new_sheet = excel_app.Sheets.Add(After=wb.Worksheets(wb.Worksheets.Count))
        new_sheet.Name = name
        new_sheet.Range("A1").Activate()
        new_sheet.Paste()
        new_sheet.Range(new_sheet.Cells(1, 1), new_sheet.Cells(1, max_cols)).EntireColumn.AutoFit()
    # 恢複自動更新
    excel_app.ScreenUpdating = True

該函數涉及的方法很多，需要反複查詢VBA文檔并測試才能寫出，不過前人栽樹後人乘涼，我已經為大家寫出來啦，可以直接使用。當然也歡迎VBA大佬對本方法進行更新改造。

一些重點的API：

Range 對象：​​https://docs.microsoft.com/zh-cn/office/vba/api/excel.range(object)​​

Range.AutoFilter：​​https://docs.microsoft.com/zh-cn/office/vba/api/excel.range.autofilter​​

Sheets.Add ：​​https://docs.microsoft.com/zh-cn/office/vba/api/excel.sheets.add​​

Worksheet.Name 屬性：​​https://docs.microsoft.com/zh-cn/office/vba/api/excel.worksheet.name​​

Application.ScreenUpdating：​​https://docs.microsoft.com/zh-cn/office/vba/api/excel.application.screenupdating​​

其他需要注意的點：

在Excel本身的VBA環境，擷取唯一值，我們往往需要使用進階篩選或字典對象。VBA的字典對象使用起來較為麻煩，文檔位址：​​https://docs.microsoft.com/zh-cn/office/vba/language/reference/user-interface-help/dictionary-object​​

但我們再Python環境中使用VBA，則無需使用VBA的數組或字典對象，使用python本身的對象操作即可。

下面我們對區域列（第2列）進行拆分：

split_excel(2)
超簡單的方法完整保留原有所有樣式拆分Excel表

可以看到拆分的結果，完全保留了原有的樣式。

最後我們儲存檔案即可：

wb.SaveAs(os.path.abspath("result.xlsx"))
直接修改原有檔案直接調用wb.Save()即可，上述指令表示另存為。

可以關閉工作簿：

wb.Close()

還可以關閉Excel軟體：

excel_app.Quit()

純VBA實作

為了沒有安裝python的童鞋使用友善，将以上過程封裝成純vba代碼，可以直接在Excel軟體中使用：

Sub 帶格式分列()
    Application.ScreenUpdating = False
    Set Sh = ActiveSheet
    
    max_rows = Sh.UsedRange.Rows.Count
    max_cols = Sh.UsedRange.Columns.Count
    Set Rng = Sh.Range(Sh.Cells(1, 1), Sh.Cells(max_rows, max_cols))
    Rng.EntireColumn.AutoFit
    
    'Col為要手動輸入要拆分的列序數
    Col = CInt(InputBox("輸入用于分組的列序号！"))
    
    Range(Cells(2, Col), Cells(max_rows, Col)).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Cells(1, max_cols + 2), Unique:=True
    LastRow = Cells(1, max_cols + 2).End(xlDown).Row
    Range(Cells(1, max_cols + 2), Cells(LastRow, max_cols + 2)).RemoveDuplicates Columns:=1, Header:=xlNo
    LastRow = Cells(1, max_cols + 2).End(xlDown).Row
    
    For i = 1 To LastRow
        Name = CStr(Sh.Cells(i, max_cols + 2))
        Sh.Activate
        Rng.AutoFilter Field:=Col, Criteria1:=Name
        Rng.Copy
        Set new_sheet = Sheets.Add(After:=Sheets(Sheets.Count))
        new_sheet.Name = Name
        new_sheet.Range("A1").Activate
        new_sheet.Paste
        new_sheet.Range(new_sheet.Cells(1, 1), new_sheet.Cells(1, max_cols)).EntireColumn.AutoFit
    Next
    Sh.Activate
    Columns(max_cols + 2).Delete Shift:=xlToLeft
    Selection.AutoFilter
    
    Application.ScreenUpdating = True
End Sub

更新：能指定起始行的帶格式拆分

後面碰過了起始行不在開頭的需求：

超簡單的方法完整保留原有所有樣式拆分Excel表

對于這類需求會增加複制非篩選區域的操作，我已經完整封裝了全部過程到一個方法。

完整代碼如下：

import win32com.client as win32  # 導入子產品
import os

excel_app = win32.gencache.EnsureDispatch('Excel.Application')


def split_excel(filename, save_name, num, title_row=1):
    """作者小小明的：https://blog.net/as604049322"""
    wb = excel_app.Workbooks.Open(os.path.abspath(filename))
    try:
        sheet = wb.ActiveSheet
        max_rows = sheet.UsedRange.Rows.Count
        max_cols = sheet.UsedRange.Columns.Count
        if title_row > 1:
            start = sheet.Range(sheet.Cells(
                1, 1), sheet.Cells(title_row-1, max_cols))
        rng = sheet.Range(sheet.Cells(title_row, 1),
                          sheet.Cells(max_rows, max_cols))
        # 設定自動列寬
        rng.EntireColumn.AutoFit()
        names = set(sheet.Range(sheet.Cells(title_row+1, num),
                    sheet.Cells(max_rows, num)).Value)
        for name, in names:
            if not name:
                continue
            new_sheet = excel_app.Sheets.Add(
                After=wb.Worksheets(wb.Worksheets.Count))
            new_sheet.Name = name
            if title_row > 1:
                sheet.Activate()
                start.Copy()
                new_sheet.Activate()
                new_sheet.Range("A1").Activate()
                new_sheet.Paste()

            sheet.Activate()
            rng.AutoFilter(Field=num, Criteria1=name)
            rng.Copy()
            new_sheet.Activate()
            new_sheet.Range(f"A{title_row}").Activate()
            new_sheet.Paste()
            new_sheet.Range(new_sheet.Cells(1, 1), new_sheet.Cells(
                1, max_cols)).EntireColumn.AutoFit()
        wb.SaveAs(os.path.abspath(save_name))
    finally:
        wb.Close()
        
split_excel("工單.xlsx", '拆分結果.xlsx', 4, 2)
#這步會關閉你正在使用的Excel軟體，視具體情況決定是否要注釋掉
excel_app.Quit()

VBA代碼翻譯成Python調用示例

前面我示範了Python帶格式拆分Excel表，可能大家對使用python來調用vba還比較生疏，下面我将示範将一段vba代碼翻譯為python調用。

下面這段拆分Excel表的vba代碼來自才哥的文章《​​Python對比VBA實作excel表格合并與拆分​​》，作者是“兩百斤的老濤”，一起看看吧：

Sub 表格拆分()
    '螢幕重新整理=false
    Application.ScreenUpdating = False
    Dim LastRow, LastCol As Long
    Dim Sh, Sht As Worksheet
    'Sh指代目前活動頁
    Set Sh = ActiveSheet
    '目前活動頁的最後一行
    LastRow = Sh.Cells(Rows.Count, 1).End(xlUp).Row
    '目前活動頁的最後一列
    LastCol = Sh.Cells(1, Columns.Count).End(xlToLeft).Column
    '定義D為字典
    Dim D As Object
    Set D = CreateObject("Scripting.Dictionary")
    Dim Col As Integer
    'Col為要手動輸入要拆分的列序數
    Col = InputBox("輸入用于分組的列序号！")
    '從第2行找到最後一行
    For i = 2 To LastRow
        '查找這個要拆分行，看它在不在字典裡
        TempStr = CStr(Sh.Cells(i, Col))
        '如果在字典裡
        If D.exists(TempStr) Then
            '将資料放到對應的頁裡
            Set Sht = Worksheets(TempStr)
            '字典key值對應的項目值記錄該頁目前内容添加的行數，每次+1
            D(TempStr) = D(TempStr) + 1
            '下面一行可以注釋掉了跟下面的重複了……
            'Sht.Cells(D(TempStr), 1) = Sh.Cells(i, 1)
            For j = 1 To LastCol
                Sht.Cells(D(TempStr), j) = Sh.Cells(i, j)
            Next
        Else
            '如果不在字典裡，就添加一個新key
            D.Add TempStr, 1
            'i = i - 1是讓該行一會兒重新檢索一遍就能進到if裡了
            i = i - 1
            '在最後一頁新加一頁，頁名就是TempStr
            Sheets.Add After:=Sheets(Sheets.Count)
            Sheets(Sheets.Count).Name = TempStr
            '下面一行也是可以注釋掉的
            'Sheets(Sheets.Count).Cells(1, 1) = Sh.Cells(1, 1)
            '把第一行标題行弄過去
            For j = 1 To LastCol
                Sheets(Sheets.Count).Cells(1, j) = Sh.Cells(1, j)
            Next
        End If
    Next
    '激活初始頁，視覺上保持不變
    Sh.Activate
    'RT,GDCDSZ
    MsgBox ("完成！")

End Sub

下面我們将其轉換為python代碼來調用：

建立在已經打開Excel檔案的前提下：

import win32com.client as win32  # 導入子產品
import os

excel_app = win32.gencache.EnsureDispatch('Excel.Application')

filename = "資料源.xlsx"
filename = os.path.abspath(filename)

wb = excel_app.Workbooks.Open(filename)

​

​Set Sh = ActiveSheet​

​等價于：

Sh = wb.ActiveSheet

對于下面這兩行代碼：

'目前活動頁的最後一行
LastRow = Sh.Cells(Rows.Count, 1).End(xlUp).Row
'目前活動頁的最後一列
LastCol = Sh.Cells(1, Columns.Count).End(xlToLeft).Column

首先對于Rows和Columns可以通過頂級的’Excel.Application’對象來引用，而xlUp和xlToLeft兩個常量值，我目前采用的方案是通過文檔進行查閱，首先定位到vba文檔的Range.End 屬性，然後再點選 Direction 參數的資料類型：​​https://docs.microsoft.com/zh-cn/office/vba/api/excel.xldirection​​

超簡單的方法完整保留原有所有樣式拆分Excel表

于是我們翻譯為：

LastRow = Sh.Cells(excel_app.Rows.Count, 1).End(-4162).Row
LastCol = Sh.Cells(1, excel_app.Columns.Count).End(-4159).Column

專業的vba程式員都習慣用上面的方法擷取資料的行數和列數，但一般情況下用我前面的UsedRange的方法就夠了。

由于資料都直接讀取到python環境中，我們直接使用python的字典，繼續翻譯剩下的循環部分：

D = {}
Col = 2
excel_app.ScreenUpdating = False
for i in range(2, LastRow+1):
    TempStr = Sh.Cells(i, Col).Value
    if TempStr in D:
        Sht = wb.Sheets(TempStr)
        D[TempStr] += 1
        for j in range(1, LastCol+1):
            Sht.Cells(D[TempStr], j).Value = Sh.Cells(i, j).Value
    else:
        D[TempStr] = 1
        excel_app.Sheets.Add(After=wb.Sheets(wb.Sheets.Count))
        wb.Sheets(wb.Sheets.Count).Name = TempStr
        for j in range(1, LastCol+1):
            wb.Sheets(wb.Sheets.Count).Cells(1, j).Value = Sh.Cells(1, j).Value
Sh.Activate()
excel_app.ScreenUpdating = True

我再按照個人的習慣重新編寫一下：

rows_dict = {}
Col = 2
excel_app.ScreenUpdating = False
for i in range(2, LastRow+1):
    k = Sh.Cells(i, Col).Value
    if k not in rows_dict:
        Sht = excel_app.Sheets.Add(After=wb.Sheets(wb.Sheets.Count))
        Sht.Name = k
        Sht.Range(Sht.Cells(1, 1), Sht.Cells(1, LastCol)).Value = Sh.Range(
            Sh.Cells(1, 1), Sh.Cells(1, LastCol)).Value
        rows_dict[k] = 1
    else:
        Sht = wb.Sheets(k)
    rows_dict[k] += 1
    Sht.Range(Sht.Cells(rows_dict[k], 1), Sht.Cells(
        rows_dict[k], LastCol)).Value = Sh.Range(Sh.Cells(i, 1), Sh.Cells(i, LastCol)).Value
Sh.Activate()
excel_app.ScreenUpdating = True

最終完整代碼：

import win32com.client as win32  # 導入子產品
import os

excel_app = win32.gencache.EnsureDispatch('Excel.Application')


filename = "資料源.xlsx"
filename = os.path.abspath(filename)

wb = excel_app.Workbooks.Open(filename)
Sh = wb.ActiveSheet
LastRow = Sh.Cells(excel_app.Rows.Count, 1).End(-4162).Row
LastCol = Sh.Cells(1, excel_app.Columns.Count).End(-4159).Column

rows_dict = {}
Col = 2
excel_app.ScreenUpdating = False
for i in range(2, LastRow+1):
    k = Sh.Cells(i, Col).Value
    if k not in rows_dict:
        Sht = excel_app.Sheets.Add(After=wb.Sheets(wb.Sheets.Count))
        Sht.Name = k
        Sht.Range(Sht.Cells(1, 1), Sht.Cells(1, LastCol)).Value = Sh.Range(
            Sh.Cells(1, 1), Sh.Cells(1, LastCol)).Value
        rows_dict[k] = 1
    else:
        Sht = wb.Sheets(k)
    rows_dict[k] += 1
    Sht.Range(Sht.Cells(rows_dict[k], 1), Sht.Cells(
        rows_dict[k], LastCol)).Value = Sh.Range(Sh.Cells(i, 1), Sh.Cells(i, LastCol)).Value
Sh.Activate()
excel_app.ScreenUpdating = True
wb.SaveAs(os.path.abspath("result.xlsx"))
wb.Close()
excel_app.Quit()

經測試，原始vba代碼在Excel環境中 運作耗時1秒以内，但運作以上python代碼，耗時接近30秒。

這是因為，python通過vba讀取Excel資料時，存在很頻繁的互動，同時也說明并不是任何vba代碼都适合用python來調用。對于大部分資料讀寫操作，用python自帶的庫會便捷很多，速度也會比vba快。對于樣式複雜粘貼使用vba則極度友善。

使用Pandas實作Excel拆分

上述vba代碼實際上僅僅隻是實作不帶樣式的拆分，對于這樣的需求，其實用Pandas會非常簡單：

from openpyxl import load_workbook
import pandas as pd

df = pd.read_excel("資料源.xlsx")

with pd.ExcelWriter('result.xlsx', engine='openpyxl') as writer:
    writer.book = load_workbook("資料源.xlsx")
    for area, df_split in df.groupby("區域"):
        df_split.to_excel(writer, area, index=False)

缺點是日期沒有保留原有的文本格式：

超簡單的方法完整保留原有所有樣式拆分Excel表

不過我們可以指定日期的格式：

from openpyxl import load_workbook
import pandas as pd

df = pd.read_excel("資料源.xlsx")

with pd.ExcelWriter('result.xlsx', engine='openpyxl', datetime_format='YYYY/MM/DD') as writer:
    writer.book = load_workbook("資料源.xlsx")
    for area, df_split in df.groupby("區域"):
        df_split.to_excel(writer, area, index=False)
超簡單的方法完整保留原有所有樣式拆分Excel表

使用了openpyxl還可以逐個單元格copy樣式資訊，相對來說會麻煩一些，也并不是所有樣式都能複制。

不過但如果我們隻需要保留表頭樣式拆分Excel表，可以通過openpyxl制作模闆并加載模闆，下面看看具體實作：

使用openpyxl保留表頭樣式拆分Excel表

我們的實作目标依然是：

超簡單的方法完整保留原有所有樣式拆分Excel表

其實這種需求，除了表頭樣式以外并不需要關心下面的資料的樣式。這時使用openpyxl才是最簡單的，下面我們看看操作流程。

首先，我們讀取資料并分組：

from openpyxl import load_workbook

num = 4
title_row = 2
filename = "工單.xlsx"

book = load_workbook(filename)
sheet = book.active
# 讀取并分組相應的資料
data = {}
for row in sheet.iter_rows(min_row=title_row+1):
    row = [cell.value for cell in row]
    data_split = data.setdefault(row[num-1], [])
    data_split.append(row)

然後周遊每組建立模闆後寫入對應資料：

for name, data_split in data.items():
    new_sheet = book.copy_worksheet(sheet)
    new_sheet.title = name
    # 删除标題行以外的資料作為模闆
    new_sheet.delete_rows(title_row+1, sheet.max_row)
    for row in data_split:
        new_sheet.append(row)
book.save("拆分結果.xlsx")

是不是非常簡單？下面我們可以封裝起來：

from openpyxl import load_workbook


def split_excel(filename, save_name, num, title_row=1):
    """小小明：https://blog.net/as604049322"""
    book = load_workbook(filename)
    sheet = book.active
    # 讀取并分組相應的資料
    data = {}
    for row in sheet.iter_rows(min_row=title_row+1):
        row = [cell.value for cell in row]
        data_split = data.setdefault(row[num-1], [])
        data_split.append(row)
    for name, data_split in data.items():
        new_sheet = book.copy_worksheet(sheet)
        new_sheet.title = name
        # 删除标題行以外的資料作為模闆
        new_sheet.delete_rows(title_row+1, sheet.max_row)
        for row in data_split:
            new_sheet.append(row)
    book.save(save_name)


split_excel("工單.xlsx", '拆分結果.xlsx', 4, 2)

但是使用openpyxl拆分也有較大缺陷，例如資料中存在日期格式時：

split_excel("資料源.xlsx", '拆分結果2.xlsx', 2, 1)
超簡單的方法完整保留原有所有樣式拆分Excel表

日期格式自定義起來會比較麻煩，難以通用化，列寬需要手工自适應（這個我在《​​Pandas指定樣式儲存excel資料的N種姿勢​​》一文中已經實作了pandas自适應調整）。

總結

辦公自動化 vba Python excel sed
上一篇: devops 是什麼，狗叔用最簡單的方式告訴你！！
下一篇: C# Framework vs與windows作業系統

繼續閱讀