• Python&JS宏 实现保留样式合并表格后拆分

    📢作者： 小小明-代码实体

    📢博客主页：https://blog.csdn.net/as604049322

    📢欢迎点赞 👍 收藏 ⭐留言 📝 欢迎讨论！

    需求如下：

    image-20221021092207082

    将所有表格的数据区域合并起来，第二列要插入当前年月，之后再对合并后的总表使用业务员进行拆分，整个操作要求保留原始数据的所有样式，包括颜色和合并单元格等。

    带格式合并表格的Python实现

    之前分享过一个普通的表格合并的需求：Python调用VBA实现保留原始样式的表格合并
    但这次需求增加了一点难度，需要额外增加一列，这次我们将使用更完善的代码解决该问题，Python编码如下：

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

excel_app = win32.gencache.EnsureDispatch('Excel.Application')
# 可以看到打开的Excel软件
excel_app.Visible = True
# vba只支持传入绝对路径
filename = "异常项清理明细10.14.xlsx"
wb = excel_app.Workbooks.Open(os.path.abspath(filename))
wb_result = excel_app.Workbooks.Add()
dest = wb_result.Sheets(1)
i, pos = 0, 1
dates = []
excel_app.ScreenUpdating = False
for sht in wb.Sheets:
    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()

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55
    • 56
    • 57

    最终合并结果如下：

    image-20221020223441560

    带格式合并表格的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`);
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47

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

    注意：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()

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55
    • 56
    • 57
    • 58
    • 59
    • 60
    • 61
    • 62

    调用该代码：

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

    • 1

    拆分结果如下：

    image-20221020230753843

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

    完整代码如下：

    function excel表拆分() {
	console.clear();
	let group_num="业务系统归属业务员", title_row=1;
	var wb=ActiveWorkbook;
	var sht = wb.ActiveSheet;
	var max_rows = sht.UsedRange.Rows.Count,max_cols = sht.UsedRange.Columns.Count;
	// 获取总表的数据区域
    rng = sht.Range(sht.Cells(title_row, 1), sht.Cells(max_rows, max_cols));
	if(title_row>1){
		// 获取标题行前面的区域
		start = sht.Range(sht.Cells(1, 1), sht.Cells(title_row-1, max_cols));
	}
    // 读取表头名称
    let header = sht.Range(sht.Cells(title_row, 1), sht.Cells(title_row, max_cols)).Value2[0];
    // 如果传入列名则找出列所在的位置
    if(typeof(group_num)=="string"){
		for(i=0;i<header.length;i++){
			if(group_num == header[i]){
				group_num=i+1;
				break;
			}
		}
	}
    Console.log(group_num);
    // 获取拆分列去除空值后的所有唯一值
    var names=sht.Range(sht.Cells(title_row+1, group_num), sht.Cells(max_rows, group_num)).Value2.flat();
    names=Array.from(new Set(names.filter(v=>v!=undefined)));
    Application.ScreenUpdating = false;
    for(name of names){
    	new_sheet = Sheets.Add(undefined,wb.Sheets(wb.Sheets.Count));
    	new_sheet.Name = name;
    	console.log(name);
    	if(title_row>1) {
    		// 先复制标题行之前的区域
			sht.Activate();
            start.Copy();
            new_sheet.Activate();
            new_sheet.Range("A1").Activate()
            new_sheet.Paste();
		}
		sht.Activate();
		rng.AutoFilter(group_num, name);
		rng.Copy();
		new_sheet.Activate();
		new_sheet.Range(`A${title_row}`).Activate();
		new_sheet.Paste();
		new_sheet.PasteSpecial(xlPasteColumnWidths);
		
		new_sheet.Range("A2").Value2 = "1";
        new_sheet.Range("A2").Select();
        let max_rows = new_sheet.Range("A1").End(xlDown).Row
        Selection.AutoFill(new_sheet.Range(`A2:A${max_rows}`), xlFillDefault)
    }
    Application.ScreenUpdating = true;
    sht.Activate();
	sht.AutoFilterMode = false;        
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55
    • 56
    • 57

    点击开发工具->JS宏，运行上述代码后即可得到与Python一样的结果，不过代码未执行保存操作，手动保存即可。

    以上就是有关带格式合并与拆分表格的综合案例。

  • 相关阅读:
    Mybatis-Plus主键生成策略
    vue3学习-1配置以及启动
    线程和进程
    04-React脚手架 & 集成Axios
    简单易懂的时序数据压缩算法分析
    DSPE-PEG2K-MAL|磷脂聚乙二醇马来酰亚胺(DSPE-PEG-MAL)|二硬脂酰基磷脂酰乙醇胺 聚乙二醇 马来酰亚胺，齐岳生物
    【附源码】计算机毕业设计SSM数据结构知识点渐进学习网站
    那些年你啃过的ConcurrentHashMap
    LeetCode307 周赛（补记）
    尚硅谷-SpringMVC篇
  • 原文地址：https://blog.csdn.net/as604049322/article/details/127437234