简介针对Python开发者在数据处理与办公自动化中经常遇到的Excel操作需求此压缩包提供了基于pandas和openpyxl库的实用示例脚本。压缩包共包含4个Python源文件整体大小仅6KB覆盖Excel文件读取、条件筛选、单元格写入以及读写综合操作等典型场景各脚本分别侧重不同功能点便于按需查阅。目前已有701人学习下载适合初学Python数据处理的人员对照练习也可作为日常开发中快速取用的代码片段。通过研读这些代码读者可以掌握read_excel、to_excel函数以及openpyxl工作簿的遍历与写入流程理解pandas对表格数据的批量处理与openpyxl对单元格精细控制之间的互补关系进而在实际项目中灵活选择合适的工具。脚本注释简洁参数设置清晰稍作修改即可适配自己的数据文件有助于提高Excel相关任务的自动化效率。1. 需求背后的真实场景为什么要把Excel写入zip我最初看到python读取Excel并写入.zip这个标题时第一反应是这不就是两步操作吗用openpyxl读Excel再用zipfile写压缩包各拎出来都不复杂。但真正在项目里用过之后我才明白这个组合远比想象中常用也远比想象中容易出问题。先说说我遇到的实际场景。前两年我在给一家做供应链的企业做数据报表自动化他们的业务人员每个月要做几十张Excel表格涵盖采购、库存、销售、退货好几个维度做完之后要压缩成一个zip包发给分销商。原来这套流程是纯手工的打开Excel模板、粘贴数据、保存然后右键文件夹压缩再改压缩包名字。一个人折腾一整天是常态而且经常出现漏掉某张表、压缩包版本发错的情况。接手之后我写了一个Python脚本从数据库或数据接口拉数据自动写入指定的Excel模板生成几十个Excel文件后统一压缩打包自动命名成月度报表_202405.zip这样的格式。从此这个流程从一整天缩短到十几分钟。这件事给我的启发是读取Excel并写入zip不是一个孤立的脚本它往往是某个自动化链条里的关键一环。这个组合最常见的应用场景大概有这么几类多表归档把几十上百个Excel文件按规则批量压缩替代手工右键添加到压缩文件。按条件筛选打包读取一个总表按部门、日期、地区等维度拆分成多个Excel再打包成zip下发。Web系统导出用户在网页上勾选数据后端用Python把多个Excel或文件打包成zip供浏览器下载这是Django/FastAPI/Flask里非常经典的功能点。读取zip内的Excel做解析别人发过来的压缩包里有一堆Excel需要先解压再逐个读取分析。不管是哪种场景核心都逃不开三个问题怎么读Excel、怎么生成/处理文件、怎么组装成zip。下面我把每一步拆开来讲。2. 环境准备与工具选型openpyxl还是pandas开始写代码之前先要搞定环境。Python这块我建议直接用3.8以上版本太老的版本在依赖库兼容性上会给你找麻烦。如果还没装Python去官网下载安装包时记得在安装界面勾选Add Python to PATH不然命令行里敲python会提示找不到命令这是新手最容易踩的第一个坑。读Excel的库主流选择就两个openpyxl和pandas。我知道很多人一上来就无脑pandas但其实两者适合的场景并不一样选错了会让代码变得很别扭。对比维度openpyxlpandas依赖体积小安装快大连带numpy等一堆依赖读取方式按单元格/行列读取贴近Excel本身一步加载为DataFrame适合数据分析对格式的保留能保留样式、公式、合并单元格基本只能读数据不关注格式写入能力可在已有模板上填充控制单元格格式适合整表输出精细格式控制较弱大文件性能中等有只读模式较快内存占用看数据量适用场景模板填充、格式敏感的报表批量统计、清洗、转换我的经验是如果你要做的是读数据、做计算、重新拼表这类数据处理pandas效率更高如果你要做的是打开某个现成的Excel模板往指定位置填数据openpyxl是更合适的工具。本文的场景偏重后者多一点但我会两个都讲因为它们在实际项目中经常混合使用。安装很简单pip install openpyxl pandaszipfile是Python标准库不需要额外安装。这个模块的名字容易让人误以为它只能压缩文件夹实际上它是Python操作zip归档的通用入口读写、追加、查看文件列表都能干。3. 读取Excel的核心操作从单元格到DataFrame3.1 openpyxl的基本读取用openpyxl读取一个Excel文件最基础的三行代码import openpyxl wb openpyxl.load_workbook(采购明细.xlsx) ws wb.active # 获取当前活动的工作表 print(ws[A1].value) # 读取A1单元格如果工作表不止一个可以通过工作表名取ws wb[Sheet1]整表遍历数据时不要用ws.rows或ws.columns直接蒙着头遍历因为如果某个单元格只是设置了样式但没写入值它也会被遍历出来。更稳妥的做法是先用ws.max_row和ws.max_column确认数据范围再通过切片或者iter_rows()控制读取区域for row in ws.iter_rows(min_row2, max_rowws.max_row, values_onlyTrue): # 跳过了表头行直接拿到每一行的值 print(row)values_onlyTrue这个参数很关键它让每一行变成元组而不是单元格对象后续处理省很多事。3.2 pandas的读取与过滤pandas读Excel只要一行import pandas as pd df pd.read_excel(采购明细.xlsx, sheet_nameSheet1)整个文件就直接进DataFrame了。接下来想按条件筛选、分组统计、排序都是一两行的事。比如按供应商分组汇总金额summary df.groupby(供应商)[金额].sum().reset_index()pandas还有一个我很常用的参数dtype可以用来强制指定某些列的数据类型。Excel里经常出现订单号这列被读成数字导致前面的零丢失——单号00123变成123。这种情况用df pd.read_excel(订单.xlsx, dtype{订单号: str})数据就不会被误转。这个坑我在对接业务系统时踩过不止一次后来凡是读这种类似ID的列都默认加上dtype约束。3.3 读取zip内部的Excel还有一种常见情况客户发过来一个压缩包你要读取里面所有的Excel。这时候两步合成一步走就可以import zipfile import openpyxl import io with zipfile.ZipFile(报表包.zip) as zf: for name in zf.namelist(): if name.endswith(.xlsx): with zf.open(name) as f: wb openpyxl.load_workbook(io.BytesIO(f.read())) ws wb.active print(f{name}: A1{ws[A1].value})这里用到了io.BytesIO因为zipfile.open返回的是二进制文件流而openpyxl的load_workbook可以直接接收字节流对象不需要先解压到磁盘再读。这样做的好处是不会在临时目录里堆积一堆中间文件处理完一个就释放一个内存和磁盘占用都可控。4. 写入zip的几种姿势从先存文件再压缩到全程内存操作zipfile写入zip文件核心就是ZipFile.write()和ZipFile.writestr()两个方法。很多人只知道前者其实后者才是很多高级用法的关键。4.1 常规用法把已有文件写入zipimport zipfile with zipfile.ZipFile(导出包.zip, w, zipfile.ZIP_DEFLATED) as zf: zf.write(采购明细.xlsx, 报表/采购明细.xlsx) zf.write(库存明细.xlsx, 报表/库存明细.xlsx)重点说一下这个arcname参数第二个参数。它决定了文件在zip包内部的路径和名字不一定等于磁盘上的真实路径。比如你磁盘上文件存放在temp/output/采购明细.xlsx如果你不传arcname压缩包里也会带着这一长串目录结构传了之后可以仅保留报表/采购明细.xlsx的相对路径接收方解压出来就是一目了然的目录。这是一个很多教程不会专门提但实际非常影响体验的细节。4.2 内存写入不用先生成文件前文说的Web导出场景如果每个Excel都要先落盘再压缩低并发还好高并发时磁盘IO会拖垮性能。这种情况建议用writestr()配合BytesIO全程在内存中完成import zipfile import io import openpyxl def excel_to_bytes(rows, sheet_nameSheet1): wb openpyxl.Workbook() ws wb.active ws.title sheet_name for row in rows: ws.append(row) bio io.BytesIO() wb.save(bio) return bio.getvalue() # 假设这是从数据库或原Excel处理后得到的多张表数据 tables { 采购明细.xlsx: [(日期, 供应商, 金额), (2024-05-01, A公司, 1000)], 库存明细.xlsx: [(SKU, 仓库, 数量), (P001, 华东仓, 500)], } with zipfile.ZipFile(月度导出.zip, w, zipfile.ZIP_DEFLATED) as zf: for filename, rows in tables.items(): zf.writestr(filename, excel_to_bytes(rows))这段代码的关键是excel_to_bytes函数把一个DataFrame或行列表写入Workbook然后把Workbook保存到BytesIO缓冲区最终拿到字节串。字节串直接喂给writestr整个链路不落盘、不产生临时文件。批量处理几百张表时这个方案考虑到性能和磁盘寿命都比先落盘再压缩好得多。4.3 压缩级别选择ZipFile的压缩级别从0到9默认是-1表示使用默认级别一般是6。如果你的zip里装的是Excel文件而Excel本身已经是压缩过的格式xlsx本质上就是一个zip包你再怎么压体积也不太可能缩得很小。这时我建议用ZIP_STORED而不是ZIP_DEFLATED直接存储而不压缩速度更快文件体积差别几乎可以忽略。with zipfile.ZipFile(打包.zip, w, zipfile.ZIP_STORED) as zf: # 适合压缩已经压过的文件格式 pass反之如果你压缩的是大段的CSV或TXT文本ZIP_DEFLATED能省不少空间就该开启压缩。这个道理跟不要用zip去压一个已经压过的文件是一样的。5. 实战案例批量Excel自动归档并打包zip下面给一个能直接拿去改的完整例子。假设存在这样的业务一个总表销售总表.xlsx记录了全公司的销售数据列包括区域销售员产品金额。每天需要按区域拆分成独立Excel文件最后打包成一个zip方便各区域负责人下载自己那份。5.1 拆分Excel的两种实现用pandas实现最顺手import pandas as pd from pathlib import Path df pd.read_excel(销售总表.xlsx, dtype{订单号: str}) output_dir Path(拆分结果) output_dir.mkdir(exist_okTrue) for region, group in df.groupby(区域): filename output_dir / f{region}_销售明细.xlsx group.to_excel(filename, indexFalse) print(f已生成: {filename})这段代码用groupby按区域分组然后用to_excel把每组数据写到独立文件。如果不用pandas用openpyxl需要自己定位行列再逐格写入代码量会大不少所以纯拆分场景我优先推荐pandas。5.2 拆完立即打包然后把这些文件统一压缩import zipfile zip_name 销售拆分包.zip with zipfile.ZipFile(zip_name, w, zipfile.ZIP_DEFLATED) as zf: for excel_file in output_dir.glob(*.xlsx): zf.write(excel_file, arcnameexcel_file.name)这里arcname取excel_file.name保证压缩包内的文件不带拆分结果这个目录前缀对方解压后直接看到一堆Excel而不是套着一层目录。很多人忽略这个细节导致接收方解压后还要再点一层文件夹体验很不好。5.3 走一遍完整流程把上面串起来加一个日期后缀import pandas as pd import zipfile from pathlib import Path from datetime import datetime def split_and_zip(src_file, key_col, zip_nameNone): df pd.read_excel(src_file) output_dir Path(temp_split) output_dir.mkdir(exist_okTrue) for key, group in df.groupby(key_col): out_file output_dir / f{key}.xlsx group.to_excel(out_file, indexFalse) if zip_name is None: zip_name fsplit_{datetime.now():%Y%m%d_%H%M%S}.zip with zipfile.ZipFile(zip_name, w, zipfile.ZIP_DEFLATED) as zf: for excel_file in output_dir.glob(*.xlsx): zf.write(excel_file, arcnameexcel_file.name) return zip_name if __name__ __main__: print(split_and_zip(销售总表.xlsx, 区域))如果你不想在磁盘上保留中间拆分文件也可以把pandas DataFrame转Excel再进zip的步骤连起来用前面说的BytesIO方式代码会更长一些但全程不落盘def df_to_excel_bytes(df): bio io.BytesIO() with pd.ExcelWriter(bio, engineopenpyxl) as writer: df.to_excel(writer, indexFalse) return bio.getvalue() with zipfile.ZipFile(拆分包.zip, w, zipfile.ZIP_DEFLATED) as zf: for key, group in df.groupby(区域): zf.writestr(f{key}.xlsx, df_to_excel_bytes(group))注意pd.ExcelWriter在保存到BytesIO时必须用with语句确保writer关闭否则数据可能没真正写入缓冲区。这个坑我踩过一次当时生成的Excel打开全是空白排查了半天才发现是writer没flush。6. 实际项目中的几个深坑与处理办法代码框架部分讲完了这部分是真正值钱的经验全是实战中遇到的。有些问题不跑到生产环境根本碰不到。6.1 zipfile写入中文文件名乱码zipfile本身支持UTF-8编码的文件名但Windows自带的资源管理器解压时对UTF-8标志的处理比较老旧有时候会出现中文文件名乱码。解决这个问题一个老旧但有效的办法是给文件名加一个标识扩展字段import zipfile def write_zip_with_gbk(zip_path, files): with zipfile.ZipFile(zip_path, w, zipfile.ZIP_DEFLATED) as zf: for arcname, data in files.items(): info zipfile.ZipInfo(arcname) # 手动补充支持中文名的扩展字段 info.flag_bits | 0x800 zf.writestr(info, data)不过我要说实话这个方案在不同系统之间兼容性并不完美。在生产环境里我用得最多的反而是统一把中文文件名转成拼音或英文让zip包内文件名保持ASCII字符集这样在所有系统上都不会乱码。如果业务上必须要中文名最好在压缩后做个自检用zipfile重新打开文件列出文件名确认无误。6.2 大Excel的内存爆炸问题pandas读一个几百MB的Excel时会一次性把全部数据加载进内存多开几个文件机器直接卡死。遇到超大的Excel建议用openpyxl的只读模式wb openpyxl.load_workbook(big_file.xlsx, read_onlyTrue) ws wb.active for row in ws.iter_rows(values_onlyTrue): # 处理每一行边读边处理不用等全部加载完 passread_onlyTrue模式下openpyxl不会把整个工作表加载进内存而是按需迭代。配合zipfile的writestr边读边写可以做到用很小的内存处理很大的文件。不过要注意只读模式下不能修改单元格它就是纯读的。6.3 多个Excel合并为一个zip时保持原有格式如果业务要求把多个Excel按原样打包不能改动格式那么你完全不需要用openpyxl或pandas去读它。最稳的做法是原样写入zipwith zipfile.ZipFile(归档.zip, w, zipfile.ZIP_DEFLATED) as zf: for file_path in file_list: zf.write(file_path, arcnamePath(file_path).name)这样做是文件级别的复制不解析Excel内部结构格式、公式、图表、宏全部原样保留。很多新手容易犯的错误是明明只需要打包却偏要把Excel读一遍再写一遍结果原来的颜色、边框、公式全丢了。6.4 追加写入zip的误用zipfile支持追加模式with zipfile.ZipFile(existing.zip, a) as zf: zf.write(new_file.xlsx)但要注意两点一是追加模式不支持ZIP_DEFLATED之外的部分压缩设置改变二是如果同一个文件名已经存在于zip中追加时会写两个同名条目解压时以最后那个为准。这事看起来不严重但如果你在循环里反复往同一个zip追加文件压缩包体积会异常膨胀而且文件列表会越来越混乱。我的建议是宁可先构建完所有要打包的文件清单再用w模式一次写入也不要反复打开追加。6.5 路径遍历与安全校验如果zip文件是外部传进来的解压时要小心zip slip攻击。简单说恶意构造的zip压缩包里的文件名可能包含../../这种路径直接解压可以把文件写到压缩目录之外。用zipfile解压之前务必校验每个文件的安全路径import os with zipfile.ZipFile(external.zip) as zf: for info in zf.infolist(): target os.path.join(safe_dir, info.filename) # 确保解压后的路径没有跳出安全目录 if not os.path.abspath(target).startswith(os.path.abspath(safe_dir)): raise Exception(f非法路径: {info.filename}) zf.extract(info, safe_dir)这个校验在内部工具里可能用不上但只要是接收外部用户上传的zip就一定要加。安全无小事这行代码能挡掉大多数恶意构造的压缩包。7. 延伸从zip到自动化流水线如果只是读写Excel和zip能做的事情已经不少但真正让这套技术产生价值的是把它嵌进自动化流程。我举几个自己实际做过的方向供你参考。定时任务自动化。用系统的计划任务Windows或cronLinux/Mac每天定时运行脚本自动读取业务系统导出的Excel拆分归档成zip发送给指定人员或上传到共享目录。这样人工只需要在异常时介入平时完全不用管。结合Web框架做在线导出。用FastAPI或Flask写一个接口前端传参数进来后端根据参数从数据库或原始Excel中筛选数据生成多个结果文件并打包成zip通过HTTP响应直接返回给前端下载。这里用到的就是前面说的BytesIO内存打包方式整个请求处理过程不产生磁盘临时文件性能很稳定。结合邮件自动发送。把生成的zip作为邮件附件用smtplib或yagmail自动发送给指定收件人列表。由于zip把多个Excel合成单个文件避免了邮件系统对多个附件的各种限制。读取zip再处理。有些上游系统会定期推送zip包里面有多个Excel。写个脚本定期扫描目录解压后逐个读取、校验、汇总入数据库。这套逻辑我在对接外部供应商数据时用过多次zipfile加openpyxl的组合完全够用。我个人在多次实战中体会最深的一点是脚本的稳健性比花哨的技术重要得多。真实业务场景里Excel数据千奇百怪有空行、有合并单元格、有数字存成文本、有日期格式不统一。写读取逻辑时多做类型转换和异常捕获一旦某一行数据有问题别让整个脚本崩溃记录日志跳过这行继续处理最后输出一份处理报告这才是能长期跑的自动化脚本该有的样子。本文还有配套的精品资源点击获取