Python 自动化 Excel创建新的 Excel 文件详细教程：写入数据、设置格式、插入图表实战指南
在日常办公自动化场景中，我们不仅需要读取Excel文件，还经常需要创建新的Excel文件，写入数据、设置格式、插入图表等。Python提供了丰富的库可以实现这些功能，本文将详细介绍如何使用Python创建Excel文件，包含完整代码示例和最佳实践。
一、环境准备
1.1 安装依赖库
创建Excel文件主要使用以下库：
安装命令：
bash
pip install openpyxl pandas xlsxwriter
1.2 库对比选择
| 库名 | 优势 | 适用场景 |
|——|——|———-|
| openpyxl | 支持读写、格式丰富、兼容性好 | 大多数场景，尤其是需要修改已有文件时 |
| xlsxwriter | 格式支持最全、图表功能强大、性能高 | 新建复杂格式Excel文件、需要插入大量图表时 |
| pandas | 接口简单、适合批量数据写入 | 快速生成结构化数据Excel文件 |
二、基础创建操作
2.1 使用openpyxl创建简单Excel文件
python
from openpyxl import Workbook
from openpyxl.utils import get_column_letter
# 创建工作簿
wb = Workbook()
# 获取默认工作表
ws = wb.active
ws.title = "销售数据" # 设置工作表名称
# 写入表头
headers = ["日期", "区域", "销售人员", "产品名称", "销售额", "利润"]
for col, header in enumerate(headers, 1):
ws.cell(row=1, column=col, value=header)
# 写入数据
data = [
["2026-01-01", "华东", "张三", "产品A", 12000, 3600],
["2026-01-02", "华南", "李四", "产品B", 8500, 2550],
["2026-01-03", "华北", "王五", "产品A", 15000, 4500],
["2026-01-04", "西南", "赵六", "产品C", 9800, 2940],
["2026-01-05", "华东", "张三", "产品B", 11000, 3300]
]
for row_idx, row_data in enumerate(data, 2): # 从第2行开始写入
for col_idx, value in enumerate(row_data, 1):
ws.cell(row=row_idx, column=col_idx, value=value)
# 调整列宽
for col in range(1, len(headers) + 1):
ws.column_dimensions[get_column_letter(col)].width = 15
# 保存文件
wb.save("销售数据.xlsx")
print("✅ Excel文件创建成功！")
2.2 使用pandas快速创建Excel文件
对于结构化数据，使用pandas更加简洁高效：
python
import pandas as pd
# 准备数据
data = {
"日期": ["2026-01-01", "2026-01-02", "2026-01-03", "2026-01-04", "2026-01-05"],
"区域": ["华东", "华南", "华北", "西南", "华东"],
"销售人员": ["张三", "李四", "王五", "赵六", "张三"],
"产品名称": ["产品A", "产品B", "产品A", "产品C", "产品B"],
"销售额": [12000, 8500, 15000, 9800, 11000],
"利润": [3600, 2550, 4500, 2940, 3300]
}
df = pd.DataFrame(data)
# 创建Excel写入器
with pd.ExcelWriter("销售数据_pandas.xlsx", engine="openpyxl") as writer:
df.to_excel(writer, sheet_name="销售数据", index=False)
# 获取工作表对象，进行进一步设置
worksheet = writer.sheets["销售数据"]
# 调整列宽
for idx, col in enumerate(df.columns):
max_width = max(df[col].astype(str).map(len).max(), len(col)) + 2
worksheet.column_dimensions[chr(65 + idx)].width = max_width
print("✅ Pandas创建Excel文件成功！")
2.3 创建多个工作表
一个Excel文件可以包含多个工作表：
python
from openpyxl import Workbook
wb = Workbook()
# 创建第一个工作表
ws1 = wb.active
ws1.title = "销售数据"
ws1.append(["日期", "销售额", "利润"])
ws1.append(["2026-01-01", 12000, 3600])
ws1.append(["2026-01-02", 8500, 2550])
# 创建第二个工作表
ws2 = wb.create_sheet(title="产品库存")
ws2.append(["产品名称", "库存数量", "成本价"])
ws2.append(["产品A", 100, 80])
ws2.append(["产品B", 150, 60])
# 创建第三个工作表（插入到指定位置）
ws3 = wb.create_sheet(title="员工信息", index=1) # 插入到第二个位置
ws3.append(["姓名", "部门", "入职日期"])
ws3.append(["张三", "销售部", "2023-05-15"])
ws3.append(["李四", "销售部", "2024-02-20"])
# 保存文件
wb.save("多工作表示例.xlsx")
print("✅ 多工作表Excel文件创建成功！")
三、高级格式设置
3.1 设置单元格样式
openpyxl支持丰富的单元格样式设置：
python
from openpyxl import Workbook
from openpyxl.styles import Font, Alignment, PatternFill, Border, Side, numbers
from openpyxl.utils import get_column_letter
wb = Workbook()
ws = wb.active
ws.title = "销售报表"
# 定义样式
# 表头样式
header_font = Font(name="微软雅黑", size=12, bold=True, color="FFFFFF")
header_fill = PatternFill(start_color="3498db", end_color="3498db", fill_type="solid")
header_alignment = Alignment(horizontal="center", vertical="center")
# 数字格式
number_font = Font(name="Consolas", size=11)
number_alignment = Alignment(horizontal="right", vertical="center")
currency_format = numbers.FORMAT_CURRENCY_USD_SIMPLE # 货币格式
# 边框
thin_border = Border(
left=Side(style='thin'),
right=Side(style='thin'),
top=Side(style='thin'),
bottom=Side(style='thin')
)
# 写入表头
headers = ["日期", "区域", "销售人员", "产品名称", "销售额", "利润", "利润率"]
for col, header in enumerate(headers, 1):
cell = ws.cell(row=1, column=col, value=header)
cell.font = header_font
cell.fill = header_fill
cell.alignment = header_alignment
cell.border = thin_border
# 写入数据并应用样式
data = [
["2026-01-01", "华东", "张三", "产品A", 12000, 3600, 0.3],
["2026-01-02", "华南", "李四", "产品B", 8500, 2550, 0.3],
["2026-01-03", "华北", "王五", "产品A", 15000, 4500, 0.3],
["2026-01-04", "西南", "赵六", "产品C", 9800, 2940, 0.3],
["2026-01-05", "华东", "张三", "产品B", 11000, 3300, 0.3]
]
for row_idx, row_data in enumerate(data, 2):
for col_idx, value in enumerate(row_data, 1):
cell = ws.cell(row=row_idx, column=col_idx, value=value)
cell.border = thin_border
# 销售额和利润列设置货币格式
if col_idx in [5, 6]:
cell.number_format = "¥#,##0"
cell.alignment = number_alignment
cell.font = number_font
# 利润率设置百分比格式
elif col_idx == 7:
cell.number_format = "0.0%"
cell.alignment = number_alignment
# 其他文字居中
else:
cell.alignment = Alignment(horizontal="center", vertical="center")
# 调整列宽
for col in range(1, len(headers) + 1):
ws.column_dimensions[get_column_letter(col)].width = 15
# 冻结首行
ws.freeze_panes = "A2"
# 保存文件
wb.save("带样式的销售报表.xlsx")
print("✅ 带样式的Excel文件创建成功！")
3.2 条件格式设置
根据单元格值自动设置样式：
python
from openpyxl import Workbook
from openpyxl.styles import PatternFill
from openpyxl.formatting.rule import ColorScaleRule, CellIsRule, FormulaRule
wb = Workbook()
ws = wb.active
# 写入测试数据
data = [
["姓名", "考试1", "考试2", "考试3", "平均分"],
["张三", 85, 92, 78, "=AVERAGE(B2:D2)"],
["李四", 76, 88, 95, "=AVERAGE(B3:D3)"],
["王五", 92, 76, 88, "=AVERAGE(B4:D4)"],
["赵六", 65, 72, 80, "=AVERAGE(B5:D5)"],
["孙七", 88, 95, 90, "=AVERAGE(B6:D6)"]
]
for row in data:
ws.append(row)
# 条件格式1：平均分大于90分的显示绿色背景
green_fill = PatternFill(start_color='98c379', end_color='98c379', fill_type='solid')
ws.conditional_formatting.add('E2:E6', CellIsRule(operator='greaterThan', formula=['90'], fill=green_fill))
# 条件格式2：平均分低于70分的显示红色背景
red_fill = PatternFill(start_color='e06c75', end_color='e06c75', fill_type='solid')
ws.conditional_formatting.add('E2:E6', CellIsRule(operator='lessThan', formula=['70'], fill=red_fill))
# 条件格式3：分数使用颜色渐变（低分红、中分红黄、高分绿）
color_scale_rule = ColorScaleRule(
start_type='min', start_color='ef4444',
mid_type='percentile', mid_value=50, mid_color='f59e0b',
end_type='max', end_color='10b981'
)
ws.conditional_formatting.add('B2:D6', color_scale_rule)
# 调整列宽
for col in ['A', 'B', 'C', 'D', 'E']:
ws.column_dimensions[col].width = 12
wb.save("条件格式示例.xlsx")
print("✅ 带条件格式的Excel文件创建成功！")
3.3 合并单元格
python
from openpyxl import Workbook
from openpyxl.styles import Font, Alignment
wb = Workbook()
ws = wb.active
# 合并单元格作为大标题
ws.merge_cells('A1:F1')
title_cell = ws['A1']
title_cell.value = "2026年第一季度销售报表"
title_cell.font = Font(name="微软雅黑", size=16, bold=True)
title_cell.alignment = Alignment(horizontal="center", vertical="center")
ws.row_dimensions[1].height = 30 # 设置行高
# 合并表头
ws.merge_cells('A2:B2')
ws['A2'].value = "基本信息"
ws.merge_cells('C2:F2')
ws['C2'].value = "销售数据"
# 写入子表头
headers = ["日期", "区域", "产品A", "产品B", "产品C", "合计"]
for col, header in enumerate(headers, 1):
ws.cell(row=3, column=col, value=header)
# 写入数据
data = [
["2026-01", "华东", 12000, 8500, 9800, "=SUM(C4:E4)"],
["2026-02", "华南", 15000, 9200, 10500, "=SUM(C5:E5)"],
["2026-03", "华北", 13500, 7800, 11200, "=SUM(C6:E6)"]
]
for row_idx, row_data in enumerate(data, 4):
for col_idx, value in enumerate(row_data, 1):
ws.cell(row=row_idx, column=col_idx, value=value)
# 调整列宽
for col in ['A', 'B', 'C', 'D', 'E', 'F']:
ws.column_dimensions[col].width = 15
wb.save("合并单元格示例.xlsx")
print("✅ 带合并单元格的Excel文件创建成功！")
四、插入图表
openpyxl支持插入多种类型的图表：
4.1 插入柱状图
python
from openpyxl import Workbook
from openpyxl.chart import BarChart, Reference, Series
wb = Workbook()
ws = wb.active
ws.title = "销售数据"
# 写入数据
data = [
["月份", "产品A", "产品B", "产品C"],
["1月", 12000, 8500, 9800],
["2月", 15000, 9200, 10500],
["3月", 13500, 7800, 11200],
["4月", 14200, 10500, 9600],
["5月", 16800, 11200, 12500],
["6月", 18500, 12800, 14200]
]
for row in data:
ws.append(row)
# 创建柱状图
chart = BarChart()
chart.type = "col"
chart.style = 10
chart.title = "上半年产品销售统计图"
chart.y_axis.title = "销售额（元）"
chart.x_axis.title = "月份"
# 设置数据范围
data_ref = Reference(ws, min_col=2, min_row=1, max_col=4, max_row=7)
categories_ref = Reference(ws, min_col=1, min_row=2, max_row=7)
chart.add_data(data_ref, titles_from_data=True)
chart.set_categories(categories_ref)
# 设置图表大小
chart.width = 15
chart.height = 10
# 将图表插入到指定位置
ws.add_chart(chart, "H2")
wb.save("柱状图示例.xlsx")
print("✅ 带柱状图的Excel文件创建成功！")
4.2 插入折线图
python
from openpyxl.chart import LineChart, Reference
# ... 数据准备和上面相同 ...
# 创建折线图
chart = LineChart()
chart.title = "销售趋势图"
chart.y_axis.title = "销售额（元）"
chart.x_axis.title = "月份"
chart.style = 12
# 添加数据
data_ref = Reference(ws, min_col=2, min_row=1, max_col=4, max_row=7)
categories_ref = Reference(ws, min_col=1, min_row=2, max_row=7)
chart.add_data(data_ref, titles_from_data=True)
chart.set_categories(categories_ref)
# 显示数据标记
for s in chart.series:
s.marker.symbol = "circle"
s.marker.size = 5
ws.add_chart(chart, "H2")
wb.save("折线图示例.xlsx")
print("✅ 带折线图的Excel文件创建成功！")
4.3 插入饼图
python
from openpyxl.chart import PieChart, Reference
# 写入数据
data = [
["产品", "销售额"],
["产品A", 80000],
["产品B", 60000],
["产品C", 45000],
["其他", 15000]
]
for row in data:
ws.append(row)
# 创建饼图
chart = PieChart()
chart.title = "产品销售占比"
labels = Reference(ws, min_col=1, min_row=2, max_row=5)
data_ref = Reference(ws, min_col=2, min_row=1, max_row=5)
chart.add_data(data_ref, titles_from_data=True)
chart.set_categories(labels)
# 显示百分比
chart.dataLabels.showPercent = True
chart.dataLabels.showValue = False
chart.dataLabels.showCatName = False
ws.add_chart(chart, "D2")
wb.save("饼图示例.xlsx")
print("✅ 带饼图的Excel文件创建成功！")
五、实战案例：自动生成月度销售报表
5.1 完整案例代码
python
import pandas as pd
from openpyxl import load_workbook
from openpyxl.styles import Font, Alignment, PatternFill, Border, Side
from openpyxl.chart import BarChart, Reference
from datetime import datetime
def generate_monthly_sales_report(month, sales_data, output_file):
"""
生成月度销售报表
:param month: 月份，如"2026-01"
:param sales_data: 销售数据字典
:param output_file: 输出文件路径
"""
# 1. 使用pandas创建基础数据
df = pd.DataFrame(sales_data)
# 计算统计数据
total_sales = df['销售额'].sum()
total_profit = df['利润'].sum()
avg_profit_rate = df['利润'].sum() / df['销售额'].sum()
# 按区域分组统计
area_stats = df.groupby('区域').agg({
'销售额': 'sum',
'利润': 'sum'
}).reset_index()
# 2. 写入Excel
with pd.ExcelWriter(output_file, engine='openpyxl') as writer:
# 销售详情表
df.to_excel(writer, sheet_name='销售详情', index=False)
# 统计报表
stats_df = pd.DataFrame({
'指标': ['总销售额', '总利润', '平均利润率'],
'数值': [total_sales, total_profit, avg_profit_rate]
})
stats_df.to_excel(writer, sheet_name='统计报表', index=False)
# 区域统计表
area_stats.to_excel(writer, sheet_name='区域统计', index=False)
# 3. 格式设置
# 处理销售详情表
ws1 = writer.sheets['销售详情']
# 设置表头样式
for cell in ws1[1]:
cell.font = Font(bold=True, color='FFFFFF')
cell.fill = PatternFill(start_color='3498db', end_color='3498db', fill_type='solid')
cell.alignment = Alignment(horizontal='center')
# 调整列宽
for col in ws1.columns:
max_length = 0
column = col[0].column_letter
for cell in col:
try:
if len(str(cell.value)) > max_length:
max_length = len(str(cell.value))
except:
pass
adjusted_width = (max_length + 2) * 1.2
ws1.column_dimensions[column].width = adjusted_width
# 4. 插入图表到统计报表
ws3 = writer.sheets['区域统计']
chart = BarChart()
chart.title = f"{month}各区域销售情况"
data = Reference(ws3, min_col=2, min_row=1, max_row=len(area_stats)+1)
categories = Reference(ws3, min_col=1, min_row=2, max_row=len(area_stats)+1)
chart.add_data(data, titles_from_data=True)
chart.set_categories(categories)
ws3.add_chart(chart, "D2")
print(f"✅ {month}销售报表生成完成！保存路径：{output_file}")
# 测试数据
sales_data = {
"日期": ["2026-01-01", "2026-01-02", "2026-01-03", "2026-01-04", "2026-01-05", "2026-01-06"],
"区域": ["华东", "华南", "华北", "西南", "华东", "华南"],
"销售人员": ["张三", "李四", "王五", "赵六", "张三", "李四"],
"产品名称": ["产品A", "产品B", "产品A", "产品C", "产品B", "产品C"],
"销售额": [12000, 8500, 15000, 9800, 11000, 13500],
"利润": [3600, 2550, 4500, 2940, 3300, 4050]
}
# 生成报表
generate_monthly_sales_report("2026年1月", sales_data, "2026年1月销售报表.xlsx")
六、常见问题与解决方案
6.1 大文件写入性能问题
问题：写入大量数据时速度慢，内存占用高
解决方案：
python
# 使用openpyxl的只读/只写模式提升性能
from openpyxl import Workbook
wb = Workbook(write_only=True) # 开启只写模式
ws = wb.create_sheet()
# 写入大量数据
for i in range(100000):
ws.append([i, f'数据{i}', i * 100])
wb.save('大数据文件.xlsx')
> ⚠️ 注意：只写模式下不能读取或修改已写入的数据，适合一次性写入大量数据的场景。
6.2 Excel文件损坏无法打开
问题：生成的Excel文件打开时提示损坏
解决方案：
1. 保存文件时确保文件后缀是`.xlsx`，不要使用`.xls`
2. 避免使用特殊字符作为工作表名称
3. 检查公式语法是否正确
4. 关闭正在打开的同名文件再保存
6.3 中文显示乱码问题
问题：Excel中的中文显示为乱码
解决方案：
6.4 公式不自动计算
问题：文件打开后公式没有自动计算
解决方案：
python
wb = Workbook()
wb.calculation.calcMode = "auto" # 设置自动计算
或者在保存文件后手动按F9刷新计算。
七、最佳实践建议
1. 选择合适的库：简单数据写入用pandas，复杂格式用openpyxl，大量图表用xlsxwriter
2. 及时保存：写入过程中定期保存，避免程序崩溃丢失数据
3. 使用模板：对于固定格式的报表，先创建好Excel模板文件，再填充数据，提高效率
4. 异常处理：文件操作时添加异常捕获，处理文件被占用、权限不足等问题
5. 文件命名规范：文件名包含日期、版本号等信息，避免覆盖重要文件
6. 测试验证：生成文件后最好打开验证一下内容和格式是否正确
通过以上方法，你可以使用Python灵活创建各种格式的Excel文件，实现办公自动化。无论是简单的数据导出还是复杂的报表生成，Python都能高效完成，大幅提升工作效率。
