openpyxl：用 Python 自动化处理 Excel 任务

openpyxl 是一款高效的 Python 库，可让您读取和写入采用现代 XML 格式（.xlsx、.xlsm，Excel 2007 引入）的 Excel 文件，并从 Excel 2010 起对高级功能提供完整支持。它最初作为 PHPExcel 的 Python 移植版本开发，如今仍由活跃的开发者社区持续维护。该库在数据自动化、报告与格式化流程方面表现出色，尤其适用于需要处理 Excel 文件但设备上未安装 Excel 的用户。

许多组织使用 openpyxl 来自动化财务报表、根据数据库导出生成图表，以及为营销报告应用公式和格式。通过纯 Python 代码，您可以对 Excel 文件进行精细化控制。

什么是 openpyxl？

openpyxl 专为提供对 Excel 2010+ 格式的原生支持而设计。该库可处理多种文件格式：

•  .xlsx - Excel 工作簿

•  .xlsm - 含宏的 Excel 工作簿

•  .xltx - Excel 模板

•  .xltm - 含宏的 Excel 模板

openpyxl 的一大优势是无需在您的计算机上安装 Excel。这使其非常适合用于服务器环境、自动化报告系统和数据处理流水线。包括 pandas 在内的多款 Python 库都将 openpyxl 作为其 Excel 接口，这也从侧面体现了它的可靠性与功能性。

安装与导入 openpyxl

使用 pip 安装 openpyxl 十分简单：

pip install openpyxl技术支持

为提高处理不受信任来源文件时的安全性，您还可以安装可选的 defusedxml 包：

pip install defusedxml技术支持

要在 Python 脚本中使用 openpyxl，请导入所需的类：

from openpyxl import Workbook, load_workbook技术支持

关键概念与术语

要高效使用 openpyxl，您需要理解 Excel 的基本结构：

• Workbook（工作簿）：Excel 文件本身，包含一个或多个工作表
• Worksheet（工作表）：工作簿中的各个标签页/表
• Cell（单元格）：工作表中的单个数据点，由列字母与行数字标识（例如 "A1"）
• Row（行）：水平的一行单元格，以数字标识（1、2、3…）
• Column（列）：垂直的一列单元格，以字母标识（A、B、C…）

在 openpyxl 中，您可以通过以下方式引用单元格：

• Excel 风格引用：sheet[“A1”]

• 行-列索引：sheet.cell(row=1, column=1)（注意：openpyxl 使用从 1 开始的索引，而非从 0 开始）

使用 openpyxl 读取 Excel 文件

下面是一个读取 Excel 文件数据的实用示例：

加载并探索工作簿

from openpyxl import load_workbook

# Load the workbook - use read_only=True for large files
wb = load_workbook('sample.xlsx', read_only=False, data_only=False)
# data_only=True reads values instead of formulas技术支持

可选参数用于控制工作簿的加载方式：

• read_only=True 可提升处理大型文件时的性能，但会限制编辑能力

• data_only=True 返回计算后的值，而非公式本身

遍历数据

要高效处理多个单元格，请使用迭代方法：

# Iterate through rows
for row in sheet.iter_rows(min_row=1, max_row=5, values_only=True):
    print(row)  # Returns a tuple of values

# Iterate through columns
for column in sheet.iter_cols(min_col=1, max_col=3, values_only=True):
    print(column)  # Returns a tuple of values

# Access a range of cells
cell_range = sheet["A1:C5"]
for row in cell_range:
    for cell in row:
        print(cell.value)技术支持

转换为 Python 数据结构

您可以轻松将 Excel 数据转换为 Python 数据结构：

# Convert worksheet data to a list of dictionaries
def sheet_to_dict(sheet):
    data = []
    headers = [cell.value for cell in sheet[1]]  # First row as headers
    
    for row in sheet.iter_rows(min_row=2, values_only=True):
        row_data = {}
        for key, value in zip(headers, row):
            row_data[key] = value
        data.append(row_data)
    
    return data

# Example usage
data_dict = sheet_to_dict(sheet)
print(data_dict)技术支持

使用 openpyxl 写入 Excel 文件

使用 openpyxl 创建和修改 Excel 文件同样直观。

创建工作簿并写入基础数据

from openpyxl import Workbook

# Create a new workbook
wb = Workbook()
sheet = wb.active

# Rename the sheet
sheet.title = "Data"

# Write values to cells
sheet["A1"] = "Name"
sheet["B1"] = "Age"
sheet["C1"] = "City"

# Add data
data = [
    ["Alice", 25, "New York"],
    ["Bob", 30, "San Francisco"],
    ["Charlie", 35, "Chicago"]
]

for row_idx, row_data in enumerate(data, start=2):
    for col_idx, cell_value in enumerate(row_data, start=1):
        sheet.cell(row=row_idx, column=col_idx, value=cell_value)

