Openpyxl 入门到精通：Python Excel 操作全攻略

前言

在数据分析、报告生成以及自动化办公等诸多领域，Excel 电子表格无疑是最常用、最普及的工具之一。然而，手动处理大量的Excel文件不仅效率低下，而且极易出错。Python 作为一种功能强大且易于学习的编程语言，为我们提供了自动化处理Excel的利器——openpyxl库。

openpyxl是一个用于读写 Excel 2010 XLSX/XLSM/XLTX/XLTM 文件的 Python 库。它不依赖于 Microsoft Excel 软件，因此可以在任何支持 Python 的操作系统上运行。无论是创建全新的工作簿、读取现有数据、修改单元格格式，还是生成复杂的图表，openpyxl都能胜任，极大地提升了我们处理Excel数据的效率和准确性。

本篇文章将带您从openpyxl的基础知识入手，逐步深入到高级功能，助您全面掌握使用Python操作Excel的技巧，实现Excel自动化操作的“从入门到精通”。

第一章：快速上手 – 安装与基础概念

1.1 安装 openpyxl

使用openpyxl的第一步是安装它。通过Python的包管理工具pip，安装过程非常简单：

bash pip install openpyxl

安装完成后，您就可以在Python脚本中导入并使用openpyxl库了。

1.2 openpyxl 的核心对象

在openpyxl中，有几个核心概念是理解和操作Excel文件的基础：

工作簿 (Workbook)：一个Excel文件被称为一个工作簿。它是所有工作表、图表、宏等的容器。在openpyxl中，您将通过openpyxl.Workbook对象来表示和操作一个Excel文件。
工作表 (Worksheet)：工作簿由一个或多个工作表组成。每个工作表都有自己的网格，用于存储数据。在openpyxl中，您将通过openpyxl.worksheet.worksheet.Worksheet对象来表示和操作一个工作表。
单元格 (Cell)：工作表的基本组成单位，用于存储单个数据点。单元格由其行号和列号（例如 A1, B5）唯一标识。在openpyxl中，您将通过openpyxl.cell.cell.Cell对象来表示和操作一个单元格。

理解这三个核心对象及其相互关系，是掌握openpyxl使用的关键。

第二章：数据读取与操作

2.1 创建与加载工作簿

创建新的工作簿：

“`python
from openpyxl import Workbook

创建一个新的工作簿

wb = Workbook()

默认会创建一个名为 “Sheet” 的工作表

可以通过 active 属性获取当前活动的工作表

ws = wb.active
ws.title = “MyNewSheet” # 重命名工作表

保存工作簿

wb.save(“new_excel_file.xlsx”)
print(“新的Excel文件 new_excel_file.xlsx 已创建。”)
“`

加载现有工作簿：

要读取一个已存在的Excel文件，您需要使用openpyxl.load_workbook()函数。

“`python
from openpyxl import load_workbook

加载一个现有工作簿

data_only=True 参数会读取单元格的显示值，而不是公式本身

read_only=True 参数可以提高读取大型文件的性能

try:
wb = load_workbook(“new_excel_file.xlsx”, data_only=True)
print(“成功加载 new_excel_file.xlsx”)
except FileNotFoundError:
print(“文件 new_excel_file.xlsx 不存在，请先创建。”)
# 如果文件不存在，可以考虑创建它或退出
exit()

“`

2.2 访问工作表

加载工作簿后，有几种方法可以访问其中的工作表：

获取活动工作表 (Active Sheet)：默认情况下，或者当您创建一个新的工作簿时，总会有一个活动工作表。
python ws = wb.active print(f"活动工作表的名称: {ws.title}")
按名称获取工作表：
python ws_by_name = wb["MyNewSheet"] print(f"按名称获取的工作表名称: {ws_by_name.title}")
遍历所有工作表：
python print("工作簿中所有工作表名称:") for sheet in wb: print(sheet.title)

2.3 读取单元格数据

2.3.1 按单元格坐标读取

通过单元格的行和列坐标可以直接访问单元格，并获取其value。

