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

疯狂Android讲义

于 2024-04-27 08:20:27 发布

阅读量1k

点赞数 17

文章标签： javascript python excel

本文链接：https://blog.csdn.net/m0_61044466/article/details/138238582

版权

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://img-blog.csdnimg.cn/img_convert/84859ca7913320a6cdccebf63d3d345e.png)


## 带格式合并表格的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(arrundefined) continue;
i += 1;
var [_,year,month]=arr;
year = yearundefined?2021:2022;
if(month.length1) 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 = i1?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：https://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()