该程序能将同目录下的所有后缀为.xlsx的文件汇总后保存为"汇总.xlsx"。

文件的模版的表头必须为一行，所有xlsx文件为同一模版。

 1  1 # coding=utf-8
 2  2
 3  3 import datetime
 4  4 import os
 5  5 from openpyxl import Workbook
 6  6 from openpyxl import load_workbook
 7  7 
 8  8 root_dir = os.getcwd()  # 获取当前工作目录路径
 9  9 file_lst = []   # 当前目录下所有文件列表
10 10 xlsx_lst = []   # 当前目录下所有.xlsx文件列表
11 11 for dirpath, dirname, files in os.walk(root_dir):
12 12     for file in files:
13 13         file_lst.append(file)
14 14 
15 15 for elem in file_lst:
16 16     if elem.endswith('xlsx') and elem != '汇总.xlsx':   # or elem.endswith('xls'): 获取文件名结尾为'xlsx'或'xls'的文件名
17 17         xlsx_lst.append(elem)
18 18 print(xlsx_lst)
19 19 
20 20 wb1 = Workbook()  # 实例化一个Excel文件
21 21 wb1.save('汇总.xlsx')
22 22 
23 23 wb2 = load_workbook(xlsx_lst[0])  # 加载任意一个文件以复制首行到“汇总”文件
24 24 ws2 = wb2.active
25 25 max_col2 = ws2.max_column
26 26 i = 0
27 27 b = []
28 28 
29 29 while i < max_col2:
30 30     i = i+1
31 31     b.insert(i, ws2.cell(1, i).value)   # 把表头存到列表b中
32 32 
33 33 wb1 = load_workbook('汇总.xlsx')
34 34 ws1 = wb1.active
35 35 i = 0
36 36 while i < max_col2:
37 37     ws1.cell(1, i+1).value = b[i]   # 从列表b读取数据并复制到汇总表中
38 38     i = i + 1
39 39 wb1.save('汇总.xlsx')
40 40 
41 41 for xlsx in xlsx_lst:
42 42     wb2 = load_workbook(xlsx)
43 43     ws2 = wb2.active
44 44     max_row2 = ws2.max_row
45 45     max_col2 = ws2.max_column
46 46     max_row1 = ws1.max_row
47 47     print(f'processing {xlsx}...')
48 48     #    for ll in range(max_row1 + 1, max_row1 + max_row2 + 2):
49 49     for j in range(2, max_row2+1):
50 50         for k in range(1, max_col2+1):
51 51             ws1.cell(max_row1+1, k).value = ws2.cell(j, k).value
52 52         max_row1 = ws1.max_row
53 53     print(f'{max_row2-1} rows are copied')
54 54 wb1.save('汇总.xlsx')
55 55 print('All Done!')