简介：这份资源面向需要处理表格数据的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 | 仅 .xls（2.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，它读文件头而不是后缀，能挡住改后缀的坑。参数上没什么可调的，但要注意：如果文件是加密的 xlsx，is_zipfile依然返回 True，后续 openpyxl 打开时会抛BadZipFile，这个要在外层捕获。
2.2 用 pandas 批量读取多 sheet 的最小写法
确认格式后，读取多 sheet 最省事的是 pandas 的sheet_name=None，它会返回一个{sheet名: DataFrame}的字典：
import pandas as pd def read_all_sheets(file_path: str) -> dict: """读取工作簿内所有 sheet，返回字典""" # sheet_name=None 表示读取全部 sheet # dtype=object 避免 pandas 自动把数字列转成 float 导致 ID 变 1.0 sheets = pd.read_excel( file_path, sheet_name=None, dtype=object, engine="openpyxl" # 显式指定引擎，避免 pandas 自动选择出错 ) return sheets # 使用示例 result = read_all_sheets("report.xlsx") for name, df in result.items(): print(f"sheet={name}, shape={df.shape}, columns={list(df.columns)}")dtype=object这个参数是血泪经验：订单号、身份证号这类长数字列，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_name=sheet_name, header=header_row, # 0 表示第一行是表头，2 表示第三行是表头 dtype=object, engine="openpyxl" ) # 去掉全空的行和列，业务表常见 df = df.dropna(how="all").dropna(axis=1, how="all") return dfheader参数接受整数或整数列表，传列表时会构造 MultiIndex 多层表头。dropna(how="all")清掉整行全空的情况，axis=1, how="all"清掉整列全空——这两个操作在真实业务表里几乎必做，因为合并单元格展开后会产生大量 NaN。如果表头行数不固定，可以先读header=None把原始网格拿到手，再自己扫描哪一行非空值最多，把它当表头。
3. 写入 zip 的完整链路：从 DataFrame 到压缩包
3.1 为什么不能先写文件再压缩
最直觉的做法是：循环把每个 DataFrame 用to_excel写到磁盘，最后用zipfile打包，再删掉临时文件。这个方案在小数据量下没问题，但有两个隐患：一是临时文件如果中途异常退出会残留，二是磁盘 IO 翻倍。更干净的做法是用io.BytesIO在内存里完成 Excel 写入，直接把字节流塞进 zip：
import 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, engine="openpyxl") as writer: df.to_excel(writer, sheet_name=sheet_name, index=False) # 注意：getvalue 要在 writer 关闭之后调用 return buffer.getvalue()这里的关键是ExcelWriter的上下文管理器，退出时会自动 flush 并关闭，之后才能安全地getvalue()。如果提前调用会拿到不完整的字节。index=False是必须的，否则 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-9，0 不压缩，9 最高压缩，默认 6 平衡速度与体积 """ with zipfile.ZipFile(zip_path, "w", zipfile.ZIP_DEFLATED, compresslevel=compress_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_only=True模式流式读取，或者用 pandas 的chunksize参数分块读。如果只是要转格式，用 openpyxl 的只读模式：
from openpyxl import load_workbook # read_only=True 不把整个工作簿加载到内存 wb = load_workbook("big.xlsx", read_only=True, data_only=True) for ws in wb.worksheets: for row in ws.iter_rows(values_only=True): # 逐行处理，内存占用恒定 process_row(row) wb.close() # 必须显式关闭，否则文件句柄泄漏data_only=True表示读取公式的计算结果而不是公式本身，如果单元格是公式且从未被 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 而不是 bytes，Python 会自动处理编码。读取别人的 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_name=sheet_name, index=False) else: df.to_excel(writer, sheet_name=sheet_name, index=False)更常见的做法是分组后过滤掉空组：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_only=True模式，逐行追加，内存占用恒定：
from openpyxl import Workbook import io def write_large_excel_streaming(rows_iter, headers: list) -> bytes: """流式写入大 Excel，rows_iter 是行迭代器""" buffer = io.BytesIO() wb = Workbook(write_only=True) 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_ascii=False, indent=2)把 manifest 也通过zf.writestr("manifest.json", build_manifest(df_dict))写进去，下游解压后第一件事就是读它。这个做法不复杂，但能省掉很多来回沟通。希望帮到你。
本文还有配套的精品资源，点击获取
