openpyxl的使用

Python openpyxl的使用

安装

运行以下命令，安装openpyxl包

python -m pip install openpyxl

对象

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

Python对象	Excel实体
Workbook	工作簿
Worksheet	工作表
Cell	单元格

新建工作簿

通过新建一个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	插入的位置
0	插入到首位(正数第0位)
1	插入到第1位
2	插入到第2位
3	插入到第3位
...	插入到第...位
-3	插入到倒数第3位
-2	插入到倒数第2位
-1	插入到倒数第1位
None 或者不填	插入到最未位（倒数第0位）

总结

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)

posted @ 2023-11-02 14:42 南风丶轻语阅读(205) 评论(0) 编辑收藏举报

刷新页面返回顶部

南风丶轻语