“`python

写入一些示例数据以便读取

ws = wb.active
ws[‘A1’] = ‘姓名’
ws[‘B1’] = ‘年龄’
ws[‘A2’] = ‘张三’
ws[‘B2’] = 25
ws[‘A3’] = ‘李四’
ws[‘B3’] = 30
wb.save(“new_excel_file.xlsx”) # 保存更改

读取单个单元格

cell_a1 = ws[‘A1’]
print(f”A1单元格的值: {cell_a1.value}”)

cell_b2 = ws.cell(row=2, column=2) # 也可以使用 .cell() 方法
print(f”B2单元格的值: {cell_b2.value}”)
“`

2.3.2 按行和列遍历数据

openpyxl提供了多种方便的方式来遍历工作表中的数据。

遍历所有行 (包括空行)：
python print("\n遍历所有行:") for row in ws.iter_rows(): for cell in row: print(f"{cell.coordinate}: {cell.value}", end="\t") print()
遍历指定范围的行：
python print("\n遍历指定范围的行 (从第2行到第3行):") for row in ws.iter_rows(min_row=2, max_row=3, min_col=1, max_col=2): for cell in row: print(f"{cell.coordinate}: {cell.value}", end="\t") print()
遍历所有列：
python print("\n遍历所有列:") for col in ws.iter_cols(): for cell in col: if cell.value is not None: # 仅打印有值的单元格 print(f"{cell.coordinate}: {cell.value}", end="\t") print()
使用 values_only 提高效率：当您只需要单元格的值而不需要单元格对象时，可以使用values_only=True来提高读取效率。
python print("\n遍历行 (仅值):") for row_values in ws.iter_rows(values_only=True): print(row_values)
获取最大行和最大列：
python print(f"\n最大行数: {ws.max_row}") print(f"最大列数: {ws.max_column}")
max_row和max_column属性会返回工作表中包含数据的最大行号和列号。

第三章：数据写入与保存

3.1 写入单元格数据

3.1.1 写入单个单元格

您可以直接通过单元格坐标或cell()方法为单元格赋值。

“`python
from openpyxl import Workbook

wb = Workbook()
ws = wb.active
ws.title = “WriteData”

直接赋值

ws[‘A1’] = “Hello”
ws[‘B1’] = “World!”

使用 cell() 方法赋值

ws.cell(row=2, column=1, value=”Python”)
ws.cell(row=2, column=2, value=”Openpyxl”)

写入不同类型的数据

ws[‘A3’] = 123 # 数字
ws[‘B3’] = 3.14159 # 浮点数
import datetime
ws[‘A4’] = datetime.date(2023, 1, 1) # 日期
ws[‘B4’] = True # 布尔值

wb.save(“write_example.xlsx”)
print(“数据已写入 write_example.xlsx”)
“`

3.2 批量写入数据

当需要写入多行或多列数据时，可以使用以下方法：

3.2.1 写入多行数据 (append())

append()方法可以将一个列表或元组作为一行数据添加到工作表的末尾。

“`python
from openpyxl import Workbook

wb = Workbook()
ws = wb.active
ws.title = “AppendData”

添加表头

ws.append([“Name”, “Age”, “City”])

添加多行数据

data = [
[“Alice”, 30, “New York”],
[“Bob”, 24, “London”],
[“Charlie”, 35, “Paris”]
]

for row_data in data:
ws.append(row_data)

wb.save(“append_example.xlsx”)
print(“批量数据已写入 append_example.xlsx”)
“`

3.2.2 写入多列数据 (迭代列)

虽然append()按行工作，但您也可以通过循环和cell()方法按列写入数据。

“`python
from openpyxl import Workbook

wb = Workbook()
ws = wb.active
ws.title = “ColumnData”

假设有两列数据

names = [“David”, “Eve”, “Frank”]
ages = [28, 22, 40]

写入表头

ws[‘A1’] = “Name”
ws[‘B1’] = “Age”

逐列写入

for i, name in enumerate(names):
ws.cell(row=i + 2, column=1, value=name) # +2 是因为有表头且行号从1开始

for i, age in enumerate(ages):
ws.cell(row=i + 2, column=2, value=age)

wb.save(“column_write_example.xlsx”)
print(“列数据已写入 column_write_example.xlsx”)
“`

3.3 保存工作簿

完成所有写入操作后，务必使用save()方法将更改保存到文件中。

