Excel自动化办公——Openpyxl的基本使用

个人感觉，相比Pandas，openpyxl对Excel的操作更为细致，Pandas则更适用于统计计算；

• 01 基本环境
• 02 Excel数据读取操作
• 03 案例
• 04 向Excel写入数据
• 05 表数据定向修改
• 06 单元格样式制定
• 07 单元格边框样式制定
• 08 单元格对其方式
• 09 筛选器
• 10 公式操作
• 11 设置行高、列宽
• 12 单元格拆分合并
• 13 冻结窗口
• 14 添加绘制图表

01 基本环境

使用库：openpyxl（3.x），依赖 lxml、pillow；

一些基本定义：

• 工作簿：一个Excel电子表格文档，保存到扩展名为.xlsx的文件中；
• sheet表：工作簿包含的多个工作表；
• 活动表：当前查看的表，或关闭前最后查看的表；
• 单元格：列从A开始，行从1开始，特定行列的位置方格即为单元格；注意在Z之后，使用两个字母AA、AB、AC等；

import openpyxl
openpyxl.__version__

'3.0.9'

02 Excel数据读取操作

# 加载工作簿，返回工作簿对象
wb = openpyxl.load_workbook("./youtube_mp3_ds_test.xlsx")

# 获取工作表名称列表
print("工作表名称列表 = ",wb.sheetnames)

# 获取sheet，返回工作表对象
ws1 = wb["Sheet1"]

# 获取活动表，返回工作表对象
ws2 = wb.active

# 从表中取单元格，按行列名字访问，返回Cell对象
cell = ws2["A4"]

# 属性：value：cell中存储的值
# 属性：row：行索引
# 属性：column：列索引
# 属性：coordinate：坐标
print("单元格诸属性 = ",cell.value, cell.row, cell.column, cell.coordinate)

# 行索引可以使用数字，代替字母，从1开始，但需要变化下方式；
cell = ws2.cell(row=1, column=4)
print("单元格诸属性（索引方式） = ",cell.value, cell.row, cell.column, cell.coordinate)


# 从工作表中 可以获取一个矩形区域
for cell_row in ws2["A2":"E6"]:
    for cell in cell_row:
#         print(cell.coordinate)
        pass

# 也可以获取单独的一行或一列：使用worksheet对象的rows和columns属性，返回的是一个迭代器
for column in ws2.columns:
    for cell in column:
        print("每一列的第一行 = ",cell.coordinate)
        break

# 获取第一列 的所有单元格        
list(ws2.columns)[0]

# 获取工作表中的最大行数和最大列数
print("工作表中的最大行数和最大列数 = ",ws2.max_row,ws2.max_column)

工作表名称列表 =  ['Sheet1']
单元格诸属性 =  114584 4 1 A4
单元格诸属性（索引方式） =  a_item 1 4 D1
每一列的第一行 =  A1
每一列的第一行 =  B1
每一列的第一行 =  C1
每一列的第一行 =  D1
每一列的第一行 =  E1
每一列的第一行 =  F1
每一列的第一行 =  G1
每一列的第一行 =  H1
工作表中的最大行数和最大列数 =  12567 8

03 案例

# 四列：普查区编号A、州简称B、县名称C、普查区人口D；每个县可能存在多个普查区；

# 代码示意
sheet = ...
countryData = {}
for row in range(2, sheet.max_row + 1):
    cell_1 = sheet["B" + str(row)]
    cell_2 = sheet["C" + str(row)]
    cell_3 = sheet["D" + str(row)]
    
    countryData.setdefault(cell_1.value, {})
    countryData[cell_1.value].setdefault(cell_2.value, {'v1':0, 'v2':0})
    countryData[cell_1.value][cell_2.value]['v1'] += 1
    countryData[cell_1.value][cell_2.value]['v2'] += int(cell_3.value)

# 结果可以保存到json  

04 向Excel写入数据

# 创建一个空工作薄
wb = openpyxl.Workbook()

wb.sheetnames # 默认工作表

['Sheet']

ws = wb.active

# 为工作表 设置名称
ws.title = "test_sheet"
ws.title

'test_sheet'

