查看: 55|回复: 3

openpyxl与pandas处理Excel:四大库选型与避坑

[复制链接]
发表于 22 分钟前 | 显示全部楼层 |阅读模式
为什么有必要先确定用库

刚开始用 Python 处理 Excel 时,最容易造成困惑的通常不是语法,而是 openpyxl、pandas、xlwings、XlsxWriter 这四个库名。它们都能“写 Excel”,但解决的问题处在不同层面:有的负责样式,有的负责数据计算,有的负责写入性能和图表,有的负责驱动 Excel 本体。选错库,代码量会多出好几倍;选对库,很多需求一行就能完成。下面按“文件格式—库定位—代码对比—决策路径—常见坑”的顺序梳理一遍。

一、先分清文件格式:.xls 与 .xlsx 不是一回事

这是新手最先遇到的障碍:


  • .xls:Excel 2003 及更早的二进制老格式,现代库基本不支持写入,读取要依赖 xlrd 一类旧库或 pyexcel-xls;
  • .xlsx:Excel 2007 之后的新格式,本质是 zip 包着的 XML,openpyxl / pandas / XlsxWriter 都支持;
  • .xlsm:带宏的 xlsx,openpyxl 可以读取,但需要传 keep_vba=True 才能保留宏;
  • .csv:纯文本表格,本身并不是 Excel 格式,pandas 原生支持,处理最简单。


实操建议:如果手上是 .xls,先用 Excel 另存为 .xlsx,再交给脚本处理——这是一次性的手动操作,不必写代码去兼容。想验证 .xlsx 的本质,可以把任意 .xlsx 文件的后缀改成 .zip 再解压,里面是一堆 XML 文件。所谓“生成 xlsx”,程序实际做的事情就是拼装 XML。

二、四个库各自负责什么


  • openpyxl:单元格级操作。擅长样式、公式、条件格式、数据验证、图表、合并单元格;不擅长大批量数据运算。装包名 openpyxl。
  • pandas:数据处理与分析。擅长读写、清洗、筛选、聚合、透视、多表拼接;不擅长复杂样式和精细格式。装包名 pandas。
  • XlsxWriter:高性能生成报表。擅长从零写大文件,图表支持最全、写入速度最好;只能写不能读,无法修改已有文件。装包名 XlsxWriter。
  • xlwings:驱动 Excel 程序本体。擅长调用 Excel 内置功能与 VBA、做交互式操作;必须本机装 Excel,启动进程慢,不适合批量处理。装包名 xlwings。


一句话记住:openpyxl 管“长什么样”,pandas 管“数据是什么”,XlsxWriter 管“写得快不快、图表好不好看”,xlwings 管“Excel 自己会不会”。

三、同一份数据,四个库的写法对比

统一使用“产品、销量、单价、销售额”这组数据,观察写法差异。

1. openpyxl:精细到单元格
  1. from openpyxl import Workbook
  2. from openpyxl.styles import Font, PatternFill
  3. wb = Workbook()
  4. ws = wb.active
  5. ws.append(["产品", "销量", "单价", "销售额"])
  6. for row in DATA:
  7.     ws.append(row)
  8. for cell in ws[1]:
  9.     cell.font = Font(bold=True, color="FFFFFF")
  10.     cell.fill = PatternFill("solid", fgColor="4472C4")
  11. wb.save("输出.xlsx")
复制代码

特点是能精确控制到某个单元格的字体颜色、填充色,但本身没有数据处理能力,无法对它“按产品分组求和”。

2. pandas:一行读写
  1. import pandas as pd
  2. df = pd.DataFrame(DATA, columns=["产品", "销量", "单价", "销售额"])
  3. df["毛利"] = (df["销售额"] * 0.35).round(2)   # 一行加计算列
  4. df.to_excel("输出.xlsx", index=False)         # 一行写出
复制代码

筛选、分组、聚合、透视基本都是一行搞定,是数据处理方面最强的工具;但改单元格颜色就很别扭。

3. XlsxWriter:性能与图表
  1. import xlsxwriter
  2. wb = xlsxwriter.Workbook("输出.xlsx")
  3. ws = wb.add_worksheet("Sheet1")
  4. fmt = wb.add_format({"bold": True, "bg_color": "#4472C4", "font_color": "white"})
  5. ws.write_row(0, 0, HEADER, fmt)
  6. chart = wb.add_chart({"type": "column"})
  7. chart.add_series({"name": "销售额", "categories": [...], "values": [...]})
  8. ws.insert_chart("F2", chart)
  9. wb.close()
复制代码

写十万行数据比 openpyxl 快得多,内置图表种类也最全;代价是只能从零创建,不能读取和修改已有文件。

