精华内容
下载资源
问答
  • 如何自动拆分excel表
    千次阅读
    2021-04-22 09:06:26

    工作场景:从系统导出后人工处理完的excel数据,需要按照部门拆分并推送给各部门人员。

    传统的工作方式就只能人工一个个部门筛选,复制粘贴出来各自发送。

    其实现在有些版本的excel或wps已经集成了根据某列数据自动拆分表格数据,不过有些老版本的仍不支持,搜集了网上前辈的资料,结合自身实际,使用VB实现了该功能。

    Sub splitTable()
        Dim d As Object, sht As Worksheet, arr, brr, r, kr, i&, j&, k&, x&
        Dim Rng As Range, Rg As Range, tRow&, tCol&, aCol&, pd&
        Application.ScreenUpdating = False '关闭屏幕更新
        Application.DisplayAlerts = False '关闭警告信息提示
        Set d = CreateObject("scripting.dictionary") 'set字典
        Set Rg = Application.InputBox("请框选拆分依据列!只能选择单列单元格区域!", Title:="提示", Type:=8)
        '用户选择的拆分依据列
        tCol = Rg.Column '取拆分依据列列标
        tRow = Val(Application.InputBox("请输入总表标题行的行数?"))
        '用户设置总表的标题行数
        If tRow = 0 Then MsgBox "你未输入标题行行数,程序退出。": Exit Sub
        Set Rng = ActiveSheet.UsedRange '总表的数据区域
        arr = Rng '数据范围装入数组arr
        tCol = tCol - Rng.Column + 1 '计算依据列在数组中的位置
        aCol = UBound(arr, 2) '数据源的列数
        For i = tRow + 1 To UBound(arr) '遍历数组arr
            If Not d.exists(arr(i, tCol)) Then
                d(arr(i, tCol)) = i '字典中不存在关键词则将行号装入字典
            Else
                d(arr(i, tCol)) = d(arr(i, tCol)) & "," & i '如果存在则合并行号,以逗号间隔
            End If
        Next
        For Each sht In Worksheets '遍历一遍工作表,如果字典中存在则删除
            If d.exists(sht.Name) Then sht.Delete
        Next
        kr = d.keys '字典的key集
        For i = 0 To UBound(kr) '遍历字典key值
            If kr(i) <> "" Then '如果key不为空
                r = Split(d(kr(i)), ",") '取出item里储存的行号
                ReDim brr(1 To UBound(r) + 1, 1 To aCol) '声明放置结果的数组brr
                k = 0
                For x = 0 To UBound(r)
                    k = k + 1 '累加记录行数
                    For j = 1 To aCol '循环读取列
                        brr(k, j) = arr(r(x), j)
                    Next
                Next
                With Worksheets.Add(, Sheets(Sheets.Count))
                '新建一个工作表,位置在所有已存在sheet的后面
                    .Name = kr(i) '表格命名
                    .[a1].Resize(tRow, aCol) = arr '放标题行
                    .[a1].Offset(tRow, 0).Resize(k, aCol) = brr '放置数据区域
                    Rng.Copy '复制粘贴总表的格式
                    .[a1].PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
                    .[a1].Select
                End With
            End If
        Next
        Sheets(1).Activate '激活第一个表格
        Set d = Nothing '释放字典
        Erase arr: Erase brr '释放数组
        MsgBox "数据拆分完成!"
        Application.ScreenUpdating = True '恢复屏幕更新
        Application.DisplayAlerts = True '恢复警示
        
    '开始生成新excel文件
        Application.ScreenUpdating = False
        For Each sht In Worksheets
            sht.Copy
            ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & sht.Name, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
            ActiveWorkbook.Close
        Next
        
        Application.ScreenUpdating = True '恢复屏幕更新
    End Sub
    
    

    运行过程如下:

    更多相关内容
  • 例如:一个综合性的excel报表,第1行C列是省份,而且此综合性报表有多个sheet要拆分,想要实现拆分所有sheet,并按省份合并成一个excel表,比如所有辽宁省的数据合并成一个excel文件以不同的sheet体现。用此脚本即可...
  • Excel表按照指定的列分组拆分并另存为单独的Excel文件 例如:A列有10条数据,汇总后是三个员工,使用小工具可以将表按照每个员工拆分成单独的Excel文件 关键点: 1、小工具要与Excel文件放在同一个文件夹下; 2、...
  • 如何将一个Excel表格根据其中一列的分类自动拆分到不同的SHEET中.
  • excel到这个文件上,自动拆分多个工作
  • 超简单的方法完整保留原有所有样式拆分Excel表

    万次阅读 多人点赞 2021-06-21 20:11:02
    下面我将使用Excel自带的筛选功能,筛选出指定的值,然后复制粘贴到一张新的工作中。唯一值不多的时候我们人工操作也可以,但数据量大唯一值多的时候,人工操作就耗时很久了。 如何使用Python实现这个自动化操作呢...

    本文作为一篇原始雏形已经过时,新版本的文章请移步到:
    深度剖析Excel表拆分的三项技术(已实现纯Openpyxl保留全部样式拆分,自适应单文件和多文件拆分等):https://blog.csdn.net/as604049322/article/details/118655016


    本文目录:


    透过本文你能够学到:

    1. 通过VBA复制粘贴全部样式进行单文件表拆分
    2. 纯Pandas拆分表,无样式保留
    3. openpyxl模板法拆分表保留表头样式

    作者:小小明,高阶数据处理玩家,帮助各行数据从业者解决各类数据处理难题。

    需求描述

    有一个Excel表格:

    image-20210621185839198

    我们希望将其按照指定的字段拆分为多个表格。如果直接用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()
    

    设置后的效果:

    image-20210621191620402

    构建一个拆分函数:

    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)
    

    image-20210621194804442

    可以看到拆分的结果,完全保留了原有的样式。

    最后我们保存文件即可:

    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
    

    升级:能指定起始行的带格式拆分

    后面碰过了起始行不在开头的需求:

    image-20210708163228106

    对于这类需求会增加复制非筛选区域的操作,我已经完整封装了全部过程到一个方法。

    完整代码如下:

    import win32com.client as win32  # 导入模块
    import os
    
    excel_app = win32.gencache.EnsureDispatch('Excel.Application')
    
    
    def split_excel(filename, save_name, num, title_row=1):
        """作者小小明的csdn:https://blog.csdn.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

    image-20210621213313300

    于是我们翻译为:

    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)
    

    缺点是日期没有保留原有的文本格式:

    image-20210621232800484

    不过我们可以指定日期的格式:

    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)
    

    使用了openpyxl还可以逐个单元格copy样式信息,相对来说会麻烦一些,也并不是所有样式都能复制。

    不过但如果我们只需要保留表头样式拆分Excel表,可以通过openpyxl制作模板并加载模板,下面看看具体实现:

    使用openpyxl保留表头样式拆分Excel表

    我们的实现目标依然是:

    image-20210708163228106

    其实这种需求,除了表头样式以外并不需要关心下面的数据的样式。这时使用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):
        """小小明的CSDN:https://blog.csdn.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)
    

    image-20210709152611806

    日期格式自定义起来会比较麻烦,难以通用化,列宽需要手工自适应(这个我在《Pandas指定样式保存excel数据的N种姿势》一文中已经实现了pandas自适应调整)。

    总结

    本文演示了通过复制粘贴筛选结果实现保留格式拆分表格的方法,并分别通过python调用和纯vba实现。作为一种一种抛砖引玉的做法并不能应对所有的需求,对于表头涉及多行合并单元格的需求还需各位童鞋发挥自己的脑洞,针对性解决相应的问题。

    通过上述代码的样式详细大家都能看到,对于样式拷贝,使用vba很简单;对于数据处理,使用Pandas很简单;仅仅只拷贝表头样式,使用openpyxl最简单,但对于日期和列宽需要特殊处理。

    展开全文
  • EXCEL按列拆分.txt

    2020-05-15 09:19:26
    EXCEL表格按列拆分,表头没有任何限制,仅需根据实际情况调整1个参数(从第XX行开始拆分),输入1个参数(依据第XX列拆分
  • 合并后的文件会自动存放在该文件的 MergeFiles 工作中; 如果要拆分文件,需要将要拆分的内容放到 SplitFiles 工作中。该宏自动会将第一列中值相同的行拆分到一个文件中,同时以该值作为文件名
  • Excel·VBA按行拆分工作

    千次阅读 2022-02-28 21:31:03
    对应之前文章《Excel·VBA按列拆分工作》,对Excel表格数据按照固定行数,将工作表拆分 工作按行拆分为工作 Sub 工作按行拆分为工作() '当前工作(worksheet)按固定行数拆分为多个工作,保存在当前...

    对应之前文章《Excel·VBA按列拆分工作表》,对Excel表格数据按照固定行数,将工作表拆分

    工作表按行拆分为工作表

    Sub 工作表按行拆分为工作表()
        '当前工作表(worksheet)按固定行数拆分为多个工作表,保存在当前工作簿(workbook)
        tm = Now()
        Set ws = Application.ActiveSheet  '当前工作表即为待拆分工作表
    '--------------------参数填写:num_row,数字;title_row表头行数,数字,第1行为1向下递增
        title_row = 1  '表头行数,每个拆分后的sheet都保留
        num_row = 100  '拆分数据行数,按多少行数据进行拆分,不能完全拆分的,多余行数单独
        max_row = ActiveSheet.UsedRange.Rows.count
        '拆分sheet数量,向上取整
        sheet_count = WorksheetFunction.RoundUp((max_row - title_row) / num_row, 0)
        
        For i = 1 To sheet_count:
            Worksheets.Add(after:=Sheets(Sheets.count)).Name = "拆分表" & i  '最后添加新sheet,并命名
            With ActiveSheet
                ws.Rows(1 & ":" & title_row).Copy  '本行复制表头,下行复制数据
    			.Range("A1").PasteSpecial Paste:=xlPasteAll
    			.Range("A1").PasteSpecial Paste:=xlPasteColumnWidths
                ws.Rows(num_row * (i - 1) + title_row + 1 & ":" & (num_row * i) + title_row).Copy .Range("A" & title_row + 1)
            End With
            'Exit For  '强制退出for循环,单次测试使用
        Next
    	Debug.Print ("拆分完成,累计用时" & Format(Now() - tm, "hh:mm:ss"))  '耗时
    End Sub
    

    工作表按行拆分为工作薄

    Sub 工作表按行拆分为工作薄()
        '当前工作表(worksheet)按固定行数拆分保存为多个工作簿(workbook),文件单独保存
        tm = Now()
        Application.Visible = False  '后台运行,不显示界面
        Application.DisplayAlerts = False  '不显示警告信息
        Set fso = CreateObject("Scripting.FileSystemObject")
    '--------------------参数填写:num_row,数字;title_row表头行数,数字,第1行为1向下递增
    	title_row = 1  '表头行数,每个拆分后的sheet都保留
        num_row = 100  '拆分数据行数,按多少行数据进行拆分,不能完全拆分的,多余行数单独
        Set ws = Application.ActiveSheet  '当前工作表即为待拆分工作表
        wb_path = Application.ActiveWorkbook.Path  '当前工作簿文件路径
        wb_name = Application.ActiveWorkbook.Name  '当前工作簿文件名和扩展名
        save_path = wb_path + "\拆分表"  '保存拆分后的表格保存路径
        max_row = ActiveSheet.UsedRange.Rows.count
        '拆分sheet数量,向上取整
        sheet_count = WorksheetFunction.RoundUp((max_row - title_row) / num_row, 0)
        
        If fso.FolderExists(save_path) Then
            Debug.Print ("拆分文件保存路径已存在:" & save_path)
        Else
            fso.CreateFolder (save_path)
            Debug.Print ("拆分文件保存路径已创建:" & save_path)
        End If
        
        For i = 1 To sheet_count:
            Workbooks.Add
            With ActiveSheet
                ws.Rows(1 & ":" & title_row).Copy  '本行复制表头,下行复制数据
    			.Range("A1").PasteSpecial Paste:=xlPasteAll
    			.Range("A1").PasteSpecial Paste:=xlPasteColumnWidths
                ws.Rows(num_row * (i - 1) + title_row + 1 & ":" & (num_row * i) + title_row).Copy .Range("A" & title_row + 1)
            End With
            '保存文件全名(文件路径、文件名、扩展名)
            save_file = save_path & "\" & fso.GetBaseName(wb_name) & "_拆分表" & i & "." & fso.GetExtensionName(wb_name)
            ActiveWorkbook.SaveAs filename:=save_file
            ActiveWorkbook.Close (False)
            'Exit For  '强制退出for循环,单次测试使用
        Next
        
        Set fso = Nothing  '释放内存
        Application.Visible = True
        Application.DisplayAlerts = True
        Debug.Print ("工作表已拆分完成,累计用时" & Format(Now() - tm, "hh:mm:ss"))  '耗时
        
    End Sub
    

    举例

    原始数据
    在这里插入图片描述
    参数:表头行数title_row = 1、按每5行拆分num_row = 5
    拆分为工作表
    在这里插入图片描述
    在这里插入图片描述
    拆分为工作薄
    在这里插入图片描述

    展开全文
  • python合并与拆分Excel文档源码,支持.xls和.xlsx两种文档格式。合并与拆分均可以指定模板文件,使结果文档带上你设定的格式。适合统计合并成汇总、汇总拆分成单(如工资条等)
  • C# 拆分Excel文件

    2018-07-07 15:57:45
    读取服务器下载的EXCEl文件,报错:外部不是预期的格式。进行格式转换后,生成新的Excel文件,根据某一列进行分组,并把分组后的结果填充到固定的Excel文件格式中。因为是用自动化测试工具写的,里面有一些是测试...
  • 3、在打开的EXCEL工作名称上点右键选择查看代码; 4、选择菜单栏:插入-模块,将以下代码复制到右边的代码框中,点击菜单:运行-运行子过程/用户窗体,直到弹出处理完毕对话框; 5、在文件夹中将生成拆分好的文件,...
  • Excel拆分多个sheet表格

    2017-11-04 14:25:39
    % 将excel表格里面多个sheet拆分成独立的excel %在EXCEl界面按Alt+F11打开VBA编辑器,插入新的模块(插入/模块), % 或者在任意一个sheet名称上点右键,选择“查看代码”即能够进入VBA编辑器 %复制下面的内容到里面...
  • 用于EXCEL表格的自动拆分宏定义,亲测好用,操作起来并不复杂,比手动拆分精准,不误工
  • Delphi操作Excel实例代码,将单个Excel文件按照其中一列,拆分为若干个Excel文件,保留原文件格式(表头、页眉、页脚等信息)
  • 【利用python+pandas 拆分excel表格】

    千次阅读 2021-12-17 16:15:26
    利用python拆分excel表格 我们常常会遇到一个表格中包含各种类型的数据,想要把表格按照不同类型拆分到多个工作簿,使用python几行代码就可以轻松搞定 首先是安装需要的包 先cmd命令行安装下需要的包 pip install ...

    利用python拆分excel表格

    我们常常会遇到一个表格中包含各种类型的数据,想要把表格按照不同类型拆分到多个工作簿,使用python几行代码就可以轻松搞定

    首先是安装需要的包

    先cmd命令行安装下需要的包
    pip install pandas
    pip install xlrd
    pip install openpyxl

    举个例子

    我的表格如下
    在这里插入图片描述
    现在我要将总表中所有数据按照学院名称的不同将其拆分并以学院名称命名工作簿,话不多说,我们上代码

    import pandas as pd
    import xlrd
    
    data = pd.read_excel(r"C:\\Users\\dell\\Desktop\\表格.xlsx") # 读取excel
    college_list = list(set(data['学院名称'])) # 将学院名称类型生成列表
    writer = pd.ExcelWriter(r"C:\\Users\\dell\\Desktop\\拆好的表.xlsx", engine='xlsxwriter') # 用 ExcelWriter 生成一个拆完表后的容纳工作簿
    data.to_excel(writer, sheet_name="总表", index=False)
    #  For 循环对某一列进行遍历,area_list 取自表格的某一列,最后通过循环每一个因子生成一个表,写入之前建好的工作簿中直至循环结束
    for j in college_list:
        df = data[data['学院名称'] == j]
        df.to_excel(writer, sheet_name=j, index=False)
    writer.save()  # 保存拆分好的表格
    

    运行后就可以在保存的路径中查看拆分后的表格,上图
    在这里插入图片描述
    在这里插入图片描述
    在这里插入图片描述

    展开全文
  • 话说Excel数据,分久必合、合久必分。Excel数据的“分”与“合”是日常办公中常见的操作。...利用Python的Pandas库,便可以自动实现Excel数据的“分分合合”。下面结合实例来分享本人整理的实用代码片段。
  • excel 自动拆分工作簿中的多个工作excel电子文件!
  • /usr/bin/env python# -*- coding:utf-8 -*-import xlrdfrom pyexcelerate import Workbookimport os"""excel 文件拆分1、指定源文件路径2、指定拆分行数大小3、输出累计金额和每个文件累计金额"""# 读取源excel所有...
  • excel 按列拆分、合并、自动分类 能自动分类表格,合并多电子表格,合并指定目录下所有xlsx 功能测试完善,判断优化,确保能正常运行
  • 今天我们来分享一个真实的自动化办公案例,希望各位 Python 爱好者能够从中得到些许启发,在自己的工作生活中更多的应用 Python,使得工作事半功倍! ●个人主页:Damon7575 ●欢迎点赞关注收藏 ●既选择了远方,便...
  • excel汇总拆分工具自动汇总拆分Excel内的数据减少数据处理工作量。是办公人员少不了的一款小软件,也可当excel汇总软件使用也可以当excel拆分工具使用。 功能介绍 【Excel汇总拆分工具基本介绍】 1、可将多个EXCEL...
  • 本文的文字及图片来源于网络...类似下图中的表格,领导要求你将各个部门筛选出,并按照“财务部.xlsx”的格式单独成立一个excel,数据少了还好说,如果数据量大了,大半夜叫你来拆分咋办呢?一、源代码:importpanda...
  • apache poi拆分excel表格

    2021-02-12 21:37:41
    前几天遇到一个需求,需要把一个有几万行有规则数据的excel文件拆分成N个excel文件,例如以下excel表格中包括以张三,李四,王五的各科考试成绩,每个学生的学号是唯一的,现在要求把每个人的成绩数据分开来生成独立...
  • python自动办公-41 excel处理实例(单工作表拆分到多工作).zip源码python项目实例源码打包下载python自动办公-41 excel处理实例(单工作表拆分到多工作).zip源码python项目实例源码打包下载python自动办公-41 ...
  • Python、VBA轻松自动化 作者 | Ryoko来源 | 凹凸数据当你收集了 n 个人的 EXCEL 记录,需要将它们汇成一个总时你会怎么做呢?如果不通过技术手段,要一个个打开再复制粘贴也太麻烦了吧!此时就需要一个通过几...
  • 通过vba 方式 自动拆分工作,拆分成独立工作,合并多个格式相同的工作为一个工作,解放双手,减轻工作量,自动化操作。

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 11,827
精华内容 4,730
关键字:

如何自动拆分excel表