网站导航
> 技术文档 > 【Python】openpyxl的使用方法_python openpyxl

【Python】openpyxl的使用方法_python openpyxl

网友: 技术文档 2025-07-30 13:14:19

openpyxl 深度解析与实战指南

openpyxl 是一个用于读取和写入 Excel 2010 xlsx/xlsm/xltx/xltm 文件的 Python 库。它允许 Python 开发者通过编程方式创建、修改和查询 Excel 文件中的数据、样式、图表等内容，是 Python 进行 Excel 自动化办公不可或缺的利器。

第一章：openpyxl 概述与基础
1.1 什么是 openpyxl？

openpyxl 是一个纯 Python 实现的库，专门设计用来处理 Office Open XML (OOXML) 格式的 Excel 文件。OOXML 是 Microsoft Office 2007 及更高版本引入的一种基于 XML 的文件格式标准，以 .xlsx (Excel 工作簿)、.xlsm (启用宏的 Excel 工作簿)、.xltx (Excel 模板) 和 .xltm (启用宏的 Excel 模板) 为主要扩展名。

与早期 Excel 的二进制 .xls 格式（由 xlrd 和 xlwt 等库处理）不同，.xlsx 文件本质上是一个 ZIP 压缩包，其中包含了多个 XML 文件和文件夹，用于描述工作簿的结构、内容、样式、元数据等。openpyxl 的核心工作原理就是解析和生成这些 XML 文件。

这意味着 openpyxl 不能 直接处理老旧的 .xls 文件格式。如果需要处理 .xls 文件，您需要使用如 xlrd (读取) 和 xlwt (写入) 这样的库，或者先将 .xls 文件手动或通过其他工具转换为 .xlsx 格式。

1.2 为什么选择 openpyxl？

在 Python 生态中，有多个库可以处理 Excel 文件，但 openpyxl 因其全面的功能和对现代 Excel 格式的良好支持而备受欢迎。

openpyxl 的主要优点：

  1. 全面的 .xlsx 支持：能够读取和写入几乎所有 Excel .xlsx 文件的特性，包括数据、公式、样式、图表、图片、条件格式、数据验证等。
  2. 活跃的社区和持续的更新：openpyxl 是一个积极维护的项目，能够及时修复 bug 并增加对新 Excel 特性的支持。
  3. 相对丰富的文档：官方文档较为完善，提供了大量的示例和API参考。
  4. 纯 Python 实现：易于安装和部署，通常不依赖于外部的非 Python 库（除了处理图片时可能需要 Pillow）。
  5. 与其他库的良好集成：可以方便地与 pandas (用于数据分析)、NumPy (用于数值计算) 等流行的数据科学库结合使用。
  6. 支持读写大型文件：提供了只读模式 (read_only=True) 和只写模式 (write_only=True)，以及基于迭代器的方法，可以有效地处理大型 Excel 文件，减少内存消耗。
  7. 细致的样式控制：允许开发者精确地控制单元格的字体、颜色、边框、填充、对齐方式、数字格式等。
  8. 图表创建与编辑：支持创建多种类型的图表（如条形图、折线图、饼图等）并将其嵌入工作表。

与其他库的简要比较：

  • xlrd / xlwt / xlutils:

    • xlrd: 主要用于读取旧版 .xls 文件。对于 .xlsx 文件的支持有限且已不再积极维护新特性。
    • xlwt: 主要用于写入旧版 .xls 文件。同样，不适用于现代 .xlsx 格式。
    • xlutils: 提供了一些在 xlrd 和 xlwt 之间操作的工具，例如修改已有的 .xls 文件。
    • 结论：如果你的工作主要围绕 .xls 文件，这些库是必要的。但对于 .xlsx，openpyxl 是更好的选择。

  • XlsxWriter:

    • 这是一个非常优秀的库，专门用于创建新的 .xlsx 文件。它以性能高、功能丰富（尤其在图表和格式化方面）著称。
    • 主要区别：XlsxWriter 只能创建新文件，不能读取或修改现有的 .xlsx 文件。
    • 结论：如果你的需求是生成复杂的、格式精美的 Excel 报告而不需要修改现有文件，XlsxWriter 是一个非常好的选择，有时甚至比 openpyxl 更快或功能更特定。如果需要读写和修改，openpyxl 更合适。

  • pandas:

    • pandas 是 Python 数据分析的核心库，它内置了读取和写入 Excel 文件的功能 (底层通常会调用 openpyxl 或 xlrd 等引擎)。
    • pandas 的 read_excel() 和 to_excel() 方法非常适合进行表格数据的快速导入导出。
    • 主要区别：pandas 更侧重于数据的表格化处理和分析，对于 Excel 文件中非表格数据（如图表、复杂样式、VBA宏等）的细致操作能力不如 openpyxl。当你需要超越纯粹的数据读写，例如精确控制单元格格式、操作图表、处理合并单元格等高级 Excel 特性时，openpyxl 提供了更底层的接口。
    • 结论：对于以数据为中心的 Excel 操作，pandas 非常方便。但如果需要深度控制 Excel 文件的结构和表现，openpyxl 是首选。两者也经常结合使用，例如用 pandas 处理数据，然后用 openpyxl 进行格式美化和添加图表。
1.3 安装 openpyxl

安装 openpyxl 非常简单，可以使用 pip（Python 的包安装器）进行安装。建议在虚拟环境中安装，以保持项目依赖的隔离。

打开您的终端或命令行提示符，然后运行以下命令：

pip install openpyxl

这条命令会从 Python Package Index (PyPI) 下载最新稳定版的 openpyxl 及其基本依赖并进行安装。

如果你需要 openpyxl 支持处理图片（例如向 Excel 中插入图片），你还需要安装 Pillow 库，openpyxl 会在需要时提示你。可以一并安装：

pip install openpyxl Pillow

验证安装：

安装完成后，可以在 Python 解释器中尝试导入 openpyxl 来验证安装是否成功：

import openpyxl # 导入 openpyxl 库print(openpyxl.__version__) # 打印 openpyxl 库的版本号

如果命令成功执行并打印出版本号（例如 3.0.10 或更高版本），则表示 openpyxl 已成功安装。

1.4 核心概念

在开始使用 openpyxl 之前，理解其核心对象模型至关重要。这些对象直接映射了 Excel 文件的结构：

  1. Workbook (工作簿):

    • 代表一个完整的 Excel 文件（.xlsx 文件）。
    • 一个 Workbook 对象可以包含一个或多个 Worksheet 对象。
    • 你可以创建一个新的 Workbook，或者从现有文件中加载一个 Workbook。

  2. Worksheet (工作表):

    • 代表工作簿中的一个单独的表格，就是你在 Excel 中看到的带有行和列的单个“标签页”。
    • 每个 Worksheet 都有一个名称（例如 “Sheet1”, “Sheet2”）。
    • Worksheet 对象是进行数据读写和格式设置的主要场所，它包含了所有的单元格。

  3. Cell (单元格):

    • 代表工作表中的一个基本数据单元，位于特定的行和列交叉处。
    • 每个 Cell 对象都有一个值 (value)，可以是数字、字符串、日期时间、布尔值或公式。
    • Cell 对象还拥有许多与样式相关的属性，如字体、颜色、边框、对齐方式等。
    • 单元格的地址通常用字母表示列，用数字表示行，例如 “A1”, “B2”, “C10”。

  4. Row 和 Column (行和列):

    • 虽然 openpyxl 主要通过 Cell 对象进行操作，但它也提供了方便的方式来访问整行或整列的数据。
    • 工作表中的行由数字索引（从1开始），列由字母索引（A, B, C,… AA, AB,…）或数字索引（从1开始）标识。

理解这些基本构建块后，我们就可以开始实际操作 Excel 文件了。

第二章：读取 Excel 文件

读取现有的 Excel 文件是 openpyxl 最常见的用途之一。我们将学习如何打开文件、访问工作表、读取单元格数据等。

2.1 打开现有的工作簿 (Workbook)

要读取一个 Excel 文件，首先需要使用 openpyxl.load_workbook() 函数将其加载为一个 Workbook 对象。

import openpyxl # 导入 openpyxl 库# 假设当前目录下有一个名为 \"example.xlsx\" 的 Excel 文件# 如果文件不在当前目录，需要提供完整或相对路径file_path = \"example.xlsx\" # 定义Excel文件的路径try: # 加载工作簿 workbook = openpyxl.load_workbook(file_path) # 使用 load_workbook 函数打开指定路径的 Excel 文件 print(f\"成功加载工作簿: { file_path}\") # 打印成功加载工作簿的消息 print(f\"工作簿类型: { type(workbook)}\") # 打印工作簿对象的类型 # 之后就可以对 workbook 对象进行操作了 # 例如，获取所有工作表的名称 sheet_names = workbook.sheetnames # 获取工作簿中所有工作表的名称列表 print(f\"工作簿中的工作表名称: { sheet_names}\") # 打印工作表名称列表except FileNotFoundError: # 如果文件未找到 print(f\"错误: 文件 \'{ file_path}\' 未找到。请确保文件路径正确。\") # 打印文件未找到的错误消息except Exception as e: # 捕获其他可能的异常 print(f\"加载工作簿时发生错误: { e}\") # 打印加载工作簿时发生的其他错误

load_workbook() 函数详解：