# 保存工作薄
wb.save("./wb_demo.xlsx")

# 创建工作表
wb = openpyxl.load_workbook("./wb_demo.xlsx")
wb.create_sheet(title="test_sheetA", index=0) # 指定sheet放的位置（该值从0开始）
wb.save("./wb_demo.xlsx")
wb.sheetnames

['test_sheetA', 'test_sheet']

# 删除工作表
del wb["test_sheetA"]
wb.sheetnames

['test_sheet']

# 指定单元格的值
sheet = wb["test_sheet"]
sheet["A1"] = "ColumnA"
sheet["B1"] = "ColumnB"
sheet.cell(row=2, column=1).value = "ValueA"
sheet.cell(row=2, column=2).value = "ValueB"
wb.save("./wb_demo.xlsx")

05 表数据定向修改

# 代码示意
wb = openpyxl.load_workbook("./xxx.xlsx")
sheet = wb["Sheet"]

PRICE_UPDATE = {
    "A": 1.0,
    "B": 2.0,
    "C": 3.0
}
for rowNum in range(2, sheet.max_row + 1):
    cur_name = sheet.cell(row=rowNum, column=1).value
    if cur_name in PRICE_UPDATE:
        sheet.cell(row=rowNum, column=2).value = PRICE_UPDATE[cur_name]

# 保存更新 wb        

06 单元格样式制定

使用openpyxl.stayles模块导入 Font 和 PatternFill 工具包；

字体

• name=“楷体”，名称
• color=“000000”，颜色
• italic=Ture，斜体
• size=12，字体大小
• underline=“sigle”，单下划线
• b=True，粗体

# 设置字体样式
import openpyxl
from openpyxl.styles import Font

wb = openpyxl.load_workbook("./wb_demo.xlsx")
sheet = wb["test_sheet"]

for row in sheet["A1":"B1"]:
    for col in row:
        col.font = Font(name="楷体")
wb.save("./wb_demo.xlsx")

# 设置字体颜色
wb = openpyxl.load_workbook("./wb_demo.xlsx")
sheet = wb["test_sheet"]

for row in sheet["A1":"B1"]:
    for col in row:
        col.font = Font(name="楷体",color="668B8B")
wb.save("./wb_demo.xlsx")

填充色

# 设置单元格填充色
import openpyxl
from openpyxl.styles import PatternFill

wb = openpyxl.load_workbook("./wb_demo.xlsx")
sheet = wb["test_sheet"]

for row in sheet["A2":"B2"]:
    for col in row:
        col.fill = PatternFill(patternType="solid",fgColor="8470FF")
wb.save("./wb_demo.xlsx")

07 单元格边框样式制定

# 设置边框（并不常用）
import openpyxl
from openpyxl.styles import Side, Border

wb = openpyxl.load_workbook("./wb_demo.xlsx")
sheet = wb["test_sheet"]

for row in sheet["A2":"B2"]:
    for col in row:
        side = Side(style='double', color='FF0000')
        col.border = Border(top=side,bottom=side, left=side, right=side)
        
wb.save("./wb_demo.xlsx")

08 单元格对其方式

# 使用Alignment工具包
import openpyxl
from openpyxl.styles import Alignment

wb = openpyxl.load_workbook("./wb_demo.xlsx")
sheet = wb["test_sheet"]

for row in sheet["A2":"B2"]:
    for col in row:
        col.alignment = Alignment(horizontal="right",vertical='center')
        
wb.save("./wb_demo.xlsx")

09 筛选器

import openpyxl

wb = openpyxl.load_workbook("./wb_demo.xlsx")
sheet = wb["test_sheet"]

# 设置筛选器:筛选器对象需制定引用的区域
sheet.auto_filter.ref = 'A1:B3'

# 设置筛选项:参数1指定对第几列应用筛选条件，参数2表示筛选条件的内容
sheet.auto_filter.add_filter_column(0, ["ValueA"]) # 但注意 数据展示样式 需要打开Excel并操作筛选后才会生效，相当于bug

# 设置排序：指定排序区域，及升降序
sheet.auto_filter.add_sort_condition(ref="B2:B3", descending=True) # 依旧存在上面的bug：配置已经存在，样式却未生效

