Python操作Excel：openpyxl使用教程

本文深入解析了Python操作Excel的利器——openpyxl库，强调其在处理.xlsx格式文件时的直接有效性。从openpyxl的安装（`pip install openpyxl`）入手，详细讲解了如何加载工作簿、选择工作表（按名称或活动表），以及如何高效地读取和写入单元格数据，包括单个赋值和追加多行数据。此外，还介绍了openpyxl在处理公式、样式和数据类型方面的强大功能，并着重强调了read_only和write_only两种模式在优化大型文件读写性能上的关键作用，分别通过降低内存占用和提升写入效率来应对大数据挑战。掌握openpyxl，能显著提升Python在数据处理和自动化任务中的效率。

最直接有效的方式是使用openpyxl库操作.xlsx格式文件。首先安装openpyxl，通过pip install openpyxl命令完成；接着加载工作簿并选择工作表，可按名称或活动工作表方式访问；随后可读取或写入单元格数据，支持单个赋值和追加多行数据；最后保存工作簿以生成新文件或覆盖原文件。openpyxl还能处理公式、样式、数据类型，并提供read_only和write_only模式优化大型文件的性能，分别降低内存占用与提升写入效率。

Python操作Excel文件，特别是.xlsx格式的，最直接有效的方式就是利用openpyxl这个第三方库。它允许你在不依赖Microsoft Excel软件本身的情况下，对工作簿进行读取、写入和修改，极大地方便了数据处理和自动化任务。

要使用Python操作Excel，核心就是openpyxl库。它提供了一套直观的API来加载工作簿、选择工作表、读写单元格数据，以及保存修改。

解决方案

首先，确保你已经安装了openpyxl。如果没有，一个简单的pip install openpyxl就能搞定。

一旦安装完毕，操作流程通常是这样的：

1. 加载工作簿： 使用openpyxl.load_workbook()函数加载一个现有的Excel文件。

   from openpyxl import load_workbook
   
   try:
       workbook = load_workbook('my_data.xlsx')
       print("工作簿加载成功！")
   except FileNotFoundError:
       print("文件未找到，尝试创建新文件或检查路径。")
       # 如果文件不存在，你可能需要创建它
       # from openpyxl import Workbook
       # workbook = Workbook()
       # workbook.save('my_data.xlsx')

   如果文件不存在，load_workbook会抛出FileNotFoundError。这时，你可以选择捕获异常，然后创建一个新的工作簿，或者提示用户检查文件路径。我个人觉得，对于自动化脚本，最好是能清晰地知道文件是否存在，避免后续操作出错。

2. 选择工作表： 工作簿加载后，你需要指定要操作的工作表。可以通过名称或索引来访问。

   # 按名称选择工作表
   sheet = workbook['Sheet1']
   print(f"当前操作的工作表是：{sheet.title}")
   
   # 获取活动工作表（默认打开时显示的）
   active_sheet = workbook.active
   print(f"活动工作表是：{active_sheet.title}")

   通常我会偏向于用名称，因为索引可能会因为工作表顺序调整而变动，不够稳定。

3. 读取单元格数据： 访问单元格非常直接，就像访问字典键值一样。

   # 读取单个单元格
   cell_a1_value = sheet['A1'].value
   print(f"A1单元格的值：{cell_a1_value}")
   
   # 遍历行和列
   for row in sheet.iter_rows(min_row=1, max_row=5, min_col=1, max_col=3):
       for cell in row:
           print(f"{cell.coordinate}: {cell.value}", end="\t")
       print() # 换行

   iter_rows和iter_cols是处理大量数据时非常高效的方法，它们不会一次性加载所有数据到内存，这对于处理大文件来说至关重要。

4. 写入单元格数据： 直接给单元格的value属性赋值即可。

   sheet['A1'] = "你好，Python!"
   sheet['B1'] = 123
   sheet['C1'] = True
   
   # 写入多行数据
   data_to_write = [
       ["姓名", "年龄", "城市"],
       ["张三", 30, "北京"],
       ["李四", 25, "上海"]
   ]
   for row_data in data_to_write:
       sheet.append(row_data) # append会在最后一行追加数据

   append()方法特别方便，它会自动找到工作表的下一行，然后把列表中的数据依次写入。

5. 保存工作簿： 完成所有修改后，记得保存。

   workbook.save('my_modified_data.xlsx')
   print("文件保存成功！")

   这里要注意，如果你保存的文件名和加载时相同，它会覆盖原文件。所以，养成保存到新文件名的习惯，或者在覆盖前做好备份，是个好习惯。我曾经就因为没注意这个细节，不小心覆盖了重要数据，那感觉真是...心疼。

如何安装和获取openpyxl，以及它在Python生态中的位置？

安装openpyxl非常简单，因为它是一个纯Python库，不依赖于任何外部二进制文件。你只需要打开命令行或终端，然后运行pip install openpyxl。这个命令会从Python包索引（PyPI）下载并安装最新的稳定版本。如果你使用的是虚拟环境（强烈推荐！），请确保在激活的虚拟环境中执行此命令，这样可以保持项目依赖的隔离性。

在Python的数据处理生态系统中，openpyxl扮演着一个非常重要的角色。虽然pandas库在数据分析和处理方面更为强大和全面，但openpyxl专注于Excel文件的读写，并且在处理单元格样式、合并单元格、图表等Excel特有功能时，提供了更细粒度的控制。很多时候，我们会将openpyxl与pandas结合使用：pandas用于数据清洗和转换，然后openpyxl用于将处理后的数据以特定格式（例如，带样式、特定布局）写入Excel文件，或者从复杂格式的Excel中精确抽取数据。这就像一个团队协作，各司其职，共同完成任务。