4. xlwings:驱动 Excel 本体
  1. import xlwings as xw
  2. app = xw.App(visible=False)
  3. wb = app.books.add()
  4. ws = wb.sheets[0]
  5. ws.range("A1").value = HEADER
  6. ws.range("A2").value = DATA
  7. wb.save("输出.xlsx")
  8. wb.close()
  9. app.quit()
复制代码

它能调用 Excel 的一切能力,包括 VBA 宏;但前提是本机装了 Excel,而且拉起一个 Excel 进程的开销不小,不适合高并发或批量处理。

四、选库决策路径

按顺序自问四个问题即可:


  • 问题 1:需不需要处理数据(去重、筛选、汇总、拼接)?需要,选 pandas;不需要,进入下一问。
  • 问题 2:需不需要精细控制格式(颜色、边框、公式、图表)?需要,选 openpyxl;不需要,进入下一问。
  • 问题 3:需不需要生成超大数据量或复杂图表?需要,选 XlsxWriter;不需要,直接用 pandas 最省事。
  • 问题 4:需不需要调用 Excel 内置功能或宏?需要,选 xlwings。


能覆盖大约九成场景的黄金组合是:pandas 处理数据,openpyxl 收尾美化。也就是 pandas 负责算,openpyxl 负责美。例如按部门拆分工作表后,再统一美化每个文件的表头:
  1. import pandas as pd
  2. from openpyxl import load_workbook
  3. from openpyxl.styles import Font, PatternFill
  4. # 第一步:pandas 按部门拆分数据
  5. for value, group in df.groupby("部门"):
  6.     group.to_excel(out_dir / f"{value}_明细表.xlsx", index=False)
  7. # 第二步:openpyxl 统一美化表头并冻结首行
  8. for f in out_dir.glob("*.xlsx"):
  9.     wb = load_workbook(f)
  10.     ws = wb.active
  11.     for cell in ws[1]:
  12.         cell.fill = PatternFill("solid", fgColor="4472C4")
  13.         cell.font = Font(bold=True, color="FFFFFF")
  14.     ws.freeze_panes = "A2"
  15.     wb.save(f)
复制代码

两个库各司其职,代码比用单一库硬做要短,也更容易维护。

五、三个必须知道的坑

坑 1:订单号、身份证号被写成科学计数法或丢掉前导零
  1. df = pd.read_excel("数据.xlsx")
  2. # 订单号 00123 被自动推断成数字,变成 123
  3. df = pd.read_excel("数据.xlsx", dtype={"订单号": str, "身份证号": str})
复制代码

原因是 pandas 会自动做类型推断,把纯数字列当数值处理。解决办法是在读取时用 dtype 显式声明为字符串。

坑 2:写进公式后,读回来的是公式而不是结果
  1. ws["D2"] = "=B2*C2"
  2. wb.save("x.xlsx")
  3. wb2 = load_workbook("x.xlsx")
  4. print(wb2.active["D2"].value)   # 输出 '=B2*C2',不是计算结果
复制代码

原因是 openpyxl 不负责计算公式,计算是 Excel 做的。修复办法是读取时加 data_only=True,并且该文件必须先用 Excel 打开并保存过一次,让 Excel 把计算结果写入文件,否则读到的仍是空值。

坑 3:保存时文件被 Excel 占用
  1. PermissionError: [Errno 13] Permission denied: '输出.xlsx'
复制代码

原因很直接:这个文件正在 Excel 里打开着。关掉 Excel 再跑脚本即可。工程上的做法是把所有输出统一写到独立目录,并建议输出文件名带时间戳,减少手工覆盖和占用冲突。

六、可直接运行的对比脚本

