Python写入Excel：从数据处理到报表自动化，全面掌握openpyxl与pandas实战252

在现代数据驱动的世界中，Excel作为最普及的数据存储和分析工具之一，扮演着举足轻重的角色。无论是数据分析师、业务人员还是软件工程师，都离不开与Excel的交互。然而，手动处理大量的Excel数据不仅效率低下，而且极易出错。这时，Python作为一门功能强大、语法简洁的编程语言，便成为了自动化Excel数据处理的理想选择。

本文将作为一份详尽的指南，深入探讨如何使用Python高效、灵活地写入Excel数据。我们将聚焦于两个最常用、功能最强大的库：openpyxl（处理Excel文件的主力军）和pandas（数据处理与分析的利器），并辅以其他一些库的简要介绍。无论您是需要生成复杂的报表、批量导入导出数据，还是自动化日常的Excel任务，本文都将为您提供全面而实用的解决方案。

为什么选择Python处理Excel？

在开始技术细节之前，我们先来明确为何Python在Excel自动化方面具有如此大的优势：
自动化： 摆脱重复性的人工操作，将枯燥且耗时的任务（如数据清洗、格式设置、报表生成）交给程序自动完成。
效率： 处理海量数据时，Python能够以远超人工的速度完成任务，大大提升工作效率。
灵活性： Python丰富的库生态系统使其能够轻松集成其他数据源（数据库、API、CSV等），实现数据的多源融合与转换。
精确性： 代码执行逻辑严谨，可以有效避免人为操作可能导致的错误。
可扩展性： 编写的脚本可以轻松地修改、扩展以适应不断变化的业务需求。

本文将围绕Python写入Excel的核心需求展开，从基础操作到高级功能，为您提供清晰的代码示例和详细的解释。

准备工作：安装必要的库

在开始编写代码之前，我们需要确保Python环境中已安装了我们将要使用的库。打开您的终端或命令行工具，执行以下命令：pip install openpyxl pandas xlrd xlsxwriter

openpyxl：用于读写.xlsx格式的Excel文件（推荐）。
pandas：用于数据处理，并提供了方便的数据框（DataFrame）写入Excel的功能。
xlrd：早期用于读取.xls和.xlsx文件，但目前openpyxl更推荐。此处安装主要是为了pandas在读取旧版Excel时可能需要的依赖。
xlsxwriter：一个专注于写入.xlsx文件的库，擅长创建高级图表和条件格式。

Part 1: 使用 openpyxl 写入 Excel 数据

openpyxl是Python处理.xlsx文件（Excel 2007及以上版本）的首选库，功能强大且易于使用。它允许我们创建新的工作簿、操作工作表、写入数据、设置单元格样式、插入公式、图表等。

1.1 创建一个新的工作簿并写入数据

首先，我们从最基本的操作开始：创建一个全新的Excel文件，并向其中写入一些数据。from openpyxl import Workbook
from import Font, PatternFill, Alignment, Border, Side
from import get_column_letter
# 1. 创建一个新的工作簿
wb = Workbook()
# 2. 获取当前活动的工作表 (默认会创建一个名为 'Sheet' 的工作表)
ws =
= "销售数据" # 设置工作表名称
# 3. 写入表头
headers = ["产品ID", "产品名称", "销售数量", "单价", "总价", "销售日期", "备注"]
(headers)
# 4. 写入多行数据
data = [
[1001, "笔记本电脑", 5, 8000.00, "=C2*D2", "2023-01-15", "畅销品"],
[1002, "无线鼠标", 20, 120.50, "=C3*D3", "2023-01-16", "捆绑销售"],
[1003, "机械键盘", 8, 450.00, "=C4*D4", "2023-01-17", ""],
[1004, "显示器", 3, 1500.00, "=C5*D5", "2023-01-18", "高清"],
[1005, "摄像头", 15, 200.00, "=C6*D6", "2023-01-19", ""],
]
for row_data in data:
(row_data)
# 5. 保存工作簿
file_name = ""
(file_name)
print(f"Excel文件 '{file_name}' 已成功创建并写入数据。")

