474 lines
14 KiB
Python
474 lines
14 KiB
Python
#!/usr/bin/env python3
|
||
from __future__ import annotations
|
||
|
||
import json
|
||
import shutil
|
||
import sqlite3
|
||
from pathlib import Path
|
||
|
||
from docx import Document
|
||
from docx.enum.table import WD_TABLE_ALIGNMENT, WD_CELL_VERTICAL_ALIGNMENT
|
||
from docx.shared import Inches, Pt
|
||
from openpyxl import Workbook
|
||
from openpyxl.styles import Alignment, Font, PatternFill, Border, Side
|
||
from openpyxl.utils import get_column_letter
|
||
|
||
|
||
ROOT = Path(__file__).resolve().parents[1]
|
||
OUT = ROOT / "manual_tests"
|
||
|
||
|
||
def main() -> None:
|
||
if OUT.exists():
|
||
shutil.rmtree(OUT)
|
||
OUT.mkdir(parents=True)
|
||
|
||
build_excel_to_word(OUT / "01_excel_to_word")
|
||
build_sqlite_to_excel(OUT / "02_sqlite_to_excel")
|
||
build_excel_to_excel(OUT / "03_excel_to_excel")
|
||
build_filter_work_order_to_word(OUT / "04_filter_work_order_to_word")
|
||
write_readme()
|
||
|
||
print(OUT)
|
||
|
||
|
||
def build_excel_to_word(path: Path) -> None:
|
||
path.mkdir(parents=True)
|
||
write_daily_source_xlsx(path / "source_daily_data.xlsx")
|
||
write_daily_word_template(path / "daily_report_template.docx")
|
||
write_json(
|
||
path / "task.json",
|
||
{
|
||
"vars": {"date": "2026-06-18", "batch": "默认班次"},
|
||
"data": {
|
||
"type": "excel",
|
||
"path": "./source_daily_data.xlsx",
|
||
"sheet": "Daily",
|
||
"header_row": 1,
|
||
},
|
||
"template": {"path": "./daily_report_template.docx"},
|
||
"output": {
|
||
"path": "./out/daily_{{date}}_{{name}}_{{_row}}.docx",
|
||
"overwrite": True,
|
||
},
|
||
},
|
||
)
|
||
|
||
|
||
def build_sqlite_to_excel(path: Path) -> None:
|
||
path.mkdir(parents=True)
|
||
write_summary_sqlite(path / "source_summary.db")
|
||
write_summary_excel_template(path / "department_summary_template.xlsx")
|
||
write_json(
|
||
path / "task.json",
|
||
{
|
||
"vars": {"date": "2026-06-18"},
|
||
"data": {
|
||
"type": "sqlite",
|
||
"path": "./source_summary.db",
|
||
"query": "select department, report_date, total_count, abnormal_count, owner from department_summary where report_date = ? order by department",
|
||
"params": ["{{date}}"],
|
||
},
|
||
"template": {"path": "./department_summary_template.xlsx"},
|
||
"output": {
|
||
"path": "./out/summary_{{department}}_{{_row}}.xlsx",
|
||
"overwrite": True,
|
||
},
|
||
},
|
||
)
|
||
|
||
|
||
def build_excel_to_excel(path: Path) -> None:
|
||
path.mkdir(parents=True)
|
||
write_audit_source_xlsx(path / "source_audit_data.xlsx")
|
||
write_audit_excel_template(path / "audit_template.xlsx")
|
||
write_json(
|
||
path / "task.json",
|
||
{
|
||
"vars": {"date": "2026-06-18"},
|
||
"data": {
|
||
"type": "excel",
|
||
"path": "./source_audit_data.xlsx",
|
||
"sheet": "Audit",
|
||
"header_row": 2,
|
||
},
|
||
"template": {"path": "./audit_template.xlsx"},
|
||
"output": {
|
||
"path": "./out/audit_{{date}}_{{employee}}_{{_row}}.xlsx",
|
||
"overwrite": True,
|
||
},
|
||
},
|
||
)
|
||
|
||
|
||
def build_filter_work_order_to_word(path: Path) -> None:
|
||
path.mkdir(parents=True)
|
||
write_work_order_source_xlsx(path / "source_work_orders.xlsx")
|
||
write_work_order_word_template(path / "work_order_template.docx")
|
||
write_json(
|
||
path / "task.json",
|
||
{
|
||
"vars": {"date": "2026-06-18"},
|
||
"data": {
|
||
"type": "excel",
|
||
"path": "./source_work_orders.xlsx",
|
||
"sheet": "工单",
|
||
"header_row": 1,
|
||
},
|
||
"filter": {
|
||
"equals": {
|
||
"日期": "{{date}}",
|
||
"工单编号": "{{ticket_no}}",
|
||
},
|
||
"expect": "one",
|
||
},
|
||
"template": {"path": "./work_order_template.docx"},
|
||
"output": {
|
||
"path": "./out/work_order_{{日期}}_{{工单编号}}.docx",
|
||
"overwrite": True,
|
||
},
|
||
},
|
||
)
|
||
|
||
|
||
def write_daily_source_xlsx(path: Path) -> None:
|
||
rows = [
|
||
["name", "department", "done", "plan", "risk"],
|
||
["张三/测试", "营销部", "完成 A 系统订单核对 128 条；同步 B 系统回款 35 笔", "跟进 3 条异常订单并补充截图", "无"],
|
||
["李四", "客服部", "整理投诉工单 21 条；完成日报提交", "复核高优先级工单并同步主管", "等待业务确认 1 条"],
|
||
]
|
||
write_table_workbook(path, "Daily", rows, title="日报源数据")
|
||
|
||
|
||
def write_daily_word_template(path: Path) -> None:
|
||
doc = Document()
|
||
section = doc.sections[0]
|
||
section.top_margin = Inches(0.8)
|
||
section.bottom_margin = Inches(0.8)
|
||
section.left_margin = Inches(0.85)
|
||
section.right_margin = Inches(0.85)
|
||
|
||
title = doc.add_paragraph()
|
||
title_run = title.add_run("员工日报（测试模板）")
|
||
title_run.bold = True
|
||
title_run.font.size = Pt(18)
|
||
|
||
meta = doc.add_table(rows=4, cols=2)
|
||
meta.alignment = WD_TABLE_ALIGNMENT.LEFT
|
||
set_table_style(meta)
|
||
labels = ["日期", "员工", "部门", "班次"]
|
||
values = ["{{date}}", "{{na", "{{department}}", "{{batch}}"]
|
||
for i, label in enumerate(labels):
|
||
meta.cell(i, 0).text = label
|
||
if label == "员工":
|
||
p = meta.cell(i, 1).paragraphs[0]
|
||
p.add_run(values[i])
|
||
p.add_run("me}}")
|
||
else:
|
||
meta.cell(i, 1).text = values[i]
|
||
|
||
for heading, placeholder in [
|
||
("今日完成", "{{done}}"),
|
||
("明日计划", "{{plan}}"),
|
||
("风险备注", "{{risk}}"),
|
||
]:
|
||
doc.add_paragraph()
|
||
h = doc.add_paragraph()
|
||
r = h.add_run(heading)
|
||
r.bold = True
|
||
r.font.size = Pt(12)
|
||
p = doc.add_paragraph()
|
||
p.add_run(placeholder)
|
||
|
||
doc.save(path)
|
||
|
||
|
||
def write_summary_sqlite(path: Path) -> None:
|
||
conn = sqlite3.connect(path)
|
||
try:
|
||
conn.executescript(
|
||
"""
|
||
create table department_summary (
|
||
department text,
|
||
report_date text,
|
||
total_count integer,
|
||
abnormal_count integer,
|
||
owner text
|
||
);
|
||
insert into department_summary values
|
||
('客服部', '2026-06-18', 76, 0, '李四'),
|
||
('营销部', '2026-06-18', 128, 3, '张三'),
|
||
('营销部', '2026-06-17', 115, 2, '张三');
|
||
"""
|
||
)
|
||
conn.commit()
|
||
finally:
|
||
conn.close()
|
||
|
||
|
||
def write_summary_excel_template(path: Path) -> None:
|
||
wb = Workbook()
|
||
ws = wb.active
|
||
ws.title = "Summary"
|
||
ws.append(["部门日报汇总（测试模板）", ""])
|
||
ws.append(["部门", "{{department}}"])
|
||
ws.append(["日期", "{{report_date}}"])
|
||
ws.append(["负责人", "{{owner}}"])
|
||
ws.append(["处理总数", "{{total_count}}"])
|
||
ws.append(["异常数", "{{abnormal_count}}"])
|
||
style_sheet(ws, title_rows={1})
|
||
ws.column_dimensions["A"].width = 18
|
||
ws.column_dimensions["B"].width = 32
|
||
wb.save(path)
|
||
|
||
|
||
def write_audit_source_xlsx(path: Path) -> None:
|
||
rows = [
|
||
["日报质检导出"],
|
||
["employee", "date", "item", "score", "comment"],
|
||
["王五", "2026-06-18", "日报完整性", "92", "字段齐全，说明清楚"],
|
||
["赵六", "2026-06-18", "系统截图一致性", "87", "缺少 1 张截图，需要补充"],
|
||
]
|
||
write_table_workbook(path, "Audit", rows, title=None)
|
||
|
||
|
||
def write_audit_excel_template(path: Path) -> None:
|
||
wb = Workbook()
|
||
ws = wb.active
|
||
ws.title = "AuditReport"
|
||
ws.append(["日报质检结果（测试模板）", ""])
|
||
ws.append(["日期", "{{date}}"])
|
||
ws.append(["员工", "{{employee}}"])
|
||
ws.append(["检查项", "{{item}}"])
|
||
ws.append(["得分", "{{score}}"])
|
||
ws.append(["评语", "{{comment}}"])
|
||
style_sheet(ws, title_rows={1})
|
||
ws.column_dimensions["A"].width = 16
|
||
ws.column_dimensions["B"].width = 42
|
||
wb.save(path)
|
||
|
||
|
||
def write_work_order_source_xlsx(path: Path) -> None:
|
||
rows = [
|
||
["日期", "工单编号", "客户名称", "系统来源", "处理结果", "下一步"],
|
||
["2026-06-18", "GD-001", "A 公司", "PMS3.0", "已派单", "等待现场反馈"],
|
||
["2026-06-18", "GD-002", "B 公司", "D5000", "已完成", "归档并同步日报"],
|
||
["2026-06-19", "GD-001", "C 公司", "风控平台", "待复核", "补充截图"],
|
||
]
|
||
write_table_workbook(path, "工单", rows, title="工单源数据")
|
||
|
||
|
||
def write_work_order_word_template(path: Path) -> None:
|
||
doc = Document()
|
||
section = doc.sections[0]
|
||
section.top_margin = Inches(0.8)
|
||
section.bottom_margin = Inches(0.8)
|
||
section.left_margin = Inches(0.85)
|
||
section.right_margin = Inches(0.85)
|
||
|
||
title = doc.add_paragraph()
|
||
title_run = title.add_run("工单处理记录（筛选测试模板）")
|
||
title_run.bold = True
|
||
title_run.font.size = Pt(18)
|
||
|
||
table = doc.add_table(rows=6, cols=2)
|
||
table.alignment = WD_TABLE_ALIGNMENT.LEFT
|
||
set_table_style(table)
|
||
rows = [
|
||
("日期", "{{日期}}"),
|
||
("工单编号", "{{工单编号}}"),
|
||
("客户名称", "{{客户名称}}"),
|
||
("系统来源", "{{系统来源}}"),
|
||
("处理结果", "{{处理结果}}"),
|
||
("下一步", "{{下一步}}"),
|
||
]
|
||
for idx, (label, value) in enumerate(rows):
|
||
table.cell(idx, 0).text = label
|
||
table.cell(idx, 1).text = value
|
||
|
||
doc.save(path)
|
||
|
||
|
||
def write_table_workbook(path: Path, sheet: str, rows: list[list[str]], title: str | None) -> None:
|
||
wb = Workbook()
|
||
ws = wb.active
|
||
ws.title = sheet
|
||
for row in rows:
|
||
ws.append(row)
|
||
title_rows = {1} if title else set()
|
||
style_sheet(ws, title_rows=title_rows)
|
||
for idx, width in enumerate([18, 16, 44, 36, 28], start=1):
|
||
ws.column_dimensions[get_column_letter(idx)].width = width
|
||
wb.save(path)
|
||
|
||
|
||
def style_sheet(ws, title_rows: set[int]) -> None:
|
||
header_fill = PatternFill("solid", fgColor="D9EAF7")
|
||
title_fill = PatternFill("solid", fgColor="1F4E79")
|
||
thin = Side(style="thin", color="D9E2F3")
|
||
border = Border(top=thin, left=thin, right=thin, bottom=thin)
|
||
for row in ws.iter_rows():
|
||
for cell in row:
|
||
cell.alignment = Alignment(vertical="center", wrap_text=True)
|
||
cell.border = border
|
||
if cell.row in title_rows:
|
||
cell.font = Font(bold=True, color="FFFFFF", size=13)
|
||
cell.fill = title_fill
|
||
elif cell.row == min(title_rows or {1}) + 1 or (not title_rows and cell.row == 1):
|
||
cell.font = Font(bold=True)
|
||
cell.fill = header_fill
|
||
ws.freeze_panes = "A2"
|
||
|
||
|
||
def set_table_style(table) -> None:
|
||
for row in table.rows:
|
||
for cell in row.cells:
|
||
cell.vertical_alignment = WD_CELL_VERTICAL_ALIGNMENT.CENTER
|
||
for paragraph in cell.paragraphs:
|
||
for run in paragraph.runs:
|
||
run.font.size = Pt(10.5)
|
||
|
||
|
||
def write_json(path: Path, data: dict) -> None:
|
||
path.write_text(json.dumps(data, ensure_ascii=False, indent=2), encoding="utf-8")
|
||
|
||
|
||
def write_readme() -> None:
|
||
text = """# docfill 手工测试素材
|
||
|
||
本目录提供四组可直接运行的本地测试素材，用于验证 `docfill` 对筛选、Word、Excel、Excel 数据源和 SQLite 数据源的支持情况。
|
||
|
||
运行前进入项目根目录：
|
||
|
||
```bash
|
||
cd /Users/zhaoyilun/Documents/xls_doc
|
||
```
|
||
|
||
构建工具：
|
||
|
||
```bash
|
||
bash scripts/build.sh
|
||
```
|
||
|
||
## 案例 1：Excel 数据生成 Word 日报
|
||
|
||
目录：
|
||
|
||
```text
|
||
manual_tests/01_excel_to_word
|
||
```
|
||
|
||
运行命令：
|
||
|
||
```bash
|
||
dist/docfill-darwin-arm64 run -c manual_tests/01_excel_to_word/task.json --var batch=早班 --verbose
|
||
```
|
||
|
||
输出示例：
|
||
|
||
```text
|
||
generated 2 file(s)
|
||
- /Users/zhaoyilun/Documents/xls_doc/manual_tests/01_excel_to_word/out/daily_2026-06-18_张三_测试_1.docx
|
||
- /Users/zhaoyilun/Documents/xls_doc/manual_tests/01_excel_to_word/out/daily_2026-06-18_李四_2.docx
|
||
```
|
||
|
||
## 案例 2：SQLite 数据生成 Excel 汇总表
|
||
|
||
目录：
|
||
|
||
```text
|
||
manual_tests/02_sqlite_to_excel
|
||
```
|
||
|
||
运行命令：
|
||
|
||
```bash
|
||
dist/docfill-darwin-arm64 run -c manual_tests/02_sqlite_to_excel/task.json --var date=2026-06-18 --verbose
|
||
```
|
||
|
||
输出示例：
|
||
|
||
```text
|
||
generated 2 file(s)
|
||
- /Users/zhaoyilun/Documents/xls_doc/manual_tests/02_sqlite_to_excel/out/summary_客服部_1.xlsx
|
||
- /Users/zhaoyilun/Documents/xls_doc/manual_tests/02_sqlite_to_excel/out/summary_营销部_2.xlsx
|
||
```
|
||
|
||
## 案例 3：Excel 数据生成 Excel 质检表
|
||
|
||
目录：
|
||
|
||
```text
|
||
manual_tests/03_excel_to_excel
|
||
```
|
||
|
||
运行命令：
|
||
|
||
```bash
|
||
dist/docfill-darwin-arm64 run -c manual_tests/03_excel_to_excel/task.json --var date=2026-06-18 --verbose
|
||
```
|
||
|
||
输出示例：
|
||
|
||
```text
|
||
generated 2 file(s)
|
||
- /Users/zhaoyilun/Documents/xls_doc/manual_tests/03_excel_to_excel/out/audit_2026-06-18_王五_1.xlsx
|
||
- /Users/zhaoyilun/Documents/xls_doc/manual_tests/03_excel_to_excel/out/audit_2026-06-18_赵六_2.xlsx
|
||
```
|
||
|
||
## 案例 4：按日期和工单编号筛选一条工单并生成 Word
|
||
|
||
目录：
|
||
|
||
```text
|
||
manual_tests/04_filter_work_order_to_word
|
||
```
|
||
|
||
输入数据内容：
|
||
|
||
```text
|
||
日期 工单编号 客户名称 系统来源 处理结果 下一步
|
||
2026-06-18 GD-001 A 公司 PMS3.0 已派单 等待现场反馈
|
||
2026-06-18 GD-002 B 公司 D5000 已完成 归档并同步日报
|
||
2026-06-19 GD-001 C 公司 风控平台 待复核 补充截图
|
||
```
|
||
|
||
运行命令：
|
||
|
||
```bash
|
||
dist/docfill-darwin-arm64 run -c manual_tests/04_filter_work_order_to_word/task.json --var date=2026-06-18 --var ticket_no=GD-002 --verbose
|
||
```
|
||
|
||
输出示例：
|
||
|
||
```text
|
||
generated 1 file(s)
|
||
- /Users/zhaoyilun/Documents/xls_doc/manual_tests/04_filter_work_order_to_word/out/work_order_2026-06-18_GD-002.docx
|
||
```
|
||
|
||
查看结果：
|
||
|
||
```bash
|
||
open manual_tests/04_filter_work_order_to_word/out
|
||
```
|
||
|
||
## 重新生成素材
|
||
|
||
如需重新生成全部手工测试文件，执行：
|
||
|
||
```bash
|
||
/Users/zhaoyilun/.cache/codex-runtimes/codex-primary-runtime/dependencies/python/bin/python3 scripts/make-manual-test-materials.py
|
||
```
|
||
|
||
输出示例：
|
||
|
||
```text
|
||
/Users/zhaoyilun/Documents/xls_doc/manual_tests
|
||
```
|
||
"""
|
||
(OUT / "README.md").write_text(text, encoding="utf-8")
|
||
|
||
|
||
if __name__ == "__main__":
|
||
main()
|