python wb.save("my_final_excel.xlsx") print("工作簿已保存为 my_final_excel.xlsx")

重要提示：

如果保存的文件名已存在，openpyxl会直接覆盖原文件，不会有警告。
如果文件正在被其他程序（如Excel本身）打开，保存操作可能会失败并抛出PermissionError。请确保文件未被占用。

第四章：单元格格式与样式

openpyxl提供了丰富的API来设置单元格的字体、颜色、对齐方式、边框、填充以及数字格式，让您的Excel报表更具专业性和可读性。

4.1 字体 (Font)

使用Font对象可以设置字体名称、大小、颜色、粗体、斜体和下划线等。

“`python
from openpyxl import Workbook
from openpyxl.styles import Font, colors

wb = Workbook()
ws = wb.active

ws[‘A1’] = “Bold Text”
ws[‘A1’].font = Font(bold=True)

ws[‘A2’] = “Italic Text”
ws[‘A2’].font = Font(italic=True)

ws[‘A3’] = “Red and Large Text”
ws[‘A3’].font = Font(color=colors.RED, size=16)

ws[‘A4’] = “Underlined Text”
ws[‘A4’].font = Font(underline=”single”)

ws[‘A5’] = “Combined Styles”
ws[‘A5′].font = Font(name=’Arial’, size=14, color=’FF0000′, bold=True, italic=True) # 使用十六进制颜色代码

wb.save(“font_styles.xlsx”)
print(“字体样式已应用于 font_styles.xlsx”)
“`

4.2 对齐方式 (Alignment)

使用Alignment对象可以设置文本的水平对齐、垂直对齐、文本换行和旋转等。

“`python
from openpyxl import Workbook
from openpyxl.styles import Alignment

wb = Workbook()
ws = wb.active

ws[‘B2’] = “Center Aligned”
ws[‘B2′].alignment = Alignment(horizontal=’center’, vertical=’center’)

ws[‘B3’] = “Wrapped Text Example for Longer Content”
ws[‘B3’].alignment = Alignment(wrapText=True)
ws.row_dimensions[3].height = 30 # 增加行高以显示换行效果
ws.column_dimensions[‘B’].width = 20 # 增加列宽

ws[‘B4’] = “Rotated Text”
ws[‘B4’].alignment = Alignment(textRotation=90) # 旋转90度

wb.save(“alignment_styles.xlsx”)
print(“对齐样式已应用于 alignment_styles.xlsx”)
“`

4.3 边框 (Border)

使用Border和Side对象可以设置单元格的边框样式。Side用于定义边框的线条样式和颜色。

“`python
from openpyxl import Workbook
from openpyxl.styles import Border, Side, colors

wb = Workbook()
ws = wb.active

thin_border = Border(left=Side(style=’thin’),
right=Side(style=’thin’),
top=Side(style=’thin’),
bottom=Side(style=’thin’))

medium_red_border = Border(left=Side(style=’medium’, color=colors.RED),
right=Side(style=’medium’, color=colors.RED),
top=Side(style=’medium’, color=colors.RED),
bottom=Side(style=’medium’, color=colors.RED))

ws[‘C2’] = “Bordered Cell”
ws[‘C2’].border = thin_border

ws[‘C3’] = “Red Border”
ws[‘C3’].border = medium_red_border

wb.save(“border_styles.xlsx”)
print(“边框样式已应用于 border_styles.xlsx”)
“`

4.4 填充 (Fill)

使用PatternFill对象可以设置单元格的背景颜色。

“`python
from openpyxl import Workbook
from openpyxl.styles import PatternFill, colors

wb = Workbook()
ws = wb.active

纯色填充

ws[‘D2’] = “Yellow Cell”
ws[‘D2’].fill = PatternFill(start_color=”FFFF00″, end_color=”FFFF00″, fill_type=”solid”) # FFFF00是黄色

ws[‘D3’] = “Light Green”
ws[‘D3’].fill = PatternFill(start_color=colors.GREEN, end_color=colors.GREEN, fill_type=”solid”)

wb.save(“fill_styles.xlsx”)
print(“填充样式已应用于 fill_styles.xlsx”)
“`

4.5 数字格式 (Number Format)

Excel支持多种数字格式，例如货币、日期、百分比等。

