Python openpyxl的使用

安装

运行以下命令，安装openpyxl包

python -m pip install openpyxl

对象

在openpyxl中，有以下几种对象，分别对应不同的Excel实体

新建工作簿

• 通过新建一个Workbook()对象，即可新建一个工作簿
• 通过wb.save("create_workbook.xlsx")保存为文件
• 文件其实就是工作簿，一个工作簿可以有多个工作表，但最少得有一个工作表
• 默认创建新的工作簿时，会自动创建一个名为Sheet的工作表

from openpyxl import Workbook
wb = Workbook()
wb.save("create_workbook.xlsx")

这样就会生成一个xlsx文件，工作表的名称默认为Sheet

新建工作表

• 通过wb.create_sheet(title)新建工作表

from openpyxl import Workbook

wb = Workbook()
wb.create_sheet("Sheet1")
wb.create_sheet("Sheet2")
wb.create_sheet("Sheet3")
wb.create_sheet("Sheet4")
wb.save("create_workbook.xlsx")

默认创建给定名称的工作表，创建出来工作表的顺序都是插入到最后面的

因为工作簿是新创建的，所以默认带有一个Sheet的工作表

插入工作表

• 通过wb.create_sheet(title,index)插入工作表
• 其实就是创建工作表，只是创建时，通过指定index，来指定插入的位置

PS:起始按照第0位，第1位，第2位，...，倒数为倒数第0位，倒数第1位，倒数第2位，...，这样去对照下表理解

总结

• index为正数，表示从前往后插入，插入到第index位
• index为负数，表示从后往前插入，插入到倒数第index位
• index为None，表示插入到最后面（倒数第0位）
• index为0，表示插入到最前面（正数第0位）

from openpyxl import Workbook

wb = Workbook()
wb.create_sheet("Sheet1")
wb.create_sheet("Sheet2")
wb.create_sheet("Sheet3")
wb.create_sheet("Sheet4")
wb.create_sheet("MySheet0", 0)
wb.create_sheet("MySheet1", 1)
wb.create_sheet("MySheet2", 2)
wb.create_sheet("MySheet3", 3)
wb.create_sheet("MySheet-1", -1)
wb.create_sheet("MySheet-2", -2)
wb.create_sheet("MySheet-3", -3)
wb.create_sheet("MySheet-last", None)
wb.save("create_workbook.xlsx")

打开已存在的工作簿

• 通过load_workbook()打开已存在的xlsx或xlsm文件

from openpyxl import load_workbook
from openpyxl.workbook import Workbook

filename = "insert_workbook.xlsx"
wb: Workbook = load_workbook(filename)
sheet_names = wb.sheetnames
for name in sheet_names:
    print(f"name:{name}")

获取所有的工作表名称

• 通过wb.sheetnames获取所有的工作表名称

from openpyxl import load_workbook
from openpyxl.workbook import Workbook

filename = "insert_workbook.xlsx"
wb: Workbook = load_workbook(filename)
sheet_names = wb.sheetnames
for name in sheet_names:
    print(f"name:{name}")

选中工作表

要操作一个工作表，首先要选中它，有以下几种方式选中工作表

wb.active

• 通过wb.active选中工作表
• 通过ws.title获取工作表的名称

from openpyxl import load_workbook
from openpyxl.workbook import Workbook
from openpyxl.worksheet.worksheet import Worksheet

filename = "insert_workbook.xlsx"
wb: Workbook = load_workbook(filename)
ws: Worksheet = wb.active
print(f"类型是:{ws}")
print(f"title:{ws.title}")

wb[sheetname]

• 通过wb[工作表名称]获取工作表
• 通过ws.title获取工作表的名称

遍历工作簿获取工作表

可以通过遍历工作簿的方式，获取工作表

from openpyxl import load_workbook
from openpyxl.workbook import Workbook
from openpyxl.worksheet.worksheet import Worksheet

filename = "insert_workbook.xlsx"
wb: Workbook = load_workbook(filename)
for sheet in wb:
    ws: Worksheet = sheet
    # print(f"类型是:{ws}")
    print(f"title:{ws.title}")

修改工作表

选中工作表后，就可以修改工作表的各种属性了