代码解释：
Workbook()：创建一个空的Excel工作簿对象。
：获取当前活动的工作表。默认情况下，新创建的工作簿会有一个名为 'Sheet' 的工作表。
= "销售数据"：重命名工作表的标题。
(row)：这是写入数据最常用的方法。它会将一个列表或元组作为一行数据追加到工作表的末尾。
(row=R, column=C, value=V)：除了append，您也可以通过指定行号和列号来写入单个单元格数据。例如：(row=1, column=1, value="产品ID")。
(file_name)：将工作簿保存到指定的文件路径。

1.2 修改现有的Excel文件

有时候，我们不仅需要创建新的文件，还需要打开一个已存在的Excel文件并对其进行修改。openpyxl同样支持此功能。from openpyxl import load_workbook
# 加载一个已存在的工作簿
try:
wb = load_workbook("")
ws = # 依然获取当前活动的工作表
# 1. 修改某个单元格的值
ws['G2'] = "非常畅销" # 修改 '笔记本电脑' 的备注
# 2. 在末尾追加一行新的数据
new_sale = [1006, "蓝牙耳机", 25, 300.00, "=C7*D7", "2023-01-20", "新品上市"]
(new_sale)
# 3. 创建一个新的工作表并写入数据
new_sheet = wb.create_sheet("库存概览")
new_sheet['A1'] = "产品名称"
new_sheet['B1'] = "当前库存"
new_sheet['A2'] = "笔记本电脑"
new_sheet['B2'] = 50
new_sheet['A3'] = "无线鼠标"
new_sheet['B3'] = 100
# 4. 保存修改后的工作簿
("") # 保存为新文件，避免覆盖原文件
print("Excel文件 '' 已成功修改并保存为 ''。")
except FileNotFoundError:
print("文件未找到，请确保已运行之前的创建文件代码。")

代码解释：
load_workbook("")：加载一个已存在的Excel文件。
直接通过单元格引用（如ws['G2']）或()方法来修改单元格的值。
wb.create_sheet("SheetName")：在工作簿中创建一个新的工作表。

1.3 单元格样式与格式化

仅仅写入数据通常不足以满足报表需求。Excel的强大之处还在于其丰富的格式化功能。openpyxl提供了Font, PatternFill, Alignment, Border等类来设置单元格的样式。from openpyxl import load_workbook
from import Font, PatternFill, Alignment, Border, Side
from import get_column_letter
try:
wb = load_workbook("")
ws = wb["销售数据"] # 获取指定名称的工作表
# 1. 设置表头样式
header_font = Font(name='Arial', size=12, bold=True, color='FFFFFF') # 白色粗体
header_fill = PatternFill(start_color='4F81BD', end_color='4F81BD', fill_type='solid') # 蓝色填充
header_alignment = Alignment(horizontal='center', vertical='center', wrap_text=True)
thin_border = Border(left=Side(style='thin'),
right=Side(style='thin'),
top=Side(style='thin'),
bottom=Side(style='thin'))
for col_idx in range(1, len(ws[1]) + 1): # 遍历第一行的所有单元格
cell = (row=1, column=col_idx)
= header_font
= header_fill
= header_alignment
= thin_border
# 2. 设置数据行样式 (交替行颜色)
light_grey_fill = PatternFill(start_color='E0E0E0', end_color='E0E0E0', fill_type='solid')
for row_idx in range(2, ws.max_row + 1): # 从第二行开始遍历数据行
for col_idx in range(1, ws.max_column + 1):
cell = (row=row_idx, column=col_idx)
= thin_border
if row_idx % 2 == 0: # 偶数行
= light_grey_fill
= Alignment(horizontal='left', vertical='center') # 默认左对齐
# 3. 设置日期列格式 (假设销售日期在第6列)
for row_idx in range(2, ws.max_row + 1):
date_cell = (row=row_idx, column=6)
date_cell.number_format = 'YYYY-MM-DD' # 设置日期格式
# 4. 设置总价列为货币格式 (假设总价在第5列)
for row_idx in range(2, ws.max_row + 1):
price_cell = (row=row_idx, column=5)
price_cell.number_format = '¥#,##0.00' # 设置人民币货币格式
# 5. 自动调整列宽
for col in :
max_length = 0
column = col[0].column # Get the column name (e.g., 'A', 'B')
for cell in col:
try: # Necessary to avoid error on empty cells
if len(str()) > max_length:
max_length = len(str())
except:
pass
adjusted_width = (max_length + 2) # Add a little extra space
ws.column_dimensions[get_column_letter(column)].width = adjusted_width
# 6. 合并单元格 (例如，在底部添加一个总计行)
([]) # 添加一个空行作为间隔
(["", "", "", "总计", "", "", ""]) # 添加总计文字
# 计算总销售额 (假设总价在E列，从第2行到倒数第2行)
# 查找'总计'所在的行
total_row = ws.max_row
(row=total_row, column=5).value = f"=SUM(E2:E{total_row - 2})" # E列的求和
(row=total_row, column=5).number_format = '¥#,##0.00'
ws.merge_cells(start_row=total_row, start_column=1, end_row=total_row, end_column=4)
(row=total_row, column=4).font = Font(name='Arial', size=12, bold=True)
(row=total_row, column=4).alignment = Alignment(horizontal='right')

