Python openpyxl库

1. 读写单元格

from openpyxl import load_workbook

wb = load_workbook('tmp.xlsx')

# 获取活动的表单
sheet = wb.active

# 读取第三行第一列的值，行和列都是从1开始计数的。
value = sheet.cell(row=3, column=1).value
print(value)

# 写入第三行第七列
sheet.cell(row=3, column=7).value = 123

wb.save('tmp.xlsx')
wb.close()
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

注意：写完之后必须保存才能生效。

2. 创建表单

from openpyxl import Workbook

wb = Workbook()

# 创建表单
wb.create_sheet('sheet1')
wb.create_sheet('sheet2')

# 选择表单
sheet2 = wb['sheet2']
print(sheet2)

wb.save('file.xlsx')
wb.close()
1
2
3
4
5
6
7
8
9
10
11
12
13
14

3. 遍历表单

from openpyxl import load_workbook

wb = load_workbook('tmp.xlsx')
sheet = wb.active

# 遍历 3~10 行，包括首尾
for row in sheet.iter_rows(min_row=3, max_row=10):
    for cell in row:
        print(cell.value, end=',')
    print()
        
    wb.close()
1
2
3
4
5
6
7
8
9
10
11
12

遍历的同时进行写入：

from openpyxl import load_workbook

wb = load_workbook('tmp.xlsx')
sheet = wb.active

for row in sheet.iter_rows(min_row=3, max_row=10):
	# row 的下标从 0 开始
    print(row[0].value)
    row[1].value = 'abc'

wb.save('tmp.xlsx')
wb.close()
1
2
3
4
5
6
7
8
9
10
11
12

遍历一个区域：

from openpyxl import load_workbook

wb = load_workbook('tmp.xlsx')
sheet = wb.active

# 包括首尾
for row in sheet.iter_rows(min_row=3, max_row=10, min_col=1, max_col=3):
    for cell in row:
        print(cell.value, end=',')
    print()

wb.close()
1
2
3
4
5
6
7
8
9
10
11
12

4. 合并单元格

from openpyxl import load_workbook

wb = load_workbook('tmp.xlsx')
sheet = wb.active

# 合并指定的区域
sheet.merge_cells(start_row=1, end_row=2, start_column=8, end_column=10)

# 通过起始行和起始列来指定合并后的单元格
sheet.cell(row=1, column=8).value = 'Hello, World!'

wb.save('tmp.xlsx')
wb.close()
1
2
3
4
5
6
7
8
9
10
11
12
13

5. 拆分单元格

from openpyxl import load_workbook

wb = load_workbook('tmp.xlsx')
sheet = wb.active

sheet.unmerge_cells(start_row=1, end_row=2, start_column=8, end_column=10)

wb.save('tmp.xlsx')
wb.close()
1
2
3
4
5
6
7
8
9