把四段演示放在一个脚本里跑,能直观看到四个输出文件在体积和样式上的差异:
  1. # -*- coding: utf-8 -*-
  2. """
  3. 第 2 讲配套脚本:四大 Excel 库对比演示
  4. 用同一份数据,分别用 openpyxl / pandas / XlsxWriter / xlwings 写出文件,
  5. 直观感受四个库的写法差异与各自擅长的场景。
  6. 运行:python lessons/L02_lib_compare.py
  7. 说明:xlwings 需要本机安装 Excel,未安装时会自动跳过(不影响其他演示)。
  8. """
  9. from __future__ import annotations
  10. import sys
  11. from pathlib import Path
  12. sys.path.insert(0, str(Path(__file__).resolve().parent.parent))
  13. from utils.helpers import OUTPUT_DIR, ensure_dir, get_logger, human_size  # noqa: E402
  14. log = get_logger("L02")
  15. OUT = ensure_dir(OUTPUT_DIR / "第02讲")
  16. HEADER = ["产品", "销量", "单价", "销售额"]
  17. ROWS = [
  18.     ["智能手环", 120, 199.0, 23880.0],
  19.     ["蓝牙耳机", 98, 249.0, 24402.0],
  20.     ["移动电源", 210, 129.0, 27090.0],
  21.     ["快充数据线", 356, 39.9, 14204.4],
  22. ]
  23. def demo_openpyxl() -> Path:
  24.     from openpyxl import Workbook
  25.     from openpyxl.styles import Font, PatternFill
  26.     wb = Workbook()
  27.     ws = wb.active
  28.     ws.title = "openpyxl"
  29.     ws.append(HEADER)
  30.     for row in ROWS:
  31.         ws.append(row)
  32.     for cell in ws[1]:
  33.         cell.font = Font(bold=True, color="FFFFFF")
  34.         cell.fill = PatternFill("solid", fgColor="4472C4")
  35.     path = OUT / "对比_openpyxl.xlsx"
  36.     wb.save(path)
  37.     log.info(" openpyxl -> %s(%s)", path.name, human_size(path.stat().st_size))
  38.     return path
  39. def demo_pandas() -> Path:
  40.     import pandas as pd
  41.     df = pd.DataFrame(ROWS, columns=HEADER)
  42.     df["毛利"] = (df["销售额"] * 0.35).round(2)   # 一行加计算列
  43.     path = OUT / "对比_pandas.xlsx"
  44.     df.to_excel(path, index=False)                # 一行写出
  45.     log.info(" pandas -> %s(%s)", path.name, human_size(path.stat().st_size))
  46.     log.info(" 加计算列、筛选、聚合都只需一行")
  47.     return path
  48. def demo_xlsxwriter() -> Path:
  49.     import xlsxwriter
  50.     path = OUT / "对比_xlsxwriter.xlsx"
  51.     wb = xlsxwriter.Workbook(str(path))
  52.     ws = wb.add_worksheet("XlsxWriter")
  53.     fmt_header = wb.add_format({"bold": True, "bg_color": "#4472C4", "font_color": "white"})
  54.     fmt_money = wb.add_format({"num_format": "#,##0.00"})
  55.     ws.write_row(0, 0, HEADER, fmt_header)
  56.     for r, row in enumerate(ROWS, start=1):
  57.         ws.write_row(r, 0, row)
  58.         ws.write_number(r, 3, row[3], fmt_money)  # 单独设数字格式
  59.     # 内置图表(XlsxWriter 的图表功能最强)
  60.     chart = wb.add_chart({"type": "column"})
  61.     chart.add_series({
  62.         "name": "销售额",
  63.         "categories": ["XlsxWriter", 1, 0, len(ROWS), 0],
  64.         "values": ["XlsxWriter", 1, 3, len(ROWS), 3],
  65.     })
  66.     chart.set_title({"name": "各产品销售额"})
  67.     ws.insert_chart("F2", chart)
  68.     wb.close()
  69.     log.info(" XlsxWriter -> %s(%s,含原生图表)",
  70.              path.name, human_size(path.stat().st_size))
  71.     return path
  72. def demo_xlwings() -> None:
  73.     try:
  74.         import xlwings as xw
  75.     except ImportError:
  76.         log.info(" xlwings -> 未安装,跳过(需要 Excel 环境,可选)")
  77.         return
  78.     try:
  79.         app = xw.App(visible=False)
  80.         wb = app.books.add()
  81.         ws = wb.sheets[0]
  82.         ws.range("A1").value = HEADER
  83.         ws.range("A2").value = ROWS
  84.         path = OUT / "对比_xlwings.xlsx"
  85.         wb.save(str(path))
  86.         wb.close()
  87.         app.quit()
  88.         log.info(" xlwings -> %s(直接驱动 Excel 进程)", path.name)
  89.     except Exception as exc:  # noqa: BLE001
  90.         log.info(" xlwings -> 跳过(%s)", exc)
  91. def main() -> None:
  92.     log.info("用同一份数据,四个库各写一遍:")
  93.     demo_openpyxl()
  94.     demo_pandas()
  95.     demo_xlsxwriter()
  96.     demo_xlwings()
  97.     log.info("")
  98.     log.info("结论:日常数据处理用 pandas;要精细样式/公式用 openpyxl;")
  99.     log.info("      要生成大量带图表的报表用 XlsxWriter;要驱动 Excel 本体用 xlwings。")
  100.     log.info("第 2 讲演示结束,输出目录:data/output/第02讲/")
  101. if __name__ == "__main__":
  102.     main()
复制代码

脚本里 xlwings 段做了两层保护:导入失败时直接跳过,运行异常时也只记日志不中断,因此没有 Excel 环境的机器同样能完整跑完其余三个演示。

七、小结

