Python&JS宏实现保留样式合并表格后拆分_python 两份excel带样式合并excel_app.Sc

m_obj = re.match("(?:(\d+)年)?(\d+)月", sht.Name)
if not m_obj:
    continue
i += 1
year, month = m_obj.groups()
year = 2021 if year == "" else 2022
date = f"'{year}年{month:0>2}月"
sht.Activate()
max_rows = sht.Range("B7").End(constants.xlDown).Row
max_cols = sht.Range("B7").End(constants.xlToRight).Column
n = 7 if i == 1 else 8
rng = sht.Range(sht.Cells(n, 1), sht.Cells(max_rows, max_cols))
rng.Select()
excel_app.Selection.Copy()
dest.Activate()
dest.Range(f"A{pos}").Activate()
dest.Paste()
if i == 1:
    # 复制第一张工作表的列宽
    dest.PasteSpecial(constants.xlPasteColumnWidths)
dates.extend([date]\*(max_rows-7))
print(sht.Name, date, max_rows, max_cols)
pos += max_rows-n+1

excel_app.ScreenUpdating = True dest.Columns("B:B").Insert() dest.Range("B1").Value2 = "月份" dest.Range("B2").GetResize(len(dates)).Value = excel_app.WorksheetFunction.Transpose(dates) dest.Columns("B:B").ColumnWidth = 20 dest.Columns("B:B").AutoFit() dest.Range("A2").Value2 = "1" dest.Range("A2").Select() excel_app.Selection.AutoFill(dest.Range( f"A2:A{pos-1}"), constants.xlFillDefault) dest.Columns("A:A").AutoFit() dest.Rows(f"1:{pos-1}").AutoFit() dest.Name = "总表"

保存并退出

filename = re.sub(".[^.]+$", "", filename) wb_result.SaveAs(os.path.abspath(f"{filename}_合并.xlsx")) wb_result.Close()


最终合并结果如下：


