Excel 财务报表模板填充

When to Use

Fill ERP financial data (资产负债表, 利润表, 现金流量表) into official tax bureau Excel templates (.xls/.xlsx) while preserving all formatting, formulas, colors, locked cells, and cell protection.

Triggered when user asks to:

• Fill in financial statements from ERP export
• Populate tax reporting templates
• Transfer data from ERP to official forms
• Fill Excel templates without breaking formulas or formatting

Core Workflow

Step 1 — Convert .xls to .xlsx first

If template is .xls (BIFF format), convert using Excel COM:

$excel = New-Object -ComObject Excel.Application
$excel.Visible = $false; $excel.DisplayAlerts = $false
$wb = $excel.Workbooks.Open("C:\path	o	emplate.xls")
$wb.SaveAs("C:\path	o	emplate.xlsx", 51)  # 51 = xlOpenXMLWorkbook
$wb.Close($false); $excel.Quit()

⚠️ Do NOT use xlutils for format-preserving operations on BIFF files.

Step 2 — Unpack xlsx

python scripts/xlsx_unpack.py input.xlsx /tmp/xlsx_work/

Step 3 — Read ERP data

Use xlrd to read ERP export files:

• 资产负债表: left cols 0-3 (资产), right cols 4-7 (负债)
• 利润表: col1=行次, col3=本期, col4=本年累计
• 现金流量表: col0=名称, col3=本期, col4=本年累计

See references/erp-row-maps.md for ERP row → template row mapping.

Step 4 — Edit XML directly

Key principle: Only modify \x3Cv> values, never touch \x3Cf> (formulas) or s= (styles).

Pattern for numeric cells:

\x3C!-- Before -->
\x3Cc r="D7" s="34">
  \x3Cv>0\x3C/v>
\x3C/c>

\x3C!-- After (replace only \x3Cv>) -->
\x3Cc r="D7" s="34">
  \x3Cv>1638.81\x3C/v>
\x3C/c>

Pattern for formula cells (update cached value only):

\x3C!-- Before -->
\x3Cc r="D21" s="40">
  \x3Cf>ROUND(D7+D8+D9+D10+D11+D12+D13+D14+D15+D20,2)\x3C/f>
  \x3Cv>0\x3C/v>
\x3C/c>

\x3C!-- After (keep \x3Cf>, update \x3Cv>) -->
\x3Cc r="D21" s="40">
  \x3Cf>ROUND(D7+D8+D9+D10+D11+D12+D13+D14+D15+D20,2)\x3C/f>
  \x3Cv>5012140.22\x3C/v>
\x3C/c>

Step 5 — Remove calcChain and repack

Delete xl/calcChain.xml (forces Excel to recalculate all formulas on open):

rm /tmp/xlsx_work/xl/calcChain.xml
python scripts/xlsx_pack.py /tmp/xlsx_work/ output.xlsx

Key Rules

1. Never use openpyxl load_workbook() + save() on existing files — it corrupts VBA, pivot tables, sparklines, and theme colors
2. Always delete calcChain.xml after editing formulas or cached values
3. Never change s= attribute (style index) or r= attribute (cell reference)
4. Only modify \x3Cv> tags — everything else is untouched

References

• ERP row mapping: references/erp-row-maps.md
• XML edit patterns: references/xlsx-edit-guide.md
• xlsx unpack/pack scripts: use from minimax-xlsx skill (copy to scripts/)

Source Scripts