python复制Excel多个sheet到Excel表

由于工作需要，经常需要复制Excel多个sheet到Excel表，今天抽时间来实现它
利用python openpyxl库实现，
将
目录这2个文件复制到其他文件

话不多说，上代码

from openpyxl import Workbook
from openpyxl import load_workbook
from openpyxl.writer.excel import ExcelWriter
# 导入字体、边框、颜色以及对齐方式相关库
from openpyxl.styles import Font, Border, Side, PatternFill, colors, Alignment

'''
该脚本用来拷贝paydroid平台化文档 到一个文档 
如果出现读数据错误 记得将源文件【paydroid平台化文档】另存为一个Excel文档
 参考https://stackoverflow.com/questions/46150893/error-when-trying-to-use-module-load-workbook-from-openpyxl
源文件：src_paydroid.xlsx
目的文件：copy_fromPaydroid
2021-0930 国庆到了 补充将修改时间列复制到测试执行人
再次备注：亲测 功能有效 
author:liuw
'''
a = 0
b = []
state = []  # 状态
typeinfo = []  # 类型
impact_model = []  # 影响机型
title = []
summary = []
test_recommendations = []
update_time = []

wb = load_workbook('C:\\Users\\liuw\\Desktop\\python\\src_paydroid.xlsx', data_only=True)

# sheets=['1.21.0034','1.21.0035','1.21.0036','1.21.0037','1.21.0038','1.21.0039','1.21.0042','1.21.0043','1.21.0044','1.21.0045','1.21.0046','1.21.0047','1.21.0048','1.21.0049','1.21.0050','1.21.0051','1.21.0052','1.21.0053','1.21.0054','1.21.0055','1.21.0056','1.21.0057','1.21.0058','1.21.0059','1.21.0060','1.21.0061','1.21.0062','1.21.0063','1.21.0064','1.21.0065','1.21.0066','1.21.0067','1.21.0068','1.21.0069','1.21.0070','1.21.0071','1.21.0072','1.21.0073','1.21.0074','1.21.0075','1.21.0076','1.21.0077','1.21.0078','1.21.0079','1.21.0080','1.21.0081','1.21.0082','1.21.0083','1.21.0084','1.21.0085','1.21.0086','1.21.0087','1.21.0088','1.21.0089','1.21.0090','1.21.0091','1.21.0092','1.21.0093','1.21.0094','1.21.0095','1.21.0096','1.21.0097','1.21.0098','1.21.0099','1.21.0100','1.21.0101']
# sheets=['1.21.0076','1.21.0077','1.21.0078','1.21.0079']

'''
2024-07-02 更新脚本 处理Excel sheet数量过多时自动循环生成
author:liuw
'''
def generate_sheets(startindex,endindex):
    sheets = [startindex, endindex]
    sheets_temp = []
    i = int(sheets[0][5:])
    j = int(sheets[1][5:])
    # print(j)
    for num in range(i, j + 1):
        # print(num)
        if num < 10:
            sheets_temp.insert(0, '1.21.000' + str(num))
        elif num < 100:
            sheets_temp.insert(0, '1.21.00' + str(num))
        elif num < 1000:
            sheets_temp.insert(0, '1.21.0' + str(num))
    reversed_list = list(reversed(sheets_temp))
    print(reversed_list)
    return reversed_list


sheets = generate_sheets('1.21.0034','1.21.0101')
sheets.remove('1.21.0041')  # 源文件paydroid-changelog有些sheet没有
sheets.remove('1.21.0040')
for i in sheets:
    ws = wb[i]
    # print(ws.max_row)  # 表行数
    # print(ws.max_column)  # 表列数
    sheet = wb.active
    a = ws.max_row
    print('当前sheet.length', a)
    i = 0
    while i < a:
        i = i + 1
        if i<5:
            continue
        print(ws.cell(i, 1).value)  # 表里的第1列 的数据
        b.append(ws.cell(i, 1).value)
        # b.insert(i, ws.cell(i, 1).value)
        state.append(ws.cell(i, 3).value)
        typeinfo.append(ws.cell(i, 5).value)
        impact_model.append(ws.cell(i, 7).value)
        title.append(ws.cell(i, 9).value)
        summary.append(ws.cell(i, 10).value)
        test_recommendations.append(ws.cell(i, 11).value)
        update_time.append(ws.cell(i,4).value)
    print('b.length', len(b))
print(b)
print("新写入的Excel 总行:",len(b))


wbtest = load_workbook('C:\\Users\\liuw\\Desktop\\python\\copyExcel.xlsx')
ws = wbtest['20240110']
ws.font = Font(u'微软雅黑',size=12)  #样式功能 暂未实现
sheet = wb.active
i = 0
while i < len(b):
    ws.cell(i + 3, 1).value = b[i]  # 复制粘贴到 目的.xlsx 的第3行 第一列
    ws.cell(i + 3, 2).value = state[i]
    ws.cell(i + 3, 3).value = typeinfo[i]
    ws.cell(i + 3, 6).value = impact_model[i]
    ws.cell(i + 3, 4).value = title[i]
    ws.cell(i + 3, 5).value = update_time[i]
    ws.cell(i + 3, 7).value = summary[i]
    ws.cell(i + 3, 9).value = test_recommendations[i]

    i = i + 1
wbtest.save('C:\\Users\\liuw\\Desktop\\python\\copyExcel.xlsx')
wbtest.close()  # 关闭

最终效果

完美实现 亲测可用 撒花点赞