Python 自动化 Excel 写入数据（列表、字典、数据库数据直接写入）详细教程
在Python自动化办公场景中，写入Excel是最常用的功能之一。无论是写入手动整理的数据、程序生成的计算结果，还是从数据库查询的数据，Python都能轻松实现。本文将详细介绍三种常见的数据写入方式，包含完整代码示例和最佳实践。
一、环境准备
1.1 安装依赖库
bash
# 基础库
pip install openpyxl pandas
# 如果需要连接数据库，安装对应的驱动
pip install pymysql # MySQL驱动
pip install psycopg2-binary # PostgreSQL驱动
pip install sqlalchemy # ORM框架，支持多种数据库
1.2 库选择建议
| 库 | 优势 | 适用场景 |
|—–|——|———-|
| openpyxl | 支持精细控制，格式设置灵活 | 需要设置单元格样式、公式、图表等场景 |
| pandas | 接口简单，批量写入速度快 | 结构化数据批量写入，不需要复杂格式 |
| xlsxwriter | 格式支持丰富，性能高 | 大量数据写入，需要复杂格式 |
> 💡 新手建议：大部分场景用pandas最简单，需要精细格式控制再用openpyxl。
二、写入列表数据
列表是最常见的数据结构，适合写入结构化的表格数据。
2.1 使用pandas写入列表数据
python
import pandas as pd
# 示例数据：列表嵌套列表，第一行是表头
data = [
["姓名", "部门", "入职日期", "工资", "绩效"],
["张三", "技术部", "2023-05-15", 15000, "A"],
["李四", "产品部", "2024-02-20", 12000, "B"],
["王五", "销售部", "2022-11-08", 18000, "A"],
["赵六", "人事部", "2023-08-10", 10000, "B"]
]
# 转换为DataFrame，第一行作为表头
df = pd.DataFrame(data[1:], columns=data[0])
# 写入Excel
df.to_excel("员工信息.xlsx", index=False, sheet_name="员工表")
print("✅ 列表数据写入成功！")
2.2 使用openpyxl写入列表数据
需要更精细控制时用openpyxl：
python
from openpyxl import Workbook
from openpyxl.utils import get_column_letter
from openpyxl.styles import Font, PatternFill, Alignment
wb = Workbook()
ws = wb.active
ws.title = "员工表"
data = [
["姓名", "部门", "入职日期", "工资", "绩效"],
["张三", "技术部", "2023-05-15", 15000, "A"],
["李四", "产品部", "2024-02-20", 12000, "B"],
["王五", "销售部", "2022-11-08", 18000, "A"],
["赵六", "人事部", "2023-08-10", 10000, "B"]
]
# 设置表头样式
header_font = Font(bold=True, color="FFFFFF")
header_fill = PatternFill(start_color="3498db", end_color="3498db", fill_type="solid")
header_alignment = Alignment(horizontal="center")
# 写入数据
for row_idx, row_data in enumerate(data, 1):
for col_idx, value in enumerate(row_data, 1):
cell = ws.cell(row=row_idx, column=col_idx, value=value)
# 表头应用样式
if row_idx == 1:
cell.font = header_font
cell.fill = header_fill
cell.alignment = header_alignment
# 调整列宽
for col in range(1, len(data[0]) + 1):
ws.column_dimensions[get_column_letter(col)].width = 15
# 保存文件
wb.save("员工信息_openpyxl.xlsx")
print("✅ 列表数据写入成功（带样式）！")
2.3 写入大列表优化
当数据量很大（超过10万行）时，使用openpyxl的只写模式提升性能：
python
from openpyxl import Workbook
# 开启只写模式，写入速度提升10倍以上
wb = Workbook(write_only=True)
ws = wb.create_sheet("大数据表")
# 生成10万行测试数据
header = ["序号", "数值1", "数值2", "数值3", "计算结果"]
ws.append(header)
for i in range(100000):
row = [i+1, i*2, i*3, i*4, i*9]
ws.append(row)
wb.save("大数据表.xlsx")
print("✅ 10万行数据写入成功！")
> ⚠️ 注意：只写模式下不能读取或修改已写入的数据，适合一次性写入大量数据的场景。
三、写入字典数据
字典数据结构更灵活，适合key-value类型的数据写入。
3.1 字典列表写入
最常见的是字典列表，每个字典代表一行数据：
python
import pandas as pd
data = [
{"姓名": "张三", "部门": "技术部", "入职日期": "2023-05-15", "工资": 15000, "绩效": "A"},
{"姓名": "李四", "部门": "产品部", "入职日期": "2024-02-20", "工资": 12000, "绩效": "B"},
{"姓名": "王五", "部门": "销售部", "入职日期": "2022-11-08", "工资": 18000, "绩效": "A"},
{"姓名": "赵六", "部门": "人事部", "入职日期": "2023-08-10", "工资": 10000, "绩效": "B"}
]
# 直接转换为DataFrame，字典的key自动作为表头
df = pd.DataFrame(data)
# 可以指定列的顺序
df = df[["姓名", "部门", "入职日期", "工资", "绩效"]]
df.to_excel("员工信息_字典.xlsx", index=False)
print("✅ 字典列表写入成功！")
3.2 单个字典写入（横向）
有时候需要把单个字典横向写入一行：
python
from openpyxl import Workbook
wb = Workbook()
ws = wb.active
data = {
"公司名称": "XX科技有限公司",
"报表日期": "2026-03-31",
"员工总数": 128,
"本月销售额": 5680000,
"利润率": 0.32
}
# 写入字典，每个key-value占一列
for col_idx, (key, value) in enumerate(data.items(), 1):
ws.cell(row=1, column=col_idx*2-1, value=key)
ws.cell(row=1, column=col_idx*2, value=value)
# 调整列宽
for col in range(1, len(data)*2 + 1):
ws.column_dimensions[chr(64 + col)].width = 15
wb.save("公司信息.xlsx")
print("✅ 单个字典写入成功！")
3.3 嵌套字典写入
处理嵌套字典结构，比如二级表头：
python
import pandas as pd
from collections import defaultdict
# 嵌套字典示例：按部门分组的员工数据
data = {
"技术部": {
"张三": {"工资": 15000, "绩效": "A", "入职年限": 3},
"王五": {"工资": 18000, "绩效": "A", "入职年限": 4}
},
"产品部": {
"李四": {"工资": 12000, "绩效": "B", "入职年限": 2}
}
}
# 转换为扁平结构
flat_data = []
for dept, employees in data.items():
for name, info in employees.items():
row = {"部门": dept, "姓名": name}
row.update(info)
flat_data.append(row)
df = pd.DataFrame(flat_data)
df.to_excel("部门员工信息.xlsx", index=False)
print("✅ 嵌套字典写入成功！")
四、写入数据库查询结果
实际工作中，经常需要把数据库查询结果直接导出到Excel。
4.1 使用pandas + SQLAlchemy（推荐）
SQLAlchemy支持几乎所有主流数据库，写法统一：
python
import pandas as pd
from sqlalchemy import create_engine
# 1. 创建数据库连接（以MySQL为例）
# 连接格式：数据库类型+驱动://用户名:密码@主机:端口/数据库名
engine = create_engine("mysql+pymysql://user:password@localhost:3306/company_db?charset=utf8mb4")
# 2. 执行SQL查询
sql = """
SELECT
e.id,
e.name,
d.dept_name,
e.hire_date,
e.salary,
e.performance
FROM
employee e
LEFT JOIN
department d ON e.dept_id = d.id
WHERE
e.status = 1
"""
# 3. 直接读取查询结果到DataFrame
df = pd.read_sql(sql, engine)
# 4. 写入Excel
df.to_excel("员工数据库导出.xlsx", index=False, sheet_name="员工数据")
print("✅ 数据库查询结果写入成功！")
4.2 不同数据库连接示例
MySQL
python
engine = create_engine("mysql+pymysql://user:password@localhost:3306/db_name?charset=utf8mb4")
PostgreSQL
python
engine = create_engine("postgresql+psycopg2://user:password@localhost:5432/db_name")
SQLite
python
engine = create_engine("sqlite:///database.db")
SQL Server
python
engine = create_engine("mssql+pyodbc://user:password@dsn_name")
4.3 使用原生数据库驱动
如果不想用SQLAlchemy，可以用原生驱动：
python
import pymysql
import pandas as pd
# 连接数据库
conn = pymysql.connect(
host="localhost",
user="user",
password="password",
database="company_db",
charset="utf8mb4"
)
# 执行查询
sql = "SELECT * FROM employee WHERE status = 1"
df = pd.read_sql(sql, conn)
# 写入Excel
df.to_excel("员工数据_原生驱动.xlsx", index=False)
# 关闭连接
conn.close()
print("✅ 数据库查询结果写入成功（原生驱动）！")
4.4 分批写入大数据量
当查询结果太大（超过100万行）时，分批读取写入：
python
import pandas as pd
from sqlalchemy import create_engine
engine = create_engine("mysql+pymysql://user:password@localhost:3306/company_db")
# 每次读取10万行
chunksize = 100000
sql = "SELECT * FROM big_table"
# 创建Excel写入器
with pd.ExcelWriter("大数据导出.xlsx", engine="openpyxl") as writer:
startrow = 0
for i, chunk in enumerate(pd.read_sql(sql, engine, chunksize=chunksize)):
# 第一个chunk写入表头，后续不写表头
chunk.to_excel(writer, index=False, sheet_name="数据", header=(i==0), startrow=startrow)
startrow += len(chunk)
print(f"✅ 已写入 {startrow} 行")
print("✅ 大数据分批写入完成！")
五、高级写入技巧
5.1 写入不同工作表
一个Excel文件写入多个工作表：
python
import pandas as pd
# 准备不同的数据
df1 = pd.DataFrame({"姓名": ["张三", "李四", "王五"], "工资": [15000, 12000, 18000]})
df2 = pd.DataFrame({"部门": ["技术部", "产品部", "销售部"], "人数": [20, 10, 30]})
df3 = pd.DataFrame({"月份": ["1月", "2月", "3月"], "销售额": [1200000, 1500000, 1800000]})
# 创建写入器
with pd.ExcelWriter("多工作表.xlsx") as writer:
df1.to_excel(writer, sheet_name="员工工资", index=False)
df2.to_excel(writer, sheet_name="部门统计", index=False)
df3.to_excel(writer, sheet_name="销售数据", index=False)
print("✅ 多工作表写入成功！")
5.2 追加数据到已有Excel
在已有的Excel文件后面追加数据：
python
import pandas as pd
from openpyxl import load_workbook
# 已有文件路径
file_path = "员工信息.xlsx"
# 新数据
new_data = pd.DataFrame({
"姓名": ["孙七", "周八"],
"部门": ["市场部", "财务部"],
"入职日期": ["2024-01-15", "2023-11-20"],
"工资": [11000, 13000],
"绩效": ["B", "A"]
})
# 读取已有文件，获取现有行数
book = load_workbook(file_path)
writer = pd.ExcelWriter(file_path, engine="openpyxl", mode="a", if_sheet_exists="overlay")
writer.book = book
writer.sheets = {ws.title: ws for ws in book.worksheets}
# 追加数据，不写表头，从最后一行开始
startrow = writer.sheets["员工表"].max_row
new_data.to_excel(writer, sheet_name="员工表", index=False, header=False, startrow=startrow)
writer.close()
print("✅ 数据追加成功！")
5.3 写入时设置数据格式
设置列的格式，比如日期、货币、百分比：
python
import pandas as pd
from openpyxl import load_workbook
from openpyxl.styles import numbers
# 准备数据
data = [
["姓名", "入职日期", "工资", "绩效", "完成率"],
["张三", "2023-05-15", 15000, "A", 0.95],
["李四", "2024-02-20", 12000, "B", 0.88],
["王五", "2022-11-08", 18000, "A", 0.98]
]
df = pd.DataFrame(data[1:], columns=data[0])
df.to_excel("带格式的数据.xlsx", index=False)
# 打开文件设置格式
wb = load_workbook("带格式的数据.xlsx")
ws = wb.active
# 设置列格式
for row in range(2, ws.max_row + 1):
# 入职日期列（B列）设置日期格式
ws[f"B{row}"].number_format = "YYYY-MM-DD"
# 工资列（C列）设置货币格式
ws[f"C{row}"].number_format = "¥#,##0"
# 完成率列（E列）设置百分比格式
ws[f"E{row}"].number_format = "0.0%"
wb.save("带格式的数据.xlsx")
print("✅ 带格式数据写入成功！")
5.4 写入公式
在Excel中写入计算公式，打开文件时自动计算：
python
from openpyxl import Workbook
wb = Workbook()
ws = wb.active
# 写入表头
ws.append(["商品名称", "成本价", "售价", "销量", "利润"])
# 写入数据
data = [
["商品A", 80, 120, 100],
["商品B", 60, 90, 150],
["商品C", 100, 150, 80]
]
for row in data:
ws.append(row)
# 写入利润计算公式：利润 = (售价 - 成本价) * 销量
for row in range(2, len(data) + 2):
ws[f"E{row}"] = f"=(C{row}-B{row})*D{row}"
# 写入合计
ws.append(["合计", "", "", f"=SUM(D2:D{len(data)+1})", f"=SUM(E2:E{len(data)+1})"])
wb.save("带公式的表格.xlsx")
print("✅ 带公式的表格写入成功！")
六、常见问题与解决方案
6.1 中文乱码问题
问题：写入Excel后中文显示乱码
解决方案：
pandas写入时指定编码：
python
df.to_excel("文件.xlsx", index=False, encoding="utf-8")
数据库查询时指定字符集：
python
# MySQL连接加charset=utf8mb4
engine = create_engine("mysql+pymysql://user:password@localhost:3306/db_name?charset=utf8mb4")
6.2 写入速度慢问题
问题：写入大量数据时速度很慢
解决方案：
用pandas代替openpyxl，速度快5-10倍
大数据量用openpyxl的只写模式
分批写入，避免一次性加载所有数据到内存
关闭自动计算：`wb.calculation.calcMode = “manual”`
6.3 数据类型错误问题
问题：数字变成字符串，日期格式不对
解决方案：
写入前转换好数据类型：
python
# 转换日期列
df['入职日期'] = pd.to_datetime(df['入职日期'])
# 转换数字列
df['工资'] = pd.to_numeric(df['工资'], errors='coerce')
写入后设置单元格格式，参考5.3节
6.4 内存不足问题
问题：写入几十万行数据时内存不足
解决方案：
分批读取写入，每次处理10万行
使用openpyxl只写模式
写入过程中及时清理不需要的变量，释放内存
6.5 文件被占用问题
问题：保存文件时提示权限错误
解决方案：
先关闭打开的Excel文件再保存
检查文件是否被其他程序占用
保存到不同的文件名，避免覆盖正在使用的文件
七、最佳实践
1. 优先用pandas：简单快速，90%的场景都能满足
2. 数据类型提前转换：写入前处理好数据类型，避免写入后格式错误
3. 大文件分批处理：超过10万行数据分批写入，避免内存溢出
4. 异常处理：文件操作时加try-except，处理文件占用、权限不足等问题
5. 及时保存：写入过程中定期保存，避免程序崩溃丢失数据
6. 命名规范：文件名包含日期、版本号，避免覆盖重要文件
通过以上方法，你可以轻松实现各种场景下的Excel数据写入，不管是简单的列表还是复杂的数据库查询结果，都能快速导出到Excel文件，大幅提升办公效率。
