xlsxファイルの各セルにjinja2テンプレートを適用するやつ

xljj.py

#!/usr/bin/env python
# -*- coding:utf8 -*-
import optparse
import sys
from contextlib import closing

import yaml
import jinja2
from openpyxl import load_workbook


def load_param_file(path):
    with closing(open(path)) as f:
        return yaml.load(f.read().decode('UTF-8'))


def replace_xl_workbook(template_path, dest_path, param):
    print template_path, dest_path
    wb = load_workbook(template_path)
    for ws in wb.worksheets:
        for ri, row in enumerate(ws.iter_rows()):
            text_cells = list(cell for cell in row if cell.value and isinstance(cell.value, basestring))
            for cell in text_cells:
                cell.value = jinja2.Template(cell.value).render(param)
    wb.save(dest_path)


def main(cmd_args):
    print cmd_args
    source_path = cmd_args.pop(0)
    dest_path = cmd_args.pop(0)

    param = dict()

    for item in cmd_args:
        param.update(load_param_file(item))

    replace_xl_workbook(source_path, dest_path, param)


if __name__ == '__main__':
    usage = """\
usage: %prog source.xlsx dest.xlsx [param_files ...]
"""
    main(sys.argv[1:])

requirements

pip install PyYAML jinja2 openpyxl

article about this https://titanwolf.org/Network/Articles/Article?AID=494af6cc-c8c8-4a4c-94e5-604936fe631f#gsc.tab=0