Python批量导出数据库到Excel多Sheet与格式控制

脚本专家

批量把数据库数据交付成 Excel，手工复制在几百张表、几十万行时基本不可行。Python 适合这条链路：sqlalchemy 建连接，pandas 取数并整理 DataFrame，openpyxl 控制 Excel 的 Sheet、列宽、冻结行和表头样式。本文围绕“数据库到 Excel”的批量导出，拆解连接、查询、类型转换、写入、格式和排查。

一、适用场景与技术选型
业务方每月要订单明细，50 万行、50 多字段，还要按省份拆子表、附汇总 Sheet；或一次导出 12 个月，每月一个 Excel、内部多 Sheet。这类需求本质是流水线：连接数据库→按规则取数→整理 DataFrame→写 Excel→校验产物。Navicat、MySQL Workbench 能导出结果集，但按省份/日期/品类拆分、列宽、冻结窗格、多级表头、公式和自动调度较弱。商业 ETL 工具能做自动化但偏重。Python 脚本轻量、透明、可复用，能和定时任务、监控告警放在同一技术栈。

二、环境准备与连接串
核心库：pandas 负责数据处理和 to_excel，sqlalchemy 用 create_engine 做连接抽象，openpyxl 负责 Excel 底层读写和格式，PyMySQL/psycopg2/cx_Oracle 按数据库选择，tqdm 用于批量进度提示。安装：

2. pip install pandas openpyxl sqlalchemy pymysql tqdm

复制代码

常用 Python 3.10 到 3.12、pandas 2.x、openpyxl 3.1.x。pandas 2.x 对 openpyxl 的依赖行为有变化，从 1.x 升级后要重新验证写 Excel，尤其是 datetime 格式和列宽设置。建议虚拟环境，系统 Python 里旧版 openpyxl 可能触发 AttributeError: 'Workbook' object has no attribute 'add_chart' 一类版本冲突。
MySQL 连接串示例：

2. from sqlalchemy import create_engine
3. engine = create_engine(
4.     'mysql+pymysql://用户名:密码@主机:3306/数据库名?charset=utf8mb4'
5. )

复制代码

密码含 @、#、% 等特殊字符时，用 urllib.parse.quote_plus 编码；端口不是 3306 要写明确；MySQL 显式加 charset=utf8mb4，否则中文或 emoji 可能乱码。长查询可加 connect_args={'connect_timeout': 30, 'read_timeout': 3600}，避免中途掉线。

三、从数据库读数据
pd.read_sql_query 会一次性把结果加载到内存。几万行可以全量；几十万行且字段多时内存会很难看。处理方式一是用 chunksize 分批：

2. chunks = pd.read_sql_query(sql, engine, chunksize=20000)
3. for chunk in chunks:
4.     process(chunk)

复制代码

chunksize 通过游标分批取数，可读一批写一批。方式二是把聚合、排序、类型转换尽量放进 SQL，Python 只接收结果。经验量级：10 万以下全量读；10 万到 100 万视字段和内存分批；超过 100 万优先导出 CSV 或按条件拆多个 Excel，因为 Excel 单 Sheet 上限是 1048576 行。
SQL 优化：只 select 需要的字段；分页不要用大偏移量 limit offset，改用 where id > 上次最大 id order by id limit 50000 的滚动键；导出期间可用只读事务或一致性快照；过滤字段建索引。
类型转换要统一处理：日期时间读进来通常是 datetime64[ns]，可写标准日期；若 SQL 用 varchar 存时间，要在 Python 转一次，否则 Excel 把日期当文本。金额 decimal/numeric 转成 float64 会精度丢失，例如 0.1+0.2 的浮点误差，财务对账场景建议转字符串或 SQL 里 CAST(amount AS CHAR)。BIGINT 超过 15 位会被 Excel 显示成 1.23457E+14，导出前转字符串或设置单元格文本格式。

四、写入 Excel 与格式控制
pandas.to_excel 方便，但列宽、冻结表头、表头底色等控制弱。常用组合是 pandas 写数据，openpyxl 打开 writer.book 调格式。多 Sheet：

2. with pd.ExcelWriter('订单数据.xlsx', engine='openpyxl') as writer:
3.     for name, df in sheet_items:
4.         df.to_excel(writer, sheet_name=name, index=False)

复制代码

Sheet 名不要超过 31 个字符，不能包含斜杠、问号、星号、方括号等特殊字符，重复名要先判断 writer.sheets 并加后缀。一个 Sheet 先汇总后明细可用 startrow：

2. df_summary.to_excel(writer, sheet_name='数据汇总', startrow=0, index=False)
3. df_detail.to_excel(writer, sheet_name='数据汇总', startrow=len(df_summary)+3, index=False)

复制代码

格式控制用 openpyxl：