.xls 老格式先手动另存为 .xlsx,现代库只在 xlsx 上做文章;四个库按职责分工:openpyxl 管样式与公式,pandas 管数据,XlsxWriter 管写入性能与图表,xlwings 管驱动 Excel 本体;最实用的组合是 pandas 处理数据加 openpyxl 收尾美化。三个高频坑分别是 dtype 声明字符串防丢前导零、公式要用 data_only=True 且文件需被 Excel 保存过、输出文件不要正被 Excel 打开。
回复

使用道具 举报

发表于 17 分钟前 | 显示全部楼层

Re: openpyxl与pandas处理Excel:四大库选型与避坑

这篇梳理得很清楚,尤其是先把 .xls 和 .xlsx 分开讲,再落到四个库的定位,对刚开始处理 Excel 的人很友好。“openpyxl 管长什么样、pandas 管数据是什么、XlsxWriter 管写得快不快、xlwings 管 Excel 自己会不会”这句话总结得很实用,基本能把选型思路立住。代码对比也能直观看出 openpyxl 偏单元格控制、pandas 偏数据处理、XlsxWriter 偏性能和图表、xlwings 偏驱动 Excel 本体。实操上建议 .xls 先另存为 .xlsx、不要写代码硬兼容,这一点很实在。四问决策路径按顺序自问也很适合实际选库,先判断要不要处理数据,再判断要不要精细格式,能少走不少弯路。整体看是一篇很适合收藏备查的入门选型帖。
回复 支持 反对

使用道具 举报

发表于 17 分钟前 | 显示全部楼层

Re: openpyxl与pandas处理Excel:四大库选型与避坑

这篇梳理得很清楚,尤其是先分清 .xls 和 .xlsx 这一点,确实能帮新手少走很多弯路。四个库的一句话定位也很实用:openpyxl 管“长什么样”,pandas 管“数据是什么”,XlsxWriter 管“写得快不快、图表好不好看”,xlwings 管“Excel 自己会不会”,比单纯背 API 更容易建立选型思路。实际使用时,我也倾向于先用 pandas 完成清洗、筛选和汇总,再把结果交给 openpyxl 或 XlsxWriter 做格式和图表;如果只是读写和计算,就不太需要上 xlwings。还有 .xls 先手动另存为 .xlsx、.xlsm 要记得 keep_vba=True 这些提醒都很接地气,属于真正容易踩的坑。期待后面把决策路径和常见坑继续补完。
回复 支持 反对

使用道具 举报

发表于 17 分钟前 | 显示全部楼层

Re: openpyxl与pandas处理Excel:四大库选型与避坑

楼主这个梳理顺序挺赞的,先讲文件格式再谈库定位,比一上来就列 API 清楚多了,尤其“把 .xls 手动另存为 .xlsx 而不是写代码兼容”这条,我踩过坑之后也是这么干的,兼容老格式纯属给自己找麻烦。 顺着这个话题补几个我实际用下来容易忽略的点,给后来人省点时间: 一是 pandas 的读写 Excel 其实不是它自己干的,底层是调 openpyxl 或 xlsxwriter 引擎。所以“pandas 写大文件慢”很多时候锅在引擎上,用 ExcelWriter 指定 engine=“xlsxwriter” 会快不少,而且还能顺便把格式写进去,等于把 pandas 的数据能力和 XlsxWriter 的样式能力串起来了,这一点对做报表特别有用。 二是 openpyxl 处理大文件时内存吃得厉害,几万行以上建议开 read_only=True 或 write_only=True 模式,不然很容易把内存撑爆;代价是只读模式下不能随机访问单元格。 三是 XlsxWriter 只能新建不能改已有文件这个限制,很多人是写到一半才发现的,如果业务上需要“读进来改一改再写回去”,那基本就得走 openpyxl 或者 pandas 读加引擎写的组合。 四是 xlwings 依赖本机装 Excel 这一点,在 Linux 服务器或者 CI 环境里直接跑不起来,别在本地调试通了就以为能上服务器,部署前
回复 支持 反对

使用道具 举报

您需要登录后才可以回帖 登录 | 注册

本版积分规则

指导单位

江苏省公安厅

江苏省通信管理局

浙江省台州刑侦支队

DEFCON GROUP 86025

Hacking Group 021A

旗下站点

态势感知中心

应急响应中心

红盟安全

联系我们

官方QQ群:112851260

官方邮箱:security#ihonker.org(#改成@)

官方核心成员

关注微信公众号

Archiver|手机版|小黑屋| ( 沪ICP备2021026908号 )

GMT+8, 2026-11-23 05:22 , Processed in 0.025919 second(s), 18 queries , Gzip On, Redis On.

Powered by ihonker.com

Copyright © 2015-现在.

  • 返回顶部