openpyxl的使用

Python openpyxl的使用

安装

运行以下命令，安装openpyxl包

python -m pip install openpyxl

image-20231031150026065

对象

在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

image-20231031164316681

新建工作表

  • 通过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的工作表

image-20231031152735640

插入工作表

  • 通过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")

image-20231031154014400

打开已存在的工作簿

  • 通过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}")

image-20231031170524758

选中工作表

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

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}")

image-20231031164930609

image-20231031162636461

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}")

image-20231031165124512

image-20231031165036590

修改工作表

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

修改名称

通过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")

image-20231031165617791

image-20231031165533342

修改名称背景

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

image-20231031165720452

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")

image-20231031170159977

访问单个单元格

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

通过键的方式

  • 通过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)

image-20231101103225989

image-20231101103304029

通过.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)

image-20231101103751444

访问大量单元格

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

访问某一列

  • 通过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)

image-20231101104449349

访问某几列

  • 通过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)

image-20231101104942718

列名是数字的示例

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)

image-20231101105704509

访问多列多行

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

  • 通过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)

image-20231101110400230

访问某一行

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

示例：访问第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)

image-20231101110615994

访问所有的数据

  • 通过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)

image-20231101111138311

  • 通过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)

image-20231101111250200

仅访问工作表的值

访问单个单元格和大量单元格，返回的都是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)

image-20231101111741099

或者这样

  • 通过访问多行多列或访问多列多行时，传递参数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)

image-20231101112004324

或

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)

image-20231101112053618

修改数据

选中单元格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")

image-20231102100640193

image-20231102100653753

保存到文件

普通文件

  • 通过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")

注意事项

image-20231102101039279

模板文件

  • 指定属性 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

image-20231102102100958

添加一行数据

  • 通过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")

image-20231102105702041

数字转换为字母

把数字转成字母，例如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)}")

image-20231102110305194

插入空行

通过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)

image-20231102140016951

插入空列

通过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")

image-20231102140927048

删除行和删除列

  • 通过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")

image-20231102141349709

链接

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

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