Python 操作 Excel 详解（openpyxl）

1 概述

官方文档：https://openpyxl.readthedocs.io

2 工作簿操作（新建/打开、保存）

from openpyxl import Workbook
from openpyxl import load_workbook

# 新建一个工作簿
wb = Workbook()
# 打开本地Excel文件，加r是为了解决转义的问题
# wb = load_workbook(r'C:\Users\Test\Desktop\test.xlsx')
# 保存工作簿，加r是为了解决转义的问题
wb.save(r'C:\Users\Test\Desktop\test.xlsx')
# 关闭工作簿，该方法在只读或只写模式下有用
wb.close()

3 工作表

3.1 工作表操作（创建、修改、移动、复制、删除）

from openpyxl import Workbook

# 新建一个工作簿
wb = Workbook()
# 获取默认的工作表
ws1 = wb.active
# 返回工作表名
print(ws1.title)
# 修改工作表名
ws1.title = 'test'
# 新建一个工作表create_sheet(self, title=None, index=None)
# title：工作表名称
# index：工作表位置，默认为None，表示在最后插入
wb.create_sheet('test1', 0)
wb.create_sheet()
# 通过表名获取工作表
ws2 = wb['test1']
# 将test工作表向右移动一位，-1为向左移动，+1为向右移动
wb.move_sheet(ws2, +1)
# 返回工作表的索引
print(wb.index(ws2))
# 复制工作表
wb.copy_worksheet(ws1)
# 删除工作表
del wb['test1']
# 返回所有工作表名
print(wb.sheetnames)

3.2 工作表信息

print(ws.max_row)  # 最大行数，例如16
print(ws.max_column)  # 最大列数，例如12
print(ws.dimensions)  # 已启用的单元格范围，例如A1:L16
print(ws.encoding)  # 编码类型，例如utf-8
print(ws.sheet_view)  # 对象信息

4 单元格

4.1 获取和修改单个单元格

from openpyxl import Workbook
import datetime

# 新建一个工作簿
wb = Workbook()
# 获取默认的工作表
ws = wb.active
# 返回基于给定坐标的单元格对象
cell1 = ws['A1']
cell2 = ws.cell(1, 1)

# 直接修改某个单元格的值
ws['A1'] = 42
# 追加一行
ws.append([1, 2, 3])
ws['A2'] = datetime.datetime.now()

# 先获取单元格对象然后再进行修改
cell3 = ws['A6']
cell3.value = 66
print(cell3, cell3.value)  # 输出：<Cell 'Sheet'.A6> 66

# 通过坐标修改某个单元格的值
cell4 = ws.cell(6, 1, 'python')
print(cell4, cell4.value)  # 输出：<Cell 'Sheet'.A6> python

1.如果使用cell(row, column, value)获取，第一个参数是行，第二个参数是列，下标都是从1开始，例如，ws[‘A6’]等同于ws.cell(6, 1)，但如果指定了第三个参数value，则修改了该单元格的值。
2.只要访问了一个cell就会被创建，不管是否赋值。

根据上面的方式，我们可以通过循环来准备一些数据：

from openpyxl import Workbook

wb = Workbook()
ws = wb.active

x = 1
for i in range(1, 11):
    for j in range(1, 6):
        ws.cell(i, j, x)
        x += 1
wb.save('test.xlsx')

4.2 获取多个单元格

4.2.1 通过范围取值

from openpyxl import Workbook

wb = Workbook()
ws = wb.active

x = 1
for i in range(1, 11):
    for j in range(1, 6):
        ws.cell(i, j, x)
        x += 1

row_cells = ws[2]  # 选取第2行（下标从1开始）
print(row_cells)  # 输出：(A2, B2, C2, D2, E2)

col_cells = ws['B']  # 选取B列
print(col_cells)  # 输出：(B1, B2, B3, B4, B5, B6, B7, B8, B9, B10)

row_range_cells = ws[2:6]  # 选取2、3、4、5、6共5行
print(row_range_cells)
# 输出：
# ((A2, B2, C2, D2, E2),
# (A3, B3, C3, D3, E3),
# (A4, B4, C4, D4, E4),
# (A5, B5, C5, D5, E5),
# (A6, B6, C6, D6, E6))

col_range_cells = ws['B:D']  # 选取B、C、D共3列
print(col_range_cells)
# 输出：
# ((B1, B2, B3, B4, B5, B6, B7, B8, B9, B10),
# (C1, C2, C3, C4, C5, C6, C7, C8, C9, C10),
# (D1, D2, D3, D4, D5, D6, D7, D8, D9, D10))

range_cells = ws['B2:D6']  # 选取 C3到F6区域共16个元素
print(range_cells)
# 输出：
# ((B2, C2, D2),
# (B3, C3, D3),
# (B4, C4, D4),
# (B5, C5, D5),
# (B6, C6, D6))

1.以上输出应该类似<Cell ‘Sheet’.A2>、 <Cell ‘Sheet’.B2>，为了好看，简化为A2、B2的形式。
2.以上获取到的多个单元格，返回的是元组或元组套元组，可以通过遍历的方式访问或修改。

4.2.2 通过iter_rows()或iter_cols()迭代取值

from openpyxl import Workbook

wb = Workbook()
ws = wb.active

x = 1
for i in range