如何用Python操作Excel自动化办公？一个案例教会你openpyxl——图表设计和透视表
毫无疑问Excel是最便捷高效的可视化工具，它可以满足基本的日常工作中的图表需求。在openpyxl中也可以设置表格样式和画图表，基本上Excel里自带的表格样式和图表类型都覆盖了，在前三篇Excel读写、公式计算、格式设置内容的基础上，本篇来讲解一下如何设置表格样式和插入图表。
一、表格样式
也许大家使用Excel表格是奔着实用性的目的，很少使用表格样式，即使想可视化表达也会选择柱状图、折线图、饼图等图表。但实际上表格也可以设计的很美观，同样达到展示数据的目的。表格样式涉及一组单元格，这使得某些操作，比如在表格里单元格样式更改更容易些。
表格样式其实就是封装好的单元格格式的集合，包括字体、颜色、边框、大小、斜体、加粗、间距等等，是内置的设计好的样式，我们有时候既想保留原始数据又想突出显示就会选择使用表格样式。在表格格式内部可以统一处理数据，比如格式更改、数据修改、汇总计算等等。
在Excel中共有三类60种内置的表格样式，分别为浅色21种、中等色28种、深色11种，还可以自定义设置。openpyxl同样也提供表格样式设置。具体怎么操作呢？
和设置单元格格式步骤一样，先创建一个表格样式对象，设置好自己需要的参数，再把对象应用到数据区域上。
先导入案例数据，还是之前的数据集。
from openpyxl import Workbook, load_workbook from openpyxl.worksheet.table import Table, TableStyleInfo wbo = load_workbook(r"D:7_Pythonpyproject estOrders7.xlsx") wso = wbo.active
对数据套用表格样式：
#第一步，创建一个表格样式对象，给表格区域起个名字
tab = Table(displayName="Table1", ref="A1:F10")
#第二步设置样式，添加一个行列带有条纹的样式，样式类型为TableStyleMedium9
style = TableStyleInfo(name="TableStyleMedium9", showFirstColumn=False,
showLastColumn=False, showRowStripes=True, showColumnStripes=True)
#第三步应用样式，把设置的样式赋值给表格对象
tab.tableStyleInfo = style
#第四步样式生效，把表格对象添加到sheet里
wso.add_table(tab)
这里为什么要用.add_table的方法而不是直接生效呢？因为表格样式必须使用.add_table方法才能避免命名重复，确保表名是唯一的。默认情况下，创建表格样式的区域第一行和第二行必须包含字符串，否则就会出现以下报错：
可供选择的套用表格样式有哪些呢？
在openpyxl的源码中就设定好了以上这60种样式，每个数字对应一种样式：
TABLESTYLES = tuple(
["TableStyleMedium{0}".format(i) for i in range(1, 29)]
+ ["TableStyleLight{0}".format(i) for i in range(1, 22)]
+ ["TableStyleDark{0}".format(i) for i in range(1, 12)]
)
在编辑页面不可见的情况下，如何查看表格样式呢？
ws.tables #查看sheet中的所有表格样式
ws.tables["Table1"] #查看指定表格样式
for table in ws.tables.values(): #迭代显示表格样式
print(table)
#还可以查看表格样式的作用区域
ws.tables.items()
[("Table1", "A1:D10")]
#删除表格样式
del ws.tables["Table1"]
以上就是设置表格样式的全部内容啦，其实很简单，通过它就可以快速生成好看的表格。
说完了表格，接下来就是透视表功能了，这在Excel中是常用的功能，不是vlookup就是透视一下。
二、透视表
透视表是一种对数据动态展示并且分类汇总的表格格式，灵活性好，可以定制分析计算需求，常常用于报表制作。
openpyxl提供对透视表的读取支持，以便将它们保留在现有文件中，但是不支持用户创建pivot表。它可以编辑和操作现有的透视表，例如更改它们的范围或是否应该自动更新设置。好像没啥用为什么还要讲呢？就是上面说的透视表被用到的很频繁，虽然不能创建但是能修改也是很有用的。
上图是已经设置好的透视表，下面要对原数据删除一行，然后通过openpyxl进行刷新，得到最新的透视表结果。
from openpyxl import load_workbook
wb = load_workbook("testOrders7(1).xlsx")
ws = wb.active
pivot = ws._pivots[0] # 这里虽然选择的是第一个其实所有透视表都保存在一个缓存里
pivot.cache.refreshOnLoad = True #这里表示根据最新数据刷新透视表
openpyxl的透视表功能就这么多，不能画透视表确实是软肋，不过可以通过其他方式来弥补，比如可以用pandas的pivot_table方法实现透视的效果。
三、图表
如果与matplotlib、seaborn等python可视化包相比，openpyxl的图表显然要弱一些，但是作为操作excel的工具包再弱也是有点用处的。它能支持的图表只有如下十种：
1. 图表分解
当我们把图表的结构进行拆解，会发现一个图表是由很多细小的组件构成的，这些组件分别是标题、轴（x轴y轴）、图形、图例、标签、文字提示，不过会根据使用场景去掉一些组件。这些组件什么作用呢？
标题：描述图表的主图，包含主标题和副标题
标签：对描述的数据内容的标注
轴用来定义坐标系中数据在方向和值的映射关系
图例：对图形内容的区分标识
文字提示：或静态突出，或动态交互来展示重点信息
图形：图表最重要的部分，通过形状上映射的视觉展现
在openpyxl中就是这样对图表元素一点一点设置，比如我们来画四种条形图，就可以知道大概怎么画图了。具体的每个图表元素都在代码的注释中得到解释。
- 柱状图
from openpyxl import Workbook,load_workbook
from openpyxl.chart import BarChart, Series, Reference
from copy import deepcopy #复制对象的库
wb = Workbook()
ws = wb.active
examples = [(Number, Batch 1, Batch 2),(2, 10, 30),(3, 40, 60),(4, 50, 70),(5, 20, 10),(6, 10, 40),(7, 50, 30),]
for example in examples:
ws.append(example)
#创建一个条形图图表对象
chart1 = BarChart()
#图表的类型是柱状图，只有两个可选值{‘col’, ‘bar’}，表示在垂直条形图和水平条形图之间切换。
chart1.type = "col"
chart1.style = 10 #风格是10
#给图表一个命名
chart1.title = "Bar Chart"
#设置y轴的名字
chart1.y_axis.title = Test number
#设置x轴的名字
chart1.x_axis.title = Sample length (mm)
#选定图表的数据区域，第一个参数是sheet名字，后面四个是行列的四个顶点
data = Reference(ws, min_col=2, min_row=1, max_row=7, max_col=3)
#给数据加上x轴标签，第一个参数是sheet名字，后面四个是行列的四个顶点
cat = Reference(ws, min_col=1, min_row=2, max_row=7)
#把数据添加到图表之中，x轴和y轴的标题从数据中得到
chart1.add_data(data, titles_from_data=True)
#把x轴标签应用到图表中
chart1.set_categories(cat)
#设置图表的大小
chart1.shape = 4
#最后把图表添加到指定区域，区域开始位置为A10单元格
ws.add_chart(chart1, "A10")
#不仅可以画单个图表，还可以在页面上画多个图表，一个一个添加
#使用复制第一张图表的方法可以省略重复步骤，只需要修改想要的样式，而不需要再添加数据和轴标签
chart2 = deepcopy(chart1)
chart2.style = 11 #修改图表的风格为11
chart2.type = "bar" #图表类型改为条形图
chart2.title = "Horizontal Bar Chart" #给第二张表命名
ws.add_chart(chart2, "G10") #把第二张表添加到G10单元格位置
chart3 = deepcopy(chart1)
chart3.type = "col"
chart3.style = 12
chart3.grouping = "stacked" #这里有点不一样，因为要堆叠，所以加上分组的属性
chart3.overlap = 100 #100表示百分之百堆叠
chart3.title = Stacked Chart
ws.add_chart(chart3, "A27")
chart4 = deepcopy(chart1)
chart4.type = "bar"
chart4.style = 13
chart4.grouping = "percentStacked" #这里的分组属性是百分比堆叠属性
chart4.overlap = 100
chart4.title = Percent Stacked Chart
ws.add_chart(chart4, "G27")
可以看到画出的图表还是很漂亮的，样式都是内置设定好的，对比突出比较明显。如果还是不清楚，我们再举一个饼图的例子，大家应该就能理解画图的套路了。虽然比较麻烦，不过你的定制自由度比较高。
- 饼图
from openpyxl import Workbook
from openpyxl.chart import (PieChart,ProjectedPieChart,Reference)
from openpyxl.chart.series import DataPoint
wb = Workbook()
ws = wb.active
data = [[Pie, Sold],[Apple, 50],[Cherry, 30],[Pumpkin, 10],[Chocolate, 40],]
for row in data:
ws.append(row)
#创建一个饼图对象
pie = PieChart()
#选定图表的数据区域，第一个参数是sheet名字，后面四个是行列的四个顶点
data = Reference(ws, min_col=2, min_row=1, max_row=5)
#给数据加上x轴标签，第一个参数是sheet名字，后面四个是行列的四个顶点
labels = Reference(ws, min_col=1, min_row=2, max_row=5)
#把数据添加到图表之中，x轴和y轴的标题从数据中得到
pie.add_data(data, titles_from_data=True)
#把x轴标签应用到图表中
pie.set_categories(labels)
#给图表一个命名
pie.title = "Pies sold by category"
#把第一个扇形区域给突出显示，idx=0表示第一个数据点，表示膨胀突出的程度
slice = DataPoint(idx=0, explosion=20)
#把膨胀效果应用到饼图中
pie.series[0].data_points = [slice]
#把画好的饼图添加到页面中，位置是D1单元格
ws.add_chart(pie, "D1")
#接下来还想创造一个饼图局部突出显示的效果
data = [[Page, Views],[Search, 95],[Products, 4],[Offers, 0.5],[Sales, 0.5],]
r = 1
for line in data: #先循环大列表的每一个小列表
for rw in range(1, len(line) + 1): #再循环每个小列表，用大列表长度作为结束条件
ws.cell(row=16+r, column=rw).value = line[rw-1] #把小列表的每个值依次写入到Sheet每个单元格中
r += 1 #每次循环计数
#创建一个投影饼图
projected_pie = ProjectedPieChart()
projected_pie.type = "pie"
projected_pie.splitType = "val" # 通过值来分割扇面
labels = Reference(ws, min_col=1, min_row=18, max_row=21)
data = Reference(ws, min_col=2, min_row=17, max_row=21)
projected_pie.add_data(data, titles_from_data=True)
projected_pie.set_categories(labels)
ws.add_chart(projected_pie, "D18")
#再画一个突出显示饼图
from copy import deepcopy
projected_bar = deepcopy(projected_pie)
projected_bar.type = "bar" #突出的图表不用饼图而是用条形图
projected_bar.splitType = pos # 通过位置来分割扇面
ws.add_chart(projected_bar, "N18")
上面这段代码看似也比较麻烦，但是如果你阅读我对每行代码做的注释，应该就能明白如何在excel中画图。不过其实当我们需要画图，可以套用openpyxl官方教程里的图表模板，把数据改改就好。参考地址如下：https://openpyxl.readthedocs.io/en/stable/charts/introduction.html
如果是日常办公，openpyxl提供的图表已经足够了，再加上单元格样式、图表样式也可以做出一些酷炫的可视化成果，甚至这还是自动化的。
当然需求永远是无法满足的，工具能做的只是满足部分需求，总会遇到各种奇奇怪怪的问题。openpyxl虽然很强大，但也有很多缺陷，不过总会有其他工具来弥补。大家还可以多去摸索。还有其他的python操作Excel的库，比如XlsxWrites/xlwings/pyexcel/pyexcelerate/xlwt/xlrd等19种，具体每种库的用法可以参考这个网址：https://www.excelpython.org/