# Save the workbook
wb.save("new_workbook.xlsx")技术支持

修改现有的 Excel 文件

from openpyxl import load_workbook

# Load an existing workbook
wb = load_workbook("existing_file.xlsx")
sheet = wb.active

# Modify cell values
sheet["D1"] = "Updated Data"
sheet.cell(row=5, column=2).value = 42

# Save to a new file (to preserve the original)
wb.save("modified_file.xlsx")技术支持

动态追加数据

.append() 方法可轻松添加新行：

from openpyxl import Workbook

wb = Workbook()
sheet = wb.active

# Add headers
sheet.append(["Date", "Product", "Quantity", "Price"])

# Add multiple rows of data
sales_data = [
    ["2025-04-01", "Laptop", 5, 1200],
    ["2025-04-01", "Mouse", 10, 25],
    ["2025-04-02", "Monitor", 3, 350]
]

for row in sales_data:
    sheet.append(row)

wb.save("sales_report.xlsx")技术支持

Excel 中的格式与样式

openpyxl 为单元格格式和样式提供了丰富选项。

添加字体、边框与对齐方式

from openpyxl import Workbook
from openpyxl.styles import Font, Alignment, Border, Side, PatternFill, NamedStyle

wb = Workbook()
sheet = wb.active

# Create a header style
header_style = NamedStyle(name="header_style")
header_style.font = Font(bold=True, size=12, color="FFFFFF")
header_style.fill = PatternFill(fill_type="solid", start_color="366092")
header_style.alignment = Alignment(horizontal="center", vertical="center")
header_style.border = Border(
    bottom=Side(border_style="medium", color="000000"),
    left=Side(border_style="thin"),
    right=Side(border_style="thin"),
    top=Side(border_style="thin")
)

# Add the style to the workbook
wb.add_named_style(header_style)

# Apply the style to header row
headers = ["ID", "Product", "Category", "Price"]
for col_idx, header in enumerate(headers, start=1):
    cell = sheet.cell(row=1, column=col_idx, value=header)
    cell.style = "header_style"

# Basic styling without named styles
sheet["A2"].font = Font(bold=True, italic=True)
sheet["A3"].alignment = Alignment(horizontal="center")
sheet["A4"].border = Border(bottom=Side(border_style="thin"))

wb.save("styled_workbook.xlsx")技术支持

条件格式

条件格式允许您根据单元格的值应用样式：

from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill
from openpyxl.styles.differential import DifferentialStyle
from openpyxl.formatting.rule import Rule, ColorScaleRule

wb = Workbook()
sheet = wb.active

# Add sample data
for row in range(1, 11):
    for col in range(1, 5):
        sheet.cell(row=row, column=col, value=(row-1)*4 + col)

# Add color scale (green to red gradient)
color_scale = ColorScaleRule(
    start_type="min", start_color="63BE7B",  # Green
    mid_type="percentile", mid_value=50, mid_color="FFEB84",  # Yellow
    end_type="max", end_color="F8696B"  # Red
)
sheet.conditional_formatting.add("A1:D10", color_scale)

# Add a rule for values less than 5
red_text = Font(color="FF0000")
red_fill = PatternFill(start_color="FFCCCC", end_color="FFCCCC", fill_type="solid")
dxf = DifferentialStyle(font=red_text, fill=red_fill)
rule = Rule(type="cellIs", operator="lessThan", formula=["5"], dxf=dxf)
sheet.conditional_formatting.add("A1:D10", rule)

wb.save("conditional_format.xlsx")技术支持

高级功能

openpyxl 支持许多高级 Excel 功能，助您处理复杂的电子表格任务。

添加公式

from openpyxl import Workbook

wb = Workbook()
sheet = wb.active

# Add data
for row in range(1, 6):
    sheet.cell(row=row, column=1, value=row)

# Add formulas
sheet["B1"] = "=A1*2"
sheet["B2"] = "=A2*2"
sheet["C1"] = "=SUM(A1:A5)"
sheet["C2"] = "=AVERAGE(A1:A5)"
sheet["C3"] = "=COUNTIF(A1:A5,\">2\")"

wb.save("formulas.xlsx")技术支持

注意：使用 load_workbook() 时，openpyxl 不会自动计算公式。若要查看计算结果，请使用 load_workbook(“file.xlsx”, data_only=True)。

插入图表

openpyxl 可以创建多种图表类型：

from openpyxl import Workbook
from openpyxl.chart import BarChart, LineChart, Reference

wb = Workbook()
sheet = wb.active

# Add data
months = ["Jan", "Feb", "Mar", "Apr", "May"]
values = [30, 45, 37, 50, 62]

for i, (month, value) in enumerate(zip(months, values), start=1):
    sheet.cell(row=i, column=1, value=month)
    sheet.cell(row=i, column=2, value=value)