wb.save("./wb_demo.xlsx")

10 公式操作

import openpyxl

wb = openpyxl.load_workbook("./wb_demo.xlsx")
sheet = wb["test_sheet"]
sheet['C4'] = '=SUM(C2:C3)'
wb.save("./wb_demo.xlsx")

# 读取使用公式的单元格的结果
wb = openpyxl.load_workbook("./wb_demo.xlsx",read_only=True)
sheet = wb["test_sheet"]
sheet['C4'].value # 注意 这是一个公式字符串，而非计算好的结果

'=SUM(C2:C3)'

11 设置行高、列宽

sheet对象有 row_dimensions 和 column_demensions 属性，控制行高和列宽；

• 行高范围 0~409的整数或小数，表示点数，默认行高12.75，一点等于1/72英寸；
• 列宽可设置0~255的整数或小数，默认8.43个字符；
• 行高或列宽设为0，将使单元格隐藏；

import openpyxl

wb = openpyxl.load_workbook("./wb_demo.xlsx")
sheet = wb["test_sheet"]

sheet.row_dimensions[2].height = 50 # 设置第2行 行高
sheet.column_dimensions['A'].width = 50 # 设置第A列 列宽

wb.save("./wb_demo.xlsx")

12 单元格拆分合并

• merge_cells() 将一个矩形区域的单元格合并为一个单元格
• unmerge_cells() 用于拆分单元格

import openpyxl

wb = openpyxl.load_workbook("./wb_demo.xlsx")
sheet = wb["test_sheet"]

sheet.merge_cells('A2:B3')
sheet['A2'] = "合并后的内容"

# sheet.unmerge_cells('A2:B3')

wb.save("./wb_demo.xlsx")

13 冻结窗口

• 悬停顶部几行或最左侧几列；
• sheet对象的freeze_panes属性，可以设置为一个Cell对象或一个单元格坐标字符串；
• 单元格上边的所有行和左边的所有列都会被冻结，但单元格所在行和列不会冻结；

属性值	受影响的行列
=‘A2’	行1
=‘B1’	列A
=‘C1’	列A和列B
=‘C2’	行1和列A和列B
=‘A1’ 或 =None	解冻所有单元格

14 添加绘制图表

利用工作表中数据创建 条形图、折线图、散点图和饼图：

• 创建一个Reference对象，表示作用于图表的数据区域；
• 创建图表对象
• 向图表对象中添加数据
• 将图表添加到指定sheet中

python_openpyxl中的48种图表样式参考

Python办公自动化—用openpyxl制作Excel图表

import openpyxl
from openpyxl.chart.legend import LegendEntry

wb = openpyxl.load_workbook("./youtube_mp3_ds_test.xlsx")
sheet = wb.active

# 仅划定数据区域
values = openpyxl.chart.Reference(sheet, min_row=2, min_col=2, max_row=5, max_col=3)

# chart = openpyxl.chart.BarChart()
chart = openpyxl.chart.LineChart()
# chart = openpyxl.chart.ScatterChart()
# chart = openpyxl.chart.PieChart()
chart.title = 'Bar for cls'
chart.x_axis.title = 'cls'
chart.y_axis.title = 'num'
# 图例的位置可以通过设置其位置来控制：
# 右、左、上、下和右上分别为r、l、t、b和tr。默认值为r
# chart.legend.position = 'tr'

## chart.add_data(values)

## 设置图例（注意 通过以下方式设置数据 不可与”chart.add_data(values)“共用
## 否则会导致新图例与”chart.add_data(values)“生成的默认图例共存）
for index in range(2,4):
    name = sheet.cell(row=1, column=index).value
    seriesObj = openpyxl.chart.Series(list(values.cols)[index-2], title=name)
    chart.append(seriesObj)

# 以第1列 作为x轴展示(需要add_data后设置)
x_label = openpyxl.chart.Reference(sheet, min_row=2, min_col=1, max_row=5, max_col=1)
chart.set_categories(x_label)

sheet.add_chart(chart, 'I1') # I1 表示放置位置的单元格
wb.save("./youtube_mp3_ds_test1.xlsx")