修改名称

通过ws.title="xxx"修改工作表名称

from openpyxl import load_workbook
from openpyxl.workbook import Workbook
from openpyxl.worksheet.worksheet import Worksheet

filename = "insert_workbook.xlsx"
wb: Workbook = load_workbook(filename)
ws: Worksheet = wb["MySheet0"]
print(f"原来的名称:{ws.title}")
ws.title = "UpdateSheet"
print(f"修改后的名称:{ws.title}")
wb.save("update.xlsx")

修改名称背景

通过ws.sheet_properties.tabColor = "xxxx"修改工作表名称背景颜色

• xxxx使用RRGGBB颜色，可以来这里复制张贴HTML颜色代码表 (rapidtables.org)

from openpyxl import load_workbook
from openpyxl.workbook import Workbook
from openpyxl.worksheet.worksheet import Worksheet

filename = "insert_workbook.xlsx"
wb: Workbook = load_workbook(filename)
ws: Worksheet = wb["MySheet0"]
ws.sheet_properties.tabColor = "F08080"
wb.save("update.xlsx")

访问单个单元格

可以通过以下几种方式访问单个单元格

通过键的方式

• 通过ws['键名']的方式访问，获取到的对象类型是Cell

• 通过Cell.value获取到真正的值

from openpyxl import load_workbook
from openpyxl.cell import Cell
from openpyxl.worksheet.worksheet import Worksheet

wb = load_workbook("src.xlsx")
ws: Worksheet = wb['Sheet']
v: Cell = ws['A1']
print(v.value)
v: Cell = ws['A100']
print(v.value)
v: Cell = ws['AA1']
print(v.value)
v: Cell = ws['AB1000']
print(v.value)

通过.cell方法

• 通过ws.cell(row,column)的方式访问，获取到的对象类型是Cell

• row和column都是大于0的整数，即最小是(1,1)

• 通过Cell.value获取到真正的值

from openpyxl import load_workbook
from openpyxl.cell import Cell
from openpyxl.worksheet.worksheet import Worksheet

wb = load_workbook("src.xlsx")
ws: Worksheet = wb['Sheet']

v: Cell = ws.cell(1, 1)
print(v.value)
v: Cell = ws.cell(14, 27)
print(v.value)
v: Cell = ws.cell(100, 28)
print(v.value)

访问大量单元格

可以通过以下几种方式访问大量单元格

访问某一列

• 通过ws["列名"]访问某一列
• 列名可以是A，B，C，AA，AB，...
• 返回是元祖，元祖内是Cell对象
• 通过Cell.value获取到真正的值

from openpyxl import load_workbook
from openpyxl.worksheet.worksheet import Worksheet

wb = load_workbook("src.xlsx")
ws: Worksheet = wb['Sheet']

a = ws['A']
for cell in a:
    print(cell.value)

访问某几列

• 通过ws["列名:列名"]的形式可以访问多列，返回是以列组成的元祖，元素类型还是Cell
• 通过Cell.value获取到真正的值
• 传递的列名可以是字母，可以是数字

列名是字母的示例

from openpyxl import load_workbook
from openpyxl.worksheet.worksheet import Worksheet

wb = load_workbook("src.xlsx")
ws: Worksheet = wb['Sheet']

a = ws['A:B']
for cell in a:
    print(cell)
a = ws['A:AB']
for cell in a:
    print(cell)

列名是数字的示例

from openpyxl import load_workbook
from openpyxl.worksheet.worksheet import Worksheet

wb = load_workbook("src.xlsx")
ws: Worksheet = wb['Sheet']

a = ws['1:2']
for cell in a:
    print(cell)
a = ws['1:28']
for cell in a:
    print(cell)

访问多行多列

• 通过ws.iter_rows 的形式可以访问多行多列，返回的是Cell迭代器
• 返回的数据按行排序，即顺序是A1,B1,C1.....A2,B2,C2,...,A3,B3,C3
• 通过Cell.value获取到真正的值

示例：访问1-10行，A-AB列

from openpyxl import load_workbook
from openpyxl.worksheet.worksheet import Worksheet