# Create a bar chart
bar_chart = BarChart()
bar_chart.title = "Monthly Sales"
bar_chart.x_axis.title = "Month"
bar_chart.y_axis.title = "Sales"

# Define the data range
data = Reference(sheet, min_col=2, min_row=1, max_row=5)
categories = Reference(sheet, min_col=1, min_row=1, max_row=5)

# Add the data to the chart
bar_chart.add_data(data)
bar_chart.set_categories(categories)

# Add the chart to the worksheet
sheet.add_chart(bar_chart, "D1")

# Create a line chart on the same data
line_chart = LineChart()
line_chart.title = "Monthly Sales Trend"
line_chart.add_data(data)
line_chart.set_categories(categories)
sheet.add_chart(line_chart, "D15")

wb.save("charts.xlsx")技术支持

插入图片

from openpyxl import Workbook
from openpyxl.drawing.image import Image

wb = Workbook()
sheet = wb.active

# Add an image
img = Image("logo.png")

# Resize the image (optional)
img.width = 150
img.height = 75

# Add the image to cell A1
sheet.add_image(img, "A1")

wb.save("with_image.xlsx")技术支持

注意：处理图片需要安装 Pillow 库：pip install pillow

工作表、行与列的管理

openpyxl 提供了管理 Excel 文件结构的功能：

创建与删除工作表

from openpyxl import Workbook

wb = Workbook()

# Create new sheets
wb.create_sheet("Data")
wb.create_sheet("Summary", 0)  # Add at the beginning

# Remove a sheet
wb.remove(wb["Sheet"])  # Remove the default sheet

print(wb.sheetnames)  # ['Summary', 'Data']

wb.save("multiple_sheets.xlsx")技术支持

重命名工作表

wb = Workbook()
sheet = wb.active
sheet.title = "Sales Report"
wb.save("renamed_sheet.xlsx")技术支持

复制工作表

from openpyxl import Workbook

wb = Workbook()
source = wb.active
source.title = "Original"

# Add some data to copy
source["A1"] = "Test Data"

# Create a copy
wb.copy_worksheet(source)

# The copied sheet will have "Copy of Original" name
wb.save("copied_sheet.xlsx")技术支持

插入与删除行列

from openpyxl import Workbook

wb = Workbook()
sheet = wb.active

# Add some data
for i in range(1, 6):
    for j in range(1, 4):
        sheet.cell(row=i, column=j, value=f"R{i}C{j}")

# Insert a row at position 2
sheet.insert_rows(2)

# Insert multiple rows
sheet.insert_rows(5, 3)  # Insert 3 rows at position 5

# Insert a column at position 2
sheet.insert_cols(2)

# Delete rows
sheet.delete_rows(7, 2)  # Delete 2 rows starting at row 7

# Delete columns
sheet.delete_cols(3)  # Delete column C

wb.save("modified_structure.xlsx")技术支持

冻结窗格并添加筛选器

from openpyxl import Workbook

wb = Workbook()
sheet = wb.active

# Add headers
headers = ["ID", "Name", "Department", "Salary"]
for col_idx, header in enumerate(headers, start=1):
    sheet.cell(row=1, column=col_idx, value=header)

# Add data
data = [
    [1, "John Smith", "HR", 55000],
    [2, "Jane Doe", "IT", 65000],
    [3, "Bob Johnson", "Finance", 60000]
]
for row_idx, row_data in enumerate(data, start=2):
    for col_idx, value in enumerate(row_data, start=1):
        sheet.cell(row=row_idx, column=col_idx, value=value)

# Freeze the header row
sheet.freeze_panes = "A2"

# Add autofilter
sheet.auto_filter.ref = "A1:D4"

wb.save("freeze_and_filter.xlsx")技术支持

局限性与社区反馈

尽管 openpyxl 功能丰富，用户仍指出了一些局限：

• 对命名区域（named ranges）的支持有限
• 针对高级功能的文档示例相对稀少
• 处理超大文件时性能可能较慢（只读模式有助于改善）

尽管如此，openpyxl 仍然具有强大的社区支持。您可以在 GitLab 追踪器上报告问题，且有时也能获得专业支持。 如需更多信息和帮助，请参阅：

• openpyxl 文档
• 源代码
• 问题追踪
• 专业支持

总结

openpyxl 是一款成熟的 Python Excel 自动化库，提供对 Excel 文件的读取、写入与样式设置的完整支持。它能在自动化流程中对电子表格输出进行细致控制，从操作单个单元格到创建图表与应用复杂格式一应俱全。

对于需要以编程方式生成或分析 Excel 文件的用户，尤其是在构建报告或处理表格数据时，openpyxl 提供了一整套强大的工具。其无需安装 Excel 的能力，使其对服务器环境和自动化数据管道尤为有价值。

无论您是在创建财务模型、分析数据，还是生成报告，openpyxl 都能让您通过 Python 高效处理 Excel 文件。