Python自动化办公：openpyxl教程(进阶)

在后台回复【阅读书籍】

即可获取python相关电子书~

Hi，我是山月。

之前给大家分享了openpyxl的基础教程：Python自动化办公：openpyxl教程(基础)。不知道大家学习的怎么样了。

今天给大家分享一下整理了一周的openpyxl进阶教程。

全文一共16000+字，包括了：批注、表格、条件格式、公式、筛选和排序、验证单元格、定义名称、分级显示、设置九个部分。

其中由于数据透视表和图表创建篇幅较长、难度较大，会在之后给他们两个分别写篇教程。

在这之前，必须重新把官网摆一次。官网+help函数+dir函数简直是学习的好帮手。

?? dir函数：新手到大师的进阶之路

?? 官网：https://openpyxl.readthedocs.io/en/stable/

下面我们一起来看吧。

01

批注

1、导入

from?openpyxl.comments?import?Comment
Comment(text,?author,?height=79,?width=144)

参数说明：

text?????#?批注的内容，必填
author???#?批注的作者，必填。
height???#?批注框高，默认79。
width????#?批注框宽，默认144。

2、实例

from?openpyxl?import?Workbook
from?openpyxl.comments?import?Comment

wb?=?Workbook()
ws?=?wb.active

ws["A1"]?=?'默认'
ws["B2"]?=?'批注'
ws["C3"]?=?'批注'

#在B2单元格设置批注
comment_B2?=?Comment('这是默认的批注',?'山月')
ws["B2"].comment?=?comment_B2

#在C3单元格设置批注
ws["C3"].comment?=?Comment('设置了高宽',?'山月',?height=50,?width=80)

#?获取批注的信息
print(comment_B2.text)?????#?获取B2单元格批注的内容，结果--->>>这是默认的批注
print(comment_B2.author)???#?获取B2单元格批注的作者，结果--->>>山月

wb.save('实例.xlsx')

效果：

02

表格

1、创建表

1）表格样式设置

name：样式名称
'''
可选项：
"TableStyleMedium1"?-?"TableStyleMedium28"
"TableStyleLight1"?-?"TableStyleLight21"
"TableStyleDark1"?-?"TableStyleDark11"
'''

showRowStripes=True：显示镶边行
showColumnStripes=True：显示镶边列
showFirstColumn=True：显示表格中第一列的特殊格式
showLastColumn=True：显示表格中最后一列的特殊格式

样式名称对应的样式：

2）实例

from?openpyxl?import?Workbook
from?openpyxl.worksheet.table?import?Table,?TableStyleInfo

wb?=?Workbook()
ws?=?wb.active

data?=?[
????["Fruit",?"2011",?"2012",?"2013",?"2014"],
????['Apples',?10000,?5000,?8000,?6000],
????['Pears',???2000,?3000,?4000,?5000],
????['Bananas',?6000,?6000,?6500,?6000],
????['Oranges',??500,??300,??200,??700],
]

#?在表中写入数据
for?row?in?data:
????ws.append(row)

tab?=?Table(displayName="Table1",?ref="A1:E5")??#?表名称和数据区域
# tab.headerRowCount = False ??#?省略标题行。如果不省略，标题行数据默认是第一行，注意标题行数据必须得是字符串，否则会报错。

#?设置表样式
style?=?TableStyleInfo(name="TableStyleMedium9",?showFirstColumn=False,
???????????????????????showLastColumn=False,?showRowStripes=False,?showColumnStripes=False)
tab.tableStyleInfo?=?style

ws.add_table(tab)?#?添加表

wb.save("实例.xlsx")

效果：

2、操作表

#获取所有表信息
ws.tables
#获取指定表信息
ws.tables["Table1"]?#Table1为表名称
#遍历所有表信息
for?table?in?ws.tables.values():
???print(table)

#获取工作表中所有表的表名和范围
ws.tables.items()???
#遍历表中表名
for?table?in?ws.tables:
???print(table)

#删除表
del?ws.tables["Table1"]?#Table1为表名称

#返回工作表中的表数
len(ws.tables)

03

条件格式

Excel支持三种不同类型的条件格式：内置、标准和自定义：

• 内置的条件格式将特定的规则与预定义的样式相结合；

• 标准条件格式将特定规则与自定义格式相结合；

• 自定义格式将自定义的规则与自定义格式相结合。

1、格式的使用

1）创建使用规则

2）使用规则

worksheet.conditional_formatting.add(range_string,?cfRule)?

参数说明：
range_string：应用条件格式的单元格区域
cfRule：要应用的条件格式规则

2、内置格式

内置条件格式有：ColorScale（色阶），IconSet（图标集），DataBar（数据条）。

其中三种内置格式里的参数cfvo是一个由【openpyxl.formatting.rule.FormatObject】对象组成的序列（列表或元组）。

FormatObject对象的语法：

from?openpyxl.formatting.rule?import?FormatObject
FormatObject(type,?val=None,?gte=None,?extLst=None)

参数：
val：值，是个浮点型
type：可选项有：百分比（percent），最小值（min），百分点值（percentile），公式（formula），最大值（max），数字（num）

1、ColorScale

色阶作为一种直观的指示，可以帮助了解数据分布和数据变化。

有两种选择：

• 双色刻度：使用两种颜色的渐变来帮助您比较单元格区域。颜色的深浅表示值的高低。

• 三色刻度：使用三种颜色的渐变来帮助您比较单元格区域。颜色的深浅表示值的高、中、低。

1）语法

#?1）通过ColorScale创建ColorScale规则
from?openpyxl.formatting.rule?import?ColorScale
ColorScale(cfvo=None,?color=None)?#创建色阶

#?2）通过ColorScaleRule创建使用ColorScale规则
from?openpyxl.formatting.rule?import?ColorScaleRule
ColorScaleRule(start_type=None,?start_value=None,?start_color=None,?mid_type=None,?mid_value=None,?mid_color=None,?end_type=None,?end_value=None,?end_color=None)