wb = load_workbook("src.xlsx")
ws: Worksheet = wb['Sheet']

a = ws.iter_rows(min_row=1, max_row=10, min_col=1, max_col=28)
for one in a:
    print(one)

访问多列多行

• 和访问多行多列一样，只是排序方式为列优先

• 通过ws.iter_cols的形式可以访问多行多列，返回的是Cell迭代器

• 返回的数据按行排序，即顺序是A1,A2,A3,....,B1,B2,B3,...,C1,C2,C3,...

• 通过Cell.value获取到真正的值

示例：访问1-10行，A-AB列

from openpyxl import load_workbook
from openpyxl.worksheet.worksheet import Worksheet

wb = load_workbook("src.xlsx")
ws: Worksheet = wb['Sheet']

a = ws.iter_cols(min_row=1, max_row=10, min_col=1, max_col=28)
for one in a:
    print(one)

访问某一行

• 在访问多行多列的方式中，把行固定，就是访问某一行

示例：访问第2行

from openpyxl import load_workbook
from openpyxl.worksheet.worksheet import Worksheet

wb = load_workbook("src.xlsx")
ws: Worksheet = wb['Sheet']

a = ws.iter_rows(min_row=2, max_row=2, min_col=1, max_col=28)
for one in a:
    print(one)

访问所有的数据

• 通过ws.rows遍历所有的数据

• 遍历出来的数据以行优先排列，即A1,B1,C1.....A2,B2,C2,...,A3,B3,C3

from openpyxl import load_workbook
from openpyxl.worksheet.worksheet import Worksheet

wb = load_workbook("src.xlsx")
ws: Worksheet = wb['Sheet']

a = ws.rows
for one in a:
    print(one)

• 通过ws.columns遍历所有的数据

• 遍历出来的数据以列优先排列，即A1,A2,A3,....,B1,B2,B3,...,C1,C2,C3,...

from openpyxl import load_workbook
from openpyxl.worksheet.worksheet import Worksheet

wb = load_workbook("src.xlsx")
ws: Worksheet = wb['Sheet']

a = ws.columns
for one in a:
    print(one)

仅访问工作表的值

访问单个单元格和大量单元格，返回的都是Cell对象，Cell对象就是单元格，可以通过Cell对象，获取单元格的属性，例如：颜色，背景，边框等。

如果只想访问单元格的值，而不关心单元格的其他属性，则可以这样获取

• 通过ws.values获取所有的值

from openpyxl import load_workbook
from openpyxl.worksheet.worksheet import Worksheet

wb = load_workbook("src.xlsx")
ws: Worksheet = wb['Sheet']

a = ws.values
for one in a:
    print(one)

或者这样

• 通过访问多行多列或访问多列多行时，传递参数values_only=True即可

from openpyxl import load_workbook
from openpyxl.worksheet.worksheet import Worksheet

wb = load_workbook("src.xlsx")
ws: Worksheet = wb['Sheet']

a = ws.iter_rows(min_row=1, max_row=10, min_col=1, max_col=28,values_only=True)
for one in a:
    print(one)

或

from openpyxl import load_workbook
from openpyxl.worksheet.worksheet import Worksheet

wb = load_workbook("src.xlsx")
ws: Worksheet = wb['Sheet']

a = ws.iter_cols(min_row=1, max_row=10, min_col=1, max_col=28,values_only=True)
for one in a:
    print(one)

修改数据

选中单元格cell后，可以修改它的数据

• 通过cell.value="xxx"修改单元格的值

• 通过ws.cell(row, cloumn, value)修改单元格的值

from openpyxl import load_workbook
from openpyxl.cell import Cell
from openpyxl.worksheet.worksheet import Worksheet

wb = load_workbook("src.xlsx")
ws: Worksheet = wb['Sheet']

cell: Cell = ws['A1']
print(f'A1原始的值{cell.value}')
cell.value = 100
print(f'A1修改后的值{cell.value}')

print(f"C2原始的值:{ws.cell(2, 3).value}")
ws.cell(2, 3, 200)
print(f"C2修改后的值:{ws.cell(2, 3).value}")
wb.save("update.xlsx")

保存到文件

普通文件

