Python自动化生成Word报告：从数据抽取到模板渲染的全流程实践

Python自动化生成Word报告：从数据抽取到模板渲染的全流程实践

一、引言

在运维工作中，我们经常需要定期生成格式固定的月度报告（如业务量统计、性能分析等），数据源涉及多种数据库：DB2、Oracle、SQL Server、MySQL、AS400（DB2 for i）等。传统的手工编写方式耗时费力，且容易出错。本文介绍了一套基于 Python 的自动化报告生成系统，实现了多数据源统一抽取 → 中间表存储 → 数据转换 → 模板渲染的完整链路，将月度报告的制作时间从数小时压缩到分钟级。

系统核心特点：

  • 多数据库适配：通过统一的工具层封装 DB2、Oracle、MySQL、SQL Server、AS400 等驱动，上层业务代码无感知。
  • 三层解耦架构：数据抽取、转换、渲染各层独立，便于维护和测试。
  • 模板与数据分离：使用 docxtpl 在 Word 模板中嵌入占位符，样式调整无需改代码。
  • 支持复杂表格：动态行数、多列统计、百分比计算、千位符格式化等。
  • 多种运行模式：全流程、仅转换、仅渲染，灵活适配不同场景。

二、系统架构

2.1 整体流程

生产库(DB2/Oracle/SQL Server/AS400) → 只读查询 → 运维库(MySQL) → 中间表 → 数据转换 → JSON/Context → Word模板 → 报告
                ↑                                    ↑
           抽取脚本（统一工具层）                 转换模块

系统分为三个独立阶段：

  1. 数据抽取（ETL）
    从各类生产只读库执行开发提供的 SQL（语法因数据库而异），将结果写入运维专用库的 t_report_dms6 表。每条记录包含 category（年月标识）、value（JSON 格式的结果集）、data_source（来源标识）。

  2. 数据转换
    从中间表读取指定 category 的所有记录，解析 JSON，经过表格构建、变量计算、统计聚合，生成一个完整的上下文 context 字典。

  3. 报告渲染
    使用 docxtpl 加载 Word 模板，传入 context，生成最终的 .docx 报告。

2.2 模块划分

report_builder/               # 核心转换包
├── data_fetcher.py           # 从中间表读取数据，校验条数
├── table_builders.py         # 构建三个主要表格（t1/t2/t3）
├── basic_vars_builder.py     # 处理单值变量（var1~var19）
├── table_stats_calculator.py # 基于表格的统计计算（var14/15/16/17/20）
├── report_context_builder.py # 协调整合，生成最终 context
data_extractor.py             # 数据抽取脚本（调用各 get_* 函数，内部使用统一工具层）
utils/                        # 数据库工具、配置加载、日志等
├── db2_util.py               # DB2 连接与查询
├── oracle_util.py            # Oracle 连接与查询
├── sqlserver2005_util.py     # SQL Server 连接与查询
├── mysql_util.py             # MySQL 连接与查询
├── as400_util.py             # AS400 (DB2 for i) 连接与查询
├── load_config.py            # 统一配置加载
├── log_util.py               # 异步日志
└── mail_util.py              # 邮件通知
main.py                       # 主入口，支持三种运行模式

三、核心技术实现

3.1 多数据库统一工具层

每种数据库的连接参数和 SQL 执行方式不同，但对外提供一致的接口。例如：

# utils/db2_util.py
def run_db2_sql(host, database, port, user, password, sql):
    conn = ibm_db.connect(f"DATABASE={database};HOSTNAME={host};PORT={port};...")
    stmt = ibm_db.exec_immediate(conn, sql)
    return fetch_all(stmt)

# utils/oracle_util.py
def run_oracle_sql(host, service_name, port, user, password, sql):
    dsn = cx_Oracle.makedsn(host, port, service_name=service_name)
    conn = cx_Oracle.connect(user, password, dsn)
    return pd.read_sql(sql, conn).values.tolist()

上层 get_* 函数通过配置文件中的 type 字段自动选择对应的工具函数：

