影刀RPA新手教程多Excel文件合并完全指南——按列合并、去重汇总与格式统一化实战本文作者林焱 | 转载请注明出处开篇案例12个部门的Excel合并完格式全乱了今年初做一个项目要把12个分公司的月度报表合并成一张总表。每个分公司的Excel格式都一样都是人力资源部发的模板我想当然地用影刀的合并Excel指令一键合并。合并完打开一看第3列在第5个文件里变成了日期格式第7列的数字变成了科学计数法表头行数还不统一有的分公司多了一行说明文字。原因是虽然用了模板但各分公司填的时候Excel自动把某些列的格式给改了。比如某分公司在工号列里填了00123Excel自动把前面的0去掉了格式也从文本变成了数字。这次经历让我明白合并Excel之前一定要先统一格式否则合并完还要花更多时间清洗。本文所有案例围绕12个分公司月度报表合并这条真实业务线展开。模块一安装与准备工作合并多个Excel推荐用Python的pandas库。安装pip install pandas openpyxlpandas的concat和merge功能非常强大比影刀自带指令灵活很多。如果你还没配置好Python环境参考 home.linyan.cloud 上的安装教程。新建流程命名为多Excel合并Demo。模块二元素定位从网页下载多个Excel多个Excel往往需要从网页后台批量下载。比如每个月要在后台选12个分公司分别点导出下载12个Excel。批量下载的实现importtime# 伪代码对应影刀的循环点击指令forbranchin[北京,上海,广州,深圳,...]:# 选择分公司select_branch(branch)# 点击导出click_export()# 等待下载完成time.sleep(3)更高效的做法用API批量导出如果后台有API可以一次请求导出所有分公司的数据不需要逐个点击。用影刀的发送HTTP请求指令或者Python的requests库。模块三变量与数据类型合并时的数据类型冲突合并多个Excel时最常见的问题是同一列在不同文件里的数据类型不一样。拼多多店群自动化上架方案比如文件1的工号列是文本“00123”文件2的工号列是数字123合并时pandas会尝试自动推断类型可能导致数据丢失前面的0被去掉。解决方法读取时强制指定类型importpandasaspddefread_excel_with_types(filepath,type_map): 读取Excel强制指定列类型 type_map: {列名: str} # 强制工号列为字符串 dfpd.read_excel(filepath,dtypetype_map)returndf# 用法type_map{工号:str,手机号:str,# 手机号必须是字符串否则前面的0会丢身份证号:str,# 同理}dfread_excel_with_types(北京分公司.xlsx,type_map)我当时踩过这个坑合并完才发现工号前面的0全没了只好重新处理一遍。模块四流程控制合并流程的标准结构多Excel合并的标准流程1. 扫描文件夹获取所有Excel文件路径 2. 对每一个文件 2.1 检查文件是否完整能正常打开 2.2 读取数据统一格式 2.3 检查必要列是否存在 2.4 追加到总表 3. 对总表进行去重和清洗 4. 保存合并结果 5. 生成合并报告每个文件的行数、合并后总行数、去重情况在影刀里步骤1用获取文件列表指令步骤2用循环步骤3-4用Python代码步骤5用写入Excel指令。模块五网页自动化结合Excel合并有些场景不需要先下载Excel再合并可以直接在网页上抓取所有分公司的数据存到一个Excel里。这样就跳过了合并这一步。判断该不该先下载再合并如果数据量小每个文件不到1000行直接在网页上抓存到一个Excel里。如果数据量大或者网页有反爬先导出再合并。模块六数据处理——按列合并横向合并按列合并是指两个表的行数相同把它们的列拼在一起。比如表A有工号、姓名表B有部门、职位按工号对齐后合并成一张表。importpandasaspddefmerge_excel_horizontal(file1,file2,output_path,key_column): 横向合并两个Excel类似VLOOKUP但是用pandas做 df1pd.read_excel(file1)df2pd.read_excel(file2)# 用merge横向合并resultpd.merge(df1,df2,onkey_column,howleft)result.to_excel(output_path,indexFalse)print(f横向合并完成{file1}{file2}-{output_path})print(f结果{len(result)}行{len(result.columns)}列)多文件横向合并按相同列名自动对齐importpandasaspdimportglobdefmerge_multiple_excel_horizontal(file_pattern,output_path,key_column): 多个Excel按key_column横向合并 适用于每个文件是一个月的销售数据要合并到一张表里 filesglob.glob(file_pattern)print(f找到{len(files)}个文件)# 读取第一个文件作为基准resultpd.read_excel(files[0])# 逐个横向合并forfilepathinfiles[1:]:dfpd.read_excel(filepath)resultpd.merge(result,df,onkey_column,howouter,suffixes(,f_{get_month_from_filename(filepath)}))result.to_excel(output_path,indexFalse)print(f多文件横向合并完成结果保存到{output_path})模块七数据处理——纵向合并追加行纵向合并是最常用的把多个结构相同的Excel拼成一个就是把行追加起来。importpandasaspdimportglobdefmerge_excel_vertical(file_pattern,output_path): 纵向合并多个Excel追加行 file_pattern: 如 C:/Reports/分公司*.xlsx filesglob.glob(file_pattern)print(f找到{len(files)}个Excel文件)all_dfs[]file_rows{}# 记录每个文件的行数forfilepathinfiles:try:dfpd.read_excel(filepath)file_rows[filepath]len(df)# 加一列来源文件方便追溯df[_来源文件]filepath.split(\\)[-1]all_dfs.append(df)print(f已读取{filepath}{len(df)}行)exceptExceptionase:print(f读取失败{filepath}错误{e})# 纵向合并resultpd.concat(all_dfs,ignore_indexTrue)# 保存result.to_excel(output_path,indexFalse)# 打印报告print(f\n 合并报告 )print(f输入文件数{len(files)})print(f合并前行数{sum(file_rows.values())})print(f合并后行数{len(result)})print(f结果保存到{output_path})returnresult模块八数据处理——去重汇总合并完后可能有重复行比如某个分公司的数据被导出了两次。importpandasaspddefdeduplicate_and_summarize(df,key_columns): 去重并汇总 key_columns: 用于去重的列如 [工号] 或 [工号, 月份] print(f去重前列数{len(df)})# 去重保留第一次出现df_dedupdf.drop_duplicates(subsetkey_columns,keepfirst)print(f去重后行数{len(df_dedup)})print(f删除了{len(df)-len(df_dedup)}条重复数据)# 汇总统计以数值列为例numeric_colsdf_dedup.select_dtypes(include[number]).columnsiflen(numeric_cols)0:print(\n 数值列汇总 )print(df_dedup[numeric_cols].sum())returndf_dedup模块九鼠标键盘与图像操作处理加密Excel合并时遇到加密Excel需要先解密。如果是打开密码可以用以下方法批量去除需要知道密码fromopenpyxlimportload_workbookdefremove_password_batch(file_list,password,output_dir): 批量去除Excel打开密码 注意这个方法只适用于知道密码的情况 不知道密码的情况需要用专业工具 importos os.makedirs(output_dir,exist_okTrue)forfilepathinfile_list:try:# openpyxl无法直接处理有打开密码的文件# 需要用msoffcrypto-toolimportmsoffcrypto decrypted_pathos.path.join(output_dir,os.path.basename(filepath))withopen(filepath,rb)asf:office_filemsoffcrypto.OfficeFile(f)ifoffice_file.is_encrypted():withopen(decrypted_path,wb)asout:office_file.decrypt(password,out)print(f已解密{filepath})else:# 没有加密直接复制importshutil shutil.copy(filepath,decrypted_path)exceptExceptionase:print(f解密失败{filepath}错误{e})安装解密工具pip install msoffcrypto-tool模块十进阶技能技能一合并时统一格式合并前先统一所有文件的格式避免合并后格式混乱fromopenpyxlimportload_workbookdefnormalize_excel_format(filepath,output_path): 统一Excel格式所有列设为文本格式 wbload_workbook(filepath)wswb.activeforcolinws.columns:forcellincol:# 把单元格格式设为文本cell.number_formatwb.save(output_path)print(f格式已统一{output_path})技能二处理合并单元格合并Excel时如果某个文件有合并单元格读取时会只有左上角有值其他单元格是空的。用前面文章里介绍的unmerge_cells函数处理。技能三大批量Excel合并内存优化如果Excel文件很多超过100个或者每个文件很大不要一次性读入内存。用逐文件处理方式importpandasaspddefmerge_large_excels(file_pattern,output_path,chunk_size5000): 大批量Excel合并逐块写入节省内存 importglob filesglob.glob(file_pattern)writerpd.ExcelWriter(output_path,engineopenpyxl)firstTrueforfilepathinfiles:dfpd.read_excel(filepath)iffirst:df.to_excel(writer,indexFalse,startrow0)firstFalseelse:# 追加写入不要表头df.to_excel(writer,indexFalse,startrowwriter.sheets[Sheet1].max_row,headerFalse)writer.close()print(f大批量合并完成{output_path})模块十一平台实战把Excel合并流程部署到影刀控制台时注意以下几点。要点一文件路径用配置管理创建一个merge_config.json{input_dir:C:/Reports/raw,output_dir:C:/Reports/merged,file_pattern:分公司*.xlsx,key_columns:[工号],notify_email:adminexample.com}流程启动时读取配置换环境时只改配置文件。TEMU店群如何管理运营要点二合并完成后自动通知defsend_merge_notification(result_path,row_count): 合并完成后发通知用影刀的发送钉钉消息或发送邮件指令 messagefExcel合并完成\n结果文件{result_path}\n总行数{row_count}print(f发送通知{message})要点三用控制台查看合并任务执行状态在影刀控制台的任务监控页面可以看到合并任务的执行时长和结果。如果某次合并特别慢可能是某个Excel文件格式有问题需要单独检查。模块十二系统联动与工程化规范工程化规范一合并前先备份importshutilimportosfromdatetimeimportdatetimedefbackup_before_merge(input_dir):backup_dirfC:/Reports/backup/{datetime.now().strftime(%Y%m%d)}os.makedirs(backup_dir,exist_okTrue)forfilenameinos.listdir(input_dir):iffilename.endswith(.xlsx):srcos.path.join(input_dir,filename)dstos.path.join(backup_dir,filename)shutil.copy(src,dst)print(f原始文件已备份到{backup_dir})工程化规范二合并报告自动生成defgenerate_merge_report(files,result_df,output_path): 生成合并报告HTML格式方便邮件发送 reportf html body h2Excel合并报告/h2 p合并时间{datetime.now()}/p p输入文件数{len(files)}/p p合并后行数{len(result_df)}/p p合并后列数{len(result_df.columns)}/p h3各文件行数/h3 ul forfinfiles:# ... 统计每个文件的行数reportf li{f}/li\nreport/ul/body/htmlwithopen(merge_report.html,w,encodingutf-8)asf:f.write(report)print(合并报告已生成merge_report.html)速查表Excel合并方式选择场景合并方式pandas函数多个相同结构的表纵向合并追加行pd.concat两个表按关键列关联横向合并类似VLOOKUPpd.merge按关键字聚合分组汇总df.groupby()去重删除重复行df.drop_duplicates()报错排查指南报错ValueError: Length of values does not match length of index原因横向合并时两个表的行数不对齐。解决检查关键列的值是否一一对应用howouter保留所有行。报错UnicodeDecodeError when reading Excel原因Excel文件损坏或者格式不对。解决用Excel手动打开文件另存为新的xlsx文件再用pandas读取。总结多Excel文件合并核心要点合并前先统一格式、用pandas的concat或merge、合并后去重并生成报告。把这三个步骤做到位合并出来的数据就不会再有格式问题。更多Excel合并的高级场景和代码访问 home.linyan.cloud 获取。#影刀RPA #RPA教程 #Excel合并 #数据处理 #Pandas作者林焱