Pythonopenpyxl操作Excel教程

想要高效读写Excel文件？Python的openpyxl库是你的不二之选。本文将带你从安装开始，一步步掌握openpyxl的各项实用技巧，包括创建、写入、读取Excel文件，以及处理大型文件时如何优化内存占用。不仅如此，你还将学会如何利用openpyxl设置单元格的字体、颜色、边框等样式，让你的Excel报表更加美观专业。对于Excel中常见的日期时间数据，本文也提供了详细的处理方法和注意事项，教你轻松转换和格式化日期时间数据。通过本文的学习，你将能够摆脱对Microsoft Excel软件的依赖，使用Python灵活、稳定地操作Excel文件，提升工作效率。现在就通过`pip install openpyxl`安装并开始你的Excel自动化之旅吧！

使用openpyxl可高效读写Excel文件，支持样式、日期处理及大型文件优化。首先通过pip install openpyxl安装库；创建文件时用Workbook()生成工作簿，通过sheet.append()或cell(row, col)写入数据，并调用save()保存；读取文件使用load_workbook()加载，遍历iter_rows()获取数据；处理大文件时启用read_only=True或write_only=True模式以降低内存占用；设置字体、填充、边框和对齐方式可实现丰富样式；日期时间数据会自动转换为Python的datetime对象，若需手动转换Excel内部数字可用datetime_from_excel()函数。整个流程无需依赖Microsoft Excel软件，操作灵活且稳定。

在 Python 里要操作 Excel 文件，尤其是 .xlsx 格式的，openpyxl 绝对是我的首选。它用起来直观又强大，无论是简单的单元格读写，还是复杂的样式、图表操作，都能轻松搞定，而且完全不需要电脑上安装 Microsoft Excel 软件，这一点简直是太方便了。

解决方案

要用 openpyxl 读写 Excel 文件，我们首先得把它安装好。通常一个 pip install openpyxl 命令就能解决问题。

写入 Excel 文件

创建一个新的 Excel 文件并写入数据，流程其实挺直接的。我通常会这么做：

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

# 1. 创建一个新的工作簿
# 说实话，每次开始一个新项目，我都会先创建一个空的，这样心里有底
workbook = openpyxl.Workbook()

# 2. 获取当前活动的工作表
# 默认情况下，openpyxl 会创建一个名为 'Sheet' 的工作表
sheet = workbook.active
sheet.title = "我的数据表" # 顺手给它改个名字，更清晰

# 3. 写入数据
# 写入单个单元格，这最常见了
sheet['A1'] = "姓名"
sheet['B1'] = "年龄"
sheet['C1'] = "城市"

# 写入一行数据，用 append 方法挺方便的
data = [
    ["张三", 30, "北京"],
    ["李四", 25, "上海"],
    ["王五", 35, "广州"]
]
for row_data in data:
    sheet.append(row_data)

# 4. 也可以直接指定行和列写入
# 比如，在第5行第1列写入一个值
sheet.cell(row=5, column=1, value="赵六")

# 5. 保存工作簿
# 这步非常关键，有时候我忙起来会忘记保存，结果白忙活一场，所以一定要记得！
try:
    workbook.save("我的第一个Excel.xlsx")
    print("Excel文件 '我的第一个Excel.xlsx' 已成功创建并写入数据。")
except Exception as e:
    print(f"保存文件时出错: {e}")

读取 Excel 文件

读取一个已有的 Excel 文件也同样简单。我们需要加载工作簿，然后选择要操作的工作表，接着就可以遍历单元格来获取数据了。

import openpyxl

# 1. 加载一个已有的工作簿
# 假设我们刚刚创建了 '我的第一个Excel.xlsx'
try:
    workbook = openpyxl.load_workbook("我的第一个Excel.xlsx")
    print("Excel文件已成功加载。")
except FileNotFoundError:
    print("文件未找到，请确保 '我的第一个Excel.xlsx' 存在。")
    exit()

# 2. 获取工作表
# 可以通过名称获取，也可以获取当前活动的工作表
sheet = workbook["我的数据表"] # 通过名称获取
# 或者 sheet = workbook.active # 获取当前活动的工作表

print(f"\n工作表名称: {sheet.title}")
print(f"最大行数: {sheet.max_row}, 最大列数: {sheet.max_column}")

