首页 > 编程笔记

openpyxl使用教程（简明版）

Python 的 xlsxwriter、xlwings、openpyxl、pandas 等库都可以用于操作 Excel，如表1所示。

表1：Python 中用于操作 Excel 的常用库
库 描述
xlwings 非常方便地读写 Excel 文件中的数据和修改单元格格式。
xlsxwriter 用于写 xlsx 格式的文件的库。它可以用来写文本、数字和公式，支持单元格格式化、文档配置、自动过滤等特性，但不能用于读取和修改 Excel 文件。
openpyxl 通过工作簿“Workbook-工作表Sheet-单元格Cell”的模式对 xlsx 格式的文件进行读、写和改，还可以调整样式。
pandas 用于数据处理和分析的强大的库，有时也可以用于自动化处理 Excel 文件。

openpyxl 是一款比较综合的工具，它不仅能够同时读取和修改 Excel 文件，而且可以详细设置 Excel 文件内的单元格，包括单元格样式等内容。它还支持图表插入、打印设置等内容。

使用 openpyxl 可以读写 xltm、xltx、xlsm、xlsx 等类型的文件，且可以处理数据量较大的 Excel 文件，它的跨平台处理大量数据的能力是其他库没法相比的。

总之，openpyxl 成为处理 Excel 复杂问题的首选库。

openpyxl 是一个非标准库，需要自行安装，它的安装过程并不复杂，Windows 或 macOS 用户均可以在命令行或终端中使用 pip 安装 openpyxl，命令为：

pip install openpyxl

openpyxl 的基本概念

openpyxl 中主要用到 3 个概念是 Workbook、Sheet 和 Cell。
openpyxl 就是围绕着这 3 个概念进行操作的，不论读写，操作步骤都是打开 Workbook，定位 Sheet，操作 Cell。

openpyxl 中有 3 个不同层次的类，Workbook 是对工作簿的抽象，Worksheet 是对工作表的抽象，Cell 是对单元格的抽象。这 3 个类中每一个类都包含很多属性和方法。

1) Workbook对象

一个 Workbook 对象表示一个 Excel 文件，在操作 Excel 之前，我们应该先创建一个 Workbook 对象。
Workbook 类和 load_workbook 函数相同，返回的都是一个 Workbook 对象。

Workbook 类有很多属性和方法，大部分方法都与表有关，如表2和表3所示。

表2：Workbook 类的属性
属性 描述
active 获取当前活跃的 Worksheet。
worksheets 以列表的形式返回所有的 Worksheet。
read_only 判断是否以 read_only（只读）模式打开 Excel 文件。
encoding 获取文档的字符集编码。
properties 获取文档的元数据，如标题、创建者、创建日期等。
sheetnames 获取工作簿中的表（列表）。

表3：Workbook 类的方法
方法 描述
get_sheet_names 获取所有表的名称（不建议使用新版的 openpyxl，可通过 Workbook 的 sheetnames 属性获取）。
get_sheet_by_name 通过表名称获取 Worksheet 对象（不建议使用新版的 openpyxl，可通过Worksheet ['表名']获取）。
get_active_sheet 获取活跃的表（建议通过 active 属性获取新版的 openpyxl）。
remove_sheet 删除一个表。
create_sheet 创建一个空表。
copy_worksheet 在 Workbook 内复制表。

2) Worksheet 对象

我们可以通过 Worksheet 对象获取表的属性，得到单元格中的数据，修改表中的内容。

openpyxl 提供了非常灵活的方式来访问表中的单元格和数据，常用的 Worksheet 类的属性和方法如表4和表5所示。其中，行以数字 1 开始，列以字母 A 开始。

表4：Worksheet 类的属性
属性 描述
title 表的标题。
dimensions 表的大小，这里的大小是指含有数据的表的大小，其值为“左上角的坐标:右下角的坐标”。
max_row 表的最大行。
min_row 表的最小行。
max_column 表的最大列。
min_column 表的最小列。
rows 按行获取单元格（Cell 对象）生成器。
columns 按列获取单元格（Cell 对象）生成器。
freeze_panes 冻结窗格。
values 按行获取表的内容（数据）生成器。

表5：Worksheet 类的方法
方法 描述
iter_rows 按行获取所有单元格，内置属性有 min_row、max_row、min_col 和 max_col。
iter_columns 按列获取所有的单元格。
append 在表末尾添加数据。
merged_cells 合并多个单元格。
unmerged_cells 移除合并的单元格。

3) Cell 对象

Cell 对象比较简单，常用的属性如表6所示。Cell 对象只存储两种数据类型——数字和字符串，除了纯数字，其他均为字符串类型。

表6：Cell 常用属性
属性 描述
row 单元格所在的行。
column 单元格所在的列。
value 单元格的值。
coordinate 单元格的坐标。

