Python 操作 Excel 之 openpyxl 模块

1.打开已有 Excel 文件

拿到该库之后，直接尝试打开一个本地 Excel ，然后才能对其中的数据进行控制。

from openpyxl  import load_workbook
wb = load_workbook('测试表格.xlsx')
# 输出
# <openpyxl.workbook.workbook.Workbook object at 0x00000195680F96D0>

继续编码前还是需要了解一些关于 Excel 的相关知识点，如下所示：

Excel 中列名是字母，行号是数字，例如锁定第一个单元格是 A1，然后是 A2，接下来是A3；
Excel 中有工作簿，工作表，单元格三个概念组成，对应到 openpyxl 中就是 Workbook 类，Worksheet 类，Cell 类。

操作 Excel 基本遵循三步走策略：

打开 Excel，产生 Workbook 对象；
获取表格对象，即 Worksheet 对象；
操作表格中的单元格对象，即 Cell 对象。

有这些概念之后，你会发现后续的代码就会变得极容易理解。

2.创建一个 Excel 文件，并修改 sheet

接下来的代码将会创建一个 Excel 文件，并将第一个默认 sheet 修改为【测试】

import openpyxl
if __name__ == '__main__':
    wb = openpyxl.Workbook()
    ws = wb.active
    # 更改第一个 sheet 名称
    ws.title = "测试"
    # 保存
    wb.save('example.xlsx')

在 Python 文件的运行目录会生成如下文件，打开关注第一个 sheet 即可。

如果希望创建第二个表格页（sheet），继续在 Workbook 对象中追加 sheet 即可。

import openpyxl
if __name__ == '__main__':
    wb = openpyxl.Workbook()
    ws = wb.active
    # 更改第一个 sheet 页名称
    ws.title = "Sheet1"
    # 新增第二个 sheet 页
    ws2 = wb.create_sheet("Sheet2")

    # 新增第三个 sheet 页
    # 将 create_sheet() 函数中的第二个参数定义为 0，表示该 sheet 在最前面
    ws3 = wb.create_sheet("Sheet3", 0)
    # 保存
    wb.save('example.xlsx')

追加 sheet 使用的函数是 create_sheet() ，在使用的时候，可以根据第二个参数进行顺序设置。

如果你希望对 sheet 页签进行更细节的设置，可以进行属性配置，例如修改 sheet 页签背景

import openpyxl
if __name__ == '__main__':
    wb = openpyxl.Workbook()
    ws = wb.active
    # 更改第一个 sheet 页名称
    ws.title = "Sheet1"
    ws.sheet_properties.tabColor = "03a9f4"
    # 新增第二个 sheet 页
    ws2 = wb.create_sheet("Sheet2")
    # 新增第三个 sheet 页
    # 将 create_sheet() 函数中的第二个参数定义为 0，表示该 sheet 在最前面
    ws3 = wb.create_sheet("Sheet3", 0)
    # 保存
    wb.save('example.xlsx')

3.选择 sheet 的不同方式

使用 wb["Sheet1"] 可以通过类字典模式进行 sheet 读取，还可以使用 get_sheet_by_name() 方法进行调用，但是该方法已被弃用，建议掌握第一种用法即可。

import openpyxl
if __name__ == '__main__':
    wb = openpyxl.Workbook()
    ws = wb.active
    # 更改第一个 sheet 页名称
    ws.title = "Sheet1"
    ws_1 = wb["Sheet1"]
    ws_1_1 = wb.get_sheet_by_name("Sheet1")  # 该方法将被弃用，建议用上述形式调用
    print(ws_1)
    print(ws_1_1)
    # wb.save('example.xlsx')

4.Worksheet对象

Worksheet 对象即表格对象，用于得到单元格中的数据，修改表格中的内容。

优先学习的相关属性如下所示：

title：表格的标题；
dimensions：表格数据区域大小；
max_row：表格的最大行；
min_row：表格的最小行；
max_column：表格的最大列；
min_column：表格的最小列；
rows：按行获取单元格（Cell）对象，返回生成器；
columns：按列获取单元格（Cell对象），返回生成器。

具体代码如下所示，其中用到的表格参考下述截图：

