from openpyxl import Workbook, load_workbookfrom openpyxl.utils import get_column_letterimport osimport datetime
# 配置参数template_file = f'D:\完美Excel\collectData\InfoTable.xlsx' # 模板文件名folder_path = f'D:\完美Excel\collectData' # Excel文件所在文件夹output_file = f'D:\完美Excel\collectData\汇总结果.xlsx' # 输出文件名
# 从模板中提取标题和对应坐标def extract_template_info(template_path): wb = load_workbook(template_path, data_only=True) ws = wb.active # 获取第一个工作表 titles = [] positions = []
# 数据区域 B7:H16 (行7-16,列2-8) for row in range(7, 17): for col in range(2, 9): cell = ws.cell(row=row, column=col) # 检查单元格是否为空 if cell.value in (None, "", " "): left_cell = ws.cell(row=row, column=col-1) if left_cell.value: title = str(left_cell.value).replace('\n', '').strip() titles.append(title) positions.append((row, col)) wb.close() return titles, positions
# 处理所有数据文件def process_files(folder, template_positions): all_data = [] files = [f for f in os.listdir(folder) if f.endswith('.xlsx') and f != template_file]
for file in files: file_path = os.path.join(folder, file) wb = load_workbook(file_path, data_only=True) ws = wb.active # 获取第一个工作表 row_data = []
for (r, c) in template_positions: cell = ws.cell(row=r, column=c) value = cell.value
# 处理日期类型 if isinstance(value, datetime.datetime): value = value.strftime('%Y-%m-%d %H:%M:%S') row_data.append(value if value is not None else "")
all_data.append(row_data) wb.close() return all_data
# 主程序if __name__ == "__main__": # 提取模板信息 titles, positions = extract_template_info(template_file)
# 处理数据文件 data = process_files(folder_path, positions)
# 创建汇总文件 wb_out = Workbook() # 创建新的工作簿 ws_out = wb_out.active
# 添加标题行 ws_out.append(titles)
# 添加数据行 for row in