“`python
from openpyxl import Workbook

wb = Workbook()
ws = wb.active

ws[‘E2’] = 123.456
ws[‘E2’].number_format = ‘0.00’ # 保留两位小数

ws[‘E3’] = 0.75
ws[‘E3’].number_format = ‘0%’ # 百分比

import datetime
ws[‘E4’] = datetime.datetime.now()
ws[‘E4’].number_format = ‘yyyy-mm-dd hh:mm:ss’ # 日期时间格式

ws[‘E5’] = 1234567
ws[‘E5’].number_format = ‘#,##0’ # 千位分隔符

wb.save(“number_formats.xlsx”)
print(“数字格式已应用于 number_formats.xlsx”)
“`

4.6 合并与取消合并单元格

合并单元格：

“`python
from openpyxl import Workbook
from openpyxl.styles import Alignment # 导入Alignment

wb = Workbook()
ws = wb.active

ws.merge_cells(‘A1:C1’) # 合并A1到C1的单元格
ws[‘A1’] = “Merged Cell Title”
ws[‘A1′].alignment = Alignment(horizontal=’center’, vertical=’center’) # 居中显示

ws.merge_cells(start_row=2, start_column=1, end_row=3, end_column=1) # 合并A2到A3
ws[‘A2’] = “Merged A2:A3”
ws[‘A2′].alignment = Alignment(horizontal=’center’, vertical=’center’)

wb.save(“merged_cells.xlsx”)
print(“单元格已合并在 merged_cells.xlsx”)
“`

取消合并单元格：

“`python
from openpyxl import load_workbook

wb = load_workbook(“merged_cells.xlsx”)
ws = wb.active

ws.unmerge_cells(‘A1:C1’) # 取消合并A1到C1

wb.save(“unmerged_cells.xlsx”)
print(“单元格已取消合并在 unmerged_cells.xlsx”)
“`

第五章：行与列操作

5.1 插入和删除行/列

插入行：

“`python
from openpyxl import Workbook

wb = Workbook()
ws = wb.active
ws[‘A1’] = “Original A1”
ws[‘A2’] = “Original A2”
ws[‘B1’] = “Original B1”

ws.insert_rows(1, amount=2) # 在第1行前插入2行，原来的A1变为A3
ws[‘A1’] = “New A1”
ws[‘A2’] = “New A2”

wb.save(“insert_rows.xlsx”)
print(“行已插入到 insert_rows.xlsx”)
“`

删除行：

“`python
from openpyxl import load_workbook

wb = load_workbook(“insert_rows.xlsx”)
ws = wb.active

ws.delete_rows(1, amount=2) # 删除从第1行开始的2行

wb.save(“delete_rows.xlsx”)
print(“行已从 delete_rows.xlsx 删除”)
“`

插入列：

“`python
from openpyxl import Workbook

wb = Workbook()
ws = wb.active
ws[‘A1’] = “Original A1”
ws[‘B1’] = “Original B1”

ws.insert_cols(1, amount=1) # 在第1列前插入1列，原来的A1变为B1
ws[‘A1’] = “New A1”

wb.save(“insert_cols.xlsx”)
print(“列已插入到 insert_cols.xlsx”)
“`

删除列：

“`python
from openpyxl import load_workbook

wb = load_workbook(“insert_cols.xlsx”)
ws = wb.active

ws.delete_cols(1, amount=1) # 删除从第1列开始的1列

wb.save(“delete_cols.xlsx”)
print(“列已从 delete_cols.xlsx 删除”)
“`

5.2 设置行高和列宽

“`python
from openpyxl import Workbook
from openpyxl.styles import Alignment # 导入Alignment

wb = Workbook()
ws = wb.active

ws[‘A1’] = “This is a long text to demonstrate column width.”
ws[‘B2’] = “This is a very very very long text that will wrap if height allows.”
ws[‘C3’] = “Hello”

设置行高 (以磅为单位)

ws.row_dimensions[1].height = 40
ws.row_dimensions[2].height = 60

设置列宽 (以字符数为单位)

ws.column_dimensions[‘A’].width = 30
ws.column_dimensions[‘B’].width = 25
ws[‘B2’].alignment = Alignment(wrapText=True) # 结合自动换行

wb.save(“row_col_dimensions.xlsx”)
print(“行高和列宽已设置在 row_col_dimensions.xlsx”)
“`

