简介这份资源面向需要处理表格数据的Python学习者与开发者聚焦Excel文件的读取与写入这一常见任务帮助解决数据分析、数据清洗和自动化报告中的表格操作问题。压缩包共4个文件全部为py脚本整体约6KB涵盖读取Excel、读取数字型数据、读取JS相关数据以及读写Excel等不同侧重的示例代码便于按需查阅与复用。资源围绕pandas与openpyxl两大常用库展开既演示了read_excel、to_excel等基础读写方法也涉及条件筛选、单元格遍历、工作簿创建与保存等操作可帮助读者理解DataFrame与工作表两种处理思路的差异。目前已有702人学习适合希望快速上手Excel自动化处理、积累实用脚本片段的初中级Python使用者参考。1. 别再用 pandas 硬扛了python读取Excel并写入.zip 的真实落地场景很多人第一次看到「python读取Excel并写入.zip」这个需求脑子里蹦出来的第一反应是pandas.read_excel读进来to_excel写出去完事。真到生产环境里跑一圈就会发现事情没这么简单——Excel 文件可能来自业务方手动上传一个工作簿里塞了七八个 sheet每个 sheet 的表头还不在第一行写出去的时候又要求按业务维度拆成多个文件最后统一打包成一个 zip 交给下游系统。这时候如果还是一条to_excel走天下轻则内存爆掉重则文件句柄泄漏跑批任务半夜挂掉。这个标题拆开看核心链路其实有三段读取 Excel可能多 sheet、多文件→ 处理/拆分数据 → 写入 zip 压缩包。它解决的是「批量 Excel 数据归档与分发」这一类问题典型场景包括财务月结把几十张报表打包、运营把分城市数据拆成独立 Excel 再压缩、数据平台把跑批结果以 zip 形式推给下游。适合的读者是已经会用 pandas 做基础读写、但还没处理过「多文件 压缩包」组合场景的 Python 工程师。下面按读取、写入、避坑、进阶四段推进每段都给能直接抄的代码。2. 读取 Excel 的三种姿势openpyxl、pandas、xlrd 怎么选2.1 先搞清楚你的 Excel 是什么格式选库之前必须先确认文件后缀和实际格式这是最容易翻车的地方。.xlsx和.xls是两种完全不同的二进制结构前者是 OOXML本质是 zip 包后者是老的 BIFF 格式。很多业务方会把.xls文件手动改后缀成.xlsx再发过来这时候用 openpyxl 打开会直接抛InvalidFileException。库支持格式读取速度写入能力适用场景openpyxl.xlsx/.xlsm中等强支持样式需要保留格式、公式pandas依赖底层引擎快依赖引擎数据分析、批量处理xlrd仅 .xls2.0 不再支持 xlsx快无老格式兼容我一般会先写一个格式探测函数用文件头的 magic number 判断而不是信后缀import zipfile from pathlib import Path def detect_excel_format(file_path: str) - str: 通过文件头判断真实格式不信任后缀名 path Path(file_path) # .xlsx 本质是 zip文件头是 PK\x03\x04 if zipfile.is_zipfile(path): return xlsx # .xls 是老 BIFF 格式文件头是 D0 CF 11 E0 with open(path, rb) as f: header f.read(8) if header[:4] b\xd0\xcf\x11\xe0: return xls raise ValueError(f无法识别的 Excel 格式: {file_path})这段代码的关键点是zipfile.is_zipfile它读文件头而不是后缀能挡住改后缀的坑。参数上没什么可调的但要注意如果文件是加密的 xlsxis_zipfile依然返回 True后续 openpyxl 打开时会抛BadZipFile这个要在外层捕获。2.2 用 pandas 批量读取多 sheet 的最小写法确认格式后读取多 sheet 最省事的是 pandas 的sheet_nameNone它会返回一个{sheet名: DataFrame}的字典import pandas as pd def read_all_sheets(file_path: str) - dict: 读取工作簿内所有 sheet返回字典 # sheet_nameNone 表示读取全部 sheet # dtypeobject 避免 pandas 自动把数字列转成 float 导致 ID 变 1.0 sheets pd.read_excel( file_path, sheet_nameNone, dtypeobject, engineopenpyxl # 显式指定引擎避免 pandas 自动选择出错 ) return sheets # 使用示例 result read_all_sheets(report.xlsx) for name, df in result.items(): print(fsheet{name}, shape{df.shape}, columns{list(df.columns)})dtypeobject这个参数是血泪经验订单号、身份证号这类长数字列pandas 默认会推断成 int64 或 float64超过 15 位就丢精度导出后变成科学计数法。设成 object 后全部按字符串处理虽然牺牲一点内存但数据不会错。engine参数在 pandas 1.3 可以省略但显式写出来能避免环境里装了多个引擎时行为不一致。2.3 表头不在第一行时怎么定位业务方给的 Excel 经常前几行是标题、说明、合并单元格真正的表头在第 3 行甚至第 5 行。这时候header参数就派上用场def read_with_offset(file_path: str, sheet_name: str, header_row: int) - pd.DataFrame: 从指定行读取表头header_row 从 0 开始计数 df pd.read_excel( file_path, sheet_namesheet_name, headerheader_row, # 0 表示第一行是表头2 表示第三行是表头 dtypeobject, engineopenpyxl ) # 去掉全空的行和列业务表常见 df df.dropna(howall).dropna(axis1, howall) return dfheader参数接受整数或整数列表传列表时会构造 MultiIndex 多层表头。dropna(howall)清掉整行全空的情况axis1, howall清掉整列全空——这两个操作在真实业务表里几乎必做因为合并单元格展开后会产生大量 NaN。如果表头行数不固定可以先读headerNone把原始网格拿到手再自己扫描哪一行非空值最多把它当表头。3. 写入 zip 的完整链路从 DataFrame 到压缩包3.1 为什么不能先写文件再压缩最直觉的做法是循环把每个 DataFrame 用to_excel写到磁盘最后用zipfile打包再删掉临时文件。这个方案在小数据量下没问题但有两个隐患一是临时文件如果中途异常退出会残留二是磁盘 IO 翻倍。更干净的做法是用io.BytesIO在内存里完成 Excel 写入直接把字节流塞进 zipimport io import zipfile import pandas as pd def df_to_excel_bytes(df: pd.DataFrame, sheet_name: str Sheet1) - bytes: 把 DataFrame 转成 xlsx 字节流不落盘 buffer io.BytesIO() with pd.ExcelWriter(buffer, engineopenpyxl) as writer: df.to_excel(writer, sheet_namesheet_name, indexFalse) # 注意getvalue 要在 writer 关闭之后调用 return buffer.getvalue()这里的关键是ExcelWriter的上下文管理器退出时会自动 flush 并关闭之后才能安全地getvalue()。如果提前调用会拿到不完整的字节。indexFalse是必须的否则 pandas 会把行号写进第一列下游打开会多一列莫名其妙的数字。3.2 把多个 Excel 写进一个 zip 的完整函数有了字节流转换函数打包逻辑就很直接了import zipfile from pathlib import Path def export_dfs_to_zip(df_dict: dict, zip_path: str, compress_level: int 6): 把 {文件名: DataFrame} 字典写成多个 Excel 并打包成 zip compress_level: 0-90 不压缩9 最高压缩默认 6 平衡速度与体积 with zipfile.ZipFile(zip_path, w, zipfile.ZIP_DEFLATED, compresslevelcompress_level) as zf: for file_name, df in df_dict.items(): # 确保文件名以 .xlsx 结尾 if not file_name.endswith(.xlsx): file_name f{file_name}.xlsx excel_bytes df_to_excel_bytes(df) # writestr 直接写入字节不需要临时文件 zf.writestr(file_name, excel_bytes) return Path(zip_path).stat().st_sizeZIP_DEFLATED是压缩算法compresslevel控制压缩级别。实测下来Excel 本身已经是 zip 压缩过的再压缩收益有限级别 6 和 9 的体积差异通常在 3% 以内但级别 9 的 CPU 耗时可能翻倍。如果下游对体积不敏感用级别 1 甚至 0 换速度更划算。writestr的第二个参数接受 bytes 或 str传 bytes 时按二进制写入不会做编码转换这是处理 Excel 的正确方式。3.3 按业务维度拆分文件的命名策略实际业务里往往不是「一个 DataFrame 一个文件」而是按某个字段分组后每组一个文件。比如按城市拆分销售数据def split_and_export(df: pd.DataFrame, group_col: str, zip_path: str): 按指定列分组每组一个 Excel打包成 zip df_dict {} for group_value, sub_df in df.groupby(group_col): # 文件名里不能有 / \ : * ? | 这些字符 safe_name str(group_value).replace(/, _).replace(\\, _) df_dict[f{safe_name}.xlsx] sub_df return export_dfs_to_zip(df_dict, zip_path)文件名清洗这一步不能省。Windows 和 Linux 对文件名的限制不同但 zip 规范里/是路径分隔符如果分组值里带斜杠解压时会变成子目录甚至路径穿越。我一般会把/\:*?|全部替换成下划线再截断到 100 字符以内避免超长文件名在某些系统上解压失败。4. 避坑指南这五个问题我几乎每次都遇到4.1 坑一内存溢出——现象是进程被 OOM Killer 杀掉现象读取一个 50MB 的 xlsx 时Python 进程内存涨到 2GB 以上容器直接 OOM。原因openpyxl 会把整个工作簿解析成对象树内存占用是文件体积的 20-50 倍。xlsx 是 zip 压缩的 XML解压后体积膨胀很大。解决改用read_onlyTrue模式流式读取或者用 pandas 的chunksize参数分块读。如果只是要转格式用 openpyxl 的只读模式from openpyxl import load_workbook # read_onlyTrue 不把整个工作簿加载到内存 wb load_workbook(big.xlsx, read_onlyTrue, data_onlyTrue) for ws in wb.worksheets: for row in ws.iter_rows(values_onlyTrue): # 逐行处理内存占用恒定 process_row(row) wb.close() # 必须显式关闭否则文件句柄泄漏data_onlyTrue表示读取公式的计算结果而不是公式本身如果单元格是公式且从未被 Excel 打开计算过会返回 None这个要提前和业务方确认。4.2 坑二日期变成数字——现象是导出后日期列显示 45000 这种整数现象读取时日期正常写入 zip 后再打开日期列变成了 45000 这样的数字。原因Excel 内部用序列号存日期1900-01-01 是 1。pandas 读进来是 datetime但某些引擎写入时没设置日期格式Excel 就按数字显示。解决写入时显式指定日期格式或者干脆在写入前把 datetime 列转成字符串def normalize_datetime_columns(df: pd.DataFrame) - pd.DataFrame: 把 datetime 列转成固定格式字符串避免 Excel 显示成数字 for col in df.columns: if pd.api.types.is_datetime64_any_dtype(df[col]): df[col] df[col].dt.strftime(%Y-%m-%d %H:%M:%S) return df转字符串的代价是丢失日期类型下游如果还要做日期计算就得再解析。如果下游是人工看转字符串最省心如果下游是程序读保留 datetime 并设置number_format更好。4.3 坑三中文乱码——现象是解压后文件名或内容变问号现象zip 解压后中文文件名变成乱码或者 Excel 内容里的中文变成问号。原因zip 规范早期用 CP437 编码存文件名中文环境下需要 UTF-8 标志位。Python 的zipfile默认会设置 UTF-8 标志但如果用其他工具打包再让 Python 读就可能踩坑。解决写入时确保文件名是 str 而不是 bytesPython 会自动处理编码。读取别人的 zip 时如果文件名乱码用cp437解码再gbk编码还原import zipfile with zipfile.ZipFile(archive.zip) as zf: for info in zf.infolist(): # 尝试修复中文文件名乱码 try: correct_name info.filename.encode(cp437).decode(gbk) except (UnicodeEncodeError, UnicodeDecodeError): correct_name info.filename print(correct_name)这个修复逻辑不是万能的只对「用 GBK 编码打包但没设 UTF-8 标志」的情况有效。最稳妥的做法是打包和解包都用同一套工具链。4.4 坑四文件句柄泄漏——现象是跑批几百个文件后报 Too many open files现象循环处理几百个 Excel 后程序抛OSError: [Errno 24] Too many open files。原因pd.ExcelWriter或load_workbook没有正确关闭文件句柄累积。解决所有打开操作都用with语句或者显式调用close()。pandas 的read_excel内部会自己关闭但ExcelWriter必须手动管理。用with包住是最省心的# 错误写法writer 不会自动关闭 writer pd.ExcelWriter(out.xlsx) df.to_excel(writer) # 忘了 writer.close() # 正确写法 with pd.ExcelWriter(out.xlsx) as writer: df.to_excel(writer) # 退出 with 块自动关闭如果确实需要在循环里创建大量 writer建议每处理 100 个就gc.collect()一次强制回收。4.5 坑五空 DataFrame 写入报错——现象是 to_excel 抛 ValueError现象某个分组结果为空 DataFrame写入时抛ValueError: No columns to write。原因空 DataFrame 没有列信息openpyxl 不知道怎么建表头。解决写入前判断空的话要么跳过要么写入一个带表头的空表def safe_to_excel(df: pd.DataFrame, writer, sheet_name: str): 空 DataFrame 也能安全写入 if df.empty and len(df.columns) 0: # 完全空的 df写一个占位表头 pd.DataFrame({提示: [无数据]}).to_excel(writer, sheet_namesheet_name, indexFalse) else: df.to_excel(writer, sheet_namesheet_name, indexFalse)更常见的做法是分组后过滤掉空组df_dict {k: v for k, v in df_dict.items() if not v.empty}从源头避免。5. 进阶技巧流式写入大文件与压缩包校验5.1 用 openpyxl 的 write_only 模式写超大 Excel当单个 Excel 超过 10 万行时pandas 的to_excel会把整个 DataFrame 加载到内存再写容易爆。openpyxl 提供了write_onlyTrue模式逐行追加内存占用恒定from openpyxl import Workbook import io def write_large_excel_streaming(rows_iter, headers: list) - bytes: 流式写入大 Excelrows_iter 是行迭代器 buffer io.BytesIO() wb Workbook(write_onlyTrue) ws wb.create_sheet(Data) ws.append(headers) # 先写表头 for row in rows_iter: ws.append(row) # 逐行追加不占内存 wb.save(buffer) return buffer.getvalue()write_only模式下不能读取单元格、不能合并单元格、不能设置复杂样式只能顺序追加。这是用功能换内存的典型取舍。如果既要样式又要大文件只能分片写多个 sheet 或者多个文件。5.2 写入后校验 zip 完整性打包完成后最好做一次完整性校验确认 zip 没损坏、文件数对得上import zipfile def verify_zip(zip_path: str, expected_count: int) - bool: 校验 zip 完整性和文件数量 try: with zipfile.ZipFile(zip_path) as zf: # testzip 返回第一个损坏文件的文件名None 表示全部正常 bad_file zf.testzip() if bad_file is not None: print(f损坏文件: {bad_file}) return False actual_count len(zf.namelist()) if actual_count ! expected_count: print(f文件数不符: 期望 {expected_count}, 实际 {actual_count}) return False return True except zipfile.BadZipFile: print(zip 文件本身损坏) return Falsetestzip()会逐个解压校验 CRC对大 zip 来说耗时不小如果只是例行检查可以只校验namelist()的数量和文件名。真正需要 CRC 校验的场景是网络传输后防止字节损坏。5.3 一个我常用的习惯跑批任务里我习惯在 zip 里额外放一个manifest.json记录每个 Excel 的行数、列名、生成时间。下游拿到 zip 后先读 manifest确认数据符合预期再处理省得解压一堆文件才发现某个表是空的。这个习惯帮我挡过好几次「上游数据源变更导致某列消失」的事故。manifest 的生成很简单import json def build_manifest(df_dict: dict) - str: 生成数据清单随 zip 一起打包 manifest { name: {rows: len(df), columns: list(df.columns)} for name, df in df_dict.items() } return json.dumps(manifest, ensure_asciiFalse, indent2)把 manifest 也通过zf.writestr(manifest.json, build_manifest(df_dict))写进去下游解压后第一件事就是读它。这个做法不复杂但能省掉很多来回沟通。希望帮到你。本文还有配套的精品资源点击获取