为什么有必要先确定用库
刚开始用 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:精细到单元格
- from openpyxl import Workbook
- from openpyxl.styles import Font, PatternFill
- wb = Workbook()
- ws = wb.active
- ws.append(["产品", "销量", "单价", "销售额"])
- for row in DATA:
- ws.append(row)
- for cell in ws[1]:
- cell.font = Font(bold=True, color="FFFFFF")
- cell.fill = PatternFill("solid", fgColor="4472C4")
- wb.save("输出.xlsx")
复制代码
特点是能精确控制到某个单元格的字体颜色、填充色,但本身没有数据处理能力,无法对它“按产品分组求和”。
2. pandas:一行读写
- import pandas as pd
- df = pd.DataFrame(DATA, columns=["产品", "销量", "单价", "销售额"])
- df["毛利"] = (df["销售额"] * 0.35).round(2) # 一行加计算列
- df.to_excel("输出.xlsx", index=False) # 一行写出
复制代码
筛选、分组、聚合、透视基本都是一行搞定,是数据处理方面最强的工具;但改单元格颜色就很别扭。
3. XlsxWriter:性能与图表
- import xlsxwriter
- wb = xlsxwriter.Workbook("输出.xlsx")
- ws = wb.add_worksheet("Sheet1")
- fmt = wb.add_format({"bold": True, "bg_color": "#4472C4", "font_color": "white"})
- ws.write_row(0, 0, HEADER, fmt)
- chart = wb.add_chart({"type": "column"})
- chart.add_series({"name": "销售额", "categories": [...], "values": [...]})
- ws.insert_chart("F2", chart)
- wb.close()
复制代码
写十万行数据比 openpyxl 快得多,内置图表种类也最全;代价是只能从零创建,不能读取和修改已有文件。
4. xlwings:驱动 Excel 本体
- import xlwings as xw
- app = xw.App(visible=False)
- wb = app.books.add()
- ws = wb.sheets[0]
- ws.range("A1").value = HEADER
- ws.range("A2").value = DATA
- wb.save("输出.xlsx")
- wb.close()
- app.quit()
复制代码
它能调用 Excel 的一切能力,包括 VBA 宏;但前提是本机装了 Excel,而且拉起一个 Excel 进程的开销不小,不适合高并发或批量处理。
四、选库决策路径
按顺序自问四个问题即可:
- 问题 1:需不需要处理数据(去重、筛选、汇总、拼接)?需要,选 pandas;不需要,进入下一问。
- 问题 2:需不需要精细控制格式(颜色、边框、公式、图表)?需要,选 openpyxl;不需要,进入下一问。
- 问题 3:需不需要生成超大数据量或复杂图表?需要,选 XlsxWriter;不需要,直接用 pandas 最省事。
- 问题 4:需不需要调用 Excel 内置功能或宏?需要,选 xlwings。
能覆盖大约九成场景的黄金组合是:pandas 处理数据,openpyxl 收尾美化。也就是 pandas 负责算,openpyxl 负责美。例如按部门拆分工作表后,再统一美化每个文件的表头:
- import pandas as pd
- from openpyxl import load_workbook
- from openpyxl.styles import Font, PatternFill
- # 第一步:pandas 按部门拆分数据
- for value, group in df.groupby("部门"):
- group.to_excel(out_dir / f"{value}_明细表.xlsx", index=False)
- # 第二步:openpyxl 统一美化表头并冻结首行
- for f in out_dir.glob("*.xlsx"):
- wb = load_workbook(f)
- ws = wb.active
- for cell in ws[1]:
- cell.fill = PatternFill("solid", fgColor="4472C4")
- cell.font = Font(bold=True, color="FFFFFF")
- ws.freeze_panes = "A2"
- wb.save(f)
复制代码
两个库各司其职,代码比用单一库硬做要短,也更容易维护。
五、三个必须知道的坑
坑 1:订单号、身份证号被写成科学计数法或丢掉前导零
- df = pd.read_excel("数据.xlsx")
- # 订单号 00123 被自动推断成数字,变成 123
- df = pd.read_excel("数据.xlsx", dtype={"订单号": str, "身份证号": str})
复制代码
原因是 pandas 会自动做类型推断,把纯数字列当数值处理。解决办法是在读取时用 dtype 显式声明为字符串。
坑 2:写进公式后,读回来的是公式而不是结果
- ws["D2"] = "=B2*C2"
- wb.save("x.xlsx")
- wb2 = load_workbook("x.xlsx")
- print(wb2.active["D2"].value) # 输出 '=B2*C2',不是计算结果
复制代码
原因是 openpyxl 不负责计算公式,计算是 Excel 做的。修复办法是读取时加 data_only=True,并且该文件必须先用 Excel 打开并保存过一次,让 Excel 把计算结果写入文件,否则读到的仍是空值。
坑 3:保存时文件被 Excel 占用
- PermissionError: [Errno 13] Permission denied: '输出.xlsx'
复制代码
原因很直接:这个文件正在 Excel 里打开着。关掉 Excel 再跑脚本即可。工程上的做法是把所有输出统一写到独立目录,并建议输出文件名带时间戳,减少手工覆盖和占用冲突。
六、可直接运行的对比脚本
把四段演示放在一个脚本里跑,能直观看到四个输出文件在体积和样式上的差异:
- # -*- coding: utf-8 -*-
- """
- 第 2 讲配套脚本:四大 Excel 库对比演示
- 用同一份数据,分别用 openpyxl / pandas / XlsxWriter / xlwings 写出文件,
- 直观感受四个库的写法差异与各自擅长的场景。
- 运行:python lessons/L02_lib_compare.py
- 说明:xlwings 需要本机安装 Excel,未安装时会自动跳过(不影响其他演示)。
- """
- from __future__ import annotations
- import sys
- from pathlib import Path
- sys.path.insert(0, str(Path(__file__).resolve().parent.parent))
- from utils.helpers import OUTPUT_DIR, ensure_dir, get_logger, human_size # noqa: E402
- log = get_logger("L02")
- OUT = ensure_dir(OUTPUT_DIR / "第02讲")
- HEADER = ["产品", "销量", "单价", "销售额"]
- ROWS = [
- ["智能手环", 120, 199.0, 23880.0],
- ["蓝牙耳机", 98, 249.0, 24402.0],
- ["移动电源", 210, 129.0, 27090.0],
- ["快充数据线", 356, 39.9, 14204.4],
- ]
- def demo_openpyxl() -> Path:
- from openpyxl import Workbook
- from openpyxl.styles import Font, PatternFill
- wb = Workbook()
- ws = wb.active
- ws.title = "openpyxl"
- ws.append(HEADER)
- for row in ROWS:
- ws.append(row)
- for cell in ws[1]:
- cell.font = Font(bold=True, color="FFFFFF")
- cell.fill = PatternFill("solid", fgColor="4472C4")
- path = OUT / "对比_openpyxl.xlsx"
- wb.save(path)
- log.info(" openpyxl -> %s(%s)", path.name, human_size(path.stat().st_size))
- return path
- def demo_pandas() -> Path:
- import pandas as pd
- df = pd.DataFrame(ROWS, columns=HEADER)
- df["毛利"] = (df["销售额"] * 0.35).round(2) # 一行加计算列
- path = OUT / "对比_pandas.xlsx"
- df.to_excel(path, index=False) # 一行写出
- log.info(" pandas -> %s(%s)", path.name, human_size(path.stat().st_size))
- log.info(" 加计算列、筛选、聚合都只需一行")
- return path
- def demo_xlsxwriter() -> Path:
- import xlsxwriter
- path = OUT / "对比_xlsxwriter.xlsx"
- wb = xlsxwriter.Workbook(str(path))
- ws = wb.add_worksheet("XlsxWriter")
- fmt_header = wb.add_format({"bold": True, "bg_color": "#4472C4", "font_color": "white"})
- fmt_money = wb.add_format({"num_format": "#,##0.00"})
- ws.write_row(0, 0, HEADER, fmt_header)
- for r, row in enumerate(ROWS, start=1):
- ws.write_row(r, 0, row)
- ws.write_number(r, 3, row[3], fmt_money) # 单独设数字格式
- # 内置图表(XlsxWriter 的图表功能最强)
- chart = wb.add_chart({"type": "column"})
- chart.add_series({
- "name": "销售额",
- "categories": ["XlsxWriter", 1, 0, len(ROWS), 0],
- "values": ["XlsxWriter", 1, 3, len(ROWS), 3],
- })
- chart.set_title({"name": "各产品销售额"})
- ws.insert_chart("F2", chart)
- wb.close()
- log.info(" XlsxWriter -> %s(%s,含原生图表)",
- path.name, human_size(path.stat().st_size))
- return path
- def demo_xlwings() -> None:
- try:
- import xlwings as xw
- except ImportError:
- log.info(" xlwings -> 未安装,跳过(需要 Excel 环境,可选)")
- return
- try:
- app = xw.App(visible=False)
- wb = app.books.add()
- ws = wb.sheets[0]
- ws.range("A1").value = HEADER
- ws.range("A2").value = ROWS
- path = OUT / "对比_xlwings.xlsx"
- wb.save(str(path))
- wb.close()
- app.quit()
- log.info(" xlwings -> %s(直接驱动 Excel 进程)", path.name)
- except Exception as exc: # noqa: BLE001
- log.info(" xlwings -> 跳过(%s)", exc)
- def main() -> None:
- log.info("用同一份数据,四个库各写一遍:")
- demo_openpyxl()
- demo_pandas()
- demo_xlsxwriter()
- demo_xlwings()
- log.info("")
- log.info("结论:日常数据处理用 pandas;要精细样式/公式用 openpyxl;")
- log.info(" 要生成大量带图表的报表用 XlsxWriter;要驱动 Excel 本体用 xlwings。")
- log.info("第 2 讲演示结束,输出目录:data/output/第02讲/")
- if __name__ == "__main__":
- main()
复制代码
脚本里 xlwings 段做了两层保护:导入失败时直接跳过,运行异常时也只记日志不中断,因此没有 Excel 环境的机器同样能完整跑完其余三个演示。
七、小结
.xls 老格式先手动另存为 .xlsx,现代库只在 xlsx 上做文章;四个库按职责分工:openpyxl 管样式与公式,pandas 管数据,XlsxWriter 管写入性能与图表,xlwings 管驱动 Excel 本体;最实用的组合是 pandas 处理数据加 openpyxl 收尾美化。三个高频坑分别是 dtype 声明字符串防丢前导零、公式要用 data_only=True 且文件需被 Excel 保存过、输出文件不要正被 Excel 打开。 |