![image-20221020223441560](https://p6-xtjj-sign.byteimg.com/tos-cn-i-73owjymdk6/ce598cb4e79d4085bd5006c5ccfeea7c~tplv-73owjymdk6-jj-mark-v1:0:0:0:0:5o6Y6YeR5oqA5pyv56S-5Yy6IEAg55So5oi3NTc5MjMwMTY3MDI=:q75.awebp?rk3s=f64ab15b&x-expires=1771407139&x-signature=pqryoJKJvscjscD5NOjqyMuwe3Y%3D)



## 带格式合并表格的JS宏实现


关于js宏的基础知识，之前已经分享过很多了，有兴趣可以查看前文：




> 

> WPS JS宏入门案例集锦  

>  <https://xxmdmst.blog.csdn.net/article/details/127097880>

> 

> 

> WPS JS宏示例-批量添加链接  

>  <https://xxmdmst.blog.csdn.net/article/details/127037824>

> 

> 

> JS宏综合示例-多维度筛选统计 带窗体  

>  <https://xxmdmst.blog.csdn.net/article/details/127138015>

> 

> 

> 



本需求最终对应宏代码如下：

function 合并并插入() { console.clear(); Application.ScreenUpdating = false; var wb=ActiveWorkbook; var wb_result = Workbooks.Add(); var dest = wb_result.Sheets(1); var i=0, pos=1; dates=[]; for(var sht of wb.Sheets){ let arr=/(?:(\d+)年)?(\d+)月/.exec(sht.Name); if(arr==undefined) continue; i += 1; var [_,year,month]=arr; year = year==undefined?2021:2022; if(month.length==1) month="0"+month; date = '${year}年${month}月; console.log(date); sht.Activate(); let max_rows = sht.Range("B7").End(xlDown).Row; let max_cols = sht.Range("B7").End(xlToRight).Column; n = i==1?7:8; rng = sht.Range(sht.Cells(n, 1), sht.Cells(max_rows, max_cols)); rng.Select(); Selection.Copy(); dest.Activate(); dest.Range(A${pos}).Activate(); dest.Paste(); // 复制第一张工作表的列宽 if(i==1) dest.PasteSpecial(xlPasteColumnWidths); for(k=0;k<max_rows-7;k++) dates.push(date); pos += max_rows-n+1; } Application.ScreenUpdating = true; dest.Columns("B:B").Insert(); dest.Range("B1").Value2 = "月份"; dest.Range("B2").Resize(dates.length).Value2 = WorksheetFunction.Transpose(dates); dest.Columns.Item("B:B").ColumnWidth = 20; dest.Columns.Item("B:B").AutoFit(); dest.Range("A2").Value2 = "1"; dest.Range("A2").Select(); Selection.AutoFill(dest.Range(A2:A${pos-1}), xlFillDefault); dest.Columns("A:A").AutoFit(); dest.Rows(1:${pos-1}).AutoFit(); dest.Name = "总表"; wb_result.SaveAs(${wb.Path}\\${wb.Name.replace(/\.[^.]+$/,"")}\_合并.xlsx); }


结果得到与上述完全一致的结果。




> 

> 注意：COM组件提供Python的接口Resize方法失效，可以使用GetResize替代。

> 

> 

> 




## 保留样式拆分表格的Python实现


之前的完整实现方法请查看：




> 

> 深度剖析Excel表拆分的三项技术  

>  https://xxmdmst.blog.csdn.net/article/details/118655016

> 

> 

> 



今天的实现只考虑将当前工作表拆分到工作簿中的情况，相对之前的代码会有一定简化，同时增加了复制列宽的功能，重新填充编号的功能。完整代码如下：

import win32com.client as win32 # 导入模块 from win32com.client import constants import os

def simple_split_excel(filename, group_num, title_row=1, excel_app=win32.gencache.EnsureDispatch('Excel.Application')): """作者小小明的csdn：blog.csdn.net/as604049322""" filename = os.path.abspath(filename) wb = excel_app.Workbooks.Open(filename) try: sheet = wb.ActiveSheet max_rows = sheet.UsedRange.Rows.Count max_cols = sheet.UsedRange.Columns.Count # 获取总表的数据区域 rng = sheet.Range(sheet.Cells(title_row, 1), sheet.Cells(max_rows, max_cols)) if title_row > 1: # 获取标题行前面的区域 start = sheet.Range(sheet.Cells( 1, 1), sheet.Cells(title_row-1, max_cols)) # 读取表头名称列表 header = sheet.Range(sheet.Cells(title_row, 1), sheet.Cells(title_row, max_cols)).Value[0] # 如果传入列名则找出列所在的位置 if isinstance(group_num, str): for i, value in enumerate(header, 1): if group_num == value: group_num = i break names = sum(sheet.Range(sheet.Cells(title_row+1, group_num), sheet.Cells(max_rows, group_num)).Value, tuple()) names = set(filter(None, names)) excel_app.ScreenUpdating = False for name in names: new_sheet = wb.Sheets.Add(After=wb.Sheets(wb.Sheets.Count)) new_sheet.Name = name if title_row > 1: # 先复制标题行之前的区域 wb.Activate() sheet.Activate() start.Copy() new_sheet.Activate() new_sheet.Range("A1").Activate() new_sheet.Paste() sheet.Activate() rng.AutoFilter(Field=group_num, Criteria1=name) rng.Copy() new_sheet.Activate() new_sheet.Range(f"A{title_row}").Activate() new_sheet.Paste() new_sheet.PasteSpecial(constants.xlPasteColumnWidths)

        new_sheet.Range("A2").Value2 = "1"
        new_sheet.Range("A2").Select()
        max_rows = new_sheet.Range("A1").End(constants.xlDown).Row
        excel_app.Selection.AutoFill(new_sheet.Range(
            f"A2:A{max\_rows}"), constants.xlFillDefault)
    excel_app.ScreenUpdating = True
    sheet.AutoFilterMode = False
    wb.Save()
finally:
    wb.Close()


调用该代码：

simple_split_excel("异常项清理明细_合并.xlsx", "业务系统归属业务员")


拆分结果如下：


![image-20221020230753843](https://p6-xtjj-sign.byteimg.com/tos-cn-i-73owjymdk6/b98fd879391744fca037e749250f811d~tplv-73owjymdk6-jj-mark-v1:0:0:0:0:5o6Y6YeR5oqA5pyv56S-5Yy6IEAg55So5oi3NTc5MjMwMTY3MDI=:q75.awebp?rk3s=f64ab15b&x-expires=1771407139&x-signature=ixYlu5%2BdhfiTOu3hQTh15nIu73k%3D)


## 保留样式拆分表格的JS宏实现


完整代码如下：