openpyxl是python操作excel的核心库，支持读取、写入和修改文件。使用load_workbook()读取，workbook()创建新文件，save()保存修改。通过sheet["a1"].value读取单元格内容，append()添加行数据。样式可通过font、alignment、patternfill等类设置。处理大型文件时，应启用read_only或write_only模式，配合iter_rows()限制读取范围以优化性能。日期时间数据会自动转换为datetime对象，写入时若格式异常可设置number_format属性。此外，避免频繁样式设置、减少公式使用、及时释放资源及考虑使用pandas辅助处理均可提升效率。
使用Python操作Excel，关键在于openpyxl库。它允许你读取、写入和修改Excel文件，而高级技巧则集中在更高效的数据处理、样式定制和公式应用上。
解决方案：
-
安装 openpyxl:
立即学习“Python免费学习笔记（深入）”；
pip install openpyxl
-
读取Excel文件:
from openpyxl import load_workbook workbook = load_workbook(filename="example.xlsx") sheet = workbook.active # 或者 workbook["Sheet1"] cell_value = sheet["A1"].value print(cell_value) # 遍历所有行 for row in sheet.iter_rows(min_row=2, max_row=5, min_col=1, max_col=3, values_only=True): print(row) -
写入Excel文件:
from openpyxl import Workbook workbook = Workbook() sheet = workbook.active sheet["A1"] = "Hello" sheet["B1"] = 42 # 添加一行数据 sheet.append(["World", 99]) workbook.save(filename="output.xlsx")
-
修改Excel文件:
直接通过单元格坐标修改，然后保存。
-
样式定制:
from openpyxl.styles import Font, Color, Alignment, PatternFill cell = sheet["A1"] cell.font = Font(name="Arial", size=12, bold=True, color="FF0000") # 红色加粗Arial字体 cell.alignment = Alignment(horizontal="center", vertical="center") cell.fill = PatternFill(start_color="FFFF0000", end_color="FFFF0000", fill_type="solid") #红色背景
-
公式应用:
sheet["C1"] = "=SUM(A1:B1)"
-
处理大型Excel文件:
使用
read_only=True和write_only=True模式，配合迭代器，可以显著降低内存占用。
openpyxl有哪些常用的函数和方法？
-
工作簿（Workbook）操作：
-
load_workbook(filename): 加载现有Excel文件。 -
Workbook(): 创建新的Excel文件。 -
save(filename): 保存Excel文件。 -
create_sheet(title=None, index=None): 创建新的工作表。 -
remove(worksheet): 删除工作表。 -
sheetnames: 获取所有工作表名称的列表。
-
-
工作表（Worksheet）操作：
-
title: 获取或设置工作表的标题。 -
active: 获取活动工作表。 -
iter_rows(min_row=None, max_row=None, min_col=None, max_col=None, values_only=False): 迭代指定范围内的行。values_only=True可以只获取单元格的值。 -
iter_cols(min_col=None, max_col=None, min_row=None, max_row=None, values_only=False): 迭代指定范围内的列。 -
append(iterable): 在工作表的末尾添加一行数据。 -
merge_cells(range_string): 合并单元格。 -
unmerge_cells(range_string): 取消合并单元格。 -
row_dimensions[row_number]: 获取或设置行的属性（例如，高度）。 -
column_dimensions[column_letter]: 获取或设置列的属性（例如，宽度）。
-
-
单元格（Cell）操作：
-
value: 获取或设置单元格的值。 -
row: 获取单元格所在的行号。 -
column: 获取单元格所在的列号（数字）。 -
column_letter: 获取单元格所在的列字母。 -
coordinate: 获取单元格的坐标（例如，"A1"）。 -
font: 获取或设置单元格的字体样式。 -
fill: 获取或设置单元格的填充样式。 -
alignment: 获取或设置单元格的对齐方式。 -
number_format: 获取或设置单元格的数字格式。 -
border: 获取或设置单元格的边框样式。
-
-
样式（Style）操作：
-
Font(name=None, size=None, bold=None, italic=None, color=None): 创建字体样式。 -
PatternFill(fill_type=None, fgColor=None, bgColor=None): 创建填充样式。 -
Alignment(horizontal=None, vertical=None, wrap_text=None): 创建对齐方式。 -
Border(left=None, right=None, top=None, bottom=None): 创建边框样式。 -
Side(style=None, color=None): 创建边框的边。 -
Color(rgb=None): 创建颜色。
-
如何处理Excel中的日期和时间数据？
openpyxl 会自动将Excel中的日期和时间值转换为Python的datetime对象。
from openpyxl import load_workbook
from datetime import datetime
workbook = load_workbook(filename="dates.xlsx")
sheet = workbook.active
date_value = sheet["A1"].value
if isinstance(date_value, datetime):
print(date_value.strftime("%Y-%m-%d")) # 格式化日期
else:
print("Not a datetime object")
#写入日期
from openpyxl import Workbook
from datetime import datetime
wb = Workbook()
ws = wb.active
ws['A1'] = datetime(2024, 1, 1)
wb.save("date_written.xlsx")如果你需要将Python的datetime对象写入Excel，openpyxl会自动处理。 如果遇到日期显示不正确的情况，可能是Excel单元格的格式问题，可以在Excel中手动修改单元格的格式，或者使用openpyxl设置单元格的number_format属性。
如何优化openpyxl的性能，处理大型Excel文件？
-
使用
read_only和write_only模式： 当处理大型文件时，使用只读或只写模式可以显著减少内存占用。from openpyxl import load_workbook workbook = load_workbook(filename="large_file.xlsx", read_only=True) for sheet_name in workbook.sheetnames: sheet = workbook[sheet_name] for row in sheet.iter_rows(max_row=100): #限制读取的行数 for cell in row: print(cell.value)对于写入：
from openpyxl import Workbook workbook = Workbook(write_only=True) sheet = workbook.create_sheet() for i in range(1000): row = [i, i * 2, i * 3] sheet.append(row) workbook.save("large_output.xlsx") 使用迭代器：
iter_rows()和iter_cols()方法允许你按行或按列迭代数据，而不是一次性加载整个工作表到内存中。限制读取的范围： 使用
min_row,max_row,min_col,max_col参数来限制读取的数据范围。避免不必要的样式设置： 样式设置会增加内存占用和处理时间。尽量减少样式的使用，或者只在必要时应用样式。
使用公式时注意： 大量公式计算可能会影响性能。考虑是否可以在Python中预先计算结果，然后将结果写入Excel。
及时释放资源： 在处理完工作簿后，确保关闭文件，释放资源。
使用更高效的库（如果适用）： 对于非常大的文件，可以考虑使用其他更底层的库，例如
csv或pandas，然后将数据导出到Excel。pandas在处理数据方面通常比直接使用openpyxl更高效。