load_workbook(filename, read_only=False, keep_vba=False, data_only=False, keep_links=True)

  • filename: 必需参数，字符串类型，指定要打开的 .xlsx 文件的路径。

  • read_only (默认为 False):

    • 设置为 True 时，以只读模式打开工作簿。这对于读取大型文件非常有用，因为它使用迭代方式解析 XML，从而显著减少内存占用，提高读取速度。
    • 在只读模式下，你不能修改工作簿或保存它。对工作表和单元格的访问方式也会有所不同（例如，不能通过 ws[\'A1\'] 直接获取单元格，通常需要迭代）。
    • 强烈建议在仅需读取数据，尤其是处理大文件时，将此参数设置为 True。
    import openpyxl # 导入 openpyxl 库file_path = \"large_example.xlsx\" # 定义一个大型Excel文件的路径 (假设存在)try: #以只读模式加载大型工作簿 # workbook_read_only = openpyxl.load_workbook(file_path, read_only=True) # 以只读模式打开Excel文件 # print(f\"成功以只读模式加载工作簿: {file_path}\") # 打印成功加载的消息 # 在只读模式下，通常通过迭代行来访问数据 # active_sheet = workbook_read_only.active # 获取活动工作表 (在只读模式下，可能需要先获取工作表对象) # for row in active_sheet.rows: # 遍历工作表中的每一行 (注意：在只读模式下，active_sheet.rows 可能直接可用) # for cell in row: # 遍历行中的每一个单元格 # # print(cell.value, end=\"\\t\") # 打印单元格的值，并用制表符分隔 # # print() # 每行结束后换行 # workbook_read_only.close() # 只读模式打开的工作簿，在操作完成后显式关闭可以帮助释放资源 (尽管通常Python的垃圾回收会处理) pass # 暂时跳过执行，因为 large_example.xlsx 可能不存在except FileNotFoundError: # 文件未找到异常处理 print(f\"错误: 文件 \'{  file_path}\' 未找到。\") # 打印文件未找到错误except Exception as e: # 其他异常处理 print(f\"以只读模式加载工作簿时发生错误: {  e}\") # 打印其他错误

  • keep_vba (默认为 False):

    • 如果打开的是包含 VBA 宏的 .xlsm 文件，并且希望在保存文件时保留这些宏，则应将此参数设置为 True。
    • 如果为 False (默认)，VBA 宏将会丢失。
    • 注意：openpyxl 不能执行、创建或修改 VBA 宏代码本身，它只能选择是否在读写过程中保留或丢弃现有的宏。
    import openpyxl # 导入 openpyxl 库xlsm_file_path = \"macro_enabled_example.xlsm\" # 定义一个启用宏的Excel文件路径 (假设存在)try: # 加载 .xlsm 文件并保留 VBA 宏 # workbook_with_macros = openpyxl.load_workbook(xlsm_file_path, keep_vba=True) # 打开 .xlsm 文件并设置 keep_vba=True # print(f\"成功加载工作簿 {xlsm_file_path} 并设置保留VBA宏。\") # 打印成功消息 # ... 进行一些操作 ... # workbook_with_macros.save(\"macro_enabled_example_modified.xlsm\") # 保存修改后的文件，宏将被保留 # print(f\"已保存修改，VBA宏应被保留在 macro_enabled_example_modified.xlsm 中。\") pass # 暂时跳过执行except FileNotFoundError: # 文件未找到异常 print(f\"错误: 文件 \'{  xlsm_file_path}\' 未找到。\") # 打印文件未找到错误except Exception as e: # 其他异常 print(f\"加载带宏的工作簿时发生错误: {  e}\") # 打印其他错误

  • data_only (默认为 False):

    • 当设置为 True 时，openpyxl 会尝试读取单元格中公式计算后的值，而不是公式本身。
    • 当为 False (默认) 时，如果单元格包含公式，cell.value 将返回公式字符串 (例如 \"=SUM(A1:A5)\")。
    • 重要限制：openpyxl 本身不执行 Excel 公式的计算。如果设置 data_only=True，它读取的是上次 Excel 应用程序保存文件时计算并存储的值。如果文件是用 openpyxl 生成或修改后保存的，并且没有使用 Excel 打开并重新计算保存，那么这些“计算后”的值可能不是最新的或可能不存在（特别是对于新添加的公式）。
    • 因此，data_only=True 主要用于读取由 Excel 应用程序生成和保存的文件中的数据。
    import openpyxl # 导入 openpyxl 库# 假设 example_formulas.xlsx 中 A3 单元格包含公式 \"=A1+A2\"# 并且 A1=10, A2=20, 文件由Excel保存过，A3显示为30formula_file_path = \"example_formulas.xlsx\" # 定义包含公式的Excel文件路径 (假设存在)# 为了演示，我们先创建一个这样的文件 (如果它不存在)try: wb_temp = openpyxl.Workbook() # 创建一个新的工作簿 ws_temp = wb_temp.active # 获取活动工作表 ws_temp[\'A1\'] = 10 # 设置A1单元格的值为10 ws_temp[\'A2\'] = 20 # 设置A2单元格的值为20 ws_temp[\'A3\'] = \"=A1+A2\" # 设置A3单元格的公式为\"=A1+A2\" ws_temp[\'B1\'] = \"Excel最后计算的值\" # B1单元格文本 ws_temp[\'B3\'] = 30 # 假设这是Excel计算并保存的值 wb_temp.save(formula_file_path) # 保存这个临时文件 print(f\"已创建或覆盖演示文件: {  formula_file_path}\")except Exception as e_create: print(f\"创建演示文件时出错: {  e_create}\")try: # 读取公式 (data_only=False, 默认) workbook_formulas = openpyxl.load_workbook(formula_file_path, data_only=False) # 以 data_only=False 模式打开 sheet_formulas = workbook_formulas.active # 获取活动工作表 cell_a3_formula_obj = sheet_formulas[\'A3\'] # 获取A3单元格对象 print(f\"当 data_only=False 时，A3单元格类型: {  cell_a3_formula_obj.data_type}, 值: {  cell_a3_formula_obj.value}\") # 打印A3单元格的类型和值 # 读取公式计算后的值 (data_only=True) workbook_values = openpyxl.load_workbook(formula_file_path, data_only=True) # 以 data_only=True 模式打开 sheet_values = workbook_values.active # 获取活动工作表 cell_a3_value_obj = sheet_values[\'A3\'] # 获取A3单元格对象 print(f\"当 data_only=True 时， A3单元格类型: {  cell_a3_value_obj.data_type}, 值: {  cell_a3_value_obj.value}\") # 打印A3单元格的类型和值 # 注意：这个值是Excel上次保存时存储的值except FileNotFoundError: # 文件未找到异常 print(f\"错误: 文件 \'{  formula_file_path}\' 未找到。\") # 打印文件未找到错误except Exception as e: # 其他异常 print(f\"处理公式文件时发生错误: {  e}\") # 打印其他错误

  • keep_links (默认为 True):

    • 控制是否保留到外部工作簿或其他文件的链接。如果设置为 False，这些链接的值将被读取，但链接本身将被移除。

处理受密码保护的文件：

openpyxl 本身不支持直接打开受密码保护（指需要密码才能打开或编辑）的 Excel 文件。

  • 只读密码 (Write Reservation Password): 如果文件有一个“建议只读”或“写入预留密码”，但没有强制打开密码，openpyxl 通常可以像打开普通文件一样打开它（可能以只读方式）。
  • 打开密码 (Password to Open): 如果文件设置了打开密码，openpyxl.load_workbook() 会失败，通常抛出 zipfile.BadZipFile 或类似的错误，因为它无法解压受密码保护的 ZIP 存档。
  • 工作表/工作簿结构保护密码: openpyxl 通常可以读取受结构保护（例如，防止修改工作表结构但允许查看数据）的文件的数据，但修改受保护元素时可能会受限或失败。openpyxl 也提供了一些API来设置或移除这类保护（如果已知密码）。

解决方案：

  1. 手动移除密码: 最简单的方法是在 Excel 中手动打开文件，移除密码，然后保存。
  2. 使用其他工具/库:
    • 对于 Windows 环境，可以尝试使用 pywin32 或 pyautogui 等库来模拟人工操作 Excel 应用程序以打开和另存为无密码文件，但这比较复杂且依赖特定环境。
    • 有一些专门的库或方法（例如 msoffcrypto-tool）可以尝试破解或移除 Office 文档密码，但这超出了 openpyxl 的范围，并可能涉及法律和道德问题。
  3. 如果文件是您自己生成的：确保在生成时不设置此类密码，或者记录好密码以便在需要时手动处理。
2.2 操作工作表 (Worksheet)

一旦加载了 Workbook 对象，下一步通常是访问其中的一个或多个 Worksheet。

import openpyxl # 导入 openpyxl 库# 创建一个示例工作簿用于演示 (如果 example.xlsx 不存在或内容不确定)try: wb_demo = openpyxl.Workbook() # 创建一个新的工作簿 ws1 = wb_demo.active # 获取活动工作表 ws1.title = \"SalesReport\" # 设置活动工作表的标题为 \"SalesReport\" ws2 = wb_demo.create_sheet(\"Inventory\") # 创建一个名为 \"Inventory\" 的新工作表 ws3 = wb_demo.create_sheet(\"Employees\", 0) # 创建一个名为 \"Employees\" 的新工作表，并将其插入到索引0的位置 (最前面) wb_demo.save(\"example_for_sheets.xlsx\") # 保存这个演示工作簿 print(\"已创建演示工作簿 \'example_for_sheets.xlsx\'\")except Exception as e_create_demo: print(f\"创建演示工作簿时出错: { e_create_demo}\")file_path = \"example_for_sheets.xlsx\" # 定义Excel文件路径try: workbook = openpyxl.load_workbook(file_path) # 加载工作簿 print(f\"\\n成功加载工作簿: { file_path}，用于演示工作表操作。\") # 1. 获取所有工作表的名称 sheet_names = workbook.sheetnames # 获取工作簿中所有工作表的名称列表 print(f\"所有工作表名称: { sheet_names}\") # 打印工作表名称列表 # 2. 通过名称获取工作表 # 方法一: 使用字典风格的访问 (推荐) if \"SalesReport\" in workbook.sheetnames: # 检查 \"SalesReport\" 是否在工作表名称列表中 sales_report_sheet = workbook[\"SalesReport\"] # 通过名称 \"SalesReport\" 获取对应的工作表对象 print(f\"通过名称获取到工作表: \'{ sales_report_sheet.title}\', 类型: { type(sales_report_sheet)}\") # 打印获取到的工作表标题和类型 else: print(\"工作表 \'SalesReport\' 未找到。\") # 打印未找到工作表的消息 # 方法二: 使用 get_sheet_by_name() (旧版方法，仍然可用，但新版建议用字典风格) # if \"Inventory\" in workbook.sheetnames: # inventory_sheet_old_way = workbook.get_sheet_by_name(\"Inventory\") # (已废弃) 使用 get_sheet_by_name 方法获取工作表 # print(f\"通过 get_sheet_by_name 获取到工作表: \'{inventory_sheet_old_way.title}\'\") # else: # print(\"工作表 \'Inventory\' 未找到。\") # 3. 通过索引获取工作表 # 工作表索引从 0 开始 if len(workbook.worksheets) > 0: # 检查工作簿中是否有工作表 first_sheet = workbook.worksheets[0] # 获取索引为0的工作表 (即第一个工作表) print(f\"通过索引0获取到工作表: \'{ first_sheet.title}\'\") # 打印第一个工作表的标题 if len(workbook.worksheets) > 1: # 检查工作簿中是否有至少两个工作表 second_sheet = workbook.worksheets[1] # 获取索引为1的工作表 (即第二个工作表) print(f\"通过索引1获取到工作表: \'{ second_sheet.title}\'\") # 打印第二个工作表的标题 # 4. 获取当前活动的工作表 # 活动工作表是工作簿在 Excel 中打开时默认显示的那一个 # 通常是最后一个被操作或选中的表，或者是新创建工作簿的第一个表 active_sheet = workbook.active # 获取当前活动的工作表对象 if active_sheet: # 检查活动工作表是否存在 print(f\"当前活动工作表: \'{ active_sheet.title}\'\") # 打印活动工作表的标题 else: print(\"没有活动的单元表。\") # 5. 遍历所有工作表对象 print(\"遍历所有工作表对象:\") for sheet_object in workbook.worksheets: # 遍历工作簿中的每一个工作表对象 print(f\" - 工作表标题: \'{ sheet_object.title}\', 行数: { sheet_object.max_row}, 列数: { sheet_object.max_column}\") # 打印工作表的标题、最大行数和最大列数 # sheet_object 就是一个 Worksheet 实例，可以对其进行读写操作 # 6. 工作表常用属性 if \"SalesReport\" in workbook.sheetnames: # 再次检查工作表是否存在 sample_sheet = workbook[\"SalesReport\"] # 获取 \"SalesReport\" 工作表 print(f\"\\n工作表 \'{ sample_sheet.title}\' 的一些属性:\") print(f\" - 标题 (title): { sample_sheet.title}\") # 打印工作表的标题 print(f\" - 最大行 (max_row): { sample_sheet.max_row}\") # 打印工作表中数据内容的最大行号 print(f\" - 最大列 (max_column): { sample_sheet.max_column}\") # 打印工作表中数据内容的最大列号 (以数字表示) print(f\" - 最小行 (min_row): { sample_sheet.min_row}\") # 打印工作表中数据内容的最小行号 (通常是1) print(f\" - 最小列 (min_column): { sample_sheet.min_column}\") # 打印工作表中数据内容的最小列号 (通常是1) print(f\" - 工作表维度 (dimensions): { sample_sheet.dimensions}\") # 打印工作表包含数据的区域范围，例如 \'A1:C10\' print(f\" - 父工作簿 (parent): { type(sample_sheet.parent)}\") # 打印工作表所属的父工作簿对象的类型 # print(f\" - 工作表颜色 (sheet_properties.tabColor): {sample_sheet.sheet_properties.tabColor}\") # 打印工作表标签页颜色 (如果设置了) # 注意：tabColor 是一个 Color 对象，可能需要 .rgb 或 .value 来获取颜色值 if sample_sheet.sheet_properties.tabColor: # 检查标签页颜色是否已设置 print(f\" - 标签页颜色 (tabColor.rgb): { sample_sheet.sheet_properties.tabColor.rgb}\") # 打印标签页颜色的RGB值 (例如 FF00FF00) else: print(\" - 标签页颜色: 未设置\") # 打印未设置颜色信息except FileNotFoundError: # 文件未找到异常 print(f\"错误: 文件 \'{ file_path}\' 未找到。\") # 打印文件未找到错误except Exception as e: # 其他异常 print(f\"操作工作表时发生错误: { e}\") # 打印其他错误

关于 max_row 和 max_column:

  • 这两个属性返回的是包含数据的最大行号和列号。
  • 如果工作表为空，它们通常返回1（或有时是0，取决于openpyxl版本和文件情况，但通常至少是1x1的维度）。
  • 这些值是根据单元格数据计算的，而不是工作表的物理边界。
  • max_column 返回的是数字，例如1代表’A’列, 2代表’B’列。可以使用 openpyxl.utils.get_column_letter() 将数字转换为字母。
2.3 操作单元格 (Cell)

获取到 Worksheet 对象后，就可以访问和读取其中的单元格了。

import openpyxl # 导入 openpyxl 库from openpyxl.utils import get_column_letter, column_index_from_string # 导入列字母和数字相互转换的工具函数# 准备一个用于演示单元格操作的Excel文件try: wb_cell_demo = openpyxl.Workbook() # 创建一个新的工作簿 ws_cd = wb_cell_demo.active # 获取活动工作表 ws_cd.title = \"CellDemoSheet\" # 设置工作表标题 # 填充一些数据 ws_cd[\'A1\'] = \"姓名\" # 设置A1单元格的值 ws_cd[\'B1\'] = \"年龄\" # 设置B1单元格的值 ws_cd[\'C1\'] = \"城市\" # 设置C1单元格的值 ws_cd[\'D1\'] = \"注册日期\" # 设置D1单元格的值 ws_cd[\'E1\'] = \"得分\" # 设置E1单元格的值 ws_cd[\'F1\'] = \"是否会员\" # 设置F1单元格的值 ws_cd[\'G1\'] = \"总计\" # 设置G1单元格的值 ws_cd[\'A2\'] = \"张三\" # 设置A2单元格的值 ws_cd[\'B2\'] = 30 # 设置B2单元格的值 ws_cd[\'C2\'] = \"北京\" # 设置C2单元格的值 ws_cd[\'D2\'] = openpyxl.utils.datetime.datetime(2023, 1, 15, 10, 30, 0) # 设置D2单元格为日期时间对象 ws_cd[\'E2\'] = 85.5 # 设置E2单元格的值 ws_cd[\'F2\'] = True # 设置F2单元格的值 ws_cd[\'G2\'] = \"=SUM(B2,E2)\" # 设置G2单元格的公式 (这个值在只读data_only=True时才有效) ws_cd.cell(row=3, column=1, value=\"李四\") # 使用 cell 方法设置 (3,1) 即 A3 单元格的值 ws_cd.cell(row=3, column=2, value=25) # 设置 (3,2) 即 B3 单元格的值 ws_cd.cell(row=3, column=3, value=\"上海\") # 设置 (3,3) 即 C3 单元格的值 # D3 为空，E3 为空 ws_cd.cell(row=3, column=6, value=False) # 设置 (3,6) 即 F3 单元格的值 wb_cell_demo.save(\"example_for_cells.xlsx\") # 保存演示文件 print(\"已创建演示工作簿 \'example_for_cells.xlsx\'\")except Exception as e_create_cells_demo: print(f\"创建单元格演示工作簿时出错: { e_create_cells_demo}\")file_path_cells = \"example_for_cells.xlsx\" # 定义Excel文件路径try: workbook = openpyxl.load_workbook(file_path_cells) # 加载工作簿 # workbook_data_only = openpyxl.load_workbook(file_path_cells, data_only=True) # 加载工作簿 (只读数据模式) sheet = workbook[\"CellDemoSheet\"] # 通过名称获取 \"CellDemoSheet\" 工作表 # sheet_data_only = workbook_data_only[\"CellDemoSheet\"] # 获取只读数据模式下的工作表 print(f\"\\n成功加载工作簿: { file_path_cells}，用于演示单元格操作。工作表: \'{ sheet.title}\'\") # --- 访问单元格 --- # 1. 通过坐标字符串访问单元格 (例如 \'A1\', \'B2\') cell_A1 = sheet[\'A1\'] # 获取A1单元格对象 cell_B2 = sheet[\'B2\'] # 获取B2单元格对象 print(f\"单元格 A1 对象: { cell_A1}, 类型: { type(cell_A1)}\") # 打印A1单元格对象及其类型 print(f\"单元格 B2 对象: { cell_B2}, 类型: { type(cell_B2)}\") # 打印B2单元格对象及其类型 # 2. 通过行号和列号访问单元格 (worksheet.cell(row, column)) # row 和 column 都是从 1 开始的整数 cell_R1C3 = sheet.cell(row=1, column=3) # 获取第1行第3列 (即C1) 的单元格对象 cell_R2C4 = sheet.cell(row=2, column=4) # 获取第2行第4列 (即D2) 的单元格对象 print(f\"单元格 (row=1, col=3) 对象: { cell_R1C3}\") # 打印C1单元格对象 print(f\"单元格 (row=2, col=4) 对象: { cell_R2C4}\") # 打印D2单元格对象 # --- 获取单元格的值 (`cell.value`) --- print(\"\\n--- 获取单元格的值 ---\") print(f\"A1 (\'{ cell_A1.coordinate}\') 的值: { cell_A1.value}, 数据类型: { cell_A1.data_type} ({ type(cell_A1.value).__name__})\") # 打印A1单元格的坐标、值、数据类型和值的Python类型 print(f\"B2 (\'{ cell_B2.coordinate}\') 的值: { cell_B2.value}, 数据类型: { cell_B2.data_type} ({ type(cell_B2.value).__name__})\") # 打印B2单元格信息 print(f\"C1 (\'{ cell_R1C3.coordinate}\') 的值: { cell_R1C3.value}, 数据类型: { cell_R1C3.data_type} ({ type(cell_R1C3.value).__name__})\") # 打印C1单元格信息 # --- 单元格的数据类型 (`cell.data_type` 和 `type(cell.value)`) --- # `cell.data_type` 返回的是 Excel 内部定义的数据类型缩写： # \'s\': string (字符串) # \'n\': number (数字, 包括整数和浮点数) # \'d\': datetime (日期时间) # \'b\': boolean (布尔值) # \'e\': error (错误类型, 例如 #DIV/0!) # \'f\': formula (公式) - 当 data_only=False 时 # \'str\': formula string (公式的缓存值是字符串) - 当 data_only=True 且公式结果是文本时 # \'inlineStr\': 内联字符串 (不常见) # `type(cell.value)` 返回的是 Python 中该值的实际数据类型。 print(\"\\n--- 不同数据类型的单元格 ---\") cell_D2 = sheet[\'D2\'] # 获取D2单元格 (日期时间) cell_E2 = sheet[\'E2\'] # 获取E2单元格 (浮点数) cell_F2 = sheet[\'F2\'] # 获取F2单元格 (布尔值) cell_A3 = sheet[\'A3\'] # 获取A3单元格 (字符串) cell_B3 = sheet.cell(row=3, column=2) # 获取B3单元格 (整数) cell_D3 = sheet[\'D3\'] # 获取D3单元格 (空单元格) print(f\"D2 (\'{ cell_D2.coordinate}\') 值: { cell_D2.value}, data_type: { cell_D2.data_type}, python_type: { type(cell_D2.value).__name__}\") # 打印D2单元格信息 # 对于日期时间，openpyxl 会自动将其转换为 Python 的 datetime.datetime 对象 print(f\"E2 (\'{ cell_E2.coordinate}\') 值: { cell_E2.value}, data_type: { cell_E2.data_type}, python_type: { type(cell_E2.value).__name__}\") # 打印E2单元格信息 print(f\"F2 (\'{ cell_F2.coordinate}\') 值: { cell_F2.value}, data_type: { cell_F2.data_type}, python_type: { type(cell_F2.value).__name__}\") # 打印F2单元格信息 print(f\"A3 (\'{ cell_A3.coordinate}\') 值: { cell_A3.value}, data_type: { cell_A3.data_type}, python_type: { type(cell_A3.value).__name__}\") # 打印A3单元格信息 print(f\"B3 (\'{ cell_B3.coordinate}\') 值: { cell_B3.value}, data_type: { cell_B3.data_type}, python_type: { type(cell_B3.value).__name__}\") # 打印B3单元格信息 print(f\"D3 (\'{ cell_D3.coordinate}\') 值: { cell_D3.value}, data_type: { cell_D3.data_type}, python_type: { type(cell_D3.value).__name__}\") # 打印D3单元格信息 (空单元格值为 None) # --- 读取日期和时间数据 --- # Excel 内部将日期时间存储为序列号 (浮点数)，表示自1900年1月0日（或1904年1月1日，取决于工作簿设置）以来的天数。 # `openpyxl` 在读取时会自动将这些序列号转换为 Python 的 `datetime.datetime` 或 `datetime.time` 对象 (如果单元格被正确格式化为日期/时间)。 # `cell.is_date` 属性可以用来判断 `openpyxl` 是否认为该单元格是一个日期格式。 print(f\"\\n单元格 D2 是否被识别为日期格式 (is_date): { cell_D2.is_date}\") # 打印D2单元格是否为日期格式 if cell_D2.is_date and isinstance(cell_D2.value, openpyxl.utils.datetime.datetime): # 检查是否为日期且值为datetime对象 print(f\"D2 日期时间值: { cell_D2.value.strftime(\'%Y-%m-%d %H:%M:%S\')}\") # 将datetime对象格式化为字符串输出 # --- 读取公式 --- # 默认情况下 (load_workbook 时 data_only=False)，如果单元格包含公式，cell.value 返回公式字符串。 cell_G2 = sheet[\'G2\'] # 获取G2单元格 (包含公式 \"=SUM(B2,E2)\") print(f\"\\nG2 (\'{ cell_G2.coordinate}\') 值 (data_only=False): { cell_G2.value}, data_type: { cell_G2.data_type}\") # 打印G2单元格的值和数据类型 # 如果以 data_only=True 模式加载工作簿，cell.value 会尝试返回公式计算后的值。 # （需要重新加载工作簿或使用之前加载的 workbook_data_only） workbook_data_only_temp = openpyxl.load_workbook(file_path_cells, data_only=True) # 以data_only=True模式重新加载 sheet_data_only_temp = workbook_data_only_temp[\"CellDemoSheet\"] # 获取工作表 cell_G2_val_only = sheet_data_only_temp[\'G2\'] # 获取G2单元格对象 print(f\"G2 (\'{ cell_G2_val_only.coordinate}\') 值 (data_only=True): { cell_G2_val_only.value}, data_type: { cell_G2_val_only.data_type}\") # 打印G2单元格的值和数据类型 # 注意：这个值是 Excel 上次保存文件时存储的计算结果。 # 如果文件是由 openpyxl 生成且未被 Excel 打开并重新计算保存，这个值可能是 None 或者上次写入的静态值。 # --- 单元格的其他属性 --- print(f\"\\n单元格 A1 (\'{ cell_A1.coordinate}\') 的其他一些属性:\") print(f\" - 行号 (row): { cell_A1.row}\") # 打印单元格的行号 (从1开始) print(f\" - 列号 (column): { cell_A1.column}\") # 打印单元格的列号 (数字表示, 从1开始) print(f\" - 列字母 (column_letter): { cell_A1.column_letter}\") # 打印单元格的列字母 (例如 \'A\') print(f\" - 坐标 (coordinate): { cell_A1.coordinate}\") # 打印单元格的坐标字符串 (例如 \'A1\') # print(f\" - 样式 (has_style): {cell_A1.has_style}\") # 检查单元格是否有自定义样式 (较早版本属性，新版可能通过 style 对象判断) # print(f\" - 数字格式 (number_format): {cell_A1.number_format}\") # 打印单元格的数字格式字符串 (例如 \'General\', \'yyyy-mm-dd\') # print(f\" - 字体 (font.name): {cell_A1.font.name if cell_A1.font else \'N/A\'}\") # 打印单元格的字体名称 (需要 cell.font 对象存在) # 样式相关的属性将在后续章节详细讲解 # 辅助工具：列字母和数字的转换 print(f\"\\n列字母与数字转换示例:\") print(f\"列号 1 对应的字母: { get_column_letter(1)}\") # 将列号1转换为字母 \'A\' print(f\"列号 27 对应的字母: { get_column_letter(27)}\") # 将列号27转换为字母 \'AA\' print(f\"列字母 \'C\' 对应的数字: { column_index_from_string(\'C\')}\") # 将列字母 \'C\' 转换为数字 3 print(f\"列字母 \'AB\' 对应的数字: { column_index_from_string(\'AB\')}\") # 将列字母 \'AB\' 转换为数字 28except FileNotFoundError: # 文件未找到异常 print(f\"错误: 文件 \'{ file_path_cells}\' 未找到。\") # 打印文件未找到错误except Exception as e: # 其他异常 print(f\"操作单元格时发生错误: { e}\") # 打印其他错误 import traceback # 导入traceback模块 traceback.print_exc() # 打印详细的异常堆栈信息

关于空单元格的说明：

  • 如果一个单元格在 Excel 文件中从未被写入过数据或样式，openpyxl 在访问它时（例如 sheet[\'Z100\'] 如果Z100是空的）会动态创建一个 Cell 对象。
  • 这些新创建的空单元格的 value 属性为 None。
  • max_row 和 max_column 属性仅反映包含实际数据（非 None 或非默认样式）的单元格范围。仅仅通过 sheet[\'Z100\'] 访问一个遥远的空单元格不会立即改变 max_row 或 max_column，除非你给它赋值。
第二章：读取 Excel 文件
2.4 迭代读取单元格数据

当需要处理工作表中大量数据时，逐个访问单元格 (sheet[\'A1\'], sheet.cell(row=1, column=1)) 效率不高且不方便。openpyxl 提供了多种迭代器来高效地遍历行、列或特定区域的单元格。

2.4.1 遍历工作表中的所有行 (worksheet.rows)

worksheet.rows 属性返回一个生成器 (generator)，该生成器会逐行产生一个元组 (tuple)，每个元组包含该行中所有的 Cell 对象。

import openpyxl # 导入 openpyxl 库# 使用之前创建的 \'example_for_cells.xlsx\' 文件file_path_cells = \"example_for_cells.xlsx\" # 定义Excel文件路径try: workbook = openpyxl.load_workbook(file_path_cells) # 加载工作簿 sheet = workbook[\"CellDemoSheet\"] # 获取 \"CellDemoSheet\" 工作表 print(f\"\\n--- 遍历工作表 \'{ sheet.title}\' 的所有行 (使用 sheet.rows) ---\") # sheet.rows 返回一个生成器，每个元素是一行单元格对象的元组 # 例如: ((, ),  # (, )) row_count = 0 # 初始化行计数器 for row_tuple in sheet.rows: # 遍历 sheet.rows 返回的每一行元组 row_count += 1 # 行计数器加1 print(f\"第 { row_count} 行数据 (元组包含Cell对象):\") # 打印当前行号 cell_values_in_row = [] # 初始化一个列表，用于存放当前行中每个单元格的值 for cell_obj in row_tuple: # 遍历当前行元组中的每一个Cell对象 cell_values_in_row.append(cell_obj.value) # 将Cell对象的值添加到列表中 # 也可以直接在这里处理 cell_obj，例如打印其坐标和值 # print(f\" 坐标: {cell_obj.coordinate}, 值: {cell_obj.value}, 类型: {cell_obj.data_type}\") print(f\" 本行所有单元格的值: { cell_values_in_row}\") # 打印本行所有单元格的值的列表 print(f\"\\n工作表 \'{ sheet.title}\' 共有 { sheet.max_row} 行 (通过 sheet.max_row 获取)。\") # 打印工作表的总行数 print(f\"通过 sheet.rows 迭代了 { row_count} 行。\") # 打印通过迭代器实际处理的行数 # 注意：sheet.rows 会遍历到工作表维度 (sheet.dimensions) 内的所有行， # 即使某些行在维度内是完全空的。 # 它基于 `sheet.min_row`, `sheet.max_row`, `sheet.min_column`, `sheet.max_column`. # 如果只想获取单元格的值，而不是Cell对象，可以使用 values_only=True (见下文 iter_rows) # 或者直接迭代 sheet.values (这是一个更简洁的方式，从 openpyxl 2.5 开始提供) print(f\"\\n--- 遍历工作表 \'{ sheet.title}\' 的所有值 (使用 sheet.values, 更简洁) ---\") # sheet.values 也是一个生成器，每行产生一个值的元组 row_count_values_only = 0 # 初始化行计数器 (仅值) for row_values_tuple in sheet.values: # 遍历 sheet.values 返回的每一行值的元组 row_count_values_only += 1 # 行计数器加1 print(f\"第 { row_count_values_only} 行的值 (元组): { row_values_tuple}\") # 打印当前行的值元组 # 例如: (\'姓名\', \'年龄\', \'城市\', \'注册日期\', \'得分\', \'是否会员\', \'总计\') # (\'张三\', 30, \'北京\', datetime.datetime(2023, 1, 15, 10, 30), 85.5, True, \'=SUM(B2,E2)\') # (\'李四\', 25, \'上海\', None, None, False, None) print(f\"通过 sheet.values 迭代了 { row_count_values_only} 行。\") # 打印通过迭代器实际处理的行数except FileNotFoundError: # 文件未找到异常 print(f\"错误: 文件 \'{ file_path_cells}\' 未找到。\") # 打印文件未找到错误except Exception as e: # 其他异常 print(f\"遍历行时发生错误: { e}\") # 打印其他错误 import traceback # 导入traceback模块 traceback.print_exc() # 打印详细的异常堆栈信息

sheet.rows 的行为特性:

  • 返回一个生成器，这意味着它不会一次性将所有行数据加载到内存中，对于大文件更高效。
  • 迭代的范围是从 sheet.min_row 到 sheet.max_row，以及从 sheet.min_column 到 sheet.max_column。
  • 如果某一行在 min_row 和 max_row 之间，但该行没有任何单元格数据（即 Excel 文件中该行是完全空的，但其上下有数据），sheet.rows 仍然会为这一行生成一个元组，该元组中的 Cell 对象的 value 属性通常为 None。
  • 每个内部元组的长度由 sheet.max_column 决定。

sheet.values 属性 (自 openpyxl 2.5):

  • 这是一个更便捷的属性，它也返回一个生成器，但直接产生每行单元格值的元组，而不是 Cell 对象。
  • 这等效于 sheet.iter_rows(values_only=True)（我们将在后面详细介绍 iter_rows）。
  • 如果你只需要数据值而不需要单元格的其他属性（如样式、注释、超链接），使用 sheet.values 是最高效和最简洁的方式。
2.4.2 遍历工作表中的所有列 (worksheet.columns)

与 sheet.rows 类似，worksheet.columns 属性返回一个生成器，该生成器会逐列产生一个元组，每个元组包含该列中所有的 Cell 对象。

import openpyxl # 导入 openpyxl 库from openpyxl.utils import get_column_letter # 导入 get_column_letter 工具函数file_path_cells = \"example_for_cells.xlsx\" # 定义Excel文件路径try: workbook = openpyxl.load_workbook(file_path_cells) # 加载工作簿 sheet = workbook[\"CellDemoSheet\"] # 获取 \"CellDemoSheet\" 工作表 print(f\"\\n--- 遍历工作表 \'{ sheet.title}\' 的所有列 (使用 sheet.columns) ---\") # sheet.columns 返回一个生成器，每个元素是一列单元格对象的元组 # 例如: ((, , ), # (, , )) col_count = 0 # 初始化列计数器 for col_tuple in sheet.columns: # 遍历 sheet.columns 返回的每一列元组 col_count += 1 # 列计数器加1 column_letter = get_column_letter(col_count) # 获取当前列的字母表示 print(f\"第 { col_count} 列 ( \'{ column_letter}\' ) 数据 (元组包含Cell对象):\") # 打印当前列号和字母 cell_values_in_col = [] # 初始化一个列表，用于存放当前列中每个单元格的值 for cell_obj in col_tuple: # 遍历当前列元组中的每一个Cell对象 cell_values_in_col.append(cell_obj.value) # 将Cell对象的值添加到列表中 # print(f\" 坐标: {cell_obj.coordinate}, 值: {cell_obj.value}\") # (可选) 打印单元格坐标和值 print(f\" 本列所有单元格的值: { cell_values_in_col}\") # 打印本列所有单元格的值的列表 print(f\"\\n工作表 \'{ sheet.title}\' 共有 { sheet.max_column} 列 (通过 sheet.max_column 获取)。\") # 打印工作表的总列数 print(f\"通过 sheet.columns 迭代了 { col_count} 列。\") # 打印通过迭代器实际处理的列数 # 注意：与 sheet.rows 类似，sheet.columns 也会遍历到工作表维度内的所有列， # 即使某些列在维度内是完全空的。 # 每个内部元组的长度由 sheet.max_row 决定。except FileNotFoundError: # 文件未找到异常 print(f\"错误: 文件 \'{ file_path_cells}\' 未找到。\") # 打印文件未找到错误except Exception as e: # 其他异常 print(f\"遍历列时发生错误: { e}\") # 打印其他错误 import traceback # 导入traceback模块 traceback.print_exc() # 打印详细的异常堆栈信息

sheet.columns 的行为特性:

  • 同样返回一个生成器。
  • 迭代的范围是从 sheet.min_column 到 sheet.max_column，以及从 sheet.min_row 到 sheet.max_row。
  • 每个内部元组的长度由 sheet.max_row 决定。
  • 重要性能提示：sheet.columns 在内部实现上可能不如 sheet.rows 高效，因为它可能需要更复杂的方式来组织列数据（Excel 文件本质上是按行存储 XML 数据的）。如果需要按列处理数据，并且性能是关键因素，有时先读取所有行然后通过 Python 代码重新组织成列可能会更快，或者考虑使用 pandas 库进行此类操作。但对于中小型文件，sheet.columns 的便利性通常是可以接受的。
  • openpyxl 没有直接对应于 sheet.values 的 sheet.column_values 这样的属性。如果需要按列获取值，通常的模式是先获取所有行（例如用 sheet.values），然后转置。
2.4.3 遍历指定范围的单元格

除了遍历整个工作表的行或列，openpyxl 还允许你遍历指定的单元格区域。

方法一：使用工作表切片 (Worksheet Slicing)

你可以像操作 Python列表的切片一样，对工作表对象使用范围字符串进行切片，例如 sheet[\'A1:C5\']。这将返回一个包含多个元组的元组，其中每个内部元组代表一行，行中的元素是 Cell 对象。

import openpyxl # 导入 openpyxl 库file_path_cells = \"example_for_cells.xlsx\" # 定义Excel文件路径try: workbook = openpyxl.load_workbook(file_path_cells) # 加载工作簿 sheet = workbook[\"CellDemoSheet\"] # 获取 \"CellDemoSheet\" 工作表 target_range_str = \'A1:C2\' # 定义目标范围字符串 \'A1:C2\' print(f\"\\n--- 遍历指定范围 \'{ target_range_str}\' 的单元格 (使用工作表切片) ---\") # sheet[\'A1:C2\'] 返回一个元组的元组，结构类似于： # ( (, , ),  # (, , ) ) # 注意：这不是一个生成器，它会立即加载这个范围内所有Cell对象到内存。 # 因此，对于非常大的区域，这种方法可能消耗较多内存。 cell_range_tuple_of_tuples = sheet[target_range_str] # 通过范围字符串获取单元格区域 (元组的元组) if cell_range_tuple_of_tuples: # 检查获取的区域是否为空 for row_tuple in cell_range_tuple_of_tuples: # 遍历区域中的每一行 (元组) row_values = [] # 初始化一个列表，用于存放当前行中每个单元格的值 for cell_obj in row_tuple: # 遍历当前行元组中的每一个Cell对象 row_values.append(f\"{ cell_obj.coordinate}:{ cell_obj.value}\") # 将单元格坐标和值格式化后添加到列表 print(f\" 行数据: { row_values}\") # 打印当前行的数据 else: print(f\"未能从范围 \'{ target_range_str}\' 获取到数据。\") # 打印未获取到数据的消息 # 获取单行或单列的切片 row_1_cells = sheet[\'1\'] # 获取第1行的所有单元格 (直到max_column) (返回一个Cell对象的元组) # 注意：sheet[\'1\'] 不是 sheet[\'A1:XFD1\']，而是只到实际有数据的最大列 # 它会返回一个扁平的元组，包含该行从min_col到max_col的所有Cell对象。 print(f\"\\n--- 获取第 1 行的所有单元格 (sheet[\'1\']) ---\") if row_1_cells: # 检查是否获取到数据 print(f\"第1行包含 { len(row_1_cells)} 个单元格对象。\") # 打印第1行单元格数量 row_1_values = [cell.value for cell in row_1_cells] # 使用列表推导式获取第1行所有单元格的值 print(f\"第1行的值: { row_1_values}\") # 打印第1行的值 column_B_cells = sheet[\'B\'] # 获取B列的所有单元格 (直到max_row) (返回一个Cell对象的元组) # 类似地，它会返回一个扁平的元组，包含该列从min_row到max_row的所有Cell对象。 print(f\"\\n--- 获取 B 列的所有单元格 (sheet[\'B\']) ---\") if column_B_cells: # 检查是否获取到数据 print(f\"B列包含 { len(column_B_cells)} 个单元格对象。\") # 打印B列单元格数量 col_B_values = [cell.value for cell in column_B_cells] # 使用列表推导式获取B列所有单元格的值 print(f\"B列的值: { col_B_values}\") # 打印B列的值except FileNotFoundError: # 文件未找到异常 print(f\"错误: 文件 \'{ file_path_cells}\' 未找到。\") # 打印文件未找到错误except Exception as e: # 其他异常 print(f\"遍历指定范围 (切片) 时发生错误: { e}\") # 打印其他错误 import traceback # 导入traceback模块 traceback.print_exc() # 打印详细的异常堆栈信息

切片方式的特点：

  • 语法简洁直观。
  • sheet[\'A1:C5\'] 返回的是一个元组的元组，不是生成器。这意味着它会一次性将指定范围内的所有 Cell 对象加载到内存中。对于非常大的区域，这可能导致较高的内存消耗。
  • sheet[\'1\'] (获取整行) 或 sheet[\'A\'] (获取整列) 也是一次性加载所有相关 Cell 对象。

方法二：使用 worksheet.iter_rows() 和 worksheet.iter_cols()

这两个方法提供了更灵活和内存高效的方式来迭代指定范围的单元格，因为它们返回生成器。

  • worksheet.iter_rows(min_row=None, max_row=None, min_col=None, max_col=None, values_only=False)
  • worksheet.iter_cols(min_row=None, max_row=None, min_col=None, max_col=None, values_only=False)

参数说明：

  • min_row, max_row, min_col, max_col: 定义迭代的边界。这些都是基于1的索引（列可以用数字指定）。如果省略，它们会默认为工作表的实际数据边界 (sheet.min_row, sheet.max_row, etc.)。
  • values_only (默认为 False):
    • 如果为 False，迭代器产生的是 Cell 对象。
    • 如果为 True，迭代器产生的是单元格的值。这通常更快，消耗更少内存，因为不需要创建和填充完整的 Cell 对象。强烈推荐在只关心数据值时使用 values_only=True。
import openpyxl # 导入 openpyxl 库file_path_cells = \"example_for_cells.xlsx\" # 定义Excel文件路径try: workbook = openpyxl.load_workbook(file_path_cells) # 加载工作簿 sheet = workbook[\"CellDemoSheet\"] # 获取 \"CellDemoSheet\" 工作表 # --- 使用 iter_rows() --- print(f\"\\n--- 遍历范围 A1:C2 (使用 iter_rows, 返回Cell对象) ---\") # 迭代第1行到第2行，第1列(A)到第3列(C) for row_tuple in sheet.iter_rows(min_row=1, max_row=2, min_col=1, max_col=3): # 迭代指定行列范围的行 row_data = [cell.value for cell in row_tuple] # 从行元组中的Cell对象提取值 print(f\" 行数据 (Cell对象的值): { row_data}\") # 打印行数据 print(f\"\\n--- 遍历范围 A1:C2 (使用 iter_rows, values_only=True) ---\") for row_values_tuple in sheet.iter_rows(min_row=1, max_row=2, min_col=1, max_col=3, values_only=True): # 迭代指定行列范围的行 (仅值) # row_values_tuple 直接是值的元组，例如 (\'姓名\', \'年龄\', \'城市\') print(f\" 行数据 (直接是值的元组): { row_values_tuple}\") # 打印行数据 (值的元组) # --- 使用 iter_cols() --- print(f\"\\n--- 遍历范围 A1:B3 (使用 iter_cols, 返回Cell对象) ---\") # 迭代第1列(A)到第2列(B)，第1行到第3行 col_idx = 0 # 初始化列索引 for col_tuple in sheet.iter_cols(min_row=1, max_row=3, min_col=1, max_col=2): # 迭代指定行列范围的列 col_idx += 1 # 列索引加1 col_data = [cell.value for cell in col_tuple] # 从列元组中的Cell对象提取值 print(f\" 第 { col_idx} 列数据 (Cell对象的值): { col_data}\") # 打印列数据 print(f\"\\n--- 遍历范围 A1:B3 (使用 iter_cols, values_only=True) ---\") col_idx_vo = 0 # 初始化列索引 (仅值) for col_values_tuple in sheet.iter_cols(min_row=1, max_row=3, min_col=1, max_col=2, values_only=True): # 迭代指定行列范围的列 (仅值) col_idx_vo += 1 # 列索引加1 # col_values_tuple 直接是值的元组，例如 (\'姓名\', \'张三\', \'李四\') print(f\" 第 { col_idx_vo} 列数据 (直接是值的元组): { col_values_tuple}\") # 打印列数据 (值的元组) # 如果只指定 min_row/max_row (或 min_col/max_col)，则另一维度会扩展到工作表的边界 print(f\"\\n--- 遍历第2行所有列 (使用 iter_rows, values_only=True) ---\") for row_val_tuple_for_row2 in sheet.iter_rows(min_row=2, max_row=2, values_only=True): # 迭代第2行 (所有列，仅值) print(f\" 第2行数据: { row_val_tuple_for_row2}\") # 打印第2行数据 # 这会产生一个包含单行数据的迭代器 # 迭代 sheet.max_row 之外的空行会怎样？ # iter_rows/iter_cols 会严格按照指定的 min/max 范围进行。 # 如果 max_row 超出了实际数据行，对于那些超出范围的“虚拟”行， # 如果 values_only=False，它会产生包含Cell对象的元组，这些Cell对象的value为None。 # 如果 values_only=True，它会产生包含None值的元组。 print(f\"\\n--- 尝试迭代超出实际最大行的范围 (使用 iter_rows, values_only=True) ---\") # 假设 sheet.max_row 是 3。我们尝试迭代到第 5 行。 for row_beyond in sheet.iter_rows(min_row=sheet.max_row + 1, max_row=sheet.max_row + 2, min_col=1, max_col=sheet.max_column, values_only=True): # 迭代超出实际数据范围的行 print(f\" 迭代到超出范围的行数据: { row_beyond}\") # 打印超出范围的行数据 (应为None元组)except FileNotFoundError: # 文件未找到异常 print(f\"错误: 文件 \'{ file_path_cells}\' 未找到。\") # 打印文件未找到错误except Exception as e: # 其他异常 print(f\"使用 iter_rows/iter_cols 时发生错误: { e}\") # 打印其他错误 import traceback # 导入traceback模块 traceback.print_exc() # 打印详细的异常堆栈信息

iter_rows() 和 iter_cols() 的优势：

  • 内存效率: 返回生成器，非常适合处理大型数据集，因为它们不会一次性将所有数据加载到内存。
  • 灵活性: 可以精确控制迭代的行和列的范围。
  • values_only=True 选项: 这是性能优化的关键。当只需要数据值时，将其设置为 True 可以避免创建完整的 Cell 对象，从而显著提高读取速度并减少内存使用。
2.4.4 性能考量与 values_only 参数总结
  • 首选 values_only=True: 当你的任务只是读取单元格中的数据值时，始终优先考虑使用 sheet.values (遍历所有行) 或 sheet.iter_rows(values_only=True) / sheet.iter_cols(values_only=True) (遍历特定范围)。这是获取数据最快、内存最友好的方式。
  • 生成器的优势: sheet.rows, sheet.columns, sheet.values, sheet.iter_rows(), sheet.iter_cols() 都返回生成器（除了工作表切片 sheet[\'A1:C5\'] 这种形式）。这意味着它们是惰性求值的，只有在迭代时才会实际读取和处理数据。
  • 避免不必要的 Cell 对象创建: 每个 Cell 对象除了存储 value 外，还可能包含样式、注释、超链接等信息。如果不需要这些额外信息，创建这些对象会带来不必要的开销。
  • 按行读取通常优于按列读取: 由于 Excel 文件格式的底层结构（通常是按行存储 XML），按行迭代（iter_rows, rows, values）通常比按列迭代（iter_cols, columns）在性能上更有优势，尤其是在处理非常宽的表格时。
2.5 读取更复杂的单元格内容

除了基本的数值、字符串和日期，单元格还可能包含超链接和注释。openpyxl 也提供了访问这些信息的方法。

2.5.1 读取超链接 (cell.hyperlink)

如果一个单元格包含超链接，其 hyperlink 属性将是一个 Hyperlink 对象（如果存在链接的话），否则为 None。Hyperlink 对象有以下常用属性：

  • target: 字符串，表示链接的目标 URL 或文件路径。
  • display: 字符串，可选，如果链接的显示文本与目标不同，这里会存储显示文本。通常情况下，单元格的值 (cell.value) 就是显示文本。
  • location: 字符串，可选，如果链接指向同一工作簿内的另一个位置（例如 \'Sheet2\'!A1），这里会存储该位置。
  • tooltip: 字符串，可选，鼠标悬停在超链接上时显示的提示文本（也称为 screen tip）。
  • id: 字符串，超链接在内部的唯一标识符。
import openpyxl # 导入 openpyxl 库# 创建一个包含超链接的示例文件wb_link_demo = openpyxl.Workbook() # 创建一个新的工作簿ws_link = wb_link_demo.active # 获取活动工作表ws_link.title = \"HyperlinkSheet\" # 设置工作表标题# 添加一个外部链接ws_link[\'A1\'] = \"Openpyxl官方文档\" # 设置A1单元格的值 (显示文本)ws_link[\'A1\'].hyperlink = \"https://openpyxl.readthedocs.io/\" # 为A1单元格设置超链接目标URL# openpyxl 会自动将 cell.value 作为显示文本 (如果 hyperlink.display 未设置)# 添加一个指向工作簿内部其他位置的链接ws_link[\'B1\'] = \"跳转到C5\" # 设置B1单元格的值 (显示文本)ws_link.cell(row=1, column=2).hyperlink = \"#\'HyperlinkSheet\'!C5\" # 为B1单元格设置内部链接，指向C5# 或者 ws_link[\'B1\'].hyperlink = Hyperlink(target=None, location=\"\'HyperlinkSheet\'!C5\", display=\"跳转到C5\")ws_link[\'C5\'] = \"这是链接目标C5\" # 在C5单元格设置一个值# 添加一个带有 tooltip 的链接ws_link[\'A3\'] = \"Python官网\" # 设置A3单元格的值# 创建一个 Hyperlink 对象来设置 tooltiplink_obj_with_tooltip = openpyxl.worksheet.hyperlink.Hyperlink( target=\"https://www.python.org\", tooltip=\"访问 Python 官方网站\") # 创建一个Hyperlink对象并设置target和tooltipws_link[\'A3\'].hyperlink = link_obj_with_tooltip # 将创建的Hyperlink对象赋给A3单元格# 保存文件link_file_path = \"example_hyperlinks.xlsx\" # 定义演示文件的路径try: wb_link_demo.save(link_file_path) # 保存工作簿 print(f\"已创建包含超链接的演示文件: { link_file_path}\")except Exception as e_save_link: print(f\"保存超链接演示文件时出错: { e_save_link}\")# 读取包含超链接的文件try: workbook = openpyxl.load_workbook(link_file_path) # 加载包含超链接的工作簿 sheet = workbook[\"HyperlinkSheet\"] # 获取 \"HyperlinkSheet\" 工作表 print(f\"\\n--- 读取工作表 \'{ sheet.title}\' 中的超链接 ---\") cell_A1 = sheet[\'A1\'] # 获取A1单元格 cell_B1 = sheet[\'B1\'] # 获取B1单元格 cell_A3 = sheet[\'A3\'] # 获取A3单元格 cell_C5 = sheet[\'C5\'] # 获取C5单元格 (没有链接) # 检查 A1 单元格的超链接 if cell_A1.hyperlink: # 检查A1单元格是否有超链接 print(f\"单元格 A1 (\'{ cell_A1.value}\') 的超链接信息:\") # 打印单元格值 print(f\" - 目标 (target): { cell_A1.hyperlink.target}\") # 打印超链接的目标URL print(f\" - 显示文本 (display): { cell_A1.hyperlink.display}\") # 打印超链接的显示文本 (可能为None) print(f\" - 位置 (location): { cell_A1.hyperlink.location}\") # 打印内部链接位置 (可能为None) print(f\" - 提示 (tooltip): { cell_A1.hyperlink.tooltip}\") # 打印超链接的提示文本 (可能为None) print(f\" - ID (id): { cell_A1.hyperlink.id}\") # 打印超链接的内部ID else: print(f\"单元格 A1 (\'{ cell_A1.value}\') 没有超链接。\") # 打印没有超链接的消息 # 检查 B1 单元格的超链接 (内部链接) if cell_B1.hyperlink: # 检查B1单元格是否有超链接 print(f\"\\n单元格 B1 (\'{ cell_B1.value}\') 的超链接信息:\") # 打印单元格值 print(f\" - 目标 (target): { cell_B1.hyperlink.target}\") # 打印超链接目标 (对于纯内部链接，target可能为None) print(f\" - 显示文本 (display): { cell_B1.hyperlink.display}\") # 打印显示文本 print(f\" - 位置 (location): { cell_B1.hyperlink.location}\") # 打印内部链接位置 (例如 #\'HyperlinkSheet\'!C5) print(f\" - 提示 (tooltip): { cell_B1.hyperlink.tooltip}\") # 打印提示文本 else: print(f\"单元格 B1 (\'{ cell_B1.value}\') 没有超链接。\") # 打印没有超链接的消息 # 检查 A3 单元格的超链接 (带tooltip) if cell_A3.hyperlink: # 检查A3单元格是否有超链接 print(f\"\\n单元格 A3 (\'{ cell_A3.value}\') 的超链接信息:\") # 打印单元格值 print(f\" - 目标 (target): { cell_A3.hyperlink.target}\") # 打印超链接目标 print(f\" - 提示 (tooltip): { cell_A3.hyperlink.tooltip}\") # 打印提示文本 else: print(f\"单元格 A3 (\'{ cell_A3.value}\') 没有超链接。\") # 打印没有超链接的消息 # 检查 C5 单元格 (应无超链接) if cell_C5.hyperlink: # 检查C5单元格是否有超链接 print(f\"\\n单元格 C5 (\'{ cell_C5.value}\') 有超链接 (这不应该发生)。\") # 打印不应发生的消息 else: print(f\"\\n单元格 C5 (\'{ cell_C5.value}\') 正确地没有超链接。\") # 打印没有超链接的消息 # 遍历所有单元格并检查超链接 (效率不高，仅为演示) print(\"\\n遍历所有单元格查找超链接:\") for row in sheet.iter_rows(): # 遍历工作表的所有行 for cell in row: # 遍历行中的每个单元格 if cell.hyperlink: # 如果单元格有超链接 print(f\" 发现超链接在单元格 { cell.coordinate} (\'{ cell.value}\'):\") # 打印发现超链接的信息 print(f\" -> 目标: { cell.hyperlink.target}, 位置: { cell.hyperlink.location}, 提示: { cell.hyperlink.tooltip}\") # 打印超链接的详细信息 except FileNotFoundError: # 文件未找到异常 print(f\"错误: 文件 \'{ link_file_path}\' 未找到。\") # 打印文件未找到错误except Exception as e: # 其他异常 print(f\"读取超链接时发生错误: { e}\") # 打印其他错误 import traceback # 导入traceback模块 traceback.print_exc() # 打印详细的异常堆栈信息

要点：

  • 直接访问 cell.hyperlink 属性。如果该单元格没有超链接，此属性为 None。
  • 如果存在超链接，cell.hyperlink 是一个 openpyxl.worksheet.hyperlink.Hyperlink 对象。
  • 你需要检查 cell.hyperlink 是否为 None 后再尝试访问其 target, location 等属性，以避免 AttributeError。
2.5.2 读取注释 (cell.comment)

与超链接类似，如果单元格包含注释（在 Excel 中通常是鼠标悬停时出现的小黄色框），其 comment 属性将是一个 Comment 对象，否则为 None。

Comment 对象的主要属性：

  • text: 字符串，注释的实际文本内容。
  • author: 字符串，注释的作者。
  • height: 数字，注释框的高度（以磅为单位）。
  • width: 数字，注释框的宽度（以磅为单位）。
import openpyxl # 导入 openpyxl 库from openpyxl.comments import Comment # 导入 Comment 类，用于创建注释# 创建一个包含注释的示例文件wb_comment_demo = openpyxl.Workbook() # 创建一个新的工作簿ws_comment = wb_comment_demo.active # 获取活动工作表ws_comment.title = \"CommentSheet\" # 设置工作表标题# 添加一个简单的注释ws_comment[\'A1\'] = \"查看注释\" # 设置A1单元格的值comment_A1 = Comment(text=\"这是一个简单的注释内容。\", author=\"Python脚本\") # 创建一个Comment对象ws_comment[\'A1\'].comment = comment_A1 # 将Comment对象赋给A1单元格的comment属性# 添加另一个注释，指定尺寸 (注意：openpyxl写入尺寸的效果可能有限，Excel自身行为可能覆盖)ws_comment[\'B2\'] = 12345 # 设置B2单元格的值comment_B2 = Comment(text=\"这是一个有指定作者的注释。\\n可以包含换行符。\", author=\"数据分析师\", height=50, width=150) # 创建一个带作者和尺寸的Comment对象ws_comment[\'B2\'].comment = comment_B2 # 将Comment对象赋给B2单元格的comment属性# 保存文件comment_file_path = \"example_comments.xlsx\" # 定义演示文件的路径try: wb_comment_demo.save(comment_file_path) # 保存工作簿 print(f\"已创建包含注释的演示文件: { comment_file_path}\")except Exception as e_save_comment: print(f\"保存注释演示文件时出错: { e_save_comment}\")# 读取包含注释的文件try: workbook = openpyxl.load_workbook(comment_file_path) # 加载包含注释的工作簿 sheet = workbook[\"CommentSheet\"] # 获取 \"CommentSheet\" 工作表 print(f\"\\n--- 读取工作表 \'{ sheet.title}\' 中的注释 ---\") cell_A1 = sheet[\'A1\'] # 获取A1单元格 cell_B2 = sheet[\'B2\'] # 获取B2单元格 cell_C3 = sheet[\'C3\'] # 获取C3单元格 (应无注释) # 检查 A1 单元格的注释 if cell_A1.comment: # 检查A1单元格是否有注释 print(f\"单元格 A1 (\'{ cell_A1.value}\') 的注释信息:\") # 打印单元格值 print(f\" - 文本 (text): \'{ cell_A1.comment.text}\'\") # 打印注释的文本内容 print(f\" - 作者 (author): \'{ cell_A1.comment.author}\'\") # 打印注释的作者 print(f\" - 高度 (height): { cell_A1.comment.height}\") # 打印注释框的高度 print(f\" - 宽度 (width): { cell_A1.comment.width}\") # 打印注释框的宽度 else: print(f\"单元格 A1 (\'{ cell_A1.value}\') 没有注释。\") # 打印没有注释的消息 # 检查 B2 单元格的注释 if cell_B2.comment: # 检查B2单元格是否有注释 print(f\"\\n单元格 B2 (\'{ cell_B2.value}\') 的注释信息:\") # 打印单元格值 print(f\" - 文本 (text): \'{ cell_B2.comment.text}\'\") # 打印注释的文本内容 print(f\" - 作者 (author): \'{ cell_B2.comment.author}\'\") # 打印注释的作者 print(f\" - 高度 (height): { cell_B2.comment.height}\") # 打印注释框的高度 print(f\" - 宽度 (width): { cell_B2.comment.width}\") # 打印注释框的宽度 else: print(f\"单元格 B2 (\'{ cell_B2.value}\') 没有注释。\") # 打印没有注释的消息 # 检查 C3 单元格 (应无注释) if cell_C3.comment: # 检查C3单元格是否有注释 print(f\"\\n单元格 C3 (\'{ cell_C3.value}\') 有注释 (这不应该发生)。\") # 打印不应发生的消息 else: print(f\"\\n单元格 C3 (\'{ cell_C3.value}\') 正确地没有注释。\") # 打印没有注释的消息 # 遍历所有单元格并检查注释 (效率不高，仅为演示) print(\"\\n遍历所有单元格查找注释:\") for row in sheet.iter_rows(): # 遍历工作表的所有行 for cell in row: # 遍历行中的每个单元格 if cell.comment: # 如果单元格有注释 print(f\" 发现注释在单元格 { cell.coordinate} (\'{ cell.value}\'):\") # 打印发现注释的信息 print(f\" -> 作者: { cell.comment.author}, 文本: \\\"{ cell.comment.text[:30]}...\\\"\") # 打印注释的作者和部分文本except FileNotFoundError: # 文件未找到异常 print(f\"错误: 文件 \'{ comment_file_path}\' 未找到。\") # 打印文件未找到错误except Exception as e: # 其他异常 print(f\"读取注释时发生错误: { e}\") # 打印其他错误 import traceback # 导入traceback模块 traceback.print_exc() # 打印详细的异常堆栈信息

要点：

  • 访问 cell.comment 属性。如果无注释，则为 None。
  • 如果存在注释，cell.comment 是一个 openpyxl.comments.Comment 对象。
  • 同样，在访问 text, author 等属性前，务必检查 cell.comment 是否为 None。
  • 读取注释时，注释的格式（例如，文本中的换行符）通常会被保留在 text 属性中。
2.6 只读模式 (read_only=True) 下的高效数据读取

我们之前在 load_workbook() 函数中提到过 read_only=True 参数，它对于处理大型 Excel 文件至关重要，可以显著降低内存消耗并提高读取速度。现在我们来更深入地探讨其工作方式和最佳实践。

2.6.1 read_only 模式回顾

当 openpyxl.load_workbook(filename, read_only=True) 被调用时：

  • openpyxl 不会将整个 XML 文件结构一次性加载到内存中并构建完整的 Workbook, Worksheet, Cell 对象树。
  • 相反，它使用一种流式解析（streaming parsing）的方式来处理底层的 XML 文件。这意味着它按需读取数据块。
  • 结果是内存占用大大减少，因为在任何给定时间，只有少量数据在内存中。
  • 读取速度通常也会更快，因为它避免了构建和管理大量对象的开销。
2.6.2 在 read_only 模式下访问数据

在只读模式下，与工作簿和工作表的交互方式会有一些关键区别：

  • 获取工作表: 你仍然可以通过名称 workbook[\'SheetName\'] 或 workbook.active 或索引 workbook.worksheets[i] 获取工作表对象。但这些工作表对象是特殊的“只读工作表”。
  • 访问单元格: 不能 使用 sheet[\'A1\'] 或 sheet.cell(row=1, column=1) 这种直接坐标访问方式来获取单个 Cell 对象。尝试这样做通常会导致错误或返回 None。
  • 迭代是关键: 在只读模式下，读取数据的唯一推荐方式是通过迭代行。
    • worksheet.rows: 返回一个生成器，逐行产生包含只读 Cell 对象的元组。这些 Cell 对象是轻量级的，只包含必要的信息（如值、数据类型，有时还有坐标，但不包含完整的样式信息）。
    • worksheet.values: 同样可用，返回一个生成器，逐行产生包含单元格值的元组。这是在只读模式下获取数据的最常用和最高效的方式。
    • worksheet.iter_rows(values_only=True): 也可以使用，功能上与 worksheet.values 类似，允许你指定迭代范围（尽管在只读模式下，范围参数的行为可能与普通模式略有不同，通常还是会流式处理整个工作表，然后根据范围过滤）。
import openpyxl # 导入 openpyxl 库import time # 导入 time 模块，用于计时import os # 导入 os 模块，用于文件操作# 创建一个较大数据量的Excel文件用于演示只读模式的优势large_file_path = \"large_readonly_demo.xlsx\" # 定义大型演示文件的路径num_rows_large = 50000 # 定义行数 (例如5万行)num_cols_large = 20 # 定义列数 (例如20列)def create_large_excel(filepath, rows, cols): # 定义一个函数用于创建大型Excel文件 \"\"\"创建一个包含指定行数和列数的演示Excel文件\"\"\" if os.path.exists(filepath): # 如果文件已存在 print(f\"文件 { filepath} 已存在，跳过创建。如需重新生成，请先删除它。\") return # 直接返回 print(f\"正在创建大型Excel文件 { filepath} ({ rows}行 x { cols}列)...\") wb_large = openpyxl.Workbook() # 创建一个新的工作簿 (非write_only模式，因为数据量不算极端到必须用write_only) ws_large = wb_large.active # 获取活动工作表 # 写入表头 header = [f\"Column{ j+1}\" for j in range(cols)] # 生成表头列表 ws_large.append(header) # 添加表头行 # 写入数据 for i in range(rows -1): # 循环写入数据行 (减1是因为表头占一行) row_data = [f\"R{ i+2}C{ j+1}\" for j in range(cols)] # 生成一行数据 ws_large.append(row_data) # 添加数据行 if (i + 1) % 10000 == 0: # 每写入10000行打印一次进度 print(f\"
网络标签：

相关问题