2. from openpyxl.utils import get_column_letter
3. wb = writer.book
4. ws = writer.sheets['订单明细']
5. for i, col in enumerate(df.columns, start=1):
6.     max_len = max(df[col].astype(str).map(len).max(), len(col)) + 2
7.     ws.column_dimensions[get_column_letter(i)].width = min(max_len, 40)
8. ws.freeze_panes = 'A2'
9. from openpyxl.styles import Font, PatternFill, Alignment
10. for cell in ws[1]:
11.     cell.font = Font(bold=True)
12.     cell.fill = PatternFill(start_color='DDEBF7', end_color='DDEBF7', fill_type='solid')
13.     cell.alignment = Alignment(horizontal='center', vertical='center')
14. ws.auto_filter.ref = ws.dimensions

复制代码

列宽不要只按字符数算，中文字符占两个字节，按长度×2估算再 clamp 到合理宽度。自动筛选让业务方可以直接下拉过滤。

五、完整批量导出代码
下面以 MySQL 为例，导出最近三个月订单数据，按月份拆 3 个文件，每个文件包含“每日汇总”和“订单明细”两个 Sheet。

2. import pandas as pd
3. from sqlalchemy import create_engine
4. from openpyxl.utils import get_column_letter
5. from openpyxl.styles import Font, PatternFill, Alignment
6. from pathlib import Path
7. from tqdm import tqdm
9. def get_engine():
10.     from urllib.parse import quote_plus
11.     user = 'your_user'
12.     password = quote_plus('your_pass_with_@special')
13.     host = '10.0.0.10'
14.     port = '3306'
15.     dbname = 'your_db'
16.     engine = create_engine(
17.         f'mysql+pymysql://{user}:{password}@{host}:{port}/{dbname}?charset=utf8mb4',
18.         connect_args={'connect_timeout': 30, 'read_timeout': 3600},
19.         pool_recycle=3600,
20.     )
21.     return engine
23. def fetch_data(sql, engine):
24.     df = pd.read_sql_query(sql, engine)
25.     for col in df.columns:
26.         if 'id' in col.lower() or 'code' in col.lower():
27.             df[col] = df[col].astype(str)
28.     return df
30. def write_excel_with_style(filepath, sheet_specs):
31.     with pd.ExcelWriter(filepath, engine='openpyxl') as writer:
32.         for spec in sheet_specs:
33.             df = spec['df']
34.             sheet_name = spec['name'][:31]
35.             df.to_excel(writer, sheet_name=sheet_name, index=False)
36.             ws = writer.sheets[sheet_name]
37.             for i, col in enumerate(df.columns, start=1):
38.                 col_len = len(col) * 2
39.                 max_len = max(
40.                     df[col].astype(str).map(lambda x: sum(2 if ord(c) > 127 else 1 for c in x)).max(),
41.                     col_len
42.                 ) + 2
43.                 ws.column_dimensions[get_column_letter(i)].width = min(max_len, 40)
44.             ws.freeze_panes = 'A2'
45.             for cell in ws[1]:
46.                 cell.font = Font(bold=True)
47.                 cell.fill = PatternFill(start_color='DDEBF7', end_color='DDEBF7', fill_type='solid')
48.                 cell.alignment = Alignment(horizontal='center', vertical='center')
49.             ws.auto_filter.ref = ws.dimensions
51. def run_export():
52.     engine = get_engine()
53.     export_dir = Path('./export')
54.     export_dir.mkdir(exist_ok=True)
55.     months = ['2024-01-01', '2024-02-01', '2024-03-01']
56.     for i in range(len(months) - 1):
57.         start = months[i]
58.         end = months[i + 1]
59.         year_month = start[:7]
60.         print(f'正在处理 {year_month} ...')
61.         sql_detail = f'''
62.         SELECT order_id, user_id, product_id, amount, status, create_time
63.         FROM order_table
64.         WHERE create_time >= '{start}' AND create_time < '{end}'
65.         ORDER BY create_time
66.         '''
67.         df_detail = fetch_data(sql_detail, engine)
68.         if df_detail.empty:
69.             print(f'{year_month} 无数据，跳过')
70.             continue
71.         sql_summary = f'''
72.         SELECT DATE(create_time) AS day, COUNT(*) AS order_cnt, SUM(amount) AS total_amount
73.         FROM order_table
74.         WHERE create_time >= '{start}' AND create_time < '{end}'
75.         GROUP BY DATE(create_time)
76.         '''
77.         df_summary = fetch_data(sql_summary, engine)
78.         filepath = export_dir / f'订单数据_{year_month}.xlsx'
79.         write_excel_with_style(filepath, [
80.             {'name': '每日汇总', 'df': df_summary},
81.             {'name': '订单明细', 'df': df_detail},
82.         ])
83.         print(f'已生成 {filepath}')
85. if __name__ == '__main__':
86.     run_export()

复制代码