# 3. 遍历读取数据
# 遍历所有行，包括标题行
print("\n遍历所有行:")
for row in sheet.iter_rows():
    row_values = [cell.value for cell in row]
    print(row_values)

# 4. 读取特定单元格的值
print(f"\nA1单元格的值: {sheet['A1'].value}")
print(f"B2单元格的值: {sheet.cell(row=2, column=2).value}")

# 5. 遍历特定范围的单元格
print("\n遍历特定范围 (A2到C4):")
for row in sheet.iter_rows(min_row=2, max_row=4, min_col=1, max_col=3):
    row_values = [cell.value for cell in row]
    print(row_values)

# 关闭工作簿，虽然openpyxl通常会自动处理，但明确关闭是个好习惯
workbook.close()

如何高效地处理大型Excel文件，避免内存溢出？

处理大型 Excel 文件时，特别是那种动辄几十万上百万行的数据，如果一股脑儿全加载到内存里，内存溢出是分分钟的事。我个人在这块儿踩过不少坑，后来发现 openpyxl 提供了两种非常实用的模式来应对：read_only 和 write_only。

read_only 模式是读取大型文件的救星。它不会把整个工作簿都加载到内存中，而是以生成器（generator）的形式一行一行地读取数据。这意味着你每次只处理一行数据，内存占用会非常小。

from openpyxl import load_workbook

# 加载一个非常大的Excel文件，开启只读模式
# 假设 'large_data.xlsx' 是一个包含几十万行数据的Excel文件
try:
    # read_only=True 是关键！
    read_only_workbook = load_workbook('large_data.xlsx', read_only=True)
    read_only_sheet = read_only_workbook.active

    print("开始以只读模式读取大型文件...")
    row_count = 0
    for row in read_only_sheet.iter_rows():
        # 这里只处理当前行的数据，而不是所有数据
        # 比如，我们可以打印前5行看看
        if row_count < 5:
            print([cell.value for cell in row])
        row_count += 1
        # 实际应用中，你可以在这里对数据进行处理、筛选或写入到其他地方
        # 如果数据量特别大，甚至可以考虑分批处理，比如每处理10000行就做一次中间存储
    print(f"总共读取了 {row_count} 行数据。")
    read_only_workbook.close()
except FileNotFoundError:
    print("大型文件 'large_data.xlsx' 未找到。")
except Exception as e:
    print(f"读取大型文件时发生错误: {e}")

而 write_only 模式则适用于需要生成大量数据到 Excel 的场景。在这种模式下，openpyxl 不会维护一个完整的内存中工作簿对象，而是将数据直接写入磁盘，同样能有效控制内存使用。

from openpyxl import Workbook

# 创建一个只写模式的工作簿
# 这对于生成报表或者导出大量数据特别有用
write_only_workbook = Workbook(write_only=True)
write_only_sheet = write_only_workbook.create_sheet() # 必须先创建sheet

# 写入标题行
write_only_sheet.append(["ID", "商品名称", "价格", "数量"])

# 生成大量数据并写入
print("开始以只写模式写入大量数据...")
for i in range(1, 100001): # 写入10万行数据
    write_only_sheet.append([i, f"商品_{i}", i * 1.23, i % 100])

# 保存文件
try:
    write_only_workbook.save("large_output.xlsx")
    print("大型文件 'large_output.xlsx' 已成功创建并写入数据。")
except Exception as e:
    print(f"保存大型文件时出错: {e}")

通过这两种模式，我们就能在处理大规模 Excel 数据时，有效地避免内存成为瓶颈，让程序运行得更稳定。

在openpyxl中，如何操作单元格的样式、字体和边框？

光能读写数据还不够，实际工作中，我们经常需要让 Excel 报表看起来更专业、更美观。openpyxl 在样式控制这块儿做得非常棒，可以精细地调整单元格的字体、颜色、填充、边框和对齐方式。我个人觉得这块儿是 openpyxl 的一个亮点，让自动化生成的报表也能有不错的视觉效果。

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

workbook = Workbook()
sheet = workbook.active
sheet.title = "样式示例"

# 1. 设置字体 (Font)
# 粗体、斜体、颜色、大小，这些都是常用的
bold_red_font = Font(name='Arial', size=12, bold=True, italic=False, color='FF0000') # 红色
sheet['A1'] = "粗体红色标题"
sheet['A1'].font = bold_red_font