("")
print("Excel文件 '' 已成功应用样式并保存为 ''。")
except FileNotFoundError:
print("文件未找到，请确保已运行之前的修改文件代码。")
except KeyError:
print("工作表 '销售数据' 未找到，请检查工作表名称。")

代码解释：
Font：设置字体（名称、大小、粗体、颜色）。
PatternFill：设置单元格背景填充（颜色、填充类型）。
Alignment：设置文本对齐方式（水平、垂直、自动换行）。
Border和Side：设置单元格边框（样式、颜色）。
cell.number_format：设置单元格的数字格式，例如日期、货币、百分比等。
get_column_letter(column_index)：将数字列索引转换为Excel字母列（例如，1 -> 'A'）。
ws.column_dimensions[column_letter].width：设置列宽。
ws.merge_cells(start_row, start_column, end_row, end_column)：合并指定范围的单元格。
ws.max_row和ws.max_column：获取当前工作表的最大行号和最大列号。

1.4 写入公式

openpyxl允许您像在Excel中一样直接写入公式。当Excel文件被打开时，这些公式会自动计算结果。# 在之前的代码中，我们已经写入了 "=C2*D2" 这样的公式。
# 示例：计算所有销售的总和
# 假设我们要在最后一行（上面例子中的total_row）的第5列计算总和
# total_row = ws.max_row
# (row=total_row, column=5).value = f"=SUM(E2:E{total_row - 2})" # 假设E列是总价列，并从E2开始计算

请注意，openpyxl本身不会执行公式计算，它只是将公式字符串写入文件。公式的计算会在Excel应用程序打开文件时进行。

Part 2: 使用 pandas 写入 Excel 数据

对于数据科学家和数据分析师来说，pandas是处理表格数据的首选工具。它可以方便地将DataFrame对象直接写入Excel文件，并且支持多张工作表、样式设置等。

2.1 将 DataFrame 写入 Excel

最常见的场景是将Pandas DataFrame对象直接保存到Excel文件中。import pandas as pd
import numpy as np
# 1. 创建一个示例 DataFrame
data = {
'学生ID': [1, 2, 3, 4, 5],
'姓名': ['张三', '李四', '王五', '赵六', '孙七'],
'科目': ['语文', '数学', '英语', '语文', '数学'],
'分数': [95, 88, 72, 91, 79],
'考试日期': pd.to_datetime(['2023-03-10', '2023-03-10', '2023-03-11', '2023-03-11', '2023-03-12']),
'是否及格': ['是', '是', '否', '是', '否']
}
df = (data)
# 2. 将 DataFrame 写入 Excel 文件
# index=False 表示不写入 DataFrame 的行索引
output_file = ""
df.to_excel(output_file, sheet_name='成绩单', index=False)
print(f"DataFrame已成功写入Excel文件 '{output_file}'。")
# 3. 将多个 DataFrame 写入同一个 Excel 文件的不同工作表
df2_data = {
'班级': ['一班', '二班', '三班'],
'学生人数': [35, 32, 38],
'平均分': [85.5, 82.1, 86.3]
}
df_summary = (df2_data)
with ('') as writer:
df.to_excel(writer, sheet_name='学生成绩', index=False)
df_summary.to_excel(writer, sheet_name='班级汇总', index=False)
print("多个DataFrame已成功写入Excel文件 '' 的不同工作表。")