5.3 冻结窗格 (Freeze Panes)

冻结窗格可以使滚动时行或列保持可见。

“`python
from openpyxl import Workbook

wb = Workbook()
ws = wb.active

for row in range(1, 10):
for col in range(1, 10):
ws.cell(row=row, column=col, value=f”Cell({row},{col})”)

冻结第一行

ws.freeze_panes = ‘A2’ # 冻结A1以上的行，A列左边的列

冻结第一列

ws.freeze_panes = ‘B1’ # 冻结A列

冻结第一行和第一列

ws.freeze_panes = ‘B2’ # 冻结A1以上的行，A列左边的列

wb.save(“freeze_panes.xlsx”)
print(“冻结窗格已应用于 freeze_panes.xlsx”)
“`

第六章：公式、图表与图片

6.1 使用公式

openpyxl允许您像在Excel中一样，直接将公式写入单元格。公式的计算结果通常在Excel打开文件时自动更新。

“`python
from openpyxl import Workbook

wb = Workbook()
ws = wb.active
ws.title = “Formulas”

ws[‘A1’] = 10
ws[‘A2’] = 20
ws[‘A3’] = 30

写入求和公式

ws[‘A4’] = “=SUM(A1:A3)”

写入平均值公式

ws[‘B1’] = 100
ws[‘B2’] = 200
ws[‘B3’] = “=AVERAGE(B1:B2)”

wb.save(“formulas_example.xlsx”)
print(“公式已写入 formulas_example.xlsx”)
“`

注意：openpyxl本身不执行公式计算。它只是将公式字符串写入文件。公式的实际计算是在您用Excel或其他兼容软件打开文件时进行的。如果您需要获取公式计算后的值，加载工作簿时应使用data_only=True参数（如第二章所示）。

6.2 插入图表 (Charts)

openpyxl支持创建多种类型的图表，如柱状图、折线图、饼图等。创建图表涉及定义数据源、图表类型和放置位置。

注意：图表功能相对复杂，这里以一个简单的柱状图为例。

“`python
from openpyxl import Workbook
from openpyxl.chart import BarChart, Reference

from openpyxl.chart.series import DataPoint # 用于单个数据点颜色，此处未直接使用

wb = Workbook()
ws = wb.active
ws.title = “Chart Example”

准备数据

data = [
[‘Product’, ‘2020’, ‘2021’],
[‘Product A’, 40, 30],
[‘Product B’, 30, 25],
[‘Product C’, 50, 45],
]

for row in data:
ws.append(row)

创建柱状图对象

chart = BarChart()
chart.type = “col” # 列状图 (Column Chart)
chart.style = 10 # 图表样式
chart.title = “Sales by Product”
chart.y_axis.title = “Sales Volume”
chart.x_axis.title = “Product”

定义数据范围

values: B2到C4 (销售数据)

categories: A2到A4 (产品名称)

titles_from_data: 使用B1和C1作为系列名称

data_ref = Reference(ws, min_col=2, min_row=1, max_col=3, max_row=4)
categories_ref = Reference(ws, min_col=1, min_row=2, max_row=4)

chart.add_data(data_ref, titles_from_data=True)
chart.set_categories(categories_ref)

将图表添加到工作表

ws.add_chart(chart, “A6”) # 将图表放置在A6单元格的位置

wb.save(“charts_example.xlsx”)
print(“图表已插入到 charts_example.xlsx”)
“`

6.3 插入图片 (Images)

您可以在工作表中插入图片，并控制其位置和大小。

