Openpyxl使用教程(包含处理大数据量案例)

---

一、简介

在 Python 数据处理领域，openpyxl 凭借其卓越的功能与易用性，成为开发者处理 Excel 文件时的得力工具。openpyxl 是一个开源的 Python 库，专为读写 Excel 2010 xlsx/xlsm/xltx/xltm 文件设计，克服了早期库在处理复杂 Excel 文件时的局限。

功能特性

1. 全面的文件读写支持：openpyxl 不仅能够读取 Excel 文件中的数据，还能对文件进行全方位修改，如新增工作表、插入数据、设置格式等。无论是数据分析师对大型数据集进行清洗，还是自动化脚本生成报告，openpyxl 都能灵活应对。

2. 样式设置功能强大：它允许开发者为 Excel 文件添加丰富的样式，包括字体、颜色、对齐方式、边框等，极大地提升了 Excel 文件的可读性与美观度。同时支持条件格式，能根据数据特征自动应用不同样式，突出关键信息。

3. 图表创建与编辑：借助 openpyxl，开发者可以轻松创建多种类型的图表，如柱状图、折线图、饼图等，实现数据的可视化展示。图表与数据紧密关联，当数据发生变化时，图表也能同步更新。

应用场景

1. 自动化办公：openpyxl 在自动化办公领域应用广泛。例如，财务人员可通过编写 Python 脚本，使用 openpyxl 自动生成财务报表，并进行数据汇总与分析，大幅提升工作效率，减少人工操作失误。

2. 数据整合：在数据整合场景中，开发者可以使用 openpyxl 读取多个 Excel 文件中的数据，按照业务逻辑进行整合，实现数据的集中管理与分析。

使用优势

openpyxl 易于上手，官方文档详尽，拥有活跃的社区支持，开发者在使用过程中遇到问题，可迅速从社区获取帮助与解决方案。同时，它与 Python 生态系统高度兼容，能与 pandas、numpy 等常用数据处理库协同工作，构建更强大的数据处理流程。凭借这些优势，openpyxl 在数据处理、自动化办公等场景发挥着重要作用，成为 Python 开发者必备工具之一。

二、常用方法

1、工作簿wb

• wb.active：获取当前活跃的sheet页

import openpyxl

# 读完文件
wb = openpyxl.load_workbook(filename=my_file, read_only=True)
# 获取当前活跃文件
print(wb.active)

• print(wb.sheetnames)：查看当前工作簿下的所有sheet

# 查看当前工作簿下的所有sheet
print(wb.sheetnames)

• wb.save('balances.xlsx')：保存成本地文件

# 保存成本地文件(balances.xlsx)
wb.save('./balances.xlsx')

2、工作表ws

• ws.title：查看名称

# 查看工作表的名称
print(ws.title)

• 访问单个单元格

# 获取单个单元格数据
print(ws['A1'].value)
# 通过访问
print(ws.cell(row=4, column=2))

• ws.rows()：遍历文件中的所有行

由于性能原因 Worksheet.columns 方法在只读模式下不可用。

# 遍历sheet页
for one in ws.rows:
    print(one)

• ws.columns()：

由于性能原因 Worksheet.columns 方法在只读模式下不可用。

# 遍历sheet页(<class 'tuple'>)
for col in ws.columns:
  # <class 'openpyxl.cell.cell.Cell'>
    for cell in col:
        print(cell.value)

• ws['A1':'C2']：获取指定范围单元格数据

# 获取指定范围单元格数据
cell_range = ws['A1':'C2']
# 循环获取数据
for one_row in cell_range:
    for one_cell in one_row:
        print(one_cell.value)

• ws.values：遍历工作簿中所有的行但只返回单元格值(不回返回)

# 获取值遍历(<class 'tuple'>)
for row in ws.values:
  # <class 'str'>
    for value in row:
        print(value)

• iter_cols()： 是 openpyxl 中用于 按列迭代 读取数据的方法。它可以让你逐列遍历 Excel 表格中的数据，每次返回的是一整列的单元格对象。

由于性能原因 Worksheet.iter_cols() 方法在只读模式下不可用。

# 遍历sheet页
for col in ws.iter_cols(min_row=1, max_col=3, max_row=2):
    for cell in col:
        print(cell)

• ws.iter_cols()：按列迭代Excel

# <class 'tuple'>
for col in ws.iter_cols():
  # <class 'openpyxl.cell.cell.Cell'>
    for cell in col:
        print(cell.value)

三、案例

1、创建新工作簿

from openpyxl.workbook import Workbook

# 创建Workbook对象
wb = Workbook()

# 创建新sheet页
ws01 = wb.create_sheet('MySheet01')
ws02 = wb.create_sheet('MySheet02')

# 创建本地文件
wb.save('/Users/lydms/openpyxl/file/test_001.xlsx')

# 关闭资源
wb.close()

2、将Excel数据存入list中

import openpyxl

my_file = '/Users/lydms/openpyxl/file/test_001.xlsx'
sheet_name = 'Sheet1'

# # 创建sheet对象
wb = openpyxl.load_workbook(my_file)
ws = wb[sheet_name]

# 仅获取值
rows_values = ws.values
# 将数据存入list中
result_list = []
for row in rows_values:
    result_list.append(row)

3、按行读取文件(适合大文件)

import openpyxl

my_file = '/Users/lydms/openpyxl/file/test_001.xlsx'
sheet_name = 'Sheet1'

# # 创建sheet对象
wb = openpyxl.load_workbook(my_file)
ws = wb[sheet_name]

# 仅获取值
rows_values = ws.values

# 将数据存入list中
result_list = []
for row in ws.iter_rows():
    one_cell = []
    for cell in row:
        one_cell.append(cell.value)
    result_list.append(one_cell)
print(result_list)

4、按指定行读取文件(适合大文件)

from itertools import islice
import openpyxl

def batch_rows(ws, batch_size):
    """
    按指定行读取文案(batch_size:单次读取文件大小)
    """
    iterator = ws.iter_rows(values_only=True)
    while True:
        batch = list(islice(iterator, batch_size))
        if not batch:
            break  # 没有更多数据，退出
        yield batch  # 生成器返回一批数据


if __name__ == '__main__':
    my_file = '/Users/lydms/openpyxl/file/test_001.xlsx'
    sheet_name = 'Sheet1'
    batch_size = 1

    # # 创建sheet对象
    wb = openpyxl.load_workbook(filename=my_file, read_only=True)
    ws = wb[sheet_name]

    # 仅获取值
    # rows_values = ws.values
    # 将数据存入list中
    result_list = []
    for batch in batch_rows(ws, batch_size):
        result_list.extend(batch)
    print(result_list)