#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
零依赖把「校准通知方案.json」转成真正的 xlsx
"""
import json
import csv
import io
import zipfile
import os
from pathlib import Path

# ---------- 1. 读 JSON -> CSV 内存流 ----------
json_file = Path("校准通知方案.json")
with json_file.open(encoding="utf-8") as f:
    data = json.load(f)
rows = data.get("rows", [])
if not rows:
    raise SystemExit("JSON 中未找到 rows 数组！")

csv_io = io.StringIO()
writer = csv.DictWriter(csv_io, fieldnames=rows[0].keys())
writer.writeheader()
writer.writerows(rows)
csv_bytes = csv_io.getvalue().encode('utf-8')   # UTF-8 带 BOM 兼容性更好

# ---------- 2. 组装最小合法 xlsx ----------
xlsx_file = json_file.with_suffix(".xlsx")
with zipfile.ZipFile(xlsx_file, 'w', compression=zipfile.ZIP_DEFLATED) as z:

    # 必须的目录结构
    z.writestr('_rels/.rels',
        '''<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
        <Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">
          <Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument" Target="xl/workbook.xml"/>
        </Relationships>''')

    z.writestr('xl/_rels/workbook.xml.rels',
        '''<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
        <Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">
          <Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Target="worksheets/sheet1.xml"/>
        </Relationships>''')

    z.writestr('[Content_Types].xml',
        '''<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
        <Types xmlns="http://schemas.openxmlformats.org/package/2006/content-types">
          <Default Extension="rels" ContentType="application/vnd.openxmlformats-package.relationships+xml"/>
          <Default Extension="xml" ContentType="application/xml"/>
          <Override PartName="/xl/workbook.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml"/>
          <Override PartName="/xl/worksheets/sheet1.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/>
        </Types>''')

    z.writestr('xl/workbook.xml',
        '''<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
        <workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
          <sheets>
            <sheet name="Sheet1" sheetId="1" r:id="rId1" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships"/>
          </sheets>
        </workbook>''')

    # 把 CSV 转成 <sheetData> … </sheetData>
    def escape(t):
        return str(t).replace("&", "&amp;").replace("<", "&lt;").replace(">", "&gt;").replace('"', "&quot;")

    lines = csv_bytes.decode('utf-8-sig').splitlines()
    reader = csv.reader(lines)
    rows_xml = []
    for r_idx, row in enumerate(reader, 1):
        cells = []
        for c_idx, value in enumerate(row, 1):
            col_letter = csv.excel().fieldnames[c_idx-1] if hasattr(csv.excel(), 'fieldnames') else ""
            cells.append(f'<c r="{chr(64+c_idx)}{r_idx}" t="str"><v>{escape(value)}</v></c>')
        rows_xml.append(f'<row r="{r_idx}">{"".join(cells)}</row>')

    sheet_xml = f'''<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
    <worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
      <sheetData>{"".join(rows_xml)}</sheetData>
    </worksheet>'''

    z.writestr('xl/worksheets/sheet1.xml', sheet_xml)

print(f"转换完成！共 {len(rows)} 条记录 → {xlsx_file.resolve()}")
os.startfile(xlsx_file) if os.name == 'nt' else print("可直接双击打开 xlsx 文件")