如前文所述，一个 Excel 文件 Workbook 由一个或者多个工作表 Worksheet 组成，一个 Worksheet 可以看作由多个行 row 组成，也可以看作由多个列 column 组成，而每一行每一列都由多个单元格 Cell 组成。

下面简要讲解一下如何读取和写入 Excel。

4) 读取 Excel

读取 Excel 的方式有如下4种。

① 载入 Excel： 
from openpyxl import load_workbook
workbook = load_workbook(filename='测试.xlsx')
print(workbook.sheetnames)
注意，load_workbook 只能打开已经存在的 Excel，不能创建新的 Excel。

② 根据名称获取工作表： 
from openpyxl import load_workbook
workbook = load_workbook(filename='其他.xlsx')
print(workbook.sheetnames)
sheet = workbook['工作业务']

③ 获取多个格子的值。

Excel 中每一列由字母确定，是字符型；每一行由一个数字确定，是整型。如果我们要输出每一个格子的值，那么需要遍历： 
for cell in cells:
    print(cell.value)

④ 读取所有的行： 
for row in sheet.rows:
    print(row)

5) Excel 写入

Excel 写入的方式有如下两种。

① 保存 Excel： 
workbook.save(filename='Excel工作表1.xlsx')
如果读取和写入 Excel 的路径相同则对原文件进行修改，如果读取和写入 Excel 的路径不同则保存成新的文件。

② 写入单元格： 
cell = sheet['A1']
cell.value = '业务需求'

Excel 样式调整

openpyxl 处理 Excel 文件中的单元格样式，总共有 6 个属性类，分别是：
例如，通过语句 From openpyxl.styles import PatternFill, Border, Side, Alignment, Protection, Font 导入相应的库。

示例

openpyxl 是读写 Excel 2010 的 xlsx、xlsm、xltx、xltm 格式文件的 Python 库，简单易用，功能广泛，单元格格式调整、图表处理、公式处理、筛选、批注、文件保护等功能应有尽有，图表处理功能是其一大亮点。

openpyxl 几乎可以实现所有的 Excel 功能，而且接口清晰，文档丰富，学习成本相对较低。

封装一个可以读取任意 Excel 文件的方法，就可以指定读取的表单，当我们多次从 Excel 文件中读取数据时，不用重复地写代码，只需调用封装的类即可，如代码清单1所示。

代码清单1：excelUtil: 
# -*- coding: utf-8 -*-
# @Time : 2022/7/12 10:29 上午
# @Project : excelDemo
# @File : excelUtil.py
# @Version: Python3.9.8
　
from openpyxl import load_workbook
from openpyxl.worksheet.worksheet import Worksheet

class ExcelHandler():
    '''
    操作Excel
    '''

    def __init__(self, file):
        '''初始化函数'''
        self.file = file

    def open_sheet(self, sheet_name) -> Worksheet:
        '''打开表单'''
        wb = load_workbook(self.file)
        sheet = wb[sheet_name]
        return sheet

    def read_header(self, sheet_name):
        '''获取表单的表头'''
        sheet = self.open_sheet(sheet_name)
        headers = []
        for i in sheet[1]:
            headers.append(i.value)
        return headers

    def read_rows(self,sheet_name):
        '''
        读取除表头外所有数据（除第一行外的所有数据）
        返回的内容是一个二维列表，若想获取每一行的数据，可使用for循环或使用*解包
        '''
        sheet = self.open_sheet(sheet_name)
        rows = list(sheet.rows)[1:]
    
        data = []
        for row in rows:
            row_data = []
            for cell in row:
                row_data.append(cell.value)
            data.append(row_data)
    
        return data

    def read_key_value(self,sheet_name):
        '''
        获取所有数据，且将表头中的内容与数据结合展示（以字典的形式）
        如：[
        {'序号':1,'会员卡号': '680021685898','机场名称':'上海机场'},
        {'序号':2,'会员卡号': '680021685899','机场名称':'广州机场'}
        ]
        '''
        sheet = self.open_sheet(sheet_name)
        rows = list(sheet.rows)

        # 获取标题
        data = []
        for row in rows[1:]:
            row_data = []
            for cell in row:
                row_data.append(cell.value)
                # 列表转换成字典，与表头内容一起使用zip函数进行打包
            data_dict = dict(zip(self.read_header(sheet_name),row_data))
            data.append(data_dict)
        return data

    @staticmethod
    def write_change(file,sheet_name,row,column,data):
        '''写入Excel数据'''
        wb = load_workbook(file)
        sheet = wb[sheet_name]
    
        # 修改单元格
        sheet.cell(row,column).value = data
        # 保存
        wb.save(file)
        # 关闭
        wb.close()
写入 Excel 使用了静态方法，原因是读取文件无须保存。如果修改文件后没有保存，其他地方又调用了该方法，则会引起报错，所以每次修改 Excel 文件，都要进行保存。

推荐阅读