简介面向Python开发者与数据处理人员的XlsxWriter实战入门资料主要讲解如何利用xlsxwriter库创建和编辑Excel文件。内容从最基础的Workbook对象创建、Worksheet新增开始逐步演示数据写入、字体加粗等单元格样式设置、SUM等公式生成并补充图表、条件格式等进阶方向的官方文档指引。包体为单个PDF文档压缩后约63KB便于快速下载阅读。资源目前已有270人学习适合初学者快速掌握XlsxWriter的核心用法也能为有自动化报表输出需求的开发者提供直接可参考的代码片段与封装思路。文档中的示例均围绕真实场景展开同时提示了如何将常用功能抽象为函数或类以提高复用性实用性较强。1. 为什么用 xlsxwriter 而不是 pandas 或 openpyxl先想清楚再动手有个做数据报表的同事前阵子找我吐槽他用 pandas 的to_excel()导出的表格老板总说“格式太素”没法直接拿去开会后来换 openpyxl又发现写大文件时内存涨得离谱一个 10 万行的报表能把他 16G 的笔记本卡到风扇狂转。我给他的建议是如果目标是“输出各种 xlsx 文件”而且对格式、图表、下拉菜单、条件格式有要求优先用 xlsxwriter。它不负责数据处理专精于把数据“写漂亮”生成的 xlsx 体积小、速度快还支持很多 openpyxl 不直接暴露的功能。这篇笔记就是围绕这个库从最小示例一直写到动态模板和避坑经验适合所有被 Excel 报表导出折磨过的 Python 开发者。2. xlsxwriter 的核心对象与工作流程从 Workbook 到 Worksheet 的最小组件xlsxwriter 的设计很朴素核心就三个对象Workbook文件、Worksheet工作表、Format单元格格式。整个工作流永远是一条线创建文件、取工作表、调写入方法、最后close()。没有复杂的对象树也没有隐式状态新手很容易建立心智模型。2.1 最小可运行示例三行代码生成第一个 xlsx先看一个连数据都算不上的最小例子目的是把环境和工作流跑通。import xlsxwriter workbook xlsxwriter.Workbook(demo.xlsx) worksheet workbook.add_worksheet(成绩单) # 参数是工作表名可省略 worksheet.write(A1, 姓名) worksheet.write(B1, 分数) workbook.close()这段代码做了四件事创建文件对象、新增工作表、在 A1 和 B1 写入两个字符串、关闭文件。注意close()必须调用它负责把缓冲区里的内容真正写入磁盘忘记调用时文件往往能创建但内容是空的或者损坏的。add_worksheet()不传参会用默认名Sheet1多个工作表会自动追加数字后缀。写入坐标用A1这种 Excel 风格字符串也可以用(row, col)的零基元组比如(0,0)等价于A1团队协作时我更推荐元组方便循环下标。2.2 理解 write 方法与三种写入模式常量、公式、单元格对象很多人一开始只用一个write()其实它是类型分派入口内部会根据 Python 值类型选择写入方式。如果你需要精确控制类型可以直接调用带后缀的方法。常见做法是把write()当成快捷方式把类型明确的方法用在关键数据上。worksheet.write(0, 0, 标题) # 字符串 worksheet.write(1, 0, 123) # 数字 worksheet.write_number(2, 0, 3.14) # 强制数字 worksheet.write_string(3, 0, 3.14) # 强制字符串避免被当成数字 worksheet.write_formula(4, 0, SUM(A1:A4)) worksheet.write_datetime(5, 0, datetime(2025, 1, 1), date_format)write_string和write_number的差别在导出的数据经常被下游程序读取时会显现如果你把一个带前导零的编号写成数字Excel 里显示是 1实际值是 1但业务上需要001就必须用字符串。write_formula是 xlsxwriter 的强项公式会写入单元格但要注意 Excel 打开时才会计算程序生成的缓存值默认是 0。还有一个不太常用但很实用的write_blank()它会写入一个空白单元格主要用来保留格式比如给某个格子只加背景色而不填内容。3. 输出各种 xlsx 的关键场景格式化、条件格式、图表、数据验证这一章是 xlsxwriter 区别于其他库的分水岭。很多人以为导出 Excel 就是把数据塞进格子直到看见需求单上写着“表头要加粗、隔行变色、超标数值自动标红、要带折线图、还要有下拉选项”才发现这些全是 xlsxwriter 的主场。3.1 单元格格式与样式把数据表做成能直接交付的报表格式是 xlsxwriter 里最需要花时间理解的概念。一个Format对象描述一组单元格属性创建后可以被多个单元格复用。不要在每个单元格上现场创建一个格式那样会大量增加文件体积而且格式对象多了之后内存消耗也上去了。header_format workbook.add_format({ bold: True, font_color: #FFFFFF, bg_color: #4472C4, border: 1, align: center, valign: vcenter, }) cell_format workbook.add_format({num_format: 0.00}) worksheet.set_row(0, 20, header_format) # 整行套用表头格式 worksheet.write_column(A2, [1.234, 5.678], cell_format)我一般把格式分成三类表头格式、数据格式、特殊标记格式。表头格式用深底白字加边框数据格式主要管数字显示方式比如金额保留两位、日期显示成yyyy-mm-dd。set_row()和set_column()可以给整行整列设置默认格式和行高列宽比逐格写入格式效率高得多。注意add_format里的键名有几十个最常用的是字体、边框、对齐、背景、数字格式五类。3.2 条件格式与数据条让表格自动突出异常条件格式是报表里最能提升观感的功能。xlsxwriter 的conditional_format()支持规则型、单元格值型、公式型还支持数据条和色阶。一个典型的预警报表会把超过阈值的单元格标红。worksheet.conditional_format(B2:B31, { type: cell, criteria: greater_than, value: 85, format: warn_format, }) worksheet.conditional_format(C2:C31, { type: data_bar, bar_color: #63BE7B, bar_solid: True, })参数里criteria支持greater_than、less_than、between、equal_to等value可以是数字也可以是单元格引用比如$E$1。数据条的控制项比大多数人想的多除了bar_color还能设置bar_border_color、bar_negative_color以及min_value/max_value。踩坑点是条件格式的范围必须写完整B2:B31和B2:B999在 Excel 里表现不一样建议按实际数据区写不要偷懒写整列否则文件会变大打开也会慢。3.3 插入图表用一行代码把同源数据变成折线图xlsxwriter 内置了折线图、柱状图、饼图、散点图等十几类图表不需要外部绘图库。它和 matplotlib 的“截图贴进 Excel”是完全不同的思路这里图表是原生对象数据和图在 Excel 里仍然联动。chart workbook.add_chart({type: line}) chart.add_series({ name: 月度收入, categories: [数据, 0, A, 2, A, 13], # 类别轴引用 values: [数据, 0, B, 2, B, 13], # 数值轴引用 line: {color: #ED7D31, width: 2.5}, }) chart.set_title({name: 近一年收入趋势}) chart.set_y_axis({num_format: #,##0}) worksheet.insert_chart(D2, chart)add_series里几组参数的写法统一是[工作表名, 首行, 首列, 尾行, 尾列]这五个元素一个都不能少。很多人会漏掉工作表名结果图表引用了错误表或者打开时提示引用无效。insert_chart的坐标是单元格左上角图表会浮在该位置上方不会挤占已有单元格。图表的样式可以在chart创建后统一设置比如set_legend、set_x_axis这些方法名和 Excel 里右键菜单的选项基本一一对应。4. 实战示例生成带格式、图表和下拉菜单的销售报表纸上谈兵到此为止下面用一个完整的销售报表示例把前面的知识点串起来。这是一个非常典型的交付物标题区、汇总区、明细区、图表、下拉菜单。如果你能用 xlsxwriter 把这种报表稳定生成基本就掌握了这个库 90% 的日常用法。4.1 明确需求与规划工作表结构假设我们要给某公司生成月度销售报表原始数据是 pandas DataFrame列有“销售员”“产品”“区域”“销售额”。需求是第一行大标题第二行汇总区第三行表头第四行起是明细区域列要做下拉菜单【华东、华南、华北、西南】销售额超过 10000 的格子标绿最后插入一个按销售员汇总的柱状图。常见做法是先把工作表布局在草稿纸上画出来再写代码避免格子坐标混乱。layout { title: A1:F1, summary: A2:C3, header: 3, # 表头所在行 data_start: 4, # 明细起始行 data_end: 103, # 假设有100行数据 }规划时尤其要注意行号从 0 开始还是从 1 开始。xlsxwriter 内部坐标是零基的但写代码时的中文习惯经常从 1 数我因此翻过不止一次车。建议把所有行号都用常量定义好配合#注释导出后如果发现整体偏移一行只要调一个常量而不是改一百处坐标。4.2 分步实现表头样式、数据区域、汇总公式下面这段代码包含完整功能我会把每个关键点拆开解释。import xlsxwriter import pandas as pd df pd.DataFrame({ 销售员: [A同学, B同学, C同学, A同学], 产品: [X1, X2, X3, X1], 区域: [华东, 华南, 华东, 西南], 销售额: [8000, 12000, 9500, 11000], }) workbook xlsxwriter.Workbook(销售月度报表.xlsx) worksheet workbook.add_worksheet(明细) # 样式 title_format workbook.add_format({bold: True, font_size: 16, align: center}) header_format workbook.add_format({bold: True, bg_color: #D9E1F2, border: 1}) highlight_format workbook.add_format({bg_color: #C6EFCE, font_color: #006100}) money_format workbook.add_format({num_format: #,##0}) # 标题 worksheet.merge_range(A1:F1, 月度销售报表, title_format) worksheet.set_row(0, 24) # 表头 headers df.columns.tolist() for col, name in enumerate(headers): worksheet.write(3, col, name, header_format) # 数据 for row_idx, row in enumerate(df.itertuples(indexFalse)): excel_row row_idx 4 worksheet.write(excel_row, 0, row.销售员) worksheet.write(excel_row, 1, row.产品) worksheet.write(excel_row, 2, row.区域) worksheet.write_number(excel_row, 3, row.销售额, money_format) if row.销售额 10000: worksheet.write(excel_row, 3, row.销售额, highlight_format) # 区域下拉菜单 worksheet.data_validation(C4:C103, { validate: list, source: [华东, 华南, 华北, 西南], }) # 汇总区使用公式 worksheet.write_formula(C2, SUM(D4:D103), money_format) # 按销售员汇总柱状图 chart workbook.add_chart({type: column}) for idx, name in enumerate(df[销售员].unique()): chart.add_series({ name: name, categories: [明细, 3, 0, 3, 0], # 表头占位无实际意义 values: [明细, idx 4, 3, idx 4, 3], }) worksheet.insert_chart(F2, chart, {x_offset: 25, y_offset: 10}) workbook.close()代码里有两个容易让人困惑的地方。第一个是数据行里销售额超过 10000 时用write再写了一次同一个格子。这在 xlsxwriter 里不会报错但第二次写入会覆盖第一次的格式吗答案是会。所以实际项目中我会把条件判断放在写入之前直接决定传给write_number的格式是普通格式还是高亮格式。第二个是data_validation的source参数可以直接给列表也可以引用单元格区域比如$E$1:$E$4但引用区域时下拉内容会和源区域联动这个区别在动态模板里很关键。汇总公式SUM(D4:D103)是直接写入的字符串。Excel 打开后会自动计算但如果你的下游程序用 openpyxl 读取这个文件不带数据缓存的情况下可能会读到 0。这不是 bug是 xlsxwriter 有意为之——为了减小文件体积它不计算结果只存公式。5. 避坑指南xlsxwriter 常见问题与排查这一章全是血泪经验。xlsxwriter 整体设计清爽但有几个坑几乎每个人都会踩我按现象、原因、解决的思路列出来。5.1 坑一重新写入同一个单元格导致数据悄然丢失现象循环里先写了默认格式再写高亮格式最后打开文件发现某些单元格内容变成了别的值或者干脆是空白的。原因xlsxwriter 对同一坐标重复写入时会保留最后一次写入的内容但格式对象是单元格的完整属性后一次写入如果没带格式就等于把之前所有属性清空。解决先判断再写入不要重复写同一个坐标如果必须更新请用undefined思路重构或者把数据准备好后一次性写入。# 正确写法 fmt highlight_format if value threshold else normal_format worksheet.write(row, col, value, fmt) # 错误写法 worksheet.write(row, col, value, normal_format) if value threshold: worksheet.write(row, col, value, highlight_format) # 第二次写入覆盖了内容不内容相同但格式变了5.2 坑二公式显示 0 或打开时提示“我们已修复此工作簿”现象用write_formula写完公式Excel 打开单元格显示 0或者弹修复提示。原因xlsxwriter 写入公式时默认不保存缓存结果而部分 Excel 版本遇到缓存值为 0 的公式可能会重新计算法律上没崩溃但交互上很难看。解决给write_formula传入value参数把预期的计算结果写进去。如果是用 pandas 的to_excel配合 xlsxwriter可以在ExcelWriter里不救但手动写时最好补。worksheet.write_formula(C2, SUM(D4:D103), money_format, 31650)5.3 坑三pandas 与 xlsxwriter 混用时的格式失效现象用pd.ExcelWriter(enginexlsxwriter)时DataFrame 写进去后手动加的格式失效或者表格多出来一层透视表结构。原因pandas 的to_excel内部会重新规划单元格且默认会写入索引列导致你之前计划的坐标整体偏移。解决指定indexFalse并用startrow/startcol控制写入起点然后通过writer.sheets[明细]拿到 worksheet 再补格式和图表。writer pd.ExcelWriter(out.xlsx, enginexlsxwriter) df.to_excel(writer, sheet_name明细, indexFalse, startrow3, startcol0) worksheet writer.sheets[明细] # 再执行 data_validation / conditional_format 等操作 writer.close()5.4 坑四图片、图表插入后文件巨大或打开卡顿现象插入大量图片或者图表数据区域过大文件从几百 KB 涨到几十 MB。原因图片没有压缩图表引用整列数据导致 Excel 内部存储了大量空值索引。解决插入图片前先用 PIL 压缩到合适尺寸图表数据范围精确到实际最后一行不要写D:D。另外insert_chart可以设置{x_scale: 0.5, y_scale: 0.5}来控制显示大小但要注意这只是显示缩放不影响数据引用范围。5.5 坑五中文文件名或路径在部分环境上报错现象代码在 Windows 上跑了 Windows 没问题部署到 Linux 后文件名带中文就报UnicodeEncodeError。原因Linux 下文件系统区域设置不是 UTF-8或者 Python 环境中 stdout 编码问题但Workbook构造函数本身不涉及编码。常见做法是路径用pathlib.Path并显式转成str同时确保部署环境设置了LC_ALLC.UTF-8。这个问题与其说是 xlsxwriter 的问题不如说是环境配置问题但排查起来往往浪费很多时间。6. 进阶动态报表模板与性能优化到了这个阶段你已经能用 xlsxwriter 写出格式丰富、结构完整的报表。但如果每次需求变化都要改动宽表结构那还不够“生产力”。我最后分享三个比较实用的进阶手段。第一个是用write_row和write_column批量写入减少循环调用开销。对一万行以内的数据逐行写入没问题但十万行以上批量写不仅能省代码还能把写入时间从秒级降到毫秒级。比如worksheet.write_row(A4, df.columns.tolist())可以一次写完整行。第二个是用workbook.define_name()创建动态名称结合INDIRECT公式做动态汇总。比如先定义data_range OFFSET(明细!$A$4,0,0,COUNTA(明细!$A$4:$A$10000),4)然后所有汇总公式都以这个名称作为引用范围以后数据量变化不用改公式只改数据写入行数。第三个是验证输出文件。我不会傻傻地用 Excel 打开看而是写一个小脚本用openpyxl读取检查关键单元格、公式、下拉验证是否存在。两个库并不冲突xlsxwriter 管写openpyxl 管读配合起来就是一套可靠的自动化验证流程。from openpyxl import load_workbook wb load_workbook(销售月度报表.xlsx) ws wb[明细] assert ws[C2].value SUM(D4:D103) assert ws[C4].data_validation is not None我现在的习惯是凡是需要交付给非技术同事的 Excel都会在导出后强制用这个脚本跑一遍确认文件能打开、公式在位、下拉可用。这些年踩过太多“我以为写对了对方打不开”的坑后来才发现绝大多数是公式缓存或者坐标偏移。这套方法帮我省掉了很多来回沟通也让我对 xlsxwriter 的把控越来越有底气。希望帮到你。本文还有配套的精品资源点击获取