# config/config.yaml
databases:
  dms6:
    type: db2
    host: 10.1.1.100
    database: DMSDB
    port: 50000
    user: readonly
    password: xxx
  operatdb:
    type: mysql
    host: 10.1.1.200
    port: 3306
    user: ops
    password: xxx
    database: report_db

调用时根据类型动态导入：

def run_sql(db_name, sql):
    db_info = find_config_by_name(config, db_name)
    util = importlib.import_module(f"utils.{db_info['type']}_util")
    return util.run_sql(**db_info, sql=sql)

3.2 中间表设计

为了保留历史快照并解耦生产库，设计了 MySQL 表 t_report_dms6：

CREATE TABLE t_report_dms6 (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    category VARCHAR(50) NOT NULL COMMENT '年月，如202603',
    value JSON NOT NULL COMMENT '查询结果的JSON数据',
    data_source VARCHAR(255) COMMENT '数据来源标识',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_category_source (category, data_source)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  • category 按月分区，支持历史回溯。
  • value 使用原生 JSON 类型，可存储数组、对象。
  • 唯一约束保证重复运行同一月份数据时会覆盖更新。

3.3 数据抽取与入库

每个统计指标对应一个独立的查询函数，例如：

def get_product_count(year, month):
    sql = "SELECT COUNT(*) FROM dmsusr.AGR_AgreementSpec WHERE CURRENTVERSION = 'Y'"
    rows = run_db2_sql(db_info['dms6'], sql)   # 实际调用统一工具层
    count = rows[0][0]
    value = {"product_count": count}
    insert_data_to_t_report_dms6(
        category=f"{year}{month:02d}",
        value=value,
        data_source="dmsusr.AGR_AgreementSpec.上线产品数"
    )

所有 get_* 函数由 extract_all_data(year, month) 统一调用，保证数据完整性。

3.4 数据转换核心

3.4.1 一次性读取与校验

fetch_all_data(year, month, expected_count) 从中间表读取所有记录，并校验记录数，防止遗漏：

rows = run_mysql_sql("SELECT data_source, value FROM t_report_dms6 WHERE category = %s", (category,))
if len(rows) != expected_count:
    raise ValueError(f"Expected {expected_count} records, got {len(rows)}")
return {ds: json.loads(val) for ds, val in rows}

3.4.2 表格构建（以 t1 为例）

t1 为“分银行分月交易件数”，需要从原始 JSON 列表生成二维数组，并添加“半年合计”和“占半年业务量百分比”列。

关键逻辑：

  • 动态提取所有月份字段（如 202509、202510…），按数值排序。
  • 对每个银行计算六个月总和，同时累加各月总计。
  • 计算每行占比，并最终添加合计行。
  • 排序规则：先按占比降序，再按半年合计降序。
def build_table_t1(data_dict):
    data = data_dict["dmsusr.ITX_Transaction.分银行分月交易件数"]
    month_keys = sorted(set(...), key=int)
    # 计算各银行数据
    for bank in data:
        row_sum = sum(bank.get(m,0) for m in month_keys)
        # 存储行...
    # 计算占比
    for row in table:
        ratio = row_sum / all_total * 100
        row.append(f"{ratio:.2f}%")
    # 多级排序
    table.sort(key=lambda r: (float(r[-1].rstrip('%')), int(r[-2].replace(',',''))), reverse=True)
    # 添加合计行
    table.append(total_row)

3.4.3 特殊列：占当月新单百分比（t3）

t3 表格需要一列“占当月新单百分比”，即每个银行的交易量占所有银行总交易量的比例。实现时先计算总交易量：

total_trans = sum(item["trans_cases"] for item in data)
for bank in data:
    percent = bank["trans_cases"] / total_trans * 100
    row.append(f"{percent:.1f}%")

3.5 Word 模板与 docxtpl

模板中使用 Jinja2 语法：

  • 普通变量：{{ var1 }}
  • 表格行循环：需要将 {%tr for row in t1 %} 和 {%tr endfor %} 放在表格行的第一个单元格和最后一个单元格内，避免 WPS 删除。同时设置标签文字为白色（隐藏）。

关键坑点：WPS 保存文档时会删除未知的自定义标签，导致 {% for %} 丢失。解决方案：

  • 使用 Microsoft Word 编辑模板（兼容性最好）。
  • 或采用 {%tr %} 标签并放在单元格内，结合隐藏文字技巧。
  • 最彻底：在代码中动态生成表格行（放弃模板循环），但会增加代码复杂度。

3.6 千位符与百分比格式化

所有数值在展示前统一格式化：

def _format_number(value: int) -> str:
    return f"{value:,}"   # 12345 -> "12,345"

def _format_percent(value: float, decimal: int = 2) -> str:
    return f"{value:.{decimal}f}%"

注意处理 Decimal 类型，需先转换为 float 或 int，否则 JSON 序列化会报错。

四、运行与部署

4.1 主入口脚本 main.py

支持三种模式：

# 全流程：抽数 + 转换 + 渲染
py3 main.py --mode full --year 2026 --month 3 --template template.docx

# 仅使用中间表数据（假设已抽数）
py3 main.py --mode mid --year 2026 --month 3 --template template.docx

# 使用已有的 JSON 上下文文件
py3 main.py --mode json --context context_202603.json --template template.docx

4.2 定时任务

使用 crontab 每月1号自动执行全流程：

0 2 1 * * cd /path/to/project && py3 main.py --mode full --year $(date +\%Y) --month $(date +\%m -d 'last month') --template template.docx

4.3 依赖环境

  • Python 3.8+
  • 库：docxtpl, python-docx, pymysql, ibm_db, cx_Oracle, pymssql, pyodbc（AS400）, pyyaml, python-dateutil
  • 数据库：MySQL 8+（运维库），DB2/Oracle/SQL Server/AS400（只读生产库）

五、常见问题与解决方案

问题 原因 解决
ModuleNotFoundError: No module named 'utils' 包路径不正确 在脚本开头添加 sys.path.insert(0, os.path.dirname(__file__))
ibm_db 找不到指定模块 缺少 DB2 驱动或 VC++ 运行库 安装 IBM Data Server Driver 和 VC++ Redistributable
Oracle 连接失败 TNS 或 service_name 配置错误 使用 cx_Oracle.makedsn 生成 DSN，避免 TNS 文件依赖
AS400 中文乱码 编码设置不正确 连接字符串中添加 CCSID=1208 或使用 pyodbc 的 CONVERT 选项
SQL Server 2005 连接失败 驱动版本过低 使用 pymssql 并指定 TDS 7.0 版本
JSON 序列化失败 Decimal 不支持 DB2 返回 Decimal 类型 在赋值前 float(value) 或自定义 JSON 编码器
WPS 保存后 {% for %} 丢失 WPS 会清理未知标签 改用 Microsoft Word 编辑模板，或将标签放入单元格并隐藏
表格循环渲染错误 Encountered unknown tag 'endfor' 未使用 {%tr %} 标签 表格行循环必须用 {%tr for %} 和 {%tr endfor %}
千位符格式化后排序失效 字符串比较导致顺序错误 在排序时使用原始数值，展示时再格式化

六、总结与展望

本系统成功将月度报告制作从手工操作转为全自动化，显著提升了效率和准确性。通过统一数据库工具层，我们能够轻松接入 DB2、Oracle、SQL Server、AS400 等多种生产数据库，上层业务逻辑完全复用。分层设计使得各模块可独立测试和替换，适应未来需求变化。

后续可优化的方向：

  • 增加可视化配置：通过 Web 界面选择报告模板、数据源，降低使用门槛。
  • 支持更多输出格式：PDF、HTML 等。
  • 实时监控与告警：数据抽取失败或超时自动通知运维。
  • 增量数据抽取：针对大数据量场景，只抽取变更数据。

整套代码已在生产环境稳定运行半年，累计生成 50+ 份报告，无一差错。希望本文能为同样面临多数据库、重复性报告工作的运维开发者提供参考。

附：由于代码仓库涉及公司内部数据库连接配置，无法公开。但上述核心逻辑和设计模式可复用于任何类似场景。如需详细代码片段或数据库适配层的实现细节，欢迎交流。

posted @ 2026-04-07 21:33  神秘园欢迎您  阅读(1)  评论(0)    收藏  举报