除了基本的读写，openpyxl如何处理特定数据类型、公式和单元格样式？

openpyxl在处理Excel的复杂性方面做得相当不错。它不仅仅是文本的搬运工。

对于数据类型，openpyxl会尝试自动识别并转换。例如，当你从单元格读取数据时，如果Excel中是数字或日期，openpyxl通常会将其转换为Python的int、float或datetime对象。写入时也类似，Python的数字和日期对象会被正确地写入Excel。但有时候，特别是日期，你可能需要确保格式正确，或者手动指定单元格的number_format属性，以确保Excel能正确显示。

公式的处理也很有意思。当你读取一个包含公式的单元格时，cell.value默认会返回公式本身（例如=SUM(A1:A5)），而不是计算结果。如果你需要获取计算结果，需要设置data_only=True当你加载工作簿时：workbook = load_workbook('my_data.xlsx', data_only=True)。但要注意，这种方式只返回Excel最后一次保存时的计算结果，而不是实时计算。写入公式则直接将公式字符串赋给cell.value即可。

# 写入公式
sheet['A6'] = "=SUM(A1:A5)"
# 尝试读取公式结果 (需要文件在Excel中被计算并保存过)
# workbook_data_only = load_workbook('my_data.xlsx', data_only=True)
# sheet_data_only = workbook_data_only['Sheet1']
# print(f"A6单元格的公式结果 (data_only): {sheet_data_only['A6'].value}")

至于单元格样式，这是openpyxl的强项之一。你可以控制字体、颜色、边框、对齐方式、填充色等等。这通常通过导入Font, PatternFill, Border, Side, Alignment等类来完成。

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

# 设置字体
sheet['A1'].font = Font(name='Arial', size=14, bold=True, italic=True, color="FF0000") # 红色

# 设置填充色
sheet['B1'].fill = PatternFill(start_color="FFFF00", end_color="FFFF00", fill_type="solid") # 黄色背景

# 设置边框
thin_border = Border(left=Side(style='thin'),
                     right=Side(style='thin'),
                     top=Side(style='thin'),
                     bottom=Side(style='thin'))
sheet['C1'].border = thin_border

# 设置对齐
sheet['D1'].alignment = Alignment(horizontal='center', vertical='center')

# 合并单元格
sheet.merge_cells('E1:F1')
sheet['E1'] = "合并单元格示例"
sheet['E1'].alignment = Alignment(horizontal='center', vertical='center')

这些样式操作让你可以生成非常专业的报告，而不仅仅是纯数据表格。我个人在做一些自动化报告时，就经常利用这些功能来美化输出，让报告更具可读性。

处理大型Excel文件时，openpyxl有哪些性能考量和优化策略？

处理大型Excel文件（例如，几十万行甚至上百万行的数据）时，性能和内存消耗是必须考虑的问题。openpyxl在这方面提供了几种优化模式。

最主要的两个模式是read_only和write_only。

1. read_only模式： 当你只需要读取文件，并且文件非常大时，使用load_workbook('your_file.xlsx', read_only=True)。在这个模式下，openpyxl不会将整个文件加载到内存中，而是按需读取。它会返回一个ReadOnlyWorksheet对象，你只能通过迭代器（如iter_rows()）来访问数据。这显著降低了内存占用，尤其是在处理GB级别的文件时，效果非常明显。

   from openpyxl import load_workbook
   
   # 以只读模式加载大型工作簿
   large_workbook = load_workbook('large_data.xlsx', read_only=True)
   large_sheet = large_workbook.active
   
   # 遍历行，逐行处理，而不是一次性加载所有数据
   for row in large_sheet.iter_rows():
       for cell in row:
           # 处理 cell.value
           pass
   large_workbook.close() # 记得关闭工作簿以释放资源

   我曾用这个模式处理过一个包含五十万行数据的报表，如果没有read_only，我的笔记本肯定会内存溢出。

2. write_only模式： 类似地，当你需要写入大量数据到一个新文件时，使用Workbook()的write_only=True参数。这会创建一个WriteOnlyWorkbook对象，工作表也变成WriteOnlyWorksheet。在这种模式下，你不能修改已经写入的单元格，也不能访问单元格的样式属性，因为数据是直接写入流中，不保留在内存里。你需要使用append()方法逐行写入数据。

   from openpyxl import Workbook
   
   # 创建一个只写工作簿
   new_workbook = Workbook(write_only=True)
   new_sheet = new_workbook.create_sheet()
   
   # 写入标题行
   new_sheet.append(["Header 1", "Header 2", "Header 3"])
   
   # 写入大量数据
   for i in range(1, 100000):
       new_sheet.append([f"Data {i}-1", f"Data {i}-2", f"Data {i}-3"])
   
   new_workbook.save('large_output.xlsx')

   这种模式对于生成大型报告文件非常有效，避免了在内存中构建整个Excel结构，从而大大减少了内存消耗和写入时间。

除了这两种模式，还有一些通用的优化建议：

• 避免不必要的循环和单元格访问： 如果你能一次性获取某个范围的数据，尽量避免逐个单元格访问。
• 及时关闭工作簿： workbook.close()可以释放文件句柄和内存资源，特别是在循环处理多个文件时，这一点很重要。
• 选择合适的数据结构： 在Python中处理数据时，使用列表推导式或生成器表达式等高效的数据结构和方法。

理解这些模式和策略，能让你在处理Excel文件时更加游刃有余，无论是面对小型配置表还是庞大的数据仓库。

今天带大家了解了的相关知识，希望对你有所帮助；关于文章的技术知识我们会一点点深入介绍，欢迎大家关注golang学习网公众号，一起学习编程~