“`python
from openpyxl import Workbook
from openpyxl.drawing.image import Image

wb = Workbook()
ws = wb.active
ws.title = “Image Example”

假设您有一个名为 ‘logo.png’ 的图片文件

如果没有，您需要提供一个实际存在的图片路径

try:
img = Image(‘C:\Users\test\Desktop\proj\Deadpool\images\deadpool.png’) # 示例图片路径
# 设置图片大小 (可选)
# img.width = 200
# img.height = 100

# 将图片添加到工作表，并指定左上角锚点单元格
ws.add_image(img, 'A1')

wb.save("image_example.xlsx")
print("图片已插入到 image_example.xlsx")

except FileNotFoundError:
print(“错误：未找到图片文件。请确保 ‘C:\Users\test\Desktop\proj\Deadpool\images\deadpool.png’ 存在或更换为您的图片路径。”)
except Exception as e:
print(f”插入图片时发生错误: {e}”)

`` The pathC:\Users\test\Desktop\proj\Deadpool\images\deadpool.png` is an example path from the provided folder structure. The user would need to ensure this file exists or provide their own.

第七章：数据验证与高级主题

7.1 数据验证 (Data Validation)

数据验证允许您限制用户在单元格中输入的数据类型或值范围，确保数据的一致性和准确性。

“`python
from openpyxl import Workbook
from openpyxl.worksheet.datavalidation import DataValidation

wb = Workbook()
ws = wb.active
ws.title = “Data Validation”

1. 整数范围验证：要求输入1到100之间的整数

dv_int = DataValidation(type=”whole”, operator=”between”, formula1=1, formula2=100)
dv_int.error = “输入的值不在1到100之间。”
dv_int.errorTitle = “输入错误”
dv_int.prompt = “请输入一个1到100之间的整数。”
dv_int.promptTitle = “提示”
ws.add_data_validation(dv_int)
dv_int.add(‘A1:A5’) # 应用到A1到A5单元格

2. 列表验证：从预定义列表中选择

dv_list = DataValidation(type=”list”, formula1='”Red,Green,Blue”‘) # 列表项用逗号分隔
dv_list.prompt = “请从下拉列表中选择颜色。”
dv_list.promptTitle = “颜色选择”
ws.add_data_validation(dv_list)
dv_list.add(‘B1:B5’) # 应用到B1到B5单元格

3. 日期验证：要求输入在某个日期之后的日期

import datetime
today = datetime.date.today()
dv_date = DataValidation(type=”date”, operator=”greaterThan”, formula1=today.strftime(‘%Y/%m/%d’))
dv_date.error = f”输入的日期必须在 {today.strftime(‘%Y/%m/%d’)} 之后。”
ws.add_data_validation(dv_date)
dv_date.add(‘C1:C5’)

wb.save(“data_validation_example.xlsx”)
print(“数据验证规则已应用于 data_validation_example.xlsx”)
“`

7.2 高级主题

7.2.1 条件格式 (Conditional Formatting)

条件格式可以根据单元格的值自动应用样式，使数据更易于分析。

“`python
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill # 导入PatternFill

from openpyxl.styles.differential import DifferentialStyle # 未直接使用

from openpyxl.formatting.rule import ColorScaleRule, CellIsRule, FormulaRule

wb = Workbook()
ws = wb.active
ws.title = “Conditional Formatting”

准备数据

for i in range(1, 11):
ws[f’A{i}’] = i * 10
ws[f’B{i}’] = i * 5
ws[f’C{i}’] = i * 7 # 为C列添加数据，以便Formula Rule有数据可作用

1. 颜色刻度 (Color Scale Rule): A列值越大越红，越小越绿

ws.conditional_formatting.add(‘A1:A10′,
ColorScaleRule(start_type=’min’, start_value=10, start_color=’00FF00′, # 绿色
mid_type=’percentile’, mid_value=50, mid_color=’FFFF00′, # 黄色
end_type=’max’, end_value=100, end_color=’FF0000′)) # 红色

2. 单元格值规则 (Cell Is Rule): B列中值大于30的单元格填充黄色

yellow_fill = PatternFill(start_color=’FFFF00′, end_color=’FFFF00′, fill_type=’solid’)
ws.conditional_formatting.add(‘B1:B10′,
CellIsRule(operator=’greaterThan’, formula=[’30’], fill=yellow_fill))

3. 公式规则 (Formula Rule): C列中，如果A列的值是偶数，则文本为蓝色粗体

blue_font_bold = Font(color=’0000FF’, bold=True)
ws.conditional_formatting.add(‘C1:C10’,
FormulaRule(formula=[‘MOD($A1,2)=0’], font=blue_font_bold))

wb.save(“conditional_formatting_example.xlsx”)
print(“条件格式已应用于 conditional_formatting_example.xlsx”)
“`

