影刀RPA 多Excel文件合并：批量汇总数据

影刀RPA 多Excel文件合并批量汇总数据署名林焱什么情况用什么RPA每天采集数据生成一个Excel文件一个月下来30个文件。月底要做汇总分析需要把这30个文件合并成一个。或者分公司各自上报Excel总部需要批量合并到一张总表里。场景推荐方式同结构文件合并表头一致Python pandas concat不同结构文件合并Python openpyxl逐文件读取大量文件合并100Python pandas分批读取怎么做方式一pandas合并同结构文件importpandasaspdimportosimportglob# 获取目录下所有Excel文件folderrD:\数据\2024年6月filesglob.glob(os.path.join(folder,*.xlsx))# 排除临时文件files[fforfinfilesifnotos.path.basename(f).startswith(~$)]# 逐个读取并合并df_list[]forfileinfiles:[video(video-dh7vqHVk-1784019025261)(type-csdn)(url-https://live.csdn.net/v/embed/525000)(image-https://v-blog.csdnimg.cn/asset/23da3fe1f67a47106d725406cfde9a97/cover/Cover0.jpg)(title-拼多多店群自动化上架方案)]dfpd.read_excel(file,sheet_nameSheet1)# 添加来源文件名df[来源文件]os.path.basename(file)df_list.append(df)print(f读取:{os.path.basename(file)},{len(df)}行)# 合并所有数据mergedpd.concat(df_list,ignore_indexTrue)print(f合并完成, 共{len(merged)}行)# 保存merged.to_excel(rD:\数据\6月汇总.xlsx,indexFalse)方式二openpyxl合并保留格式importopenpyxlfromopenpyxl.stylesimportFontimportosimportglob folderrD:\数据\各分行filesglob.glob(os.path.join(folder,*.xlsx))files[fforfinfilesifnotos.path.basename(f).startswith(~$)]# 创建合并后的工作簿wb_outopenpyxl.Workbook()ws_outwb_out.active ws_out.title汇总header_writtenFalsecurrent_row1forfileinsorted(files):wbopenpyxl.load_workbook(file,read_onlyTrue,data_onlyTrue)wswb.active# 写表头只写一次ifnotheader_written:forcolinrange(1,ws.max_column1):valws.cell(row1,columncol).value cellws_out.cell(rowcurrent_row,columncol)cell.valueval cell.fontFont(boldTrue)current_row1header_writtenTrue# 写数据行forrowinrange(2,ws.max_row1):forcolinrange(1,ws.max_column1):ws_out.cell(rowcurrent_row,columncol).valuews.cell(rowrow,columncol).value current_row1wb.close()print(f合并:{os.path.basename(file)})# 添加来源列ws_out.cell(row1,columnws_out.max_column1).value来源ws_out.cell(row1,columnws_out.max_column).fontFont(boldTrue)wb_out.save(rD:\数据\分行汇总.xlsx)print(f完成共{current_row-2}行数据)方式三按Sheet分别合并importpandasaspdimportosimportglob folderrD:\数据\季度报表filesglob.glob(os.path.join(folder,*.xlsx))files[fforfinfilesifnotos.path.basename(f).startswith(~$)]# 获取所有Sheet名sheet_namesset()forfileinfiles:xlpd.ExcelFile(file)sheet_names.update(xl.sheet_names)# 按Sheet分别合并result{}# {sheet_name: merged_df}forsheetinsheet_names:df_list[]forfileinfiles:try:dfpd.read_excel(file,sheet_namesheet)df[来源文件]os.path.basename(file)df_list.append(df)exceptExceptionase:print(f跳过{os.path.basename(file)}的{sheet}表:{e})ifdf_list:result[sheet]pd.concat(df_list,ignore_indexTrue)# 保存到不同Sheetwithpd.ExcelWriter(rD:\数据\季度汇总.xlsx)aswriter:forsheet,dfinresult.items():# Sheet名最多31字符safe_namesheet[:31]df.to_excel(writer,sheet_namesafe_name,indexFalse)print(f完成共{len(result)}个Sheet)完整流程多文件智能合并importpandasaspdimportosimportglob# yd_input: folder, output_path, sheet_namefolderyd_input.get(folder,rD:\数据\待合并)output_pathyd_input.get(output_path,rD:\数据\合并结果.xlsx)sheet_nameyd_input.get(sheet_name,None)# None自动读取第一个Sheet# 获取文件列表filesglob.glob(os.path.join(folder,*.xls*))files[fforfinfilesifnotos.path.basename(f).startswith(~$)]files.sort()ifnotfiles:yd_output{status:error,message:未找到Excel文件}else:df_list[]errors[]forfileinfiles:try:ifsheet_name:dfpd.read_excel(file,sheet_namesheet_name)else:dfpd.read_excel(file)# 数据清洗# 去掉全空行dfdf.dropna(howall)# 添加来源信息df[_来源文件]os.path.basename(file)df_list.append(df)print(f✓{os.path.basename(file)}:{len(df)}行)exceptExceptionase:errors.append(f{os.path.basename(file)}:{str(e)})print(f✗{os.path.basename(file)}:{e})ifdf_list:# 检查列是否一致all_columns[set(df.columns)fordfindf_list]common_colsset.intersection(*all_columns)ifall_columnselseset()iflen(common_cols)!len(all_columns[0]):print(警告文件列不完全一致取交集列合并)df_list[df[list(common_cols)]fordfindf_list]mergedpd.concat(df_list,ignore_indexTrue)merged.to_excel(output_path,indexFalse)yd_output{status:ok,total_rows:len(merged),file_count:len(df_list),errors:errors}else:yd_output{status:error,message:所有文件读取失败,errors:errors}有什么坑坑一文件列顺序不一致导致数据错位现象两个文件A文件的列顺序是姓名|年龄|部门B文件是姓名|部门|年龄合并后年龄和部门列数据对不上。原因pandas concat按列名对齐但如果用openpyxl按列号复制就会错位。解决用pandas合并自动按列名对齐或手动对齐列# pandas自动按列名对齐不会错位mergedpd.concat(df_list,ignore_indexTrue)# 如果用openpyxl需要按列名对齐# 先建立列名到列号的映射header_map{}forcolinrange(1,ws_out.max_column1):header_map[ws_out.cell(row1,columncol).value]col# 读取时按列名找到对应列号forrowinrange(2,ws.max_row1):forcolinrange(1,ws.max_column1):headerws.cell(row1,columncol).valueifheaderinheader_map:out_colheader_map[header]ws_out.cell(rowcurrent_row,columnout_col).valuews.cell(rowrow,columncol).value坑二Excel临时文件干扰现象glob获取文件列表时多出了~$开头的文件读取报错。原因Excel打开文件时会创建~$文件名.xlsx临时文件。解决过滤掉临时文件filesglob.glob(os.path.join(folder,*.xlsx))# 排除临时文件files[fforfinfilesifnotos.path.basename(f).startswith(~$)]# 也排除以~开头的files[fforfinfilesifnotf.startswith(~)]坑三合并后数据类型变化TEMU店群如何管理运营现象合并前每个文件的手机号列是文本格式防止前面0丢失合并后变成了数字前面的0没了。原因pandas读取时自动推断数据类型如果某列全是数字会转成int64。解决读取时指定dtype# 指定列为字符串类型dfpd.read_excel(file,dtype{手机号:str,身份证号:str,工号:str})# 或全部读为字符串dfpd.read_excel(file,dtypestr)# 保存时也指定格式# openpyxl写入时设置number_format坑四文件太多导致内存溢出现象合并200个Excel文件每个10万行运行到一半内存溢出。原因pandas concat把所有数据加载到内存。解决分批读取合并或用openpyxl流式写入# 方案1分批合并batch_size20all_dfs[]foriinrange(0,len(files),batch_size):batchfiles[i:ibatch_size]batch_dfs[pd.read_excel(f)forfinbatch]batch_mergedpd.concat(batch_dfs,ignore_indexTrue)all_dfs.append(batch_merged)# 可选写中间文件释放内存batch_merged.to_csv(fbatch_{i//batch_size}.csv,indexFalse)# 最后合并所有批次finalpd.concat(all_dfs,ignore_indexTrue)# 方案2用openpyxl流式写入不在内存中保存所有数据wb_outopenpyxl.Workbook()ws_outwb_out.active current_row1forfileinfiles:# read_only模式不全部加载到内存wbopenpyxl.load_workbook(file,read_onlyTrue)wswb.activeforrowinws.iter_rows(min_row2,values_onlyTrue):forcol,valinenumerate(row,1):ws_out.cell(rowcurrent_row,columncol).valueval current_row1wb.close()wb_out.save(output_path)