查看: 110|回复: 3

Python批量生成Excel:openpyxl样式函数与目录自动化

[复制链接]
发表于 半小时前 | 显示全部楼层 |阅读模式
在财务或多部门报表场景中,常见需求是批量生成格式完全一致的Excel文件:例如5个大区各一份2026年度预算表,表头和文件名不同,但样式、公式结构和列宽相同。手动操作要新建文件、写标题、设表头、调列宽、写公式并另存,每个文件约3分钟,5个文件约15分钟;若每月重复,累计成本更高。这个场景适合用Python配合openpyxl自动化:把样式封装成函数,循环只负责数据逻辑和文件命名。

一、核心原则:样式代码不要塞进循环
批量生成最容易犯的错,是把表头的填充色、字体、边框、对齐方式全部写在for循环里。这样每生成一个文件都重复一大段样式代码,后期修改颜色或边框要改多处。更合理的做法是把“美化表头”抽成独立函数:
  1. def style_header(ws, header, row=1):
  2.     fill = PatternFill('solid', fgColor='4472C4')
  3.     font = Font(bold=True, color='FFFFFF')
  4.     thin = Side(style='thin', color='BFBFBF')
  5.     border = Border(left=thin, right=thin, top=thin, bottom=thin)
  6.     for col, text in enumerate(header, start=1):
  7.         cell = ws.cell(row=row, column=col, value=text)
  8.         cell.fill = fill
  9.         cell.font = font
  10.         cell.alignment = Alignment(horizontal='center', vertical='center')
  11.         cell.border = border
复制代码
这样循环里只保留三行核心动作:创建Workbook、调用style_header、保存文件。样式函数可以复用到其他报表,后续要统一改配色也只改这一处。

二、按部门批量生成预算表
下面函数按部门列表生成5份预算表。标题行合并A1:E1,表头放在第2行,数据从第3行开始。差额和执行率使用Excel公式,执行率单元格设置百分比格式:
  1. def make_department_budgets():
  2.     out_dir = ensure_dir(OUTPUT_DIR / '第04讲' / '部门预算')
  3.     for dep in DEPARTMENTS:
  4.         wb = Workbook()
  5.         ws = wb.active
  6.         ws.title = f'{dep}预算'
  7.         ws.merge_cells('A1:E1')
  8.         ws['A1'] = f'{dep}大区 · 2026 年度预算表'
  9.         ws['A1'].font = Font(bold=True, size=14)
  10.         ws['A1'].alignment = Alignment(horizontal='center', vertical='center')
  11.         style_header(ws, BUDGET_HEADER, row=2)
  12.         for i, subject in enumerate(BUDGET_ROWS, start=3):
  13.             budget = (i - 1) * 50000
  14.             actual = int(budget * 0.85)
  15.             ws.cell(row=i, column=1, value=subject)
  16.             ws.cell(row=i, column=2, value=budget)
  17.             ws.cell(row=i, column=3, value=actual)
  18.             ws.cell(row=i, column=4, value=f'=B{i}-C{i}')
  19.             rate = ws.cell(row=i, column=5, value=f'=C{i}/B{i}')
  20.             rate.number_format = '0.0%'
  21.         for col, width in zip('ABCDE', (14, 14, 14, 12, 10)):
  22.             ws.column_dimensions[col].width = width
  23.         path = out_dir / f'{dep}大区_2026预算表.xlsx'
  24.         wb.save(path)
  25.         log.info(' 生成 %s', path.name)
复制代码
这里有四个实现要点。第一,ensure_dir要先创建输出目录,因为wb.save不会自动创建父目录,目录不存在会直接报错。第二,ws.title设置为“部门+预算”,打开文件即可识别Sheet归属。第三,公式用f-string拼接当前行号,例如f'=B{i}-C{i}',一般不需要绝对引用。第四,执行率使用number_format='0.0%',0.85会显示为85.0%。运行后日志会依次输出华东、华北、华南、西南、华中5个文件,1秒内完成。

三、目录和文件一起批量创建
除了生成文件,还常遇到按月建目录并放一份台账模板。下面的循环同时创建12个月份目录和对应Excel文件:
  1. def make_monthly_folders():
  2.     base = ensure_dir(OUTPUT_DIR / '第04讲' / '月度台账')
  3.     for month in range(1, 13):
  4.         month_dir = ensure_dir(base / f'2026-{month:02d}')
  5.         wb = Workbook()
  6.         ws = wb.active
  7.         ws.title = f'{month}月'
  8.         ws.append(['日期', '部门', '摘要', '收入', '支出', '备注'])
  9.         wb.save(month_dir / f'2026年{month:02d}月台账.xlsx')
复制代码
注意{month:02d}的补零写法:1会格式化为01。批量生成带序号的文件名时必须补零,否则按字符串排序会出现1、10、11、2的顺序。这段代码会生成2026-01到2026-12共12个目录,以及每个目录下的2026年01月台账.xlsx到2026年12月台账.xlsx。

四、抽象成可复用的批量生成模板
把“创建Workbook、写内容、保存”的流程抽象后,以后遇到部门名单、月份、员工清单等批量文件,只需传入数据列表、内容填充函数和文件名函数:
  1. def batch_generate(items, template_func, out_dir, name_func):
  2.     ensure_dir(out_dir)
  3.     for item in items:
  4.         wb = Workbook()
  5.         ws = wb.active
  6.         template_func(item, ws)
  7.         wb.save(out_dir / name_func(item))
