python 使用 openpyxl 操作 excel

• openpyxl 最好用的 python 操作 excel 表格库，不接受反驳（想反驳自己去学其他的）
• openpyxl 官网链接：https://openpyxl.readthedocs.io/en/stable/
• openpyxl 只支持【.xlsx / .xlsm / .xltx / .xltm】格式的文件
• 建议在jupyter-notebook里面操作

打开 Excel 表格并获取表格名称；通过 sheet 名称获取表格

from openpyxl import load_workbook 
workbook = load_workbook(filename = "test.xlsx") 
workbook.sheetnames #打开 Excel 表格并获取表格名称
sheet = workbook["Sheet1"] #通过 sheet 名称获取表格
sheet.dimensions # 获取表格的尺寸大小(几行几列数据)

获取表格内某个格子的数据

workbook = load_workbook(filename = "test.xlsx") 
sheet = workbook.active #打开激活的表格
print(sheet) 
cell1 = sheet["A1"] #获取 A1 格子的数据
cell2 = sheet["C11"] 
cell3 = sheet.cell(row = 1,column = 1) #通过指定行列号获取格子数据
cell4 = sheet.cell(row = 11,column = 3)
print(cell1.value, cell1.row, cell1.column, cell1.coordinate) 
#获取格子中的值、行数、列数、坐标；
sheet["A"] --- 获取 A 列的数据
sheet["A:C"] --- 获取 A,B,C 三列的数据
sheet[5] --- 只获取第 5 行的数据
# 获取 A1:C2 区域的值
cell = sheet["A1:C2"] 
print(cell) 
for i in cell: 
  for j in i: 
    print(j.value)

• .iter_rows()方式（类似pandas里面的iterrows）有.iter_rows()方式，肯定也会有.iter_cols()方式，只不过一个是按行读取，一个是按
  列读取。

workbook = load_workbook(filename = "test.xlsx") 
sheet = workbook.active 
print(sheet) 
# 按行获取值
for i in sheet.iter_rows(min_row=2, max_row=5, min_col=1, max_col=2): #按行读取
  for j in i: 
    print(j.value)
# 按列获取值
for i in sheet.iter_cols(min_row=2, max_row=5, min_col=1, max_col=2): #按列读取
  for j in i: 
    print(j.value)
for i in sheet.rows: #获取所有行
  print(i)

修改表格中的内容: 向某个格子中写入内容并保存

workbook = load_workbook(filename = "test.xlsx") 
sheet = workbook.active 
print(sheet)
sheet["A1"] = "哈喽" 
# 这句代码也可以改为 cell = sheet["A1"] cell.value = "哈喽" 
workbook.save(filename = "哈喽.xlsx") 
""" 
注意：我们将“A1”单元格的数据改为了“哈喽”，并另存为了“哈喽.xlsx”文
件。 如果我们保存的时候，不修改表名，相当于直接修改源文件；
"""

• .append()方式：会在表格已有的数据后面，按行插入数据(很有用)；

workbook = load_workbook(filename = "test.xlsx") 
sheet = workbook.active
print(sheet) 
data = [ 
["唐僧","男","180cm"], 
["孙悟空","男","188cm"], 
["猪八戒","男","175cm"], 
["沙僧","男","176cm"], 
] 
for row in data: 
  sheet.append(row) 
workbook.save(filename = "test.xlsx")

使用 excel 函数公式(很有用)

import openpyxl
from openpyxl.utils import FORMULAE 
print(FORMULAE)#python 支持写哪些“excel 函数公式”

# 这是我们在 excel 中输入的公式
=IF(RIGHT(C2,2)="cm",C2,SUBSTITUTE(C2,"m","")*100&"cm") 
# 那么，在 python 中怎么插入 excel 公式呢？
workbook = load_workbook(filename = "test.xlsx") 
sheet = workbook.active 
print(sheet) 
sheet["D1"] = "标准身高" 
for i in range(2,16): 
  sheet["D{}".format(i)] = 
  '=IF(RIGHT(C{},2)="cm",C{},SUBSTITUTE(C{},"m","")*100&"cm")'.format(i,i,i) 
workbook.save(filename = "test.xlsx")

.insert_cols()和.insert_rows()：插入空行和空列

