用python解放右手系列(三) Excel自动化-告别复制粘贴的噩梦

Excel 自动化告别复制粘贴的噩梦本文基于 Python 3.9涉及库pandas、openpyxl。阅读时间约 12 分钟。安装依赖pip install pandas openpyxl每月 1 号的酷刑阿明刚用 Python 搞定文件重命名还没高兴两天新的折磨来了。每月 1 号他要从 5 个部门收上来的 Excel 表里汇总数据。这活儿他干了快一年每次都想辞职。这次5 个部门的文件准时出现在邮箱里销售部_1月报表.xlsx 市场部_1月报表.xlsx 技术部_1月报表.xlsx 客服部_1月报表.xlsx 财务部_1月报表.xlsx阿明打开第一个——销售部的。表头长这样日期销售额订单数客户数2024-01-0115000032045“还行挺规范。”打开第二个——市场部的时间推广费用点击量转化率2024/1/150000120003.2%“……列名不一样就算了日期格式还不同”打开第三个——技术部的日期BUG数修复数未修复2024年1月1日23203阿明眼前一黑。日期格式有三种2024-01-01、2024/1/1、2024年1月1日。列名完全不统一有的叫日期有的叫时间。更离谱的是财务部的——合并单元格、空行、备注列里还插着文字说明。阿明以前的做法是打开 5 个文件逐个复制粘贴到一个新 Excel 里手动改格式、调列宽、加汇总公式。一上午过去眼睛花了公式还总报错#REF!。这次……阿明握紧拳头“我要用 Python”他走到老张工位“张哥Excel 能用 Python 操作吗”老张从显示器后面探出头笑了“你知道 Python 读 Excel 比 Excel 自己还快吗”第一步认识pandas——Excel 的电锯“阿明你手动处理 Excel就像用手工锯木头——能锯但累、慢、容易歪。”“pandas就是电锯——不是不会用手工锯是有更好的工具为啥不用”老张打开 VS Code先装库pipinstallpandas openpyxl“pandas是数据处理神器openpyxl是读写.xlsx的引擎。”最简示例读取一个 Excelimportpandasaspd# 读取 Excel 文件dfpd.read_excel(销售部_1月报表.xlsx)# 看看前 5 行print(df.head())# 看看数据形状多少行、多少列print(f\n形状:{df.shape})# (行数, 列数)# 看看列名和数据类型print(f\n列名:{df.columns.tolist()})print(f\n数据类型:\n{df.dtypes})运行结果日期 销售额 订单数 客户数 0 2024-01-01 150000 320 45 1 2024-01-02 180000 380 52 2 2024-01-03 165000 350 48 ... 形状: (31, 4) 列名: [日期, 销售额, 订单数, 客户数] 数据类型: 日期 datetime64[ns] 销售额 int64 订单数 int64 客户数 int64 dtype: object阿明瞪眼“就这么一行代码整个表读进来了”“对。pandas把 Excel 读成一个叫DataFrame的对象——你可以把它想象成超级 Excel 表格能筛选、能计算、能合并而且比 Excel 快得多。”第二步读取多个文件一键合并“现在你有 5 个部门的文件咱们把它们合并到一个表里。”importpandasaspdfrompathlibimportPath# 所有报表文件files[销售部_1月报表.xlsx,市场部_1月报表.xlsx,技术部_1月报表.xlsx,客服部_1月报表.xlsx,财务部_1月报表.xlsx,]# 读取所有文件存到一个列表里dataframes[]forfileinfiles:dfpd.read_excel(file)df[来源]Path(file).stem# 加一列标记来自哪个部门dataframes.append(df)print(f✅ 读取{file}:{df.shape[0]}行)# 合并成一个大数据框mergedpd.concat(dataframes,ignore_indexTrue)print(f\n 合并完成总共{merged.shape[0]}行 ×{merged.shape[1]}列)print(merged.head(10))运行结果✅ 读取 销售部_1月报表.xlsx: 31 行 ✅ 读取 市场部_1月报表.xlsx: 31 行 ✅ 读取 技术部_1月报表.xlsx: 31 行 ✅ 读取 客服部_1月报表.xlsx: 31 行 ✅ 读取 财务部_1月报表.xlsx: 31 行 合并完成总共 155 行 × 6 列阿明倒吸一口凉气“5 个文件就这么……合并完了”“pd.concat就是’把几个表粘在一起’。ignore_indexTrue表示重新排索引不然 5 个表各有自己的 0,1,2,3……会乱。”“df[来源] Path(file).stem这行是干啥的”“给每行数据打标签标记来自哪个部门。这样合并后你还能知道这行数据原来是哪个部的。”第三步数据清洗——让混乱变整齐“合并是合并了但你看这数据能直接用吗”老张指着屏幕——合并后的表日期格式乱七八糟有的列名不统一还有空值。“来咱们做数据清洗——这是数据处理最花时间的环节但代码做比人做快 100 倍。”3.1 统一日期格式# 看看日期列的现状print(merged[日期].head(10))输出0 2024-01-01 1 2024-01-02 2 2024-01-03 3 2024/1/1 4 2024/1/2 5 2024年1月1日 ...“三种格式手动改得改到明年。”“但pandas自带日期解析自动识别各种格式”# 把日期列转成标准格式merged[日期]pd.to_datetime(merged[日期],errorscoerce)# 看看结果print(merged[日期].head(10))输出0 2024-01-01 1 2024-01-02 2 2024-01-03 3 2024-01-01 4 2024-01-02 5 2024-01-01 ...“pd.to_datetime就像个翻译官——你不管说’2024-01-01’还是’2024年1月1日’它都翻译成统一格式。”“errorscoerce表示如果某个值实在转不了比如’待定’就转成空值NaT不会报错中断。”3.2 处理缺失值# 看看有多少空值print(merged.isnull().sum())输出日期 2 销售额 5 订单数 31 ...“有些部门没有’订单数’这一列合并过来就是空的。”“怎么处理看场景”# 方案 1空值填 0适合数值列merged[订单数]merged[订单数].fillna(0)# 方案 2空值填上一行的值适合连续数据merged[销售额]merged[销售额].fillna(methodffill)# 方案 3直接删除有空值的行mergedmerged.dropna()“fillna就是’填空’dropna就是’把有空的行删掉’。”3.3 去重# 检查有没有完全重复的行print(f重复行数:{merged.duplicated().sum()})# 有就删掉mergedmerged.drop_duplicates()第四步数据计算——自动汇总“数据干净了现在做汇总。”“老板是不是要看’各部门销售额总和’、平均值’之类的”阿明点头“对以前我都是手动写 SUM 公式。”“在pandas里一句话搞定”# 按部门分组计算销售额的总和和平均值summarymerged.groupby(来源)[销售额].agg([sum,mean,count])# 重命名列更直观summary.columns[总销售额,平均销售额,记录数]print(summary)运行结果总销售额 平均销售额 记录数 来源 客服部_1月报表 890000 28709.68 31 市场部_1月报表 1200000 38709.68 31 技术部_1月报表 0 0.00 31 ...“groupby(来源)就是’按来源列分组’agg就是’聚合计算’。”“以前你在 Excel 里写 SUMIF、AVERAGEIF现在一句话搞定。”阿明已经说不出话了只是疯狂点头。第五步openpyxl——让输出文件像人做的“数据算完了但直接输出 Excel 太丑了——列宽不对、没边框、表头没颜色。”“pandas负责数据处理openpyxl负责打扮输出文件。”fromopenpyxlimportWorkbookfromopenpyxl.stylesimportFont,PatternFill,Alignment,Border,Sidefromopenpyxl.utils.dataframeimportdataframe_to_rows# 创建新工作簿wbWorkbook()wswb.active ws.title汇总数据# 把 pandas 数据写入工作表forr_idx,rowinenumerate(dataframe_to_rows(merged,indexFalse,headerTrue),1):forc_idx,valueinenumerate(row,1):ws.cell(rowr_idx,columnc_idx,valuevalue)# 美化格式 # 表头样式加粗、蓝底白字header_fontFont(boldTrue,colorFFFFFF)header_fillPatternFill(start_color366092,end_color366092,fill_typesolid)header_alignAlignment(horizontalcenter,verticalcenter)# 边框样式thin_borderBorder(leftSide(stylethin),rightSide(stylethin),topSide(stylethin),bottomSide(stylethin))# 应用表头样式forcellinws[1]:cell.fontheader_font cell.fillheader_fill cell.alignmentheader_align cell.borderthin_border# 自动调整列宽forcolumninws.columns:max_length0column_lettercolumn[0].column_letterforcellincolumn:try:ifcell.value:max_lengthmax(max_length,len(str(cell.value)))except:passadjusted_widthmin(max_length2,50)# 最多 50太宽不好看ws.column_dimensions[column_letter].widthadjusted_width# 所有单元格加边框、居中forrowinws.iter_rows(min_row2):forcellinrow:cell.borderthin_border cell.alignmentAlignment(horizontalcenter,verticalcenter)# 保存wb.save(1月汇总报表_已美化.xlsx)print(✅ 美化后的 Excel 已保存)“这段代码干了啥”“1. 创建新 Excel 文件2. 把pandas处理好的数据写进去3.表头加粗、蓝底白字4.自动调整列宽5.所有单元格加边框、居中6. 保存”“以前你手动调格式要半小时代码 20 行跑完 1 秒。”踩坑提醒Excel 操作的几个大坑老张表情严肃起来“Excel 自动化有几个坑我踩过你别踩。”坑 1合并单元格“pandas读合并单元格时只有第一行有值其他行是空值。”“如果源文件有合并单元格要么让同事别合并推荐要么读完之后用fillna(methodffill)填充。”坑 2日期被读成数字“Excel 内部存日期是数字比如 45292 代表 2024-01-01。pandas通常能自动识别但如果识别失败”# 强制指定日期列dfpd.read_excel(file.xlsx,parse_dates[日期列])坑 3大文件内存爆炸“如果 Excel 有几十万行pandas一次性读进来可能内存不够。”“解决办法分块读取。”# 每次读 10000 行chunk_size10000forchunkinpd.read_excel(大文件.xlsx,chunksizechunk_size):# 处理这一块print(f处理{len(chunk)}行...)坑 4公式不计算“openpyxl写入公式后打开 Excel 时可能不自动计算显示#VALUE!。”“解决办法用data_onlyTrue读取时只读值或者写入时直接写计算结果而不是公式。”一句话总结阿明看着桌面上整整齐齐的1月汇总报表_已美化.xlsx心情复杂。以前这活儿要一上午现在10 分钟——其中 9 分钟是在等老张讲原理代码跑完不到 1 秒。老张拍拍他“手动处理 Excel 就像用手工锯木头pandas就是电锯——不是不会用锯子是有更好的工具为啥不用记住数据清洗占 80% 的时间但代码做比人做快 100 倍。”阿明点头突然想到什么“张哥这报表每个月都要做能不能让它自动发给老板”老张笑了“下篇就教你。”扩展思考老张临走前又补充今天学的是’合并多个 Excel’。实际工作中你可能还会遇到按条件拆分一个大表按部门拆成多个 Sheet 或多个文件数据透视像 Excel 的透视表用pandas.pivot_table()一句话搞定图表嵌入把matplotlib生成的图插到 Excel 里“这些变体核心逻辑都一样读取 → 清洗 → 计算 → 格式化输出。”下集预告下一篇阿明要写周报邮件——每周五下午固定格式、固定收件人、固定内容模板。老张会教他yagmail让 Python 当你的私人秘书定时自动发邮件。记住pandas负责数据处理openpyxl负责打扮输出。数据清洗是重头戏但代码做比人做快 100 倍。你每个月要处理多少个 Excel 文件最让你崩溃的 Excel 操作是什么欢迎在评论区吐槽。