二十四、openpyxl

一. openpyxl

  • openpyxl 用于读写 Excel2010、xlsx、xlsm、xltx、xltm 文件
  • 它的诞生是因为缺少可从 Python 本地读取/写入 office open xml 格式的库

1.1 安装

1
pip install openpyxl

1.2 基本概念

  • 工作簿(Workbook): 包含一个或多个工作表,
    • 使用 Wrokbook() 打开或创建工作簿;
  • 工作表(Worksheet): 由一系列行和列组成,
    • 使用 active 属性获取当前活动工作表,
    • 使用 worksheets 属性获取工作薄中的所有工作表
  • 单元格(Cell): 用于存储数据的最小单元,
    • 可以使用单元格坐标来读写数据, 如: sheet['A1'] 表示单元格 “A1”
  • 行(Row): 工作表中的水平序列, 行号从 1 开始,
    • 可以使用行号读写数据, 如 sheet[1] 表示第一行
  • 列(Column): 工作表中的垂直序列, 列号从 A 开始,
    • 可以使用列号读写数据, 如 `sheet[‘A’] 表示 A 列
  • 单元格范围(Cell Range): 工作表中连续的单元格区域, 由左上角和右下角单元格坐标定义

二. 工作簿操作

2.1 创建工作簿

1
2
3
4
from openpyxl import Workbook

wb = Workbook()   # 实例化
ws = wb.active    # 激活 工作表

2.2 打开已有的工作簿

1
2
3
4
5
from openpyxl import load_workbook

wb = load_workbook('文件名.xlsx')

wb.save("文件名.xlsx")

三. 表操作

3.1 创建表

3.1.1 插入到最后(默认)

1
2
3
4
5
6
7
8
from openpyxl import load_workbook

wb = load_workbook('test.xlsx')   # 打开已有的工作簿
wb.create_sheet("sheet1")   # 创建工作表

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

print(wb.sheetnames)  # ['Sheet', 'sheet1']

3.1.2 插入到最开始的位置

1
2
3
4
5
6
7
8
from openpyxl import load_workbook

wb = load_workbook('test.xlsx')   # 打开已有的工作簿
wb.create_sheet("sheet2", 0)   # 创建工作表

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

print(wb.sheetnames)  # ['sheet2', 'Sheet', 'sheet1']

3.2 选择表

sheet 名称可以作为 key 进行索引

1
2
ws = wb["New Title"]
ws = wb.get_sheet_by_name("New Title")

3.3 修改工作表的名称

1
2
3
4
5
6
7
8
9
10
from openpyxl import load_workbook

wb = load_workbook('test.xlsx')   # 打开已有的工作簿
print(wb.sheetnames)    # ['sheet2', 'Sheet', 'sheet1', 'sheet11']

ws = wb["sheet1"]
ws.title = '测试 1'   # 修改工作表名称

wb.save("test.xlsx")  # 保存工作薄
print(wb.sheetnames)  # ['sheet2', 'Sheet', '测试 1', 'sheet11']

3.4 删除工作表

1
2
3
4
5
6
7
8
9
from openpyxl import load_workbook

wb = load_workbook('test.xlsx')   # 打开已有的工作簿
print(wb.sheetnames)    # ['sheet2', 'Sheet', '测试 1', 'sheet11']

del wb['sheet11']   # 删除工作表

wb.save("test.xlsx")  # 保存工作薄
print(wb.sheetnames)  # ['sheet2', 'Sheet', '测试 1']

四. 写入数据

4.1 按单元格写入

1
2
3
ws.cell(row, column, values)
ws.cell(row, column).value = values
ws['J1'] = values
  • row: 工作表的行号, 第一行从 1 开始
  • column: 工作表的列号, 第一列从 1 开始
  • values: 要写入单元格的数据
1
2
3
4
5
6
7
8
9
10
11
from openpyxl import load_workbook

wb = load_workbook('test.xlsx')   # 打开已有的工作簿
print(wb.sheetnames)    # ['sheet2', 'Sheet', '测试 1', 'sheet11']

ws = wb['测试 1']
ws.cell(1, 1, '张三')
ws.cell(2, 2).value = '李四'
ws['C3'] = '王五'

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

4.2 按行写入

1
2
3
4
5
6
7
8
9
10
11
from openpyxl import load_workbook

wb = load_workbook('test.xlsx')   # 打开已有的工作簿
print(wb.sheetnames)    # ['sheet2', 'Sheet', '测试 1']

ws = wb['测试 1']
data = (['赵', '钱', '孙', '李'], ['周', '吴', '郑', '王'])
for i in data:
    ws.append(i)

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

五. 读取数据

5.1 获取工作表中已有全部数据

  • .values: 获取目标工作表中已有全部数据, 返回值是一个对象, 需要进行转换
1
2
3
4
5
6
7
8
9
10
11
12
from openpyxl import load_workbook

wb = load_workbook('test.xlsx')   # 打开已有的工作簿

ws = wb['测试 1']

print(ws.values)
print(list(ws.values))

# 输出结果
<generator object Worksheet.values at 0x100e7c110>
[('张三', None, None, None), (None, '李四', None, None), (None, None, '王五', None), ('赵', '钱', '孙', '李'), ('周', '吴', '郑', '王')]
1
2
3
4
5
6
7
8
9
10
11
12
13
14
from openpyxl import load_workbook

wb = load_workbook('test.xlsx')   # 打开已有的工作簿

ws = wb['测试 1']
for row in ws.values:
    print(row)

# 输出结果
('张三', None, None, None)
(None, '李四', None, None)
(None, None, '王五', None)
('赵', '钱', '孙', '李')
('周', '吴', '郑', '王')

5.2 获取指定范围内的值

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
from openpyxl import load_workbook

wb = load_workbook('test.xlsx')   # 打开已有的工作簿
ws = wb['测试 1']

cells = ws["B2:C4"]
print(cells)

for row in cells:
    for cell in row:
        print(cell.value, end=", ")
    print()

# 结果输出
((<Cell '测试 1'.B2>, <Cell '测试 1'.C2>), (<Cell '测试 1'.B3>, <Cell '测试 1'.C3>), (<Cell '测试 1'.B4>, <Cell '测试 1'.C4>))
李四, None, 
None, 王五, 
钱, 孙,

5.3 获取指定列的值

1
2
3
4
5
6
7
8
9
10
11
12
13
14
from openpyxl import load_workbook

wb = load_workbook('test.xlsx')   # 打开已有的工作簿
ws = wb['测试 1']

columns = ws['C']   # 多列 ['A:C']
print(columns)

for col in columns:
    print(col.value, end=', ')

# 结果输出
(<Cell '测试 1'.C1>, <Cell '测试 1'.C2>, <Cell '测试 1'.C3>, <Cell '测试 1'.C4>, <Cell '测试 1'.C5>)
None, None, 王五, 孙, 郑,

5.4 获取指定行的值

1
2
3
4
5
6
7
8
9
10
11
12
13
14
from openpyxl import load_workbook

wb = load_workbook('test.xlsx')   # 打开已有的工作簿
ws = wb['测试 1']

columns = ws[4]   # 多列 [3:4]
print(columns)

for col in columns:
    print(col.value, end=', ')

# 输出结果
(<Cell '测试 1'.A4>, <Cell '测试 1'.B4>, <Cell '测试 1'.C4>, <Cell '测试 1'.D4>)
赵, 钱, 孙, 李,

5.5 按行、列获取工作表中已有全部数据

5.5.1 .rows, 获取工作表中存在数据的所有行

1
2
3
4
5
6
7
8
9
10
11
12
13
14
wb = load_workbook('test.xlsx')   # 打开已有的工作簿
ws = wb['测试 1']

for row in ws.rows:
    for i in row:
        print(i.value, end=', ')
    print()

# 输出结果
张三, None, None, None, 
None, 李四, None, None, 
None, None, 王五, None, 
赵, 钱, 孙, 李, 
周, 吴, 郑, 王,

5.5.2 .columns, 获取工作表中存在数据的所有行

1
2
3
4
5
6
7
8
9
10
11
12
13
wb = load_workbook('test.xlsx')   # 打开已有的工作簿
ws = wb['测试 1']

for col in ws.columns:
    for i in col:
        print(i.value, end=', ')
    print()

# 输出结果 
张三, None, None, 赵, 周, 
None, 李四, None, 钱, 吴, 
None, None, 王五, 孙, 郑, 
None, None, None, 李, 王,