• 通过wb.save()保存Workbook对象到文件

from openpyxl import Workbook
wb = Workbook()
wb.save("create_workbook.xlsx")

或

from openpyxl import load_workbook

wb = load_workbook("src.xlsx")
wb.save("update.xlsx")

注意事项

模板文件

• 指定属性 template=True 将工作表保存为模板

from openpyxl import load_workbook
from openpyxl.workbook import Workbook

wb: Workbook = load_workbook("src.xlsx")
wb.template = True
wb.save("template.xltm")

• 指定属性 template=False将模板文件保存为普通文件

from openpyxl import load_workbook
from openpyxl.workbook import Workbook

wb: Workbook = load_workbook("template.xltm")
wb.template = False
wb.save("new.xlsx")

注意事项

• 需要保存为后缀名相同的文件

• 打开xlsm需要传递参数keep_vba=True

• 模板文件后缀名应该为xltm

添加一行数据

• 通过ws.append()添加一行数据
• 添加的数据在所有数据的最后面
• 可以理解为就是列表添加数据

from openpyxl import load_workbook
from openpyxl.workbook import Workbook
from openpyxl.worksheet.worksheet import Worksheet

wb: Workbook = load_workbook("src.xlsx")

ws: Worksheet = wb.active
for i in range(5):
    ws.append(range(10))
wb.save("a.xlsx")

数字转换为字母

把数字转成字母，例如1就是A列，2就是B列，C就是C列,26就是Z列

from openpyxl.utils import get_column_letter

print(f"1对应的列是{get_column_letter(1)}")
print(f"25对应的列是{get_column_letter(25)}")
print(f"26对应的列是{get_column_letter(26)}")
print(f"30对应的列是{get_column_letter(30)}")
print(f"42对应的列是{get_column_letter(42)}")

插入空行

通过ws.insert_rows(index, amount)插入空行

• index表示插入的位置

• amount表示插入的行数

示例：插入到第3行，插入2行空行

from openpyxl import Workbook
from openpyxl import load_workbook
from openpyxl.worksheet.worksheet import Worksheet

wb: Workbook = load_workbook("src.xlsx")
ws: Worksheet = wb.active
for one in ws.values:
    print(one)

ws.insert_rows(3, 2)
for one in ws.values:
    print(one)

插入空列

通过ws.insert_cols(index, amount)插入空列

• index表示插入的位置

• amount表示插入的列数

示例：插入到第三列，插入2列空列

from openpyxl import Workbook
from openpyxl import load_workbook
from openpyxl.worksheet.worksheet import Worksheet

wb: Workbook = load_workbook("src.xlsx")
ws: Worksheet = wb.active
for one in ws.values:
    print(one)

ws.insert_cols(3, 2)
for one in ws.values:
    print(one)
wb.save("a.xlsx")

删除行和删除列

• 通过ws.delete_rows(index,amount)删除行
• 通过ws.delete_cols(index,amount)删除列
• index表示要删除的行或列位置
• amount表示要删除的行数或列数

from openpyxl import Workbook
from openpyxl import load_workbook
from openpyxl.worksheet.worksheet import Worksheet

wb: Workbook = load_workbook("src.xlsx")
ws: Worksheet = wb.active
for one in ws.values:
    print(one)

ws.delete_rows(2,2)
ws.delete_cols(2,2)
wb.save("a.xlsx")

链接

教程 — openpyxl 3.0.7 文档 (openpyxl-chinese-docs.readthedocs.io)

本栏目推荐文章
• openpyxl模块---------------------------------------------提取身份证信息及计算年龄
• openpyxl模块--------------------------------重复数据
• openpyxl模块------------------------------------------统计加班时间
• openpyxl模块---------------------------------隔行变色
• openpyxl模块-----------------------打印工资条
• Python用openpyxl操作Excel问题集
• openpyxl------------多excel操作
• openpyxl模块创建excel
• openpyxl模块---读取数据
• Openpyxl操作Excel

openpyxlopenpyxl excel openpyxl python excel openpyxl教程 openpyxl常用python openpyxl文件excel 模块openpyxl数据 openpyxl bug 模块openpyxl 单元openpyxl 维度openpyxl数据