# 2. 设置填充色 (PatternFill)
# 背景色，通常用来突出显示某些单元格
yellow_fill = PatternFill(start_color='FFFF00', end_color='FFFF00', fill_type='solid') # 黄色填充
sheet['B1'] = "黄色背景"
sheet['B1'].fill = yellow_fill

# 3. 设置边框 (Border)
# 边框样式可以很丰富，虚线、实现、粗细都能调
thin_border = Border(left=Side(style='thin'),
                     right=Side(style='thin'),
                     top=Side(style='thin'),
                     bottom=Side(style='thin'))
sheet['C1'] = "有边框的单元格"
sheet['C1'].border = thin_border

# 4. 设置对齐方式 (Alignment)
# 居中、左对齐、右对齐，垂直对齐，还有文本换行
center_aligned_text = Alignment(horizontal='center', vertical='center', wrap_text=True)
sheet['D1'] = "居中对齐并自动换行"
sheet['D1'].alignment = center_aligned_text
sheet.column_dimensions['D'].width = 15 # 调整列宽以便看到换行效果

# 5. 组合多种样式
# 通常我们会把多种样式组合起来应用
header_font = Font(name='Calibri', size=14, bold=True, color='FFFFFF') # 白色粗体
header_fill = PatternFill(start_color='0070C0', end_color='0070C0', fill_type='solid') # 蓝色填充
header_border = Border(bottom=Side(style='thick', color='000000')) # 粗黑底边框

header_cells = ['A3', 'B3', 'C3']
header_titles = ['产品名称', '销售额', '利润率']

for i, cell_ref in enumerate(header_cells):
    cell = sheet[cell_ref]
    cell.value = header_titles[i]
    cell.font = header_font
    cell.fill = header_fill
    cell.border = header_border
    cell.alignment = Alignment(horizontal='center', vertical='center')

# 6. 设置行高和列宽
# 这也是样式的一部分，让报表看起来更规整
sheet.row_dimensions[1].height = 30
sheet.column_dimensions['A'].width = 20

try:
    workbook.save("excel_样式示例.xlsx")
    print("Excel文件 'excel_样式示例.xlsx' 已创建，并应用了多种样式。")
except Exception as e:
    print(f"保存文件时出错: {e}")

通过这些 Font、PatternFill、Border 和 Alignment 对象，我们几乎可以实现 Excel 里所有常见的单元格样式设置。有个小技巧是，如果你有很多单元格需要应用相同的样式，可以先定义好一个样式对象，然后重复赋值给不同的单元格，这样代码会更简洁，也方便维护。

处理Excel日期和时间数据时，openpyxl有哪些注意事项？

在处理 Excel 中的日期和时间数据时，我个人觉得最容易让人迷惑的就是 Excel 内部存储日期的方式。它不是直接存字符串或者我们理解的日期格式，而是以数字的形式存储的。具体来说，Excel 把日期看作是从 1900 年 1 月 1 日（或者 1904 年，Mac 版 Excel 有点不同，但通常我们用 1900 年制）开始的天数。比如，数字 1 代表 1900 年 1 月 1 日，数字 2 代表 1900 年 1 月 2 日。时间部分则是这一天中的小数部分。

好在 openpyxl 在这方面做得挺智能的，通常它会自动帮我们把这些数字转换成 Python 的 datetime 对象，这省去了我们很多手动转换的麻烦。

from openpyxl import Workbook
from datetime import datetime, date, time

workbook = Workbook()
sheet = workbook.active
sheet.title = "日期时间示例"

# 1. 写入日期和时间数据
# openpyxl 会自动识别 Python 的 datetime/date/time 对象并正确写入
sheet['A1'] = "日期"
sheet['B1'] = "时间"
sheet['C1'] = "日期时间"
sheet['D1'] = "自定义格式日期"

current_date = date(2023, 10, 26)
current_time = time(14, 30, 0)
current_datetime = datetime(2023, 10, 26, 14, 30, 45)

sheet['A2'] = current_date
sheet['B2'] = current_time
sheet['C2'] = current_datetime

# 如果你希望 Excel 显示特定的日期格式，可以设置单元格的 number_format
# 但 openpyxl 写入时，会先写入 datetime 对象，Excel 会根据其默认或你设置的格式显示
sheet['D2'] = current_date
sheet['D2'].number_format = 'yyyy"年"m"月"d"日"' # 例如：2023年10月26日

