我要评论在销售、财务、运营等业务场景中，按"区域 × 产品"、"季度 × 品类"等维度对明细数据进行交叉汇总，是日常报表里出现频率最高的需求。手动操作 excel 插入数据透视表，再依次拖拽行字段、值字段、筛选字段，并设置汇总方式与样式，往往要花上好几分钟；数据一旦更新或维度调整，整套流程还要重做一次。当需要按月、按季把同一份透视规则套用到不同周期的明细表上时，重复成本会被迅速放大。
如果改用 python 脚本读取销售明细并生成 excel 报表，过程可以被自动化、可复用化，便于批量处理多份数据，也方便嵌入到定时任务里。常见做法是用 pandas.pivot_table 生成静态汇总表，再写回 excel。这种做法简单直接，但生成的结果是普通单元格——没有透视表对象，也就不存在 excel 中真正可点击展开/折叠、可点击下拉刷新、可在字段列表里勾选维度的"原生透视表"。
本文将使用 free spire.xls for python 在 excel 工作簿中创建一份真正可交互的原生 excel 数据透视表（pivottable）——既保留 python 自动化的效率，又能在 excel/wps 中以熟悉的透视表方式查看与分析。下面会从环境准备开始，依次演示：写入销售明细 → 创建带多行字段、双数据字段与内置样式的透视表 → 修改汇总方式与百分比显示 → 控制行/列总计与空值显示 → 明细展开/折叠与刷新设置。
1. 环境准备与库安装
本文使用 free spire.xls for python 作为驱动库，安装命令如下：
pip install spire.xls.free
该库支持创建、修改、刷新 excel 数据透视表对象，保存后的 .xlsx 用 excel/wps 打开后即为可交互透视表。
安装完成后，先准备销售明细数据（脚本会同时创建数据工作表与透视表工作表）：
from spire.xls import *
from spire.xls.common import *
# 创建一个新的工作簿
workbook = workbook()
sheet = workbook.worksheets[0]
sheet.name = "销售明细"
# 写入表头
headers = ["区域", "产品", "季度", "销售额", "订单数"]
for col, header in enumerate(headers, start=1):
sheet.range[1, col].value = header
# 写入示例数据（区域、产品、季度、销售额、订单数）
sales_data = [
["华东", "激光打印机", "q1", 182000, 42],
["华东", "激光打印机", "q2", 154000, 35],
["华东", "扫描仪", "q1", 96000, 28],
["华东", "投影仪", "q1", 128000, 19],
["华北", "激光打印机", "q1", 145000, 33],
["华北", "扫描仪", "q2", 88000, 24],
["华北", "投影仪", "q2", 117000, 17],
["华南", "激光打印机", "q2", 166000, 38],
["华南", "扫描仪", "q1", 79000, 21],
["华南", "投影仪", "q1", 138000, 20],
["西南", "激光打印机", "q2", 102000, 26],
["西南", "扫描仪", "q2", 61000, 15],
["西南", "投影仪", "q1", 86000, 12],
]
for row, data in enumerate(sales_data, start=2):
for col, value in enumerate(data, start=1):
if isinstance(value, (int, float)):
sheet.range[row, col].numbervalue = value
else:
sheet.range[row, col].value = value说明：
workbook()默认包含 3 个工作表；这里将第一个工作表命名为销售明细并写入数据，作为后续透视表的数据源。- 数值字段用
numbervalue写入，文本字段用value写入，二者均来自 spire.xls 的官方写入 api。 - 销售明细共 13 条，覆盖 4 个区域、3 类产品、2 个季度，足以演示多层级汇总。
2. 创建基础数据透视表
在销售明细的基础上，添加一个数据透视表工作表，把"区域"与"产品"作为行字段，把"销售额"与"订单数"分别作为求和汇总的数据字段。
# 基于 a1:e14 区域创建透视缓存
datarange = sheet.range["a1:e14"]
cache = workbook.pivotcaches.add(datarange)
# 新建工作表用于存放透视表
sheet2 = workbook.createemptysheet()
sheet2.name = "数据透视表"
# 创建透视表对象
pt = sheet2.pivottables.add("销售透视表", sheet2.range["a1"], cache)
# 把字段拖入行区域（区域外层 → 产品内层）
pf_region = pt.pivotfields["区域"]
pf_region.axis = axistypes.row
pf_product = pt.pivotfields["产品"]
pf_product.axis = axistypes.row
# 把字段拖入数据区域
pt.datafields.add(pt.pivotfields["销售额"], "销售额汇总", subtotaltypes.sum)
pt.datafields.add(pt.pivotfields["订单数"], "订单数汇总", subtotaltypes.sum)
# 应用内置样式并触发计算
pt.builtinstyle = pivotbuiltinstyles.pivotstylemedium12
pt.calculatedata()
workbook.savetofile("salespivot.xlsx", excelversion.version2016)
workbook.dispose()
print("salespivot.xlsx 已生成")说明：
pivotcaches.add(datarange)将数据源注册为透视缓存，是创建透视表的先决条件。pivottables.add(名称, 锚点单元格, 缓存)在指定工作表的指定位置插入透视表对象。pf.axis = axistypes.row把字段放入行区域；先后顺序决定层级（先放外层、后放内层）。datafields.add(字段, 显示名称, 汇总方式)把数值字段放入数据区域。这里使用subtotaltypes.sum按求和汇总。builtinstyle直接套用 excel 自带的pivotstylemedium12配色，节省自定义样式的工作量。calculatedata()必须显式调用，透视表才会把汇总结果物化到单元格上。
效果预览（基于示例 1 生成的 salespivot.xlsx 物化结果渲染）：
预览为脚本对透视表已物化结果的静态展示；用 excel/wps 打开 salespivot.xlsx 后将呈现完整的可交互透视表——可点击行列字段旁的 +/- 折叠/展开明细、右键刷新数据、拖动字段列表中的字段重新组织透视。
3. 修改透视表：汇总方式与显示格式
实际报表里除了"求和"，还需要"平均值"、"计数"、"最大/最小"等不同汇总方式；同时也常常需要把数字以"占列百分比"、"占总计百分比"等形式呈现。spire.xls 支持通过修改现有透视表对象来调整这些行为。
下面的示例加载上一步生成的 salespivot.xlsx，把销售额改为平均值汇总、把订单数改为最大值汇总，并把销售额显示为列百分比：
from spire.xls import *
from spire.xls.common import *
workbook = workbook()
workbook.loadfromfile("salespivot.xlsx")
sheet = workbook.worksheets["数据透视表"]
pt = sheet.pivottables[0]
# 修改汇总方式
pt.datafields[0].subtotal = subtotaltypes.average # 销售额 → 平均值
pt.datafields[1].subtotal = subtotaltypes.max # 订单数 → 最大值
# 数据字段显示为列百分比（适用于销售额这一列）
pt.datafields[0].showdataas = pivotfieldformattype.percentageofcolumn
# 重新计算并保存
pt.calculatedata()
workbook.savetofile("salespivot_modified.xlsx", excelversion.version2016)
workbook.dispose()
print("salespivot_modified.xlsx 已生成")说明：
subtotal属性决定该数据字段的汇总方式，可选sum / average / max / min / count / product等枚举值。showdataas控制数据字段的展示格式，percentageofcolumn把每个单元格的数值转换为占其所在列总和的百分比；其它可用枚举包括percentageofrow、percentageofgrandtotal、runningtotal等。- 修改后调用
calculatedata()让新规则生效，再保存为新文件保留原始版本以便对照。
4. 控制外观：布局、总计、空值显示与内置样式
透视表的"行小计"、"列总计"、"空值显示方式"以及"页面字段排列顺序"等外观属性，可以通过 options、showrowgrand 等顶层属性统一设置。
from spire.xls import *
from spire.xls.common import *
workbook = workbook()
workbook.loadfromfile("salespivot.xlsx")
sheet = workbook.worksheets["数据透视表"]
pt = sheet.pivottables[0]
# 切换到 tabular 布局（行字段并排显示，附带显式小计行）
pt.options.rowlayout = pivottablelayouttype.tabular
# 显示行列总计
pt.showrowgrand = true
pt.showcolumngrand = true
# 空值单元格显示为自定义字符串
pt.displaynullstring = true
pt.nullstring = "—"
# 报表筛选字段自上而下、再自左而右
pt.pagefieldorder = pagesordertype.downthenover
# 是否显示行小计（默认 true；设为 false 时小计行会被隐藏）
pt.showsubtotals = true
# 重新应用内置样式
pt.builtinstyle = pivotbuiltinstyles.pivotstylelight10
pt.calculatedata()
workbook.savetofile("salespivot_styled.xlsx", excelversion.version2016)
workbook.dispose()说明：
options.rowlayout控制行字段布局：compact（默认，分级缩进）、outline（带分级线）、tabular（表格化，每个行字段占一列）。showrowgrand/showcolumngrand分别控制是否在透视表底部/右侧显示行总计与列总计。displaynullstring = true配合nullstring让没有数据的单元格显示自定义占位符（默认为"null"，这里改为—）。pagefieldorder决定报表筛选字段（页面字段）的摆放顺序：downthenover（先下后右）/overthendown（先右后下）。
5. 展开/折叠明细与自动刷新
完成布局调整后，下一步是控制透视表里"区域"或"产品"明细行的展开状态，以及让透视表在打开文件时自动随源数据更新。
from spire.xls import *
from spire.xls.common import *
workbook = workbook()
workbook.loadfromfile("salespivot.xlsx")
sheet = workbook.worksheets["数据透视表"]
pt = sheet.pivottables[0]
# 1. 计算数据（展开/折叠前需先调用）
pt.calculatedata()
# 2. 折叠"华东"区域的产品明细行（仅显示华东合计）
pt.pivotfields["区域"].hideitemdetail("华东", true)
# 3. 展开"华北"区域的产品明细行
pt.pivotfields["区域"].hideitemdetail("华北", false)
# 4. 启用"打开文件时自动刷新"
pt.cache.isrefreshonload = true
workbook.savetofile("salespivot_refresh.xlsx", excelversion.version2016)
workbook.dispose()说明：
hideitemdetail(item, true)将该行字段下的某个条目折叠（只显示合计，隐藏明细）；false则展开。cache.isrefreshonload = true会在用户用 excel/wps 打开文件时，自动根据当前源数据重新计算透视表结果，避免"数据更新了透视表还是老的"的情况。
实测提醒：以上示例在 free spire.xls 当前版本中保存后，明细展开/折叠的初始状态在部分环境下不会立即物化到 xlsx 文件中。用 excel/wps 打开文件并触发一次"刷新"后，折叠/展开状态会按脚本设定生效。
6. 关键类与方法解析
下表汇总了本文示例涉及到的核心 api，便于查阅与复用：
| 类 / 成员 | 作用 |
|---|---|
workbook.worksheets[i] | 按索引访问工作表 |
workbook.createemptysheet() | 新建空白工作表 |
workbook.pivotcaches.add(range) | 将指定数据区域注册为透视缓存 |
worksheet.pivottables.add(name, anchor, cache) | 在工作表上创建透视表对象 |
pivottable.pivotfields[name] | 获取字段引用，可设置其 axis（行/列区域） |
pivotfield.axis | 设置字段所属区域，axistypes.row 表示行区域 |
pivotfield.sorttype | 设置字段排序方式，如 pivotfieldsorttype.descending |
pivotfield.hideitemdetail(item, bool) | 折叠或展开指定条目的明细 |
pivottable.datafields.add(field, displayname, subtotal) | 向数据区域添加一个汇总字段 |
pivottable.datafields[i].subtotal | 修改已有数据字段的汇总方式（sum/average/max/...） |
pivottable.datafields[i].showdataas | 修改数据字段显示格式（百分比、占比等） |
pivottable.builtinstyle | 应用 excel 内置透视表样式枚举 |
pivottable.calculatedata() | 触发透视表汇总计算，物化到单元格 |
pivottable.options.rowlayout | 设置行字段布局（compact/outline/tabular） |
pivottable.showrowgrand / showcolumngrand | 控制行列总计的显示 |
pivottable.showsubtotals | 是否显示行小计 |
pivottable.displaynullstring / nullstring | 自定义空值单元格的占位符 |
pivottable.pagefieldorder | 报表筛选字段排列顺序 |
pivottable.cache.isrefreshonload | 是否在打开文件时自动刷新 |
总结
通过本文示例，你已经掌握了如何使用 python + free spire.xls 在 excel 中创建原生可交互的数据透视表。从把销售明细写入工作簿、到通过 pivotcaches 与 pivottables.add 生成透视对象、再到把多个字段分别拖入行/数据区域，整个过程都通过脚本完成，避免了在 excel 里反复手动拖拽的重复劳动。相比 pandas.pivot_table 生成的静态汇总结果，本方案生成的 .xlsx 在 excel/wps 打开后可以直接点击展开/折叠、修改字段列表、设置筛选条件，使用体验与手动建立的透视表完全一致。
在示例基础上，你可以继续扩展更多能力：
- 批量生成月度透视表：对每个月度销售明细文件重复同一段透视代码，结合遍历文件夹实现"一键出 n 张月报"；
- 结合邮件自动发送：把生成的
salespivot.xlsx作为附件，通过smtplib或yagmail自动发到指定邮箱； - 搭配 spire.pdf 生成报告：把透视表用 spire.pdf for python 转为 pdf 报告，配合图表与封面页，输出标准化月报；
- 按权限只读分享：利用 spire.xls 的
workbook.protect/sheet.protect设置工作簿或工作表的口令保护，避免他人误改透视表结构。
如果你正在做销售/财务/运营方向的报表自动化，这种"excel 原生透视 + python 脚本"的组合将显著缩短从明细数据到可视化报表的距离。
以上就是python在excel中创建数据透视表的完整指南的详细内容，更多关于python excel创建数据透视表的资料请关注代码网其它相关文章！
发表评论