7.2.2 处理大型文件：只读模式和只写模式

当处理非常大的Excel文件时，为了节省内存和提高性能，openpyxl提供了只读和只写模式。

只读模式 (Read-only mode): 加载文件时，设置read_only=True。这会以流的方式读取数据，避免将整个文件加载到内存中。

“`python
from openpyxl import load_workbook

# 示例：创建一个大型文件用于测试只读模式

wb_temp = Workbook()

ws_temp = wb_temp.active

for r_idx in range(1, 10001):

ws_temp.append([f”Cell {r_idx},{c_idx}” for c_idx in range(1, 11)])

wb_temp.save(“large_temp_file.xlsx”)

wb = load_workbook(filename=’large_temp_file.xlsx’, read_only=True)

for row in wb.active.iter_rows():

for cell in row:

# print(cell.value)

pass # 处理单元格数据

break # 只读取一行作为示例

wb.close() # 在只读模式下，建议显式关闭工作簿以释放资源

“`
只写模式 (Write-only mode): 创建新的工作簿时，设置write_only=True。这允许您以流的方式写入数据，特别适合生成大型报告。

“`python
from openpyxl import Workbook

wb = Workbook(write_only=True)

ws = wb.create_sheet()

ws.title = “Large Report”

for row_idx in range(1, 10001): # 写入10000行数据

ws.append([f”Data {row_idx}-{col_idx}” for col_idx in range(10)])

wb.save(“large_report.xlsx”)

`` **注意**：在只写模式下，不能访问单元格值或修改现有单元格，只能通过append()`方法添加行。

7.3 错误处理

在实际应用中，文件操作常常会遇到各种问题，良好的错误处理机制至关重要。

“`python
from openpyxl import load_workbook
from openpyxl.utils.exceptions import InvalidFileException
import os # 导入os模块用于文件检查

def safe_load_workbook(filename):
if not os.path.exists(filename):
print(f”错误: 文件 ‘{filename}’ 不存在。”)
return None
try:
wb = load_workbook(filename=filename)
print(f”成功加载文件: {filename}”)
return wb
except InvalidFileException:
print(f”错误: 文件 ‘{filename}’ 不是有效的Excel文件格式。”)
return None
except Exception as e:
print(f”加载文件 ‘{filename}’ 时发生未知错误: {e}”)
return None

示例使用 (假设存在 valid_excel.xlsx 和 corrupt.xlsx)

wb_ok = safe_load_workbook(“valid_excel.xlsx”)

wb_not_found = safe_load_workbook(“non_existent_file.xlsx”)

wb_corrupt = safe_load_workbook(“corrupt.xlsx”) # 假设这是一个损坏或非Excel文件

“`

第八章：最佳实践与总结

8.1 最佳实践

及时保存：在对工作簿进行一系列操作后，不要忘记调用wb.save()来保存更改。频繁保存可以避免数据丢失，但也要注意不要频繁覆盖文件，尤其是在调试阶段。
使用read_only和write_only模式：处理大型Excel文件时，为了优化内存使用和提高性能，强烈建议使用只读模式加载（load_workbook(filename, read_only=True)）和只写模式创建（Workbook(write_only=True)）工作簿。
避免循环访问单元格过多：虽然可以通过ws['A1']或ws.cell()来访问单元格，但在处理大量单元格时，应优先使用iter_rows()和iter_cols()等迭代器，尤其是配合values_only=True，以提高效率。
错误处理：始终将文件操作代码放入try-except块中，以捕获FileNotFoundError、PermissionError、InvalidFileException等常见错误，增加程序的健壮性。
理解Excel数据类型：openpyxl会自动处理Python数据类型到Excel数据类型的转换（例如，Python的datetime对象会自动转换为Excel日期时间格式）。但在写入数据时，了解Excel的底层数据类型有助于更好地控制格式和显示。
避免硬编码：尽量避免在代码中硬编码单元格地址（如'A1'）。对于动态数据，使用cell(row, column, value)方法配合循环或变量会更灵活。
使用常量：对于样式设置中的颜色代码、字体名称等，可以定义常量来提高代码可读性和维护性。

8.2 总结

