"""生成单表结构的测试数据（与 data/templates 的 10 份模板一一对应）。""" from __future__ import annotations import sys from pathlib import Path from openpyxl import Workbook from openpyxl.comments import Comment from openpyxl.styles import Alignment, Border, Font, PatternFill, Side from openpyxl.utils import get_column_letter from openpyxl.worksheet.datavalidation import DataValidation sys.stdout.reconfigure(encoding="utf-8") OUT_DIR = Path(r"E:\CODE\knowledge_agent\data\test_data") OUT_DIR.mkdir(parents=True, exist_ok=True) HEADER_FILL = PatternFill("solid", fgColor="2F5597") HEADER_FONT = Font(name="微软雅黑", size=10, bold=True, color="FFFFFF") BODY_FONT = Font(name="微软雅黑", size=10) NOTE_FONT = Font(name="微软雅黑", size=9, color="808080") THIN = Side(style="thin", color="BFBFBF") BORDER = Border(left=THIN, right=THIN, top=THIN, bottom=THIN) TEST_FILL = PatternFill("solid", fgColor="FFF2CC") def _make_test_file(filename: str, title: str, headers: list[str], widths: list[int], rows: list[list], notes: list[str], validations: dict[int, list[str]] | None = None, comments: dict[int, str] | None = None) -> None: wb = Workbook() ws = wb.active ws.title = title for c, (h, w) in enumerate(zip(headers, widths), start=1): cell = ws.cell(row=1, column=c, value=h) cell.font = HEADER_FONT cell.fill = HEADER_FILL cell.alignment = Alignment(horizontal="center", vertical="center", wrap_text=True) cell.border = BORDER ws.column_dimensions[get_column_letter(c)].width = w ws.row_dimensions[1].height = 30 for r, row in enumerate(rows, start=2): for c, v in enumerate(row, start=1): cell = ws.cell(row=r, column=c, value=v) cell.font = BODY_FONT cell.border = BORDER cell.fill = TEST_FILL cell.alignment = Alignment(vertical="center", wrap_text=True) if validations: for col, values in validations.items(): letter = get_column_letter(col) dv = DataValidation(type="list", formula1='"' + ",".join(values) + '"', allow_blank=True, showDropDown=False) ws.add_data_validation(dv) dv.add(f"{letter}2:{letter}500") if comments: for col, text in comments.items(): ws.cell(row=1, column=col).comment = Comment(text, "测试数据生成") ws.freeze_panes = "A2" note_ws = wb.create_sheet("填写说明") note_ws.column_dimensions["A"].width = 115 note_ws.cell(row=1, column=1, value=f"【{title}】测试数据说明").font = Font(name="微软雅黑", size=12, bold=True) lines = ["本文件为测试数据（黄色行），与 data/templates 对应模板同构，用于管线/问答测试。", "覆盖场景：续签链、项目从属、人员跨项目流动（董一青）、设备台账、采购维保、检查记录等。"] + notes for i, n in enumerate(lines, start=2): cell = note_ws.cell(row=i, column=1, value=f"· {n}") cell.font = NOTE_FONT cell.alignment = Alignment(wrap_text=True, vertical="top") wb.save(OUT_DIR / filename) def main() -> None: days = [f"{i}日" for i in range(1, 32)] dw = [5] * 31 _make_test_file( "项目信息_测试数据.xlsx", "项目主表", ["项目编号*", "项目名称*", "项目简称（选填）", "上级项目编号", "续签前项目编号", "项目起止时间", "省份", "市（区）", "细分业态", "汇总业态", "委托方式", "计费方式", "合同面积（㎡）", "合同金额（元）", "年化合同额（元）", "合同额具体信息", "服务期限", "合同状况", "首次服务日期", "年化合同收入（元）", "客户满意度（0-100）", "客户投诉（条）", "甲方名称", "项目地址", "项目负责人", "项目负责人工号*", "实际毛利率（%）", "服务状态", "备注"], [26, 18, 20, 22, 22, 24, 10, 10, 12, 12, 12, 12, 12, 14, 16, 16, 20, 18, 12, 14, 16, 16, 12, 28, 24, 14, 16, 14, 12, 20], [ ["XMSQ0199", "青浦区图书馆（2025期）", "青浦-区图书馆", "", "", "2025-02-28~2026-02-27", "上海", "青浦", "场馆", "公众物业", "多项单委", "包干", 16562, 3199128, 3199110.84, "当期合同额 3199110.84", "服务期一年（公开）", "已签", "2020-01-01", 3199110.84, "", "", "上海市青浦区图书馆", "青浦区青龙路60号", "张某某", "KWL2699006", "", "历史", "测试（上一期，续签链起点）"], ["XMSQ0102", "上海市青浦区图书馆", "青浦-区图书馆", "", "XMSQ0199", "2026-02-28~2027-02-27", "上海", "青浦", "场馆", "公众物业", "多项单委", "包干", 16562, 3199128, 3199110.84, "当期合同额 3199110.84", "服务期一年（公开）", "已签", "2020-01-01", 3199110.84, "", "", "上海市青浦区图书馆", "青浦区青龙路60号", "张某某", "KWL2699006", "", "服务中", "测试（当前期，续签自 2025期）"], ["XMSQ0108", "国家税务总局上海市青浦区税务局", "青浦-税务局", "", "", "2026-01-01~2026-12-31", "上海", "青浦", "政府机构", "机关办公楼", "全委", "包干", 38999, 7943908, 7943908, "当期合同额 7943908", "服务期一年（公开）", "已签", "2020-01-01", 7943908, "", "", "国家税务总局上海市青浦区税务局", "青浦区城中西路80号", "王某某", "KWL2699007", "", "服务中", "测试（父项目）"], ["XMSQ-5-1", "税务局本部", "税务局本部", "XMSQ0108", "", "2026-01-01~2026-12-31", "上海", "青浦", "政府机构", "机关办公楼", "", "包干", 8000, "", "", "", "", "", "2020-01-01", "", "", "", "国家税务总局上海市青浦区税务局", "青浦区城中西路80号", "赵某某", "KWL2699021", "", "服务中", "测试（子项目，无金蝶编码）"], ["XMSQ-5-2", "第十税务所", "第十税务所", "XMSQ0108", "", "2026-01-01~2026-12-31", "上海", "青浦", "政府机构", "机关办公楼", "多项单委", "包干", 5000, "", "", "", "", "", "2020-01-01", "", "", "", "国家税务总局上海市青浦区税务局", "青浦区徐泾镇盈港东路1838号", "赵某某", "KWL2699021", "", "服务中", "测试（子项目）"], ["XMSQ0146", "青浦区赵巷公园", "青浦-赵巷公园", "", "", "2026-01-01~2026-12-31", "上海", "青浦", "公园", "公众物业", "多项单委", "包干", 60000, 800000, 800000, "", "服务期一年", "已签", "2024-01-01", 800000, "", "", "青浦区赵巷镇人民政府", "青浦区赵巷公园", "周某某", "KWL2699008", "", "服务中", "测试"], ["XMSQ0130", "青浦区赵巷镇政府", "青浦-赵巷镇政府", "", "", "2026-01-01~2026-12-31", "上海", "青浦", "政府机构", "机关办公楼", "全委", "包干", 14350, 1435000, 1435000, "", "服务期一年", "已签", "2021-01-01", 1435000, "", "", "青浦区赵巷镇人民政府", "青浦区赵巷镇沙家埭路18号", "周某某", "KWL2699008", "", "服务中", "测试（董一青 1-2月）"], ["XMSQ01141", "青浦-北斗园区", "青浦-北斗园区", "", "", "2026-01-01~2026-12-31", "上海", "青浦", "园区", "商业办公", "全委", "包干", 50000, 3600000, 3600000, "", "服务期一年", "已签", "2024-01-01", 3600000, "", "", "北斗园区运营方", "青浦区北斗园区", "吴某某", "KWL2699023", "", "服务中", "测试（董一青 3-4月）"], ["XMSQ0131", "青浦区青东办", "青浦-青东办", "", "", "2026-01-01~2026-12-31", "上海", "青浦", "机关办公楼", "机关办公楼", "全委", "包干", 10000, 600000, 600000, "", "服务期一年", "已签", "2023-01-01", 600000, "", "", "青浦区机关事务管理局", "青浦区青东办", "郑某某", "KWL2699024", "", "服务中", "测试（徐娥英）"], ["XMSQ0153", "青浦工业园区大楼", "青浦-工业园区", "", "", "2026-01-01~2026-12-31", "上海", "青浦", "园区", "商业办公", "全委", "包干", 80000, 1800000, 1800000, "", "服务期一年", "已签", "2005-01-01", 1800000, "", "", "青浦工业园区管理委员会", "青浦工业园区", "孙某某", "KWL2699025", "", "服务中", "测试（设备台账项目）"], ["XMSQ0176", "上海市环境监测中心", "徐汇-环境监测中心", "", "", "2026-01-01~2026-12-31", "上海", "徐汇", "机关办公楼", "机关办公楼", "全委", "包干", 30000, 1500000, 1500000, "", "服务期一年", "已签", "2014-01-01", 1500000, "", "", "上海市环境监测中心", "上海市环境监测中心", "钱某某", "KWL2699026", "", "服务中", "测试（设备台账项目）"], ["XMSQ9031", "上海市青浦区档案馆", "青浦-档案馆", "", "", "2026-01-01~2026-12-31", "上海", "青浦", "政府机构", "机关办公楼", "全委", "包干", 12000, 900000, 900000, "", "服务期一年", "已签", "2018-01-01", 900000, "", "", "上海市青浦区档案馆", "青浦区档案馆", "刘某某", "KWL2699105", "", "服务中", "测试（新增项目）"], ["XMSQ9032", "青浦区民防城运中心", "青浦民防城运", "", "", "2026-01-01~2026-12-31", "上海", "青浦", "机关办公楼", "机关办公楼", "全委", "包干", 8000, 600000, 600000, "", "服务期一年", "已签", "2019-01-01", 600000, "", "", "青浦区民防办公室", "青浦区民防城运中心", "何某某", "KWL2699106", "", "服务中", "测试（新增项目）"], ["XMSQ9033", "上海大众工业学校", "嘉定-大众工业学校", "", "", "2026-01-01~2026-12-31", "上海", "嘉定", "学校", "学校", "全委", "包干", 40000, 2200000, 2200000, "", "服务期一年", "已签", "2019-09-01", 2200000, "", "", "上海大众工业学校", "嘉定区环城路2290号", "罗某某", "KWL2699107", "", "服务中", "测试（新增项目）"], ["XMSQ9034", "上海市徐汇区群众艺术馆", "徐汇-群艺馆", "", "", "2026-01-01~2026-12-31", "上海", "徐汇", "场馆", "公众物业", "全委", "包干", 18000, 1200000, 1200000, "", "服务期一年", "已签", "2017-01-01", 1200000, "", "", "上海市徐汇区文化和旅游局", "徐汇区群艺馆", "彭某某", "KWL2699108", "", "服务中", "测试（新增项目）"], ["XMSQ9035", "上海市松江区公安局", "松江-公安局", "", "", "2026-01-01~2026-12-31", "上海", "松江", "政府机构", "机关办公楼", "全委", "包干", 25000, 1800000, 1800000, "", "服务期一年", "已签", "2016-01-01", 1800000, "", "", "上海市松江区公安局", "松江区公安局", "田某某", "KWL2699109", "", "服务中", "测试（新增项目）"], ], ["覆盖续签链（2025期→当前期）与项目从属（税务局→税务局本部/第十税务所）。"], ) _make_test_file( "项目月度财务_测试数据.xlsx", "项目财务明细", ["项目编号*", "项目名称（选填）", "年月（YYYY-MM）*", "开票金额（元）", "收款金额（元）", "口径说明", "财务经办人工号*", "财务经办人（选填）", "备注"], [20, 26, 16, 16, 16, 30, 16, 14, 20], [ ["XMSQ0102", "上海市青浦区图书馆", "2026-01", 266594, 266594, "测试口径", "KWL2699012", "财务员甲", "测试"], ["XMSQ0102", "上海市青浦区图书馆", "2026-02", 266594, 266594, "测试口径", "KWL2699012", "财务员甲", "测试"], ["XMSQ0102", "上海市青浦区图书馆", "2026-03", 266594, 266594, "测试口径", "KWL2699012", "财务员甲", "测试"], ["XMSQ0153", "青浦工业园区大楼", "2026-01", 150000, 140000, "测试口径", "KWL2699012", "财务员甲", "测试"], ["XMSQ0176", "上海市环境监测中心", "2026-02", 125000, 125000, "测试口径", "KWL2699012", "财务员甲", "测试"], ["XMSQ9031", "上海市青浦区档案馆", "2026-01", 75000, 75000, "测试口径", "KWL2699012", "财务员甲", "测试"], ["XMSQ9032", "青浦区民防城运中心", "2026-02", 50000, 48000, "测试口径", "KWL2699012", "财务员甲", "测试"], ["XMSQ9033", "上海大众工业学校", "2026-03", 183333, 180000, "测试口径", "KWL2699012", "财务员甲", "测试"], ["XMSQ9034", "上海市徐汇区群众艺术馆", "2026-01", 100000, 100000, "测试口径", "KWL2699012", "财务员甲", "测试"], ], ["测试项目月度财务（开票/收款）。"], ) _make_test_file( "投标记录_测试数据.xlsx", "投标记录", ["项目编号*", "项目名称（选填）", "年份*", "投标类型", "中标结果", "中标日期", "投标金额（元）", "文档路径", "投标经办人工号*", "投标经办人（选填）", "备注"], [20, 26, 10, 14, 12, 14, 16, 50, 16, 14, 20], [ ["XMSQ0102", "上海市青浦区图书馆", 2024, "中标通知书", "中标", "2024-02-01", 3199128, "1-青浦区域/102-青浦区图书馆", "KWL2699010", "陈某某", "测试"], ["XMSQ0102", "上海市青浦区图书馆", 2026, "中标通知书", "中标", "2026-02-01", 3199128, "1-青浦区域/102-青浦区图书馆", "KWL2699010", "陈某某", "测试"], ["XMSQ0108", "国家税务总局上海市青浦区税务局", 2026, "中标通知书", "中标", "2025-12-20", 7943908, "1-青浦区域/108-青浦区税务局", "KWL2699010", "陈某某", "测试"], ["XMSQ9031", "上海市青浦区档案馆", 2026, "中标通知书", "中标", "2026-01-10", 900000, "1-青浦区域/青浦区档案馆", "KWL2699010", "陈某某", "测试"], ["XMSQ9034", "上海市徐汇区群众艺术馆", 2025, "中标公告", "中标", "2025-06-15", 1200000, "4-中心区域/徐汇群艺馆", "KWL2699010", "陈某某", "测试"], ["XMSQ9033", "上海大众工业学校", 2026, "投标文件", "未知", "", 2200000, "3-嘉定区域/大众工业学校", "KWL2699010", "陈某某", "测试"], ], ["测试投标/中标事件记录。"], ) _make_test_file( "人员信息_测试数据.xlsx", "人员主表", ["姓名*", "工号*", "组织名称", "岗位名称*", "当前服务项目编号（选填）", "服务起始年月", "服务结束年月", "职级", "员工层级", "所属组织名称", "入职日期", "离职日期", "司龄", "性别", "出生日期", "政治面貌", "联系电话", "备注"], [14, 16, 14, 20, 22, 14, 14, 10, 16, 14, 14, 14, 10, 8, 14, 12, 16, 20], [ ["陆雷雷", "KWL2306998", "保安部", "保安_领班", "XMSQ0146", "2026-01", "2026-12", "P3", "一线员工", "保安部", "2025-01-01", "", "1.5年", "男", "1980-05-12", "汉族", "", "真实员工"], ["王惠云", "KWL2307010", "保安部", "保安_普保", "XMSQ0146", "2026-01", "2026-12", "P2", "一线员工", "保安部", "2024-06-01", "", "2年", "男", "1982-03-08", "汉族", "", "真实员工"], ["董一青", "KWL2307024", "管理处", "项目经理", "XMSQ01141", "2026-01", "2026-04", "P5", "项目管理人员", "管理处", "2023-02-01", "", "3年", "男", "1978-11-20", "汉族", "", "真实员工（1-2月赵巷镇政府，3-4月北斗园区，由考勤明细体现）"], ["徐娥英", "KWL2309539", "保洁部", "保洁_内场", "XMSQ0131", "2026-01", "2026-12", "P1", "一线员工", "保洁部", "2020-05-01", "", "6年", "女", "1975-09-15", "汉族", "", "真实员工"], ["测试甲", "KWL2699001", "会务部", "会务_接待", "XMSQ0102", "2026-01", "2026-12", "P3", "一线员工", "会务部", "2024-03-01", "", "2年", "女", "1990-01-01", "汉族", "", "构造员工"], ["测试乙", "KWL2699002", "保安部", "保安_领班", "XMSQ0102", "2026-01", "2026-12", "P2", "一线员工", "保安部", "2023-08-01", "", "2.5年", "男", "1985-06-15", "汉族", "", "构造员工"], ["测试丙", "KWL2699003", "工程部", "工程_强电", "XMSQ0153", "2026-01", "2026-12", "P3", "一线员工", "工程部", "2022-04-01", "", "4年", "男", "1988-12-01", "汉族", "", "构造员工"], ["测试丁", "KWL2699004", "保洁部", "保洁_外场", "XMSQ0176", "2026-01", "2026-12", "P1", "一线员工", "保洁部", "2023-01-10", "", "3年", "女", "1979-07-07", "汉族", "", "构造员工"], ["陈志强", "KWL2699101", "保安部", "保安_普保", "XMSQ0146", "2026-01", "2026-12", "P2", "一线员工", "保安部", "2023-05-20", "", "3年", "男", "1986-04-15", "汉族", "", "构造员工"], ["林芳", "KWL2699102", "保洁部", "保洁_外场", "XMSQ0146", "2026-01", "2026-12", "P1", "一线员工", "保洁部", "2022-08-01", "", "4年", "女", "1981-10-02", "汉族", "", "构造员工"], ["黄伟", "KWL2699103", "工程部", "工程_水电", "XMSQ0153", "2026-01", "2026-12", "P3", "一线员工", "工程部", "2021-03-15", "", "5年", "男", "1984-02-28", "汉族", "", "构造员工"], ["杨丽", "KWL2699104", "会务部", "会务_领班", "XMSQ0102", "2026-01", "2026-12", "P3", "一线员工", "会务部", "2022-11-01", "", "3.5年", "女", "1990-08-12", "汉族", "", "构造员工"], ["张三", "KWL2699005", "管理处", "品质专员", "XMSQ0102", "2026-01", "2026-12", "P3", "职能人员", "管理处", "2021-06-01", "", "5年", "男", "1983-03-03", "汉族", "", "构造员工（检查人）"], ["李四", "KWL2699202", "管理处", "品质专员", "XMSQ0108", "2026-01", "2026-12", "P3", "职能人员", "管理处", "2021-09-01", "", "5年", "男", "1982-07-19", "汉族", "", "构造员工（检查人）"], ["王五", "KWL2699203", "管理处", "品质专员", "XMSQ0176", "2026-01", "2026-12", "P3", "职能人员", "管理处", "2020-12-01", "", "5.5年", "男", "1984-11-11", "汉族", "", "构造员工（检查人）"], ["陈某某", "KWL2699010", "管理处", "投标专员", "", "2026-01", "2026-12", "P3", "职能人员", "管理处", "2021-04-01", "", "5年", "男", "1986-05-05", "汉族", "", "构造员工（投标经办人）"], ["采购员甲", "KWL2699011", "管理处", "采购专员", "", "2026-01", "2026-12", "P3", "职能人员", "管理处", "2020-03-01", "", "6年", "女", "1985-09-09", "汉族", "", "构造员工（采购经办人）"], ["财务员甲", "KWL2699012", "管理处", "财务专员", "", "2026-01", "2026-12", "P3", "职能人员", "管理处", "2019-08-01", "", "7年", "女", "1981-12-12", "汉族", "", "构造员工（财务经办人）"], ["设备员甲", "KWL2699013", "工程部", "设备管理员", "", "2026-01", "2026-12", "P3", "一线员工", "工程部", "2020-05-01", "", "6年", "男", "1983-01-25", "汉族", "", "构造员工（设备责任人）"], ["王片区", "KWL2699020", "管理处", "片区负责人", "", "2026-01", "2026-12", "P4", "项目管理人员", "管理处", "2018-01-01", "", "8年", "男", "1978-06-06", "汉族", "", "构造员工（片区负责人）"], ["李片区", "KWL2699022", "管理处", "片区负责人", "", "2026-01", "2026-12", "P4", "项目管理人员", "管理处", "2019-02-01", "", "7年", "女", "1980-04-04", "汉族", "", "构造员工（片区负责人）"], ["张某某", "KWL2699006", "管理处", "项目经理", "XMSQ0102", "2026-01", "2026-12", "P5", "项目管理人员", "管理处", "2019-05-01", "", "7年", "男", "1979-08-08", "汉族", "", "构造员工（项目负责人）"], ["王某某", "KWL2699007", "管理处", "项目经理", "XMSQ0108", "2026-01", "2026-12", "P5", "项目管理人员", "管理处", "2018-10-01", "", "8年", "男", "1977-02-02", "汉族", "", "构造员工（项目负责人）"], ["赵某某", "KWL2699021", "管理处", "项目经理", "XMSQ-5-1", "2026-01", "2026-12", "P4", "项目管理人员", "管理处", "2020-07-01", "", "6年", "男", "1981-10-10", "汉族", "", "构造员工（项目负责人）"], ["周某某", "KWL2699008", "管理处", "项目经理", "XMSQ0146", "2026-01", "2026-12", "P4", "项目管理人员", "管理处", "2019-11-01", "", "7年", "男", "1980-12-12", "汉族", "", "构造员工（项目负责人）"], ["吴某某", "KWL2699023", "管理处", "项目经理", "XMSQ01141", "2026-01", "2026-12", "P4", "项目管理人员", "管理处", "2020-01-01", "", "6年", "男", "1979-09-09", "汉族", "", "构造员工（项目负责人）"], ["郑某某", "KWL2699024", "管理处", "项目经理", "XMSQ0131", "2026-01", "2026-12", "P4", "项目管理人员", "管理处", "2020-02-01", "", "6年", "女", "1982-05-05", "汉族", "", "构造员工（项目负责人）"], ["孙某某", "KWL2699025", "管理处", "项目经理", "XMSQ0153", "2026-01", "2026-12", "P4", "项目管理人员", "管理处", "2019-03-01", "", "7年", "男", "1978-07-07", "汉族", "", "构造员工（项目负责人）"], ["钱某某", "KWL2699026", "管理处", "项目经理", "XMSQ0176", "2026-01", "2026-12", "P4", "项目管理人员", "管理处", "2019-04-01", "", "7年", "女", "1981-01-01", "汉族", "", "构造员工（项目负责人）"], ["刘某某", "KWL2699105", "管理处", "项目经理", "XMSQ9031", "2026-01", "2026-12", "P4", "项目管理人员", "管理处", "2020-06-01", "", "6年", "男", "1979-06-06", "汉族", "", "构造员工（项目负责人）"], ["何某某", "KWL2699106", "管理处", "项目经理", "XMSQ9032", "2026-01", "2026-12", "P4", "项目管理人员", "管理处", "2020-08-01", "", "6年", "女", "1983-03-03", "汉族", "", "构造员工（项目负责人）"], ["罗某某", "KWL2699107", "管理处", "项目经理", "XMSQ9033", "2026-01", "2026-12", "P4", "项目管理人员", "管理处", "2019-12-01", "", "6.5年", "男", "1978-11-11", "汉族", "", "构造员工（项目负责人）"], ["彭某某", "KWL2699108", "管理处", "项目经理", "XMSQ9034", "2026-01", "2026-12", "P4", "项目管理人员", "管理处", "2020-09-01", "", "6年", "女", "1980-10-10", "汉族", "", "构造员工（项目负责人）"], ["田某某", "KWL2699109", "管理处", "项目经理", "XMSQ9035", "2026-01", "2026-12", "P4", "项目管理人员", "管理处", "2019-01-01", "", "7.5年", "男", "1977-12-12", "汉族", "", "构造员工（项目负责人）"], ], ["服务起始/结束年月用于直接生成 服务于 边；董一青的跨项目流动由考勤明细体现。"], ) def att_row(emp_id, name, proj_code, proj_name, ym, day_code, days, work, ot, hol, meal, ot_fee, hol_fee, duty, note): d = [day_code if i <= days else "" for i in range(1, 32)] return [name, emp_id, proj_code, proj_name, ym, *d, work, ot, hol, meal, ot_fee, hol_fee, duty, "", note] att_headers = ["姓名*", "工号*", "项目编号*", "项目名称（选填）", "年月（YYYY-MM）*", *days, "上班天数", "平时加班小时", "国定加班小时", "餐费补助金额（元）", "加班超时费金额（元）", "国定加班费金额（元）", "值班费金额（元）", "税后工资（元）", "备注"] att_widths = [14, 16, 20, 26, 16, *dw, 10, 12, 12, 16, 18, 18, 16, 16, 20] att_rows = [ att_row("KWL2306998", "陆雷雷", "XMSQ0146", "青浦区赵巷公园", "2026-01", "日1", 22, 22, 74, 0, 308, 1748.25, 0, 4896.25, "测试"), att_row("KWL2306998", "陆雷雷", "XMSQ0146", "青浦区赵巷公园", "2026-02", "日1", 20, 20, 60, 0, 280, 1417.5, 0, 4620, "测试"), att_row("KWL2306998", "陆雷雷", "XMSQ0146", "青浦区赵巷公园", "2026-03", "日1", 21, 21, 70, 0, 294, 1653.75, 0, 4725, "测试"), att_row("KWL2307010", "王惠云", "XMSQ0146", "青浦区赵巷公园", "2026-01", "日", 21, 21, 57, 11, 147, 1346.625, 519.75, 0, "测试（日夜班示例）"), att_row("KWL2307024", "董一青", "XMSQ0130", "青浦区赵巷镇政府", "2026-01", "常", 21, 21, 0, 0, 147, 0, 0, 0, "测试（1月赵巷镇政府）"), att_row("KWL2307024", "董一青", "XMSQ0130", "青浦区赵巷镇政府", "2026-02", "常", 20, 20, 8, 0, 140, 189, 0, 0, "测试（2月赵巷镇政府）"), att_row("KWL2307024", "董一青", "XMSQ01141", "青浦-北斗园区", "2026-03", "常", 21, 21, 0, 0, 147, 0, 0, 0, "测试（3月北斗园区）"), att_row("KWL2307024", "董一青", "XMSQ01141", "青浦-北斗园区", "2026-04", "常", 21, 21, 5, 0, 147, 118.125, 0, 0, "测试（4月北斗园区）"), att_row("KWL2309539", "徐娥英", "XMSQ0131", "青浦区青东办", "2026-01", "常", 21, 21, 0, 0, 147, 0, 0, 0, "测试"), att_row("KWL2309539", "徐娥英", "XMSQ0131", "青浦区青东办", "2026-02", "常", 20, 20, 0, 0, 140, 0, 0, 0, "测试"), att_row("KWL2309539", "徐娥英", "XMSQ0131", "青浦区青东办", "2026-03", "常", 21, 21, 0, 0, 147, 0, 0, 0, "测试"), att_row("KWL2699001", "测试甲", "XMSQ0102", "上海市青浦区图书馆", "2026-01", "常", 21, 21, 0, 0, 147, 0, 0, 0, "测试"), att_row("KWL2699001", "测试甲", "XMSQ0102", "上海市青浦区图书馆", "2026-02", "常", 20, 20, 4, 0, 140, 94.5, 0, 0, "测试"), att_row("KWL2699001", "测试甲", "XMSQ0102", "上海市青浦区图书馆", "2026-03", "常", 21, 21, 6, 0, 147, 150.75, 0, 0, "测试"), att_row("KWL2699002", "测试乙", "XMSQ0102", "上海市青浦区图书馆", "2026-01", "常", 21, 21, 0, 0, 147, 0, 0, 0, "测试"), att_row("KWL2699002", "测试乙", "XMSQ0102", "上海市青浦区图书馆", "2026-02", "常", 20, 20, 0, 0, 140, 0, 0, 0, "测试"), att_row("KWL2699003", "测试丙", "XMSQ0153", "青浦工业园区大楼", "2026-02", "常", 20, 20, 10, 0, 140, 236.25, 0, 0, "测试"), att_row("KWL2699004", "测试丁", "XMSQ0176", "上海市环境监测中心", "2026-02", "常", 20, 20, 0, 0, 140, 0, 0, 0, "测试"), att_row("KWL2699002", "测试乙", "XMSQ0102", "上海市青浦区图书馆", "2026-03", "常", 21, 21, 0, 0, 147, 0, 0, 0, "测试"), att_row("KWL2699005", "张三", "XMSQ0102", "上海市青浦区图书馆", "2026-03", "常", 21, 21, 0, 0, 147, 0, 0, 0, "测试"), att_row("KWL2699006", "张某某", "XMSQ0102", "上海市青浦区图书馆", "2026-03", "常", 21, 21, 0, 0, 147, 0, 0, 0, "测试"), att_row("KWL2699101", "陈志强", "XMSQ0146", "青浦区赵巷公园", "2026-01", "日", 21, 21, 0, 0, 147, 0, 0, 0, "测试"), att_row("KWL2699101", "陈志强", "XMSQ0146", "青浦区赵巷公园", "2026-02", "日", 20, 20, 0, 0, 140, 0, 0, 0, "测试"), att_row("KWL2699101", "陈志强", "XMSQ0146", "青浦区赵巷公园", "2026-03", "日", 21, 21, 0, 0, 147, 0, 0, 0, "测试"), att_row("KWL2699102", "林芳", "XMSQ0146", "青浦区赵巷公园", "2026-01", "常", 21, 21, 0, 0, 147, 0, 0, 0, "测试"), att_row("KWL2699102", "林芳", "XMSQ0146", "青浦区赵巷公园", "2026-02", "常", 20, 20, 0, 0, 140, 0, 0, 0, "测试"), att_row("KWL2699102", "林芳", "XMSQ0146", "青浦区赵巷公园", "2026-03", "常", 21, 21, 0, 0, 147, 0, 0, 0, "测试"), att_row("KWL2699103", "黄伟", "XMSQ0153", "青浦工业园区大楼", "2026-01", "常", 21, 21, 0, 0, 147, 0, 0, 0, "测试"), att_row("KWL2699103", "黄伟", "XMSQ0153", "青浦工业园区大楼", "2026-02", "常", 20, 20, 0, 0, 140, 0, 0, 0, "测试"), att_row("KWL2699103", "黄伟", "XMSQ0153", "青浦工业园区大楼", "2026-03", "常", 21, 21, 0, 0, 147, 0, 0, 0, "测试"), att_row("KWL2699104", "杨丽", "XMSQ0102", "上海市青浦区图书馆", "2026-01", "常", 21, 21, 0, 0, 147, 0, 0, 0, "测试"), att_row("KWL2699104", "杨丽", "XMSQ0102", "上海市青浦区图书馆", "2026-02", "常", 20, 20, 0, 0, 140, 0, 0, 0, "测试"), att_row("KWL2699104", "杨丽", "XMSQ0102", "上海市青浦区图书馆", "2026-03", "常", 21, 21, 0, 0, 147, 0, 0, 0, "测试"), ] _make_test_file( "考勤与人员财务_测试数据.xlsx", "考勤与人员财务", att_headers, att_widths, att_rows, ["18 条记录覆盖 2026-01~04，含跨项目流动（董一青）；每日状态为简化测试值。"], ) def shift_row(emp_id, name, proj_code, proj_name, ym, code, days): d = [code if i <= days else "OFF" for i in range(1, 32)] return [name, emp_id, proj_code, proj_name, ym, *d, "测试"] _make_test_file( "排班明细_测试数据.xlsx", "排班明细", ["姓名*", "工号*", "项目编号*", "项目名称（选填）", "年月（YYYY-MM）*", *days, "备注"], [14, 16, 20, 26, 16, *dw, 20], [ shift_row("KWL2306998", "陆雷雷", "XMSQ0146", "青浦区赵巷公园", "2026-01", "A003", 22), shift_row("KWL2307024", "董一青", "XMSQ0130", "青浦区赵巷镇政府", "2026-01", "SQ-C13", 21), shift_row("KWL2307024", "董一青", "XMSQ0130", "青浦区赵巷镇政府", "2026-02", "SQ-C13", 20), shift_row("KWL2307024", "董一青", "XMSQ01141", "青浦-北斗园区", "2026-03", "SQ-C13", 21), shift_row("KWL2309539", "徐娥英", "XMSQ0131", "青浦区青东办", "2026-01", "A003", 21), shift_row("KWL2699001", "测试甲", "XMSQ0102", "上海市青浦区图书馆", "2026-03", "A003", 21), shift_row("KWL2699002", "测试乙", "XMSQ0102", "上海市青浦区图书馆", "2026-03", "A003", 21), shift_row("KWL2699005", "张三", "XMSQ0102", "上海市青浦区图书馆", "2026-03", "A003", 21), shift_row("KWL2699006", "张某某", "XMSQ0102", "上海市青浦区图书馆", "2026-03", "A003", 21), shift_row("KWL2699101", "陈志强", "XMSQ0146", "青浦区赵巷公园", "2026-01", "A003", 21), shift_row("KWL2699102", "林芳", "XMSQ0146", "青浦区赵巷公园", "2026-02", "A003", 20), shift_row("KWL2699103", "黄伟", "XMSQ0153", "青浦工业园区大楼", "2026-03", "A003", 21), shift_row("KWL2699104", "杨丽", "XMSQ0102", "上海市青浦区图书馆", "2026-01", "SQ-C13", 21), ], ["排班测试行，班次代码常用值见模板说明。"], ) _make_test_file( "岗位编制_测试数据.xlsx", "岗位编制", ["项目编号*", "项目名称（选填）", "岗位", "预算编制", "项目编制", "标准工时人数", "在岗人数", "备注"], [20, 26, 20, 10, 10, 14, 12, 20], [ ["XMSQ0102", "上海市青浦区图书馆", "保安_领班", 1, 1, 1, 1, "测试"], ["XMSQ0102", "上海市青浦区图书馆", "保洁_主管", 2, 2, 2, 2, "测试"], ["XMSQ0102", "上海市青浦区图书馆", "会务_接待", 1, 1, 1, 1, "测试"], ["XMSQ0146", "青浦区赵巷公园", "保安_领班", 1, 1, 1, 1, "测试"], ["XMSQ9031", "上海市青浦区档案馆", "保安_领班", 1, 1, 1, 1, "测试"], ["XMSQ9032", "青浦区民防城运中心", "保洁_主管", 2, 2, 2, 2, "测试"], ["XMSQ9033", "上海大众工业学校", "保安_主管", 2, 2, 2, 2, "测试"], ["XMSQ0153", "青浦工业园区大楼", "工程_强电", 3, 3, 3, 2, "测试（在岗缺编示例）"], ], ["岗位编制测试行。"], ) _make_test_file( "设备信息_测试数据.xlsx", "设备主表", ["项目编号*", "项目名称（选填）", "设备编号*", "设备类型", "名称", "规格型号参数", "制造厂商", "安装位置", "出厂日期", "启用日期", "入项目日期", "退项目日期", "出厂编号", "完好状况", "原值（元）", "设备责任人工号*", "设备责任人（选填）", "备注"], [20, 26, 20, 12, 16, 26, 24, 22, 14, 14, 14, 14, 16, 14, 14, 16, 14, 20], [ ["XMSQ0153", "青浦工业园区大楼", "XF-PLB-01-B1-01", "消防", "喷淋泵", "200T/H 110M 55KW", "上海熊猫", "主楼地下水泵房", "2004-12", "2005-09", "2005-09", "", "10379", "使用正常", "", "KWL2699013", "设备员甲", "真实台账"], ["XMSQ0153", "青浦工业园区大楼", "XF-XFB-01-B1-01", "消防", "消防泵", "200T/H 110M 55KW", "上海熊猫", "主楼地下水泵房", "2004-12", "2005-09", "2005-09", "", "10478", "使用正常", "", "KWL2699013", "设备员甲", "真实台账"], ["XMSQ0153", "青浦工业园区大楼", "DT-KT-01-01", "电梯", "客梯", "OTIS3200 17站17门 1000KG", "中澳集团", "主楼中间", "2006-08", "", "2006-09", "", "07NA5711", "使用正常", "", "KWL2699013", "设备员甲", "真实台账"], ["XMSQ0153", "青浦工业园区大楼", "KT-LSJZ-01-B1-01", "空调", "冷水机组", "30HXC350A", "开利", "主楼地下室", "2005-03", "2005-09", "2005-09", "", "H350E1240", "使用正常", "", "KWL2699013", "设备员甲", "真实台账"], ["XMSQ0176", "上海市环境监测中心", "KT-DJ-01-13-1", "空调", "大金空调", "RUXYQ16AB", "上海大金", "1号13楼楼顶", "2013-07", "2014-03", "2014-03", "", "", "使用正常", "", "KWL2699013", "设备员甲", "真实台账"], ["XMSQ0176", "上海市环境监测中心", "DT-KT-01-01-01", "电梯", "电梯", "HGP 13层", "日立电梯", "行政楼", "2010-12", "2014-06", "2014-06", "", "09G035218", "使用正常", "", "KWL2699013", "设备员甲", "真实台账"], ["XMSQ0153", "青浦工业园区大楼", "GPS-RSB-01-B1-01", "给排水", "热水泵", "6T/H 40M 1.1KW", "凯利", "主楼地下泵房", "2004-05", "2005-09", "2005-09", "", "10801", "使用正常", "", "KWL2699013", "设备员甲", "真实台账"], ["XMSQ0176", "上海市环境监测中心", "XF-XFB-1-B1F-01", "消防", "消防泵", "XBD8/40-125L", "上海双解", "地下水泵房", "2013-07", "2014-06", "2014-06", "", "1307131", "使用正常", "", "KWL2699013", "设备员甲", "真实台账"], ], ["设备为真实台账片段（工业园区/环境监测中心）。"], ) _make_test_file( "采购与维保_测试数据.xlsx", "采购与维保", ["记录类型*", "项目编号*", "项目名称（选填）", "供应商名称", "供应商编号（选填）", "类别", "名称/合同名称", "数量", "单位", "单价（元）", "金额（元）", "合同年限", "月份/日期", "当前状态", "经办人工号*", "经办人（选填）", "备注"], [14, 20, 26, 24, 14, 16, 24, 10, 8, 12, 14, 10, 14, 12, 16, 14, 20], [ ["供应商", "", "", "上海熊猫", "SUP0001", "设备厂家", "", "", "", "", "", "", "", "", "KWL2699011", "采购员甲", "测试"], ["供应商", "", "", "上海大金", "SUP0002", "设备厂家", "", "", "", "", "", "", "", "", "KWL2699011", "采购员甲", "测试"], ["供应商", "", "", "开利", "SUP0003", "设备厂家", "", "", "", "", "", "", "", "", "KWL2699011", "采购员甲", "测试"], ["供应商", "", "", "日立电梯", "SUP0004", "维保单位", "", "", "", "", "", "", "", "", "KWL2699011", "采购员甲", "测试"], ["供应商", "", "", "洁力清洁用品", "SUP0005", "耗材供应商", "", "", "", "", "", "", "", "", "KWL2699011", "采购员甲", "测试"], ["维保合同", "XMSQ0153", "青浦工业园区大楼", "上海熊猫", "SUP0001", "电梯维保", "电梯维保合同", "", "", "", 125000, 3, "2026-01", "", "KWL2699011", "采购员甲", "测试"], ["维保合同", "XMSQ0176", "上海市环境监测中心", "上海大金", "SUP0002", "空调维保", "空调维保合同", "", "", "", 83000, 2, "2026-01", "", "KWL2699011", "采购员甲", "测试"], ["耗材采购", "XMSQ0102", "上海市青浦区图书馆", "洁力清洁用品", "SUP0005", "保洁耗材", "垃圾袋", 500, "卷", 2.5, 1250, "", "2026-02", "", "KWL2699011", "采购员甲", "测试"], ["耗材采购", "XMSQ0153", "青浦工业园区大楼", "洁力清洁用品", "SUP0005", "工程耗材", "灯泡", 100, "个", 8, 800, "", "2026-02", "", "KWL2699011", "采购员甲", "测试"], ["固定资产", "XMSQ0102", "上海市青浦区图书馆", "", "", "低值易耗", "塑封机", 1, "台", "", 159, "", "2025-02-05", "在用", "KWL2699011", "采购员甲", "测试"], ["固定资产", "XMSQ0102", "上海市青浦区图书馆", "", "", "低值易耗", "一米栏", 30, "根", "", 4200, "", "2025-03-12", "在用", "KWL2699011", "采购员甲", "测试"], ["维保合同", "XMSQ9031", "上海市青浦区档案馆", "开利", "SUP0003", "消防维保", "消防维保合同", "", "", "", 60000, 2, "2026-01", "", "KWL2699011", "采购员甲", "测试"], ["耗材采购", "XMSQ9033", "上海大众工业学校", "洁力清洁用品", "SUP0005", "办公耗材", "打印纸", 50, "箱", 120, 6000, "", "2026-03", "", "KWL2699011", "采购员甲", "测试"], ["固定资产", "XMSQ0153", "青浦工业园区大楼", "", "", "办公设备", "打印机", 2, "台", "", 3000, "", "2025-08-01", "在用", "KWL2699011", "采购员甲", "测试"], ], ["一张表四类记录（供应商/维保合同/耗材采购/固定资产），按记录类型填对应列。"], ) _make_test_file( "检查记录_测试数据.xlsx", "检查记录", ["项目编号*", "项目名称（选填）", "检查类型", "检查日期", "检查得分（0-100）", "问题描述", "整改情况", "整改完成日期", "检查人工号*", "检查人（选填）", "备注"], [20, 26, 14, 14, 16, 40, 30, 14, 16, 12, 20], [ ["XMSQ0102", "上海市青浦区图书馆", "品质检查", "2026-03-15", 92, "大厅地面清洁不及时", "已整改", "2026-03-20", "KWL2699005", "张三", "测试"], ["XMSQ0108", "国家税务总局上海市青浦区税务局", "安全检查", "2026-02-10", 88, "消防通道堆放杂物", "已清理", "2026-02-12", "KWL2699202", "李四", "测试"], ["XMSQ0176", "上海市环境监测中心", "消防检查", "2026-01-20", 95, "", "", "", "KWL2699203", "王五", "测试"], ["XMSQ9031", "上海市青浦区档案馆", "安全检查", "2026-02-18", 90, "档案库房灭火器压力不足", "已更换", "2026-02-20", "KWL2699005", "张三", "测试"], ["XMSQ9033", "上海大众工业学校", "消防检查", "2026-01-25", 93, "教学楼消防通道堆放杂物", "已清理", "2026-01-27", "KWL2699202", "李四", "测试"], ["XMSQ0146", "青浦区赵巷公园", "品质检查", "2026-04-10", 91, "公厕异味", "已处理", "2026-04-12", "KWL2699203", "王五", "测试"], ], ["测试品质/安全/消防检查记录。"], ) _make_test_file( "片区信息_测试数据.xlsx", "片区主表", ["片区编号*", "片区名称*", "片区负责人工号*", "片区负责人（选填）", "备注"], [16, 20, 16, 14, 20], [ ["PQ-001", "青东片区", "KWL2699022", "李片区", "测试"], ["PQ-002", "青中1区", "KWL2699020", "王片区", "测试"], ["PQ-003", "青中2区", "KWL2699020", "王片区", "测试"], ["PQ-004", "中心区域", "KWL2699020", "王片区", "测试"], ["PQ-005", "嘉定区域", "KWL2699020", "王片区", "测试"], ["PQ-006", "松江区域", "KWL2699020", "王片区", "测试"], ], ["片区主数据测试行，片区负责人为人员节点关联。"], ) _make_test_file( "项目片区关系_测试数据.xlsx", "项目片区关系", ["项目编号*", "项目名称（选填）", "片区编号*", "片区名称（选填）", "备注"], [20, 26, 16, 20, 20], [ ["XMSQ0102", "上海市青浦区图书馆", "PQ-003", "青中2区", "测试"], ["XMSQ0108", "国家税务总局上海市青浦区税务局", "PQ-002", "青中1区", "测试"], ["XMSQ-5-1", "税务局本部", "PQ-002", "青中1区", "测试"], ["XMSQ-5-2", "第十税务所", "PQ-001", "青东片区", "测试"], ["XMSQ-5-2", "第十税务所", "PQ-002", "青中1区", "测试（一个项目多片区示例）"], ["XMSQ0146", "青浦区赵巷公园", "PQ-001", "青东片区", "测试"], ["XMSQ0130", "青浦区赵巷镇政府", "PQ-001", "青东片区", "测试"], ["XMSQ01141", "青浦-北斗园区", "PQ-001", "青东片区", "测试"], ["XMSQ0131", "青浦区青东办", "PQ-001", "青东片区", "测试"], ["XMSQ0153", "青浦工业园区大楼", "PQ-002", "青中1区", "测试"], ["XMSQ0176", "上海市环境监测中心", "PQ-004", "中心区域", "测试"], ["XMSQ9031", "上海市青浦区档案馆", "PQ-002", "青中1区", "测试"], ["XMSQ9032", "青浦区民防城运中心", "PQ-001", "青东片区", "测试"], ["XMSQ9033", "上海大众工业学校", "PQ-005", "嘉定区域", "测试"], ["XMSQ9034", "上海市徐汇区群众艺术馆", "PQ-004", "中心区域", "测试"], ["XMSQ9035", "上海市松江区公安局", "PQ-006", "松江区域", "测试"], ], ["项目-片区 M:N 关系测试行（第十税务所属于两个片区作示例）。"], ) _make_test_file( "人员证书_测试数据.xlsx", "人员证书", ["工号*", "姓名（选填）", "专业类别", "证书类别", "证书名称*", "备注"], [16, 14, 14, 14, 32, 20], [ ["KWL2306998", "陆雷雷", "安防", "从业资格证", "保安员（三级）", "测试"], ["KWL2307010", "王惠云", "安防", "从业资格证", "保安员（五级）", "测试"], ["KWL2699103", "黄伟", "工程", "从业资格证", "低压电工作业", "测试"], ["KWL2699003", "测试丙", "工程", "从业资格证", "高压电工作业", "测试"], ["KWL2699003", "测试丙", "工程", "其他", "电梯维修证", "测试"], ["KWL2307024", "董一青", "综合管理", "职称证", "物业管理师", "测试"], ["KWL2699005", "张三", "综合管理", "从业资格证", "上物协物业经理证", "测试"], ["KWL2699005", "张三", "综合管理", "职称证", "物业管理师", "测试"], ["KWL2699104", "杨丽", "综合管理", "其他", "会务服务证", "测试"], ], ["人员证书测试行，覆盖安防/工程/综合管理及多证书人员（测试丙、张三）。"], ) print("测试数据已生成（与模板一一对应）：") for f in sorted(OUT_DIR.glob("*.xlsx")): print(f" {f.name}") if __name__ == "__main__": main()