这段代码把 SQL 当成配置：调整日期范围、字段、汇总逻辑，改 SQL 和循环列表即可。运行后 export 目录生成订单数据_2024-01.xlsx 等文件，“每日汇总”在前，“订单明细”在后，冻结首行、表头底色、列筛选都已加上。实际跑几十万行、20 个字段时，时间主要在数据库查询和 Excel 写入。超大 Sheet 可考虑 xlsxwriter，写入更快，但格式 API 不同；openpyxl 的后期样式调整更灵活。

六、常见问题与排查
1. 内存溢出与超时。症状是进程被杀或数据库报 Lock wait timeout exceeded。先用 EXPLAIN 看 SQL 是否走索引；单表大就缩小范围，按小时或 ID 段分批；在读取前、读取后、写 Excel 前打印行数和内存日志。循环跑几十张表时，用完的 DataFrame 及时 del，必要时 gc.collect()。
2. 特殊字符与时间格式。字符串里有换行符时，写进 Excel 会在单元格内显示多行，若不需要可清洗掉换行和回车。日期 pandas 读出来是 2024-01-01 00:00:00，Excel 可能显示成 2024/1/1 0:00 或数字。写 Excel 前用 dt.strftime('%Y-%m-%d') 转字符串，显示稳定；若要真日期，用 openpyxl 的 number_format 设为 yyyy-mm-dd hh:mm:ss。单个单元格最多 32767 个字符，长文本要预处理或提示。
3. 连接中断与权限。长批量导出容易断开，可加重试机制、查询失败等待几秒重试三次，连接串加 pool_pre_ping=True 让 sqlalchemy 取连接前检查有效性。权限报错常见 Access denied for user 或 1044，需要 DBA 开权限。生产环境导出尽量走只读副本或从库，避免压主库。排查时先看报错栈在 SQLAlchemy 层还是 PyMySQL 层，连接问题常在 pymysql.err，权限问题常在 sqlalchemy.exc.OperationalError。
最后，每次导出前先写一条 SQL 统计各表行数，再和 Excel 行数比对。这个校验只有几行代码，但能避免“文件生成了但少了一半数据”。数据交付信任建立很慢，崩塌只要一次。建议先从单表导出跑通，再叠加格式、拆分和多 Sheet。

热心网友7

这篇总结挺完整的，把数据库到 Excel 的批量导出拆成连接、取数、整理、写入、格式和排查几个环节，思路很清晰。连接串里密码特殊字符编码、charset=utf8mb4、超时参数这些点很实用；分批读数和提前在 SQL 里做聚合、排序、类型转换，也确实是控制内存的关键。金额 decimal 转 float 的精度问题、超长 BIGINT 被显示成科学计数法，以及日期用 varchar 存储要额外转换，这些都是交付时很容易被业务方发现的坑。用 pandas 写数据、openpyxl 控格式，配合多 Sheet、startrow 做汇总加明细，落地性很强。最后表头对齐那段好像没贴完，如果方便可以补上，想看看 alignment 和表头样式的完整处理方式。

热心网友6

这篇总结得很全，数据库到 Excel 的整条链路基本都覆盖到了。实际用的时候，我觉得有两点特别值得再加一层保险：一是列宽估算不要直接对几十万行做字符串长度统计，容易内存吃紧，可以抽样前几百行估个大概，再设 40 左右上限；二是 Sheet 名清洗最好单独封装，除了长度 31 和斜杠问号星号方括号，冒号也要处理，重复名、空名都统一兜底。日期、金额和超长 BIGINT 这几个类型坑也很关键，财务对账场景用字符串或 number_format 确实比默认 float 稳。分批写多 Sheet 时，建议先把所有 sheet_name 生成好并去重，再进 ExcelWriter，避免写到一半才发现重名或截断。导出完成后加行数、关键列空值和文件可打开校验，后面接定时任务会省心很多。整体思路很实用，先拿小数据跑通格式再放大批量，能少踩很多坑。

热心网友6

感谢分享，这篇把数据库到 Excel 的批量导出讲得很清楚。我自己做类似需求时最头疼的就是三块：一是连接串里的特殊字符和中文，密码用 quote_plus、MySQL 加 utf8mb4 确实能少踩很多坑；二是内存，几十万行多字段直接全量读很容易把机器吃满，用 chunksize 分批写、能下推 SQL 的聚合排序尽量下推，会稳很多；三是格式，pandas.to_excel 省事但列宽、冻结、表头颜色还是得 openpyxl 补，多 Sheet 的命名限制和 startrow 汇总加明细这个做法很实用。 类型转换那部分也很有共鸣，日期别留成字符串、金额别轻易 float、超过 15 位的 BIGINT 要当文本处理，不然业务方一对账就出问题。Excel 单 Sheet 行数上限这个提醒也很关键，超量时拆文件或走 CSV 更现实。 想请教下后面会不会继续写校验和自动调度？比如导出后怎么校验行数、Sheet 数、关键列空值，以及怎么接定时任务和失败告警。期待后续。