• .insert_cols(idx=数字编号, amount=要插入的列数)，插入的位置是在 idx 列数的左侧插入；
• .insert_rows(idx=数字编号, amount=要插入的行数)，插入的行数是在 idx 行数的下方插入；

workbook = load_workbook(filename = "test.xlsx") 
sheet = workbook.active 
print(sheet) 
sheet.insert_cols(idx=4,amount=2) #从第4列开始插入2列
sheet.insert_rows(idx=5,amount=4) #第5行开始插入2行
workbook.save(filename = "test.xlsx")

.delete_rows()和.delete_cols()：删除行和列

• .delete_rows(idx=数字编号, amount=要删除的行数)
• .delete_cols(idx=数字编号, amount=要删除的列数)

workbook = load_workbook(filename = "test.xlsx") 
sheet = workbook.active print(sheet) 
# 删除第一列，第一行
sheet.delete_cols(idx=1) 
sheet.delete_rows(idx=1) 
workbook.save(filename = "test.xlsx")

.move_range()：移动格子

• .move_range("数据区域",rows=,cols=)：row正整数表示向下、负整数表示向上移动；cols正整数表示向右、负整数表示向左移动。

sheet.move_range("C1:D4",rows=2,cols=-1)# 向左移动两列，向下移动两行

.create_sheet()：创建新的 sheet 表格

• .create_sheet("新的 sheet 名")：创建一个新的 sheet 表；

workbook = load_workbook(filename = "test.xlsx") 
sheet = workbook.active 
print(sheet) 
workbook.create_sheet("我是一个新的 sheet") 
print(workbook.sheetnames) 
workbook.save(filename = "test.xlsx")

.remove()：删除某个 sheet 表

• .remove("sheet 名")：删除某个 sheet 表；

workbook = load_workbook(filename = "test.xlsx") 
sheet = workbook.active print(workbook.sheetnames) 
# 这个相当于激活的这个 sheet 表，激活状态下，才可以操作；
sheet = workbook['我是一个新的 sheet'] 
print(sheet) 
workbook.remove(sheet) 
print(workbook.sheetnames) 
workbook.save(filename = "test.xlsx")

.copy_worksheet()：复制一个 sheet 表到另外一张 excel 表

• 这个操作的实质，就是复制某个 excel 表中的 sheet 表，然后将文件存储到另外一张excel 表中

workbook = load_workbook(filename = "a.xlsx") 
sheet = workbook.active 
print("a.xlsx 中有这几个 sheet 表",workbook.sheetnames) 
sheet = workbook['姓名'] 
workbook.copy_worksheet(sheet) 
workbook.save(filename = "test.xlsx")

sheet.title：修改 sheet 表的名称

workbook = load_workbook(filename = "a.xlsx") 
sheet = workbook.active 
print(sheet) 
sheet.title = "我是修改后的 sheet 名" 
print(sheet)

创建新的 excel 表格文件

from openpyxl import Workbook 
workbook = Workbook() 
sheet = workbook.active 
sheet.title = "表格 1" 
workbook.save(filename = "新建的 excel 表格")

sheet.freeze_panes：冻结窗口

• .freeze_panes = "单元格"

workbook = load_workbook(filename = "花园.xlsx") 
sheet = workbook.active print(sheet) sheet.freeze_panes = "C3" 
workbook.save(filename = "花园.xlsx") 
""" 
冻结窗口以后，你可以打开源文件，进行检验；
"""

sheet.auto_filter.ref：给表格添加“筛选器”

• .auto_filter.ref = sheet.dimension 给所有字段添加筛选器；
• .auto_filter.ref = "A1" 给 A1 这个格子添加“筛选器”，就是给第一列添加“筛选器”；

workbook = load_workbook(filename = "花园.xlsx") 
sheet = workbook.active 
print(sheet) 
sheet.auto_filter.ref = sheet["A1"] 
workbook.save(filename = "花园.xlsx")

批量调整字体和样式

1）修改字体样式

• Font(name=字体名称,size=字体大小,bold=是否加粗,italic=是否斜体,color=字体颜色)