openpyxl库为Python用户提供了一个强大而灵活的工具集，用于自动化处理Excel电子表格。从基本的读写操作，到复杂的样式设置、公式应用、图表生成乃至数据验证，openpyxl几乎涵盖了Excel的日常所有操作。

通过本篇指南的学习，您应该已经掌握了：

openpyxl的安装和基本概念（工作簿、工作表、单元格）。
如何创建、加载和保存Excel文件。
如何读取和写入单元格数据，以及批量操作数据。
如何设置单元格的字体、颜色、对齐、边框、填充和数字格式。
如何进行行、列的插入、删除、调整大小和冻结窗格。
如何在Excel中应用公式、插入图表和图片。
如何利用数据验证和条件格式提升数据质量和可视化效果。
处理大型文件的策略和基本的错误处理方法。

掌握openpyxl不仅能极大地提高您的工作效率，将繁琐的Excel操作自动化，还能让您在数据处理和报告生成的过程中更加游刃有余。现在，是时候将这些知识运用到您的实际项目中，探索openpyxl的更多可能性了！

祝您在使用openpyxl的旅程中一切顺利，创作出更多高效且优雅的自动化解决方案！

前言

第一章：快速上手 – 安装与基础概念

1.1 安装 openpyxl

1.2 openpyxl 的核心对象

第二章：数据读取与操作

2.1 创建与加载工作簿

创建一个新的工作簿

默认会创建一个名为 “Sheet” 的工作表

可以通过 active 属性获取当前活动的工作表

保存工作簿

加载一个现有工作簿

data_only=True 参数会读取单元格的显示值，而不是公式本身

read_only=True 参数可以提高读取大型文件的性能

2.2 访问工作表

2.3 读取单元格数据

写入一些示例数据以便读取

读取单个单元格

第三章：数据写入与保存

3.1 写入单元格数据

直接赋值

使用 cell() 方法赋值

写入不同类型的数据

3.2 批量写入数据

添加表头

添加多行数据

假设有两列数据

写入表头

逐列写入

3.3 保存工作簿

第四章：单元格格式与样式

4.1 字体 (Font)

4.2 对齐方式 (Alignment)

4.3 边框 (Border)

4.4 填充 (Fill)

纯色填充

4.5 数字格式 (Number Format)

4.6 合并与取消合并单元格

第五章：行与列操作

5.1 插入和删除行/列

5.2 设置行高和列宽

设置行高 (以磅为单位)

设置列宽 (以字符数为单位)

5.3 冻结窗格 (Freeze Panes)

冻结第一行

冻结第一列

ws.freeze_panes = ‘B1’ # 冻结A列

冻结第一行和第一列

ws.freeze_panes = ‘B2’ # 冻结A1以上的行，A列左边的列

第六章：公式、图表与图片

6.1 使用公式

写入求和公式

写入平均值公式

6.2 插入图表 (Charts)

from openpyxl.chart.series import DataPoint # 用于单个数据点颜色，此处未直接使用

准备数据

创建柱状图对象

定义数据范围

values: B2到C4 (销售数据)

categories: A2到A4 (产品名称)

titles_from_data: 使用B1和C1作为系列名称

将图表添加到工作表

6.3 插入图片 (Images)

假设您有一个名为 ‘logo.png’ 的图片文件

如果没有，您需要提供一个实际存在的图片路径

第七章：数据验证与高级主题

7.1 数据验证 (Data Validation)

1. 整数范围验证：要求输入1到100之间的整数

2. 列表验证：从预定义列表中选择

3. 日期验证：要求输入在某个日期之后的日期

7.2 高级主题

from openpyxl.styles.differential import DifferentialStyle # 未直接使用

准备数据

1. 颜色刻度 (Color Scale Rule): A列值越大越红，越小越绿

2. 单元格值规则 (Cell Is Rule): B列中值大于30的单元格填充黄色

3. 公式规则 (Formula Rule): C列中，如果A列的值是偶数，则文本为蓝色粗体

# 示例：创建一个大型文件用于测试只读模式

wb_temp = Workbook()

ws_temp = wb_temp.active

for r_idx in range(1, 10001):

ws_temp.append([f”Cell {r_idx},{c_idx}” for c_idx in range(1, 11)])