代码解释：
(data)：从字典或其他数据结构创建DataFrame。
df.to_excel(file_path, sheet_name='...', index=False)：这是最直接的方法。

file_path：保存Excel文件的路径。
sheet_name：指定工作表的名称。
index=False：非常重要，用于阻止将DataFrame的索引作为一列写入Excel。

(file_path) as writer:：当需要将多个DataFrame写入同一个Excel文件的不同工作表时，推荐使用ExcelWriter。它能更好地管理文件句柄。

2.2 在现有文件中追加数据或特定位置写入

默认情况下，to_excel会覆盖目标文件。如果想在现有文件中追加数据或写入到特定单元格，需要借助ExcelWriter和openpyxl的配合。from openpyxl import load_workbook
# 创建一个小的DataFrame用于追加
new_students_data = {
'学生ID': [6, 7],
'姓名': ['周八', '吴九'],
'科目': ['英语', '物理'],
'分数': [80, 75],
'考试日期': pd.to_datetime(['2023-03-12', '2023-03-13']),
'是否及格': ['是', '是']
}
df_new_students = (new_students_data)
# 方案一：使用ExcelWriter的mode='a' (append) 和 if_sheet_exists参数
# 注意：这种方式如果sheet_name已存在，会覆盖该sheet，而不是追加行
# 要真正追加行，需要先读取，再合并DataFrame，再写回。
# 或者使用openpyxl进行更精细的控制
# 方案二：结合openpyxl和pandas，更精细地控制写入位置和样式
# 1. 先用pandas将数据写入一个临时文件或者直接用openpyxl
# 这里我们演示如何在一个已有的sheet末尾追加pandas数据
try:
# 加载已有的工作簿
wb = load_workbook("")
writer = ("", engine='openpyxl') # 指定引擎为openpyxl
= wb # 将加载的wb对象赋给writer的book属性
# 获取要写入的工作表
# 如果工作表不存在，则创建
if '成绩单' in :
ws = wb['成绩单']
else:
ws = wb.create_sheet('成绩单')
# 找到当前工作表的最后一行的下一个行号
startrow = ws.max_row
# 将新的DataFrame写入到工作表的指定位置
# header=False是因为我们不需要再次写入列头
df_new_students.to_excel(writer, sheet_name='成绩单', startrow=startrow, index=False, header=False)
() # 必须关闭writer来保存更改
print("新的学生数据已成功追加到 '' 的 '成绩单' 工作表。")
except FileNotFoundError:
print("文件未找到，请确保已运行之前的创建文件代码。")
except Exception as e:
print(f"追加数据时发生错误: {e}")

代码解释：
要实现真正意义上的“追加行”，最稳健的方式是：先用pandas.read_excel()读取现有数据到一个DataFrame，将新数据DataFrame与旧数据DataFrame合并（），然后再将合并后的DataFrame完整地写回Excel。
上述示例展示了如何结合的openpyxl引擎和openpyxl.load_workbook来实现向现有工作表追加数据。关键在于设置 = wb，并计算出startrow。
startrow参数控制数据从哪一行开始写入。
header=False：因为我们是在已有数据下方追加，通常不需要再次写入列头。

2.3 使用 Pandas 写入 Excel 并应用样式

