如何用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中就是这样对图表元素一点一点设置,比如我们来画四种条形图,就可以知道大概怎么画图了。具体的每个图表元素都在代码的注释中得到解释。

  1. 柱状图
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")

可以看到画出的图表还是很漂亮的,样式都是内置设定好的,对比突出比较明显。如果还是不清楚,我们再举一个饼图的例子,大家应该就能理解画图的套路了。虽然比较麻烦,不过你的定制自由度比较高。

  1. 饼图
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/

经验分享 程序员 微信小程序 职场和发展