# 写入一个纯数字，模拟 Excel 内部的日期存储
sheet['E1'] = "Excel内部日期数字"
sheet['E2'] = 45225 # 这个数字大概对应 2023-10-26

try:
    workbook.save("excel_日期时间示例.xlsx")
    print("Excel文件 'excel_日期时间示例.xlsx' 已创建，包含日期时间数据。")
except Exception as e:
    print(f"保存文件时出错: {e}")

# 2. 读取日期和时间数据时的注意事项
# 加载文件并读取
loaded_workbook = openpyxl.load_workbook("excel_日期时间示例.xlsx")
loaded_sheet = loaded_workbook["日期时间示例"]

print("\n读取日期时间数据:")
# 读取 A2 (日期)
cell_a2_value = loaded_sheet['A2'].value
print(f"A2 (日期) 值: {cell_a2_value}, 类型: {type(cell_a2_value)}")

# 读取 B2 (时间)
cell_b2_value = loaded_sheet['B2'].value
print(f"B2 (时间) 值: {cell_b2_value}, 类型: {type(cell_b2_value)}")

# 读取 C2 (日期时间)
cell_c2_value = loaded_sheet['C2'].value
print(f"C2 (日期时间) 值: {cell_c2_value}, 类型: {type(cell_c2_value)}")

# 读取 D2 (自定义格式日期)
# 尽管我们设置了 number_format，openpyxl 读取时依然会返回 datetime/date 对象
cell_d2_value = loaded_sheet['D2'].value
print(f"D2 (自定义格式日期) 值: {cell_d2_value}, 类型: {type(cell_d2_value)}")
print(f"D2 单元格的 number_format: {loaded_sheet['D2'].number_format}")


# 读取 E2 (Excel内部日期数字)
# 对于这种没有明确日期格式的纯数字，openpyxl 不会主动转换成 datetime
cell_e2_value = loaded_sheet['E2'].value
print(f"E2 (Excel内部日期数字) 值: {cell_e2_value}, 类型: {type(cell_e2_value)}")

# 如果需要手动将 Excel 的日期数字转换为 datetime 对象
# openpyxl 提供了 util.datetime_from_excel 函数
from openpyxl.utils import datetime_from_excel

if isinstance(cell_e2_value, (int, float)):
    converted_date = datetime_from_excel(cell_e2_value)
    print(f"E2 转换为日期: {converted_date}, 类型: {type(converted_date)}")

loaded_workbook.close()

从上面的例子可以看出，openpyxl 在读写 datetime、date、time 对象时，处理得非常智能。但有几点我个人觉得需要特别注意：

1. 自动转换：大多数情况下，openpyxl 会自动将 Excel 中的日期时间数字转换为 Python 的 datetime 对象。这很方便，但如果单元格的格式不是标准的日期时间格式（比如只是一个纯数字，但用户希望它是日期），openpyxl 可能就不会自动转换。
2. number_format 的影响：写入 datetime 对象时，openpyxl 会写入其内部表示。Excel 会根据单元格的 number_format 来决定如何显示。如果你希望强制显示某种格式，需要手动设置 cell.number_format。读取时，number_format 不会影响 openpyxl 返回的 Python 对象类型，它依然会尝试返回 datetime 对象。
3. 手动转换：如果遇到那些 openpyxl 没有自动转换的纯数字，但你知道它们代表日期，可以使用 openpyxl.utils.datetime_from_excel() 函数进行手动转换。这在处理一些“不规范”的 Excel 文件时特别有用。
4. 时区问题：openpyxl 默认处理的是不带时区的 datetime 对象。如果你的应用涉及到不同时区，需要额外用 pytz 或 Python 3.9+ 的 zoneinfo 模块进行处理，这不是 openpyxl 的核心功能，但实际项目中经常会遇到。

总的来说，openpyxl 在日期时间处理上已经很自动化了，但理解 Excel 内部的存储机制，以及知道如何手动干预，能帮助我们更好地应对各种复杂情况。

本篇关于《Pythonopenpyxl操作Excel教程》的介绍就到此结束啦，但是学无止境，想要了解学习更多关于文章的相关知识，请关注golang学习网公众号！