pandas本身通过ExcelWriter和Styler对象提供了一些基本的样式功能，但如果需要更复杂的样式，通常需要结合openpyxl（或者xlsxwriter）。from openpyxl import load_workbook
from import Font, PatternFill, Alignment, Border, Side
# 1. 创建 DataFrame
data_for_style = {
'产品': ['A', 'B', 'C', 'D'],
'销量': [100, 150, 75, 200],
'利润率': [0.15, 0.20, 0.10, 0.25],
'库存': [500, 300, 800, 150]
}
df_styled = (data_for_style)
# 2. 写入Excel文件
styled_output_file = ""
df_styled.to_excel(styled_output_file, sheet_name='产品表现', index=False)
# 3. 使用openpyxl进行后续样式调整
wb = load_workbook(styled_output_file)
ws = wb['产品表现']
# 设置列头样式
header_font = Font(bold=True, color="FF0000") # 红色粗体
header_fill = PatternFill(start_color="FFFF00", end_color="FFFF00", fill_type="solid") # 黄色背景
for col_idx in range(1, ws.max_column + 1):
cell = (row=1, column=col_idx)
= header_font
= header_fill
= Alignment(horizontal='center')
# 设置“利润率”列为百分比格式
for row_idx in range(2, ws.max_row + 1):
(row=row_idx, column=3).number_format = '0.00%' # 第三列是利润率
# 设置“销量”和“库存”列为数字格式
for col_idx in [2, 4]: # 第二列和第四列
for row_idx in range(2, ws.max_row + 1):
(row=row_idx, column=col_idx).number_format = '#,##0'
# 调整列宽
for col in :
max_length = 0
column = col[0].column # Get the column name (e.g., 'A', 'B')
for cell in col:
try:
if len(str()) > max_length:
max_length = len(str())
except:
pass
adjusted_width = (max_length + 2)
ws.column_dimensions[get_column_letter(column)].width = adjusted_width
(styled_output_file)
print(f"Excel文件 '{styled_output_file}' 已成功写入并应用了样式。")

总结： pandas.to_excel()可以快速将DataFrame写入Excel，但其内置的样式控制相对有限。对于复杂的样式，最佳实践是先用pandas写入数据，再用openpyxl（或xlsxwriter）打开文件进行精细的样式调整。

Part 3: 其他库的简要介绍

除了openpyxl和pandas，还有一些库可以用于Python与Excel的交互，尽管它们各有侧重：
xlsxwriter： 这是一个专门用于写入.xlsx文件的库，它的强项在于对Excel功能的支持度非常高，包括创建复杂的图表、条件格式、数据验证、自定义样式等。如果您的报表需要高度定制的视觉效果，xlsxwriter是一个很好的选择。但请注意，它不支持读取Excel文件。
xlwt： 用于写入旧版.xls格式的Excel文件（Excel 2003及以下）。由于.xls格式的限制和.xlsx的普及，xlwt在新项目中已较少使用。如果您的目标是.xlsx文件，请优先选择openpyxl或xlsxwriter。

Part 4: 最佳实践与性能考量

在实际应用中，除了实现功能，我们还需要考虑代码的健壮性和性能。
选择合适的库：

对于标准的.xlsx文件读写，特别是需要修改现有文件和单元格样式，首选openpyxl。
处理数据框（DataFrame）并快速生成Excel文件，首选pandas。
需要高度定制的图表、条件格式等高级写入功能，且不需要读取功能时，考虑xlsxwriter。
避免使用xlwt，除非您必须生成.xls格式。

内存管理：

写入大量数据时，openpyxl默认会将整个工作簿加载到内存中。对于极大的文件（数百万行），这可能导致内存不足。openpyxl提供了“只写模式”（write_only=True），可以在写入时减少内存消耗，但会牺牲一些功能（如不能读取或修改单元格）。
pandas在处理大数据时本身就有较好的内存管理机制，但df.to_excel()也会在内存中构建Excel文件结构，对于超大DataFrame同样需要注意。

错误处理：

始终使用try-except块来处理文件操作可能遇到的错误，如FileNotFoundError、权限问题等。
保存文件后，最好进行简单的验证，确保文件已正确生成。

文件路径：

使用绝对路径或确保相对路径正确，尤其是在脚本部署到不同环境时。
考虑跨平台兼容性，例如使用()来构建路径。

备份： 在修改现有Excel文件之前，最好先创建备份，以防意外数据丢失。
性能优化：

避免在循环中重复打开和关闭Excel文件。
批量操作优于单个单元格操作，例如使用()写入整行数据。
对于非常大的数据集，可以考虑将数据分批写入多个工作表，或者生成CSV文件后再导入Excel。