复制代码
调用时可以用lambda把具体业务函数包起来,例如template_func=lambda dep, ws: fill_budget(ws, dep),name_func=lambda dep: f'{dep}大区_2026预算表.xlsx'。这样批量生成逻辑和具体报表内容解耦,维护成本更低。

五、三个容易忽略的细节
1. 文件名非法字符清洗。Windows文件名不能包含反斜杠、斜杠、冒号、星号、问号、双引号、尖括号和竖线。若部门名来自用户输入,应先用正则替换:
  1. import re
  2. def safe_filename(name):
  3.     return re.sub(r'[\\/:*?"<>|]', '_', name).strip()
复制代码
2. 覆盖还是跳过。wb.save()在目标文件已存在时会静默覆盖。如果不希望覆盖,可在保存前判断path.exists(),存在则记录日志并continue。
3. 生成报告。批量生成几百个文件时,建议维护created列表,保存成功后append(path.name),最后用log.info输出文件总数和文件名清单,便于核对是否漏生成。

六、小结
这类批量新建Excel的需求,判断标准是“格式相同、内容或文件名不同”。实现时先封装表头样式函数,再在循环中只写标题、表头、数据、列宽和保存;务必先ensure_dir创建目录,序号用{n:02d}补零;需要扩展时使用batch_generate(items, template_func, out_dir, name_func)模板,并补上文件名清洗、覆盖策略和生成清单。完整示例脚本可按原文入口运行:python lessons/L04_batch_create.py,依赖openpyxl及脚本内的OUTPUT_DIR、ensure_dir、get_logger等工具函数。
回复

使用道具 举报

发表于 半小时前 | 显示全部楼层

Re: Python批量生成Excel:openpyxl样式函数与目录自动化

这个思路很实用,尤其是把表头样式抽成 style_header 函数这一点,能避免在循环里反复写字体、填充、边框和对齐,后期统一调整配色或边框时也省事。按部门批量生成预算表的流程也清晰:创建 Workbook、设置 Sheet 名称、合并标题、写表头和公式、调列宽、保存,循环只负责数据逻辑和文件命名,结构比较干净。 几个细节也值得注意:ensure_dir 必须先创建输出目录,因为 wb.save 不会自动创建父目录;公式用 f-string 拼当前行号,适合这种每行结构一致的报表;执行率设置 number_format='0.0%' 后,0.85 显示为 85.0% 比较直观。按月建目录那段用 {month:02d} 补零也很关键,否则文件名按字符串排序会出现 1、10、11、2 这种顺序,补零后 01 到 12 才符合预期。 整体上,这套写法适合把重复性报表生成标准化。后续如果部门或月份配置有变化,也可以把部门列表、表头、数据行继续做成参数或配置,样式函数保持不变,复用性会更好。
回复 支持 反对

使用道具 举报

发表于 半小时前 | 显示全部楼层

Re: Python批量生成Excel:openpyxl样式函数与目录自动化

这篇总结很实用,尤其是把表头样式抽成 style_header 函数这一点,确实比把填充色、字体、边框全塞进循环里更清晰,后续统一改配色也省事。按部门生成预算表那段把公式行号、百分比格式和列宽都讲到了,月度目录用 {month:02d} 补零这个细节也很关键,不然文件名排序很容易乱。另外提醒 ensure_dir 要先建目录也很必要,wb.save 不会自动创建父目录,这点踩过坑的人应该很有共鸣。最后“抽象成可复用的批量生成模板”部分好像没写完,期待后续把部门名之后的模板补全,如果能顺带把 DEPARTMENTS、BUDGET_HEADER、BUDGET_ROWS、OUTPUT_DIR 和 ensure_dir 这些依赖也列一下,读者照着跑会更方便。
回复 支持 反对

使用道具 举报

发表于 半小时前 | 显示全部楼层

Re: Python批量生成Excel:openpyxl样式函数与目录自动化

这个思路很实用,尤其赞同把表头样式抽成 style_header,循环里只保留创建、写数据、保存,后面改配色或边框确实省心。ensure_dir 先建目录、文件名用 02d 补零、执行率用 number_format 这些细节都很关键,能避免不少批量生成时的小坑。 另外有两个小提醒:列宽那里最好按列字母设置,比如 ws.column_dimensions.width,直接给 ws.column_dimensions.width 赋值有时不生效;执行率公式 C/B 如果 B 为 0 会报除零,可以套一层 IFERROR 或先判断预算值。整体上把标题、表头、列宽、公式行再封装成一个通用模板函数,部门配置用列表或字典驱动,后面扩展大区或月份会更顺手。适合财务月报这类重复性报表场景。
回复 支持 反对

使用道具 举报

您需要登录后才可以回帖 登录 | 注册

本版积分规则

指导单位

江苏省公安厅

江苏省通信管理局

浙江省台州刑侦支队

DEFCON GROUP 86025

Hacking Group 021A

旗下站点

态势感知中心

应急响应中心

红盟安全

联系我们

官方QQ群:112851260

官方邮箱:security#ihonker.org(#改成@)

官方核心成员

关注微信公众号

Archiver|手机版|小黑屋| ( 沪ICP备2021026908号 )

GMT+8, 2026-11-23 04:45 , Processed in 0.020433 second(s), 17 queries , Gzip On, Redis On.

Powered by ihonker.com

Copyright © 2015-现在.

  • 返回顶部