在财务或多部门报表场景中,常见需求是批量生成格式完全一致的Excel文件:例如5个大区各一份2026年度预算表,表头和文件名不同,但样式、公式结构和列宽相同。手动操作要新建文件、写标题、设表头、调列宽、写公式并另存,每个文件约3分钟,5个文件约15分钟;若每月重复,累计成本更高。这个场景适合用Python配合openpyxl自动化:把样式封装成函数,循环只负责数据逻辑和文件命名。
一、核心原则:样式代码不要塞进循环
批量生成最容易犯的错,是把表头的填充色、字体、边框、对齐方式全部写在for循环里。这样每生成一个文件都重复一大段样式代码,后期修改颜色或边框要改多处。更合理的做法是把“美化表头”抽成独立函数:- def style_header(ws, header, row=1):
- fill = PatternFill('solid', fgColor='4472C4')
- font = Font(bold=True, color='FFFFFF')
- thin = Side(style='thin', color='BFBFBF')
- border = Border(left=thin, right=thin, top=thin, bottom=thin)
- for col, text in enumerate(header, start=1):
- cell = ws.cell(row=row, column=col, value=text)
- cell.fill = fill
- cell.font = font
- cell.alignment = Alignment(horizontal='center', vertical='center')
- cell.border = border
复制代码 这样循环里只保留三行核心动作:创建Workbook、调用style_header、保存文件。样式函数可以复用到其他报表,后续要统一改配色也只改这一处。
二、按部门批量生成预算表
下面函数按部门列表生成5份预算表。标题行合并A1:E1,表头放在第2行,数据从第3行开始。差额和执行率使用Excel公式,执行率单元格设置百分比格式:- def make_department_budgets():
- out_dir = ensure_dir(OUTPUT_DIR / '第04讲' / '部门预算')
- for dep in DEPARTMENTS:
- wb = Workbook()
- ws = wb.active
- ws.title = f'{dep}预算'
- ws.merge_cells('A1:E1')
- ws['A1'] = f'{dep}大区 · 2026 年度预算表'
- ws['A1'].font = Font(bold=True, size=14)
- ws['A1'].alignment = Alignment(horizontal='center', vertical='center')
- style_header(ws, BUDGET_HEADER, row=2)
- for i, subject in enumerate(BUDGET_ROWS, start=3):
- budget = (i - 1) * 50000
- actual = int(budget * 0.85)
- ws.cell(row=i, column=1, value=subject)
- ws.cell(row=i, column=2, value=budget)
- ws.cell(row=i, column=3, value=actual)
- ws.cell(row=i, column=4, value=f'=B{i}-C{i}')
- rate = ws.cell(row=i, column=5, value=f'=C{i}/B{i}')
- rate.number_format = '0.0%'
- for col, width in zip('ABCDE', (14, 14, 14, 12, 10)):
- ws.column_dimensions[col].width = width
- path = out_dir / f'{dep}大区_2026预算表.xlsx'
- wb.save(path)
- 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文件:- def make_monthly_folders():
- base = ensure_dir(OUTPUT_DIR / '第04讲' / '月度台账')
- for month in range(1, 13):
- month_dir = ensure_dir(base / f'2026-{month:02d}')
- wb = Workbook()
- ws = wb.active
- ws.title = f'{month}月'
- ws.append(['日期', '部门', '摘要', '收入', '支出', '备注'])
- 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、写内容、保存”的流程抽象后,以后遇到部门名单、月份、员工清单等批量文件,只需传入数据列表、内容填充函数和文件名函数:- def batch_generate(items, template_func, out_dir, name_func):
- ensure_dir(out_dir)
- for item in items:
- wb = Workbook()
- ws = wb.active
- template_func(item, ws)
- 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文件名不能包含反斜杠、斜杠、冒号、星号、问号、双引号、尖括号和竖线。若部门名来自用户输入,应先用正则替换:- import re
- def safe_filename(name):
- 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等工具函数。 |