from openpyxl  import load_workbook
wb = load_workbook('测试表格.xlsx')
if __name__ == '__main__':
    ws = wb.active
    # 更改第一个 sheet 页名称
    ws.title = "My_Sheet"
    ws_1 = wb["My_Sheet"]
    print(ws_1.title)  # 获取标题
    print(ws_1.dimensions)  # 有数据的区域大小，无数据是 A1:A1
    print(ws_1.max_row) # 最大行数
    print(ws_1.min_row) # 最小行数
    print(ws_1.max_column)
    print(ws_1.min_column)
    print(ws_1.rows)
    print(ws_1.columns)

除属性外，Worksheet 对象的方法如下所示 Worksheet方法如下:

iter_rows：按行获取所有单元格，其值可以调用 Cell 的属性（min_row, max_row, min_col, max_col）；
iter_columns：按列获取所有的单元格
append：在表格末尾添加数据
merged_cells：合并多个单元格
unmerged_cells：移除合并的单元格

Worksheet 对象的属性和方法基本都会返回 Cell对象，即单元格对象。

5.Cell 对象

Cell对象比较简单，常用的属性如下:

row ：单元格所在的行
column：单元格坐在的列
value：单元格的值
coordinate：单元格的坐标

from openpyxl import load_workbook
wb = load_workbook('测试表格.xlsx')
if __name__ == '__main__':
    ws = wb.active
    for item in ws.rows:
        print(item)  # 按照行输出
        print(item[0].value)  # 输出单元格的值

操作修改单元格数据 获取单元格之后，就可以对其中的值进行操作，例如下述代码

from openpyxl import load_workbook
wb = load_workbook('测试表格.xlsx')
if __name__ == '__main__':
    ws = wb.active
    # 输出修改前
    for item in ws.rows:
        print(item)  # 按照行输出
        print(item[0].value)  # 输出单元格的值
        item[0].value = 1
    # 输出修改后的值，注意不保存文件，数据不会存储
    for item in ws.rows:
        print(item[0].value)  # 输出单元格的值
    # wb.save('example.xlsx')

单元格内容修改完毕之后，需要使用 wb.save() 方法才可以存储文件修改之后的内容。

使用行列式定位单元格 使用工作表的 cell() 方法，可以定位到单元格，具体用法如下所示

from openpyxl import load_workbook
wb = load_workbook('测试表格.xlsx')
if __name__ == '__main__':
    ws = wb.active
    # 输出修改前
    for item in ws.rows:
        print(item)  # 按照行输出
        print(item[0].value)  # 输出单元格的值
        item[0].value = 1
    # 输出修改后的值，注意不保存文件，数据不会存储
    for item in ws.rows:
        print(item[0].value)  # 输出单元格的值

    # 直接定位单元格
    c = ws.cell(row=2, column=3)
    print(c)
    print("单元格A2,B3里面的内容是",c.value)
    wb.save('example.xlsx')

6.单元格插入图像

使用如下代码，可以在Excel 中的单元格内添加图片。在正式编码前，先导入如下模块

from openpyxl.drawing.image import Image

接下来就可以在对应的单元格中去添加相应的图片了，具体代码如下所示，其中比较重要的是 add_image() 方法。

 # 创建图像，参数是图片路径
img = Image('cup_PNG2001.png')
# add_image(图片,要添加单元格的位置)
ws.add_image(img, 'A2')
wb.save('example.xlsx')

7.设置单元格样式

在 Excel 中，我们可以针对单元格样式进行操作，例如加粗，倾斜，背景色。

需要提前导入的相关类：

from openpyxl.styles import Font, colors, Alignment

接下来可以修改相应的字体：

from openpyxl import load_workbook
from openpyxl.styles import Font, colors, Alignment

wb = load_workbook('测试表格.xlsx')

if __name__ == '__main__':
    ws = wb.active
 
    c_style = Font(size=20, bold=True, italic=True, color='ff0000')

    ws['A1'].font = c_style

    wb.save('example.xlsx')

运行结果如下所示，注意 A1 单元格相关变化。在这里需要注意代码中只要存在一处错误，新的样式就不会起到作用。

其它内容你可自行进行扩展，例如设置对齐方式

ws['B1'].alignment = Alignment(horizontal='center', vertical='center')

行高，列宽，单元格合并与取消合并，这些内容由你自行完成。

频道导航