from openpyxl.styles import Font 
from openpyxl import load_workbook 
workbook = load_workbook(filename="花园.xlsx") 
sheet = workbook.active 
cell = sheet["A1"] 
font = Font(name="微软雅黑",size=20,bold=True,italic=True,color="FF0000") 
cell.font = font
workbook.save(filename = "花园.xlsx") 
""" 
这个 color 是 RGB 的 16 进制表示，自己下去百度学习；
"""

2）获取表格中格子的字体样式

from openpyxl.styles import Font 
from openpyxl import load_workbook 
workbook = load_workbook(filename="花园.xlsx") 
sheet = workbook.active 
cell = sheet["A2"] 
font = cell.font 
print(font.name, font.size, font.bold, font.italic, font.color)

3）设置对齐样式

• Alignment(horizontal=水平对齐模式,vertical=垂直对齐模式,text_rotation=旋转角
  度,wrap_text=是否自动换行)
• 水平对齐：‘distributed'，‘justify'，‘center'，‘leftfill'， ‘centerContinuous'，‘right，
  ‘general'；
• 垂直对齐：‘bottom'，‘distributed'，‘justify'，‘center'，‘top'；

from openpyxl.styles import Alignment 
from openpyxl import load_workbook 
workbook = load_workbook(filename="花园.xlsx") 
sheet = workbook.active 
cell = sheet["A1"]
alignment = 
Alignment(horizontal="center",vertical="center",text_rotation=45,wrap_text=True) 
cell.alignment = alignment 
workbook.save(filename = "花园.xlsx")

4）设置边框样式

• Side(style=边线样式，color=边线颜色)
• Border(left=左边线样式，right=右边线样式，top=上边线样式，bottom=下边线样式)
• style 参数的种类： 'double, 'mediumDashDotDot', 'slantDashDot', 'dashDotDot','dotted','hair',
  'mediumDashed, 'dashed', 'dashDot', 'thin', 'mediumDashDot','medium', 'thick'

from openpyxl.styles import Side,Border 
from openpyxl import load_workbook 
workbook = load_workbook(filename="花园.xlsx") 
sheet = workbook.active 
cell = sheet["D6"]
side1 = Side(style="thin",color="FF0000") 
side2 = Side(style="thick",color="FFFF0000") 
border = Border(left=side1,right=side1,top=side2,bottom=side2) 
cell.border = border 
workbook.save(filename = "花园.xlsx")

5）设置填充样式

• PatternFill(fill_type=填充样式，fgColor=填充颜色）
• GradientFill(stop=(渐变颜色 1，渐变颜色 2……))

from openpyxl.styles import PatternFill,GradientFill 
from openpyxl import load_workbook 
workbook = load_workbook(filename="花园.xlsx") 
sheet = workbook.active 
cell_b9 = sheet["B9"] 
pattern_fill = PatternFill(fill_type="solid",fgColor="99ccff") 
cell_b9.fill = pattern_fill 
cell_b10 = sheet["B10"]
gradient_fill = GradientFill(stop=("FFFFFF","99ccff","000000")) 
cell_b10.fill = gradient_fill 
workbook.save(filename = "花园.xlsx")

6）设置行高和列宽

• .row_dimensions[行编号].height = 行高
• .column_dimensions[列编号].width = 列宽

workbook = load_workbook(filename="花园.xlsx") 
sheet = workbook.active 
# 设置第 1 行的高度
sheet.row_dimensions[1].height = 50 #将整个表的行高设置为 50
# 设置 B 列的宽度
sheet.column_dimensions["B"].width = 20 #列宽设置为 30；
workbook.save(filename = "花园.xlsx")

7）合并单元格

• .merge_cells(待合并的格子编号)
• .merge_cells(start_row=起始行号，start_column=起始列号，end_row=结束行号，
  end_column=结束列号)

workbook = load_workbook(filename="花园.xlsx")
sheet = workbook.active sheet.merge_cells("C1:D2") 
sheet.merge_cells(start_row=7,start_column=1,end_row=8,end_column=3) 
workbook.save(filename = "花园.xlsx")

当然，也有“取消合并单元格”，用法一致。

• .unmerge_cells(待合并的格子编号)
• .unmerge_cells(start_row=起始行号，start_column=起始列号，end_row=结束行号，
  end_column=结束列号)