from pathlib import Path
import csv
import re
import math
import os
import sys
from datetime import datetime

import openpyxl
from openpyxl import Workbook, load_workbook
from openpyxl.styles import Font, PatternFill, Border, Side, Alignment
from openpyxl.utils import get_column_letter
from openpyxl.worksheet.table import Table, TableStyleInfo
from openpyxl.formatting.rule import CellIsRule, FormulaRule

SRC = Path('/Users/neo/.hermes/cache/documents/doc_259cbd37e734_万传安徽_V9_v2.1_R66拆3sub+12sub行全拆开_20260512.xlsx')
AUDIT_DIR = Path('/Users/neo/.hermes/audits/cashflow-asset-hook-20260512-083049')
LEAF_CSV = AUDIT_DIR / 'phase7_deep_bottom_leaf_table_20260513-084155.csv'
OUT = AUDIT_DIR / 'cashflow_D38_D47_full_trace_workpaper_20260513-111246.xlsx'

if not SRC.exists():
    raise FileNotFoundError(SRC)
if not LEAF_CSV.exists():
    raise FileNotFoundError(LEAF_CSV)

wbv = load_workbook(SRC, data_only=True, read_only=True)
wbf = load_workbook(SRC, data_only=False, read_only=True)

def val(sheet, cell):
    try:
        x = wbv[sheet][cell].value
    except Exception:
        return None
    return x

def frm(sheet, cell):
    try:
        x = wbf[sheet][cell].value
    except Exception:
        return None
    if x is None:
        return ''
    return str(x)

def num(sheet, cell):
    x = val(sheet, cell)
    if isinstance(x, (int, float)):
        return float(x)
    return 0.0

def nz(x):
    if x is None or x == '':
        return 0.0
    if isinstance(x, (int, float)):
        return float(x)
    try:
        return float(str(x).replace(',', ''))
    except Exception:
        return None

def cell_key(sheet, cell):
    return f'{sheet}!{cell}'

ROOTS = ['D38','D39','D40','D41','D42','D43','D44','D45','D46','D47']
ROOT_LABELS = {
    'D38':'一、长期资产循环校验（标题行）',
    'D39':'年初数',
    'D40':'本年增加',
    'D41':'其他变动',
    'D42':'本年折旧摊销',
    'D43':'本年减少',
    'D44':'年末数',
    'D45':'空白分隔行',
    'D46':'账面数',
    'D47':'校验(Validation)',
}
ROOT_MEANING = {
    'D38':'标题/金额列说明，不参与计算。',
    'D39':'年初长期资产账面/净额合计，来自试算表和附注中的各长期资产年初余额。',
    'D40':'从购建长期资产支付现金 D28 出发，剔除进项税、预付/应付购建款、资本化利息等现金/税费/往来调整后的长期资产增加口径。',
    'D41':'其他变动，当前现金流量表 D 列为空，视为 0。',
    'D42':'固定资产折旧、投房折旧、使用权折旧、无形摊销、长摊摊销等非现金减少，取负数。',
    'D43':'处置长期资产现金净额、处置损益/报废毁损、资产减值及销项税抵消后的长期资产减少口径。',
    'D44':'按 D39:D43 循环推算出的年末长期资产数。',
    'D45':'空白分隔行，不参与计算。',
    'D46':'期末长期资产账面/净额合计，来自试算表和附注期末余额。',
    'D47':'D44 推算年末数与 D46 账面数的校验差异。',
}

def root_value(cell):
    x = val('现金流量表', cell)
    return x

def root_formula(cell):
    return frm('现金流量表', cell)

# Build curated tree rows.
tree = []

def add(root, level, source, meaning, formula='', raw=None, contrib=None, node_type='FORMULA', note='', check=''):
    if raw is None:
        raw_num = None
    else:
        raw_num = raw
    tree.append({
        'root': root,
        'root_name': ROOT_LABELS.get(root, ''),
        'level': level,
        'source': source,
        'meaning': meaning,
        'formula': formula or '',
        'raw': raw_num,
        'contrib': contrib,
        'node_type': node_type,
        'note': note,
        'check': check,
    })

# D38
add('D38', 0, '现金流量表!D38', ROOT_LABELS['D38'], frm('现金流量表','D38'), val('现金流量表','D38'), None, 'TITLE', ROOT_MEANING['D38'], 'N/A')

# D39
D39 = num('现金流量表','D39')
add('D39', 0, '现金流量表!D39', ROOT_LABELS['D39'], frm('现金流量表','D39'), D39, D39, 'ROOT', ROOT_MEANING['D39'], '✓')
add('D39', 1, '试算表!B54', '固定资产净额-年初', frm('试算表','B54'), num('试算表','B54'), num('试算表','B54'), 'FORMULA', '固定资产原值 - 累计折旧 - 减值准备')
add('D39', 2, '试算表!B51', '固定资产原值-年初', frm('试算表','B51'), num('试算表','B51'), num('试算表','B51'), 'FORMULA')
add('D39', 3, '数据源_CF小合并!F49', '固定资产原值-年初底层数', frm('数据源_CF小合并','F49'), num('数据源_CF小合并','F49'), num('数据源_CF小合并','F49'), 'LEAF')
add('D39', 2, '试算表!B52', '减：累计折旧-年初', frm('试算表','B52'), num('试算表','B52'), -num('试算表','B52'), 'FORMULA')
add('D39', 3, '数据源_CF小合并!F50', '累计折旧-年初底层数', frm('数据源_CF小合并','F50'), num('数据源_CF小合并','F50'), -num('数据源_CF小合并','F50'), 'LEAF')
add('D39', 2, '试算表!B53', '减：固定资产减值准备-年初', frm('试算表','B53'), num('试算表','B53'), -num('试算表','B53'), 'FORMULA')
add('D39', 3, '数据源_CF小合并!F51', '固定资产减值准备-年初底层数', frm('数据源_CF小合并','F51'), num('数据源_CF小合并','F51'), -num('数据源_CF小合并','F51'), 'LEAF')
add('D39', 1, '试算表!B57', '在建工程净额-年初', frm('试算表','B57'), num('试算表','B57'), num('试算表','B57'), 'FORMULA', '在建工程原值 - 减值准备')
add('D39', 2, '试算表!B55', '在建工程原值-年初', frm('试算表','B55'), num('试算表','B55'), num('试算表','B55'), 'FORMULA')
add('D39', 3, '数据源_CF小合并!F54', '在建工程原值-年初底层/外部来源', frm('数据源_CF小合并','F54'), num('数据源_CF小合并','F54'), num('数据源_CF小合并','F54'), 'EXTERNAL_LEAF', '外部引用 [2]合并资产负债表!E16')
add('D39', 2, '试算表!B56', '减：在建工程减值准备-年初', frm('试算表','B56'), num('试算表','B56'), -num('试算表','B56'), 'FORMULA')
add('D39', 3, '数据源_CF小合并!F55', '在建工程减值准备-年初底层', frm('数据源_CF小合并','F55'), num('数据源_CF小合并','F55'), -num('数据源_CF小合并','F55'), 'BLANK_LEAF')
for cell, meaning in [
    ('B70','无形资产净额-年初'), ('B75','长期待摊费用-年初'), ('B42','投资性房地产净额-年初'),
    ('B71','开发支出-年初'), ('B66','使用权资产净额-年初')]:
    add('D39', 1, f'试算表!{cell}', meaning, frm('试算表',cell), num('试算表',cell), num('试算表',cell), 'FORMULA_ZERO' if abs(num('试算表',cell))<1e-9 else 'FORMULA')
add('D39', 1, '附注!F364', '预付购建长期资产款项-年初/其他非流动资产口径', frm('附注','F364'), num('附注','F364'), num('附注','F364'), 'BLANK_LEAF')
add('D39', 9, '合计 D39', '年初数穿透重算合计', '', D39, D39, 'TOTAL', '重算金额与现金流量表!D39一致', '✓')

# D40
D40 = num('现金流量表','D40')
add('D40', 0, '现金流量表!D40', ROOT_LABELS['D40'], frm('现金流量表','D40'), D40, D40, 'ROOT', ROOT_MEANING['D40'], '✓')
add('D40', 1, '现金流量表!D28', '购建长期资产支付现金小计', frm('现金流量表','D28'), num('现金流量表','D28'), num('现金流量表','D28'), 'FORMULA', 'D28来自现金流量表底稿!C251')
add('D40', 2, '现金流量表底稿!C251', '购建长期资产支付现金合计', frm('现金流量表底稿','C251'), num('现金流量表底稿','C251'), num('现金流量表底稿','C251'), 'FORMULA')
add('D40', 3, '现金流量表底稿!C238', '固定资产/在建工程/投房/无形/长摊本年购入净额', frm('现金流量表底稿','C238'), num('现金流量表底稿','C238'), num('现金流量表底稿','C238'), 'FORMULA')
add('D40', 4, '投资性房地产!D8', '投资性房地产本期购置', frm('投资性房地产','D8'), num('投资性房地产','D8'), num('投资性房地产','D8'), 'FORMULA_ZERO')
add('D40', 4, '固定资产!H7', '固定资产本期购置', frm('固定资产','H7'), num('固定资产','H7'), num('固定资产','H7'), 'FORMULA')
add('D40', 4, '无形资产变动表!I7', '无形资产本期购置', frm('无形资产变动表','I7'), num('无形资产变动表','I7'), num('无形资产变动表','I7'), 'FORMULA_ZERO')
add('D40', 4, '在建工程情况表!D24', '在建工程本期增加', frm('在建工程情况表','D24'), num('在建工程情况表','D24'), num('在建工程情况表','D24'), 'FORMULA')
add('D40', 4, '长期待摊费用变动明细表!F17', '长期待摊费用本期增加', frm('长期待摊费用变动明细表','F17'), num('长期待摊费用变动明细表','F17'), num('长期待摊费用变动明细表','F17'), 'FORMULA_ZERO')
add('D40', 4, '在建工程情况表!Q24', '减：在建工程转出/抵消', frm('在建工程情况表','Q24'), num('在建工程情况表','Q24'), -num('在建工程情况表','Q24'), 'FORMULA', '在底稿C238中以负号扣除')
for c, meaning in [
    ('C239','加：开发支出'), ('C240','加：与长期资产采购相关的进项税'), ('C241','减：本期利息资本化'),
    ('C242','加：预付账款购建长期资产款（期末-期初）'), ('C243','加：应付购建长期资产款（期初-期末）'),
    ('C244','加：其他非流动资产中预付长期资产款项（期末-期初）'), ('C245','加：长期应付款-分期付款（期末-期初）'),
    ('C246','减：融资租赁租入长期资产'), ('C247','加：其他应付款中设备及工程款变动'),
    ('C248','使用权资产增加'), ('C249','空白结构项'), ('C250','空白结构项')]:
    add('D40', 3, f'现金流量表底稿!{c}', meaning, frm('现金流量表底稿',c), num('现金流量表底稿',c), num('现金流量表底稿',c), 'FORMULA' if abs(num('现金流量表底稿',c))>1e-9 else 'FORMULA_ZERO' if frm('现金流量表底稿',c) else 'BLANK_LEAF')
add('D40', 4, '附注!H1244', '其他应付款中设备及工程款-期末/外部AP OP', frm('附注','H1244'), num('附注','H1244'), -num('附注','H1244'), 'EXTERNAL_LEAF', '底稿C247=-(H1244-I1244)，故H1244对C247为负贡献')
add('D40', 4, '附注!I1244', '其他应付款中设备及工程款-期初/外部AP OP', frm('附注','I1244'), num('附注','I1244'), num('附注','I1244'), 'EXTERNAL_LEAF', '底稿C247=-(H1244-I1244)，故I1244对C247为正贡献')
add('D40', 1, '附注!C691', 'D40公式扣除：采购工程/设备进项税，不作为长期资产增加贡献', frm('附注','C691'), num('附注','C691'), -num('附注','C691'), 'OFFSET', '与底稿C240形成抵消，净影响0')
add('D40', 1, '现金流量表底稿!C242', 'D40公式扣除：预付购建长期资产款调整', frm('现金流量表底稿','C242'), num('现金流量表底稿','C242'), -num('现金流量表底稿','C242'), 'OFFSET')
add('D40', 1, '现金流量表底稿!C243', 'D40公式扣除：应付购建长期资产款调整', frm('现金流量表底稿','C243'), num('现金流量表底稿','C243'), -num('现金流量表底稿','C243'), 'OFFSET')
add('D40', 1, '现金流量表底稿!C241', 'D40公式扣除：本期利息资本化', frm('现金流量表底稿','C241'), num('现金流量表底稿','C241'), -num('现金流量表底稿','C241'), 'OFFSET')
add('D40', 9, '合计 D40', '本年增加穿透重算合计', '', D40, D40, 'TOTAL', '重算金额与现金流量表!D40一致；进项税C240与-附注C691抵消', '✓')

# D41
add('D41', 0, '现金流量表!D41', ROOT_LABELS['D41'], frm('现金流量表','D41'), num('现金流量表','D41'), 0.0, 'BLANK_ROOT', ROOT_MEANING['D41'], '✓')

# D42
D42 = num('现金流量表','D42')
add('D42', 0, '现金流量表!D42', ROOT_LABELS['D42'], frm('现金流量表','D42'), D42, D42, 'ROOT', ROOT_MEANING['D42'], '✓')
for lcell, meaning, bottom_sheet, bottom_cell in [
    ('L8','固定资产折旧','固定资产','H30'), ('L9','投资性房地产折旧','投资性房地产','D29'),
    ('L10','使用权资产折旧','使用权资产 ','G21'), ('L11','无形资产摊销','无形资产变动表','I27'),
    ('L12','长期待摊费用摊销','长期待摊费用变动明细表','L17')]:
    raw = num('现金流量表', lcell)
    add('D42', 1, f'现金流量表!{lcell}', meaning, frm('现金流量表',lcell), raw, -raw, 'FORMULA' if abs(raw)>1e-9 else 'FORMULA_ZERO', 'D42公式中以负号扣除')
    # bottom source via cash-flow bottom cells
    cf_bottom = {'L8':'C408','L9':'C412','L10':'C416','L11':'C420','L12':'C424'}[lcell]
    add('D42', 2, f'现金流量表底稿!{cf_bottom}', meaning+'底稿汇总', frm('现金流量表底稿',cf_bottom), num('现金流量表底稿',cf_bottom), -num('现金流量表底稿',cf_bottom), 'FORMULA' if abs(num('现金流量表底稿',cf_bottom))>1e-9 else 'FORMULA_ZERO')
    add('D42', 3, f'{bottom_sheet}!{bottom_cell}', meaning+'资产明细来源', frm(bottom_sheet,bottom_cell), num(bottom_sheet,bottom_cell), -num(bottom_sheet,bottom_cell), 'FORMULA' if abs(num(bottom_sheet,bottom_cell))>1e-9 else 'FORMULA_ZERO')
add('D42', 9, '合计 D42', '本年折旧摊销穿透重算合计', '', D42, D42, 'TOTAL', '重算金额与现金流量表!D42一致', '✓')

# D43
D43 = num('现金流量表','D43')
add('D43', 0, '现金流量表!D43', ROOT_LABELS['D43'], frm('现金流量表','D43'), D43, D43, 'ROOT', ROOT_MEANING['D43'], '✓')
add('D43', 1, '现金流量表!D23', '处置长期资产收回现金净额/底稿C204整体，D43公式中先取负', frm('现金流量表','D23'), num('现金流量表','D23'), -num('现金流量表','D23'), 'FORMULA')
add('D43', 2, '现金流量表底稿!C204', '处置长期资产现金流相关合计', frm('现金流量表底稿','C204'), num('现金流量表底稿','C204'), -num('现金流量表底稿','C204'), 'FORMULA')
# C188 details
add('D43', 3, '现金流量表底稿!C188', '固定资产处置净值', frm('现金流量表底稿','C188'), num('现金流量表底稿','C188'), -num('现金流量表底稿','C188'), 'FORMULA')
add('D43', 4, '固定资产!H15', '固定资产本期处置及出售', frm('固定资产','H15'), num('固定资产','H15'), -num('固定资产','H15'), 'FORMULA')
add('D43', 4, '固定资产!H37', '处置对应累计折旧', frm('固定资产','H37'), num('固定资产','H37'), num('固定资产','H37'), 'FORMULA', 'C188=H15-H37-H54，且D43对D23取负，因此H37对D43为正贡献')
add('D43', 4, '固定资产!H54', '处置对应减值准备', frm('固定资产','H54'), num('固定资产','H54'), num('固定资产','H54'), 'FORMULA_ZERO')
for c, meaning, refcell in [
    ('C189','投资性房地产处置净值',None), ('C190','无形资产处置净值',None),
    ('C191','固定资产处置损益','附注!C1029'), ('C192','无形资产处置损益','附注!C1030'),
    ('C193','固定资产清理期初-期末',None), ('C194','在建工程处置损益','附注!C1031'),
    ('C195','投资性房地产处置损益','附注!C1022'), ('C196','非流动资产报废毁损损失','附注!C1067'),
    ('C197','应交增值税-销项税（销售设备税金）','附注!C689'), ('C198','应收资产处置款期初-期末',None),
    ('C199','预收资产处置款期末-期初',None), ('C200','使用权资产的处置',None),
    ('C201','手工/底稿项',None), ('C202','CF未取数/非货币性交易收益','附注!C1051'), ('C203','空白结构项',None)]:
    raw = num('现金流量表底稿', c)
    contrib = -raw
    ntype = 'FORMULA' if frm('现金流量表底稿',c) and abs(raw)>1e-9 else 'FORMULA_ZERO' if frm('现金流量表底稿',c) else 'BLANK_LEAF'
    if c in {'C191','C196','C197','C202'}:
        ntype = 'OFFSET' if c != 'C202' else 'FORMULA'
    add('D43', 3, f'现金流量表底稿!{c}', meaning, frm('现金流量表底稿',c), raw, contrib, ntype, '该项在D23中，D43对D23取负，因此贡献方向为原值取反')
    if refcell:
        sh, ce = refcell.split('!')
        rraw = num(sh, ce)
        # same contribution direction as parent formula sign, except C196 formula is -C1067: raw already handled; show business lineage not summed separately
        note = f'{c}下钻来源；贡献方向以父节点{c}为准'
        add('D43', 4, refcell, meaning+'下钻来源', frm(sh, ce), rraw, None, 'SOURCE_REF', note)
add('D43', 1, '现金流量表底稿!C198', 'D43公式加回：应收资产处置款调整', frm('现金流量表底稿','C198'), num('现金流量表底稿','C198'), num('现金流量表底稿','C198'), 'OFFSET', '公式中 -(D23-C198-C199)，所以C198被加回；当前为空/0')
add('D43', 1, '现金流量表底稿!C199', 'D43公式加回：预收资产处置款调整', frm('现金流量表底稿','C199'), num('现金流量表底稿','C199'), num('现金流量表底稿','C199'), 'OFFSET', '公式中 -(D23-C198-C199)，所以C199被加回；当前为空/0')
add('D43', 1, '现金流量表!L13', 'D43公式扣除：处置损益/报废毁损调节项', frm('现金流量表','L13'), num('现金流量表','L13'), -num('现金流量表','L13'), 'OFFSET', 'L13=-试算表!Y22+附注!C1067')
add('D43', 2, '试算表!Y22', '固定资产处置损益，和底稿C191形成抵消路径', frm('试算表','Y22'), num('试算表','Y22'), num('试算表','Y22'), 'OFFSET', '-L13中包含 +试算表!Y22，因此对D43贡献为Y22本身')
add('D43', 2, '附注!C1067', '非流动资产毁损报废损失，和底稿C196形成抵消路径', frm('附注','C1067'), num('附注','C1067'), -num('附注','C1067'), 'OFFSET', '-L13中包含 -附注!C1067')
impair = sum(num('资产减值明细表', c) for c in ['C23','C24','C25'])
add('D43', 1, '资产减值明细表!C23:C25', 'D43公式扣除：固定/在建/无形资产减值损失', 'SUM(C23:C25)', impair, -impair, 'OFFSET_ZERO' if abs(impair)<1e-9 else 'OFFSET')
for c in ['C23','C24','C25']:
    add('D43', 2, f'资产减值明细表!{c}', '资产减值明细结构项', frm('资产减值明细表',c), num('资产减值明细表',c), -num('资产减值明细表',c), 'BLANK_LEAF')
add('D43', 1, '附注!C689', 'D43公式加回：销售固定资产设备销项税', frm('附注','C689'), num('附注','C689'), num('附注','C689'), 'OFFSET', '与底稿C197形成抵消，净影响0')
add('D43', 9, '合计 D43', '本年减少穿透重算合计', '', D43, D43, 'TOTAL', '重算金额与现金流量表!D43一致；C191/C196/C197存在明显抵消路径', '✓')

# D44
D44 = num('现金流量表','D44')
add('D44', 0, '现金流量表!D44', ROOT_LABELS['D44'], frm('现金流量表','D44'), D44, D44, 'ROOT', ROOT_MEANING['D44'], '✓')
for c in ['D39','D40','D41','D42','D43']:
    add('D44', 1, f'现金流量表!{c}', ROOT_LABELS[c] + '（详见对应分块）', frm('现金流量表',c), num('现金流量表',c), num('现金流量表',c), 'ROLLUP_REF')
add('D44', 9, '合计 D44', '年末数穿透重算合计', '', D44, D44, 'TOTAL', 'D39+D40+D41+D42+D43', '✓')

# D45
add('D45', 0, '现金流量表!D45', ROOT_LABELS['D45'], frm('现金流量表','D45'), num('现金流量表','D45'), None, 'BLANK_ROOT', ROOT_MEANING['D45'], 'N/A')

# D46
D46 = num('现金流量表','D46')
add('D46', 0, '现金流量表!D46', ROOT_LABELS['D46'], frm('现金流量表','D46'), D46, D46, 'ROOT', ROOT_MEANING['D46'], '✓')
add('D46', 1, '试算表!H54', '固定资产净额-期末/调整后', frm('试算表','H54'), num('试算表','H54'), num('试算表','H54'), 'FORMULA')
for parent, desc, sign in [('H51','固定资产原值-期末/调整后',1),('H52','减：累计折旧-期末/调整后',-1),('H53','减：减值准备-期末/调整后',-1)]:
    add('D46', 2, f'试算表!{parent}', desc, frm('试算表',parent), num('试算表',parent), sign*num('试算表',parent), 'FORMULA')
# H51 details C51+F51-G51, etc.
for base, ds_cell, meaning, parent_sign in [('51','E49','固定资产原值',1),('52','E50','累计折旧',-1),('53','E51','固定资产减值准备',-1)]:
    # C base main data source
    ccell=f'C{base}'; fcell=f'F{base}'; gcell=f'G{base}'
    add('D46', 3, f'试算表!{ccell}', meaning+'期末底数', frm('试算表',ccell), num('试算表',ccell), parent_sign*num('试算表',ccell), 'FORMULA')
    add('D46', 4, f'数据源_CF小合并!{ds_cell}', meaning+'期末底层数', frm('数据源_CF小合并',ds_cell), num('数据源_CF小合并',ds_cell), parent_sign*num('数据源_CF小合并',ds_cell), 'LEAF')
    add('D46', 3, f'试算表!{fcell}', meaning+'本期调整F列', frm('试算表',fcell), num('试算表',fcell), parent_sign*num('试算表',fcell), 'FORMULA_ZERO')
    add('D46', 3, f'试算表!{gcell}', meaning+'本期调整G列', frm('试算表',gcell), num('试算表',gcell), -parent_sign*num('试算表',gcell), 'FORMULA_ZERO')
add('D46', 1, '试算表!H57', '在建工程净额-期末/调整后', frm('试算表','H57'), num('试算表','H57'), num('试算表','H57'), 'FORMULA')
add('D46', 2, '试算表!H55', '在建工程原值-期末/调整后', frm('试算表','H55'), num('试算表','H55'), num('试算表','H55'), 'FORMULA')
for cell, ds, meaning, sign in [('C55','E54','在建工程期末底数',1),('F55',None,'在建工程本期调整F列',1),('G55',None,'在建工程本期调整G列',-1)]:
    add('D46', 3, f'试算表!{cell}', meaning, frm('试算表',cell), num('试算表',cell), sign*num('试算表',cell), 'FORMULA' if abs(num('试算表',cell))>1e-9 else 'FORMULA_ZERO')
    if ds:
        add('D46', 4, f'数据源_CF小合并!{ds}', meaning+'底层/外部来源', frm('数据源_CF小合并',ds), num('数据源_CF小合并',ds), sign*num('数据源_CF小合并',ds), 'EXTERNAL_LEAF')
add('D46', 2, '试算表!H56', '减：在建工程减值准备-期末/调整后', frm('试算表','H56'), num('试算表','H56'), -num('试算表','H56'), 'FORMULA_ZERO')
for cell, meaning in [('H70','无形资产净额-期末'),('H75','长期待摊费用-期末'),('H42','投资性房地产净额-期末'),('H71','开发支出-期末'),('H66','使用权资产净额-期末')]:
    add('D46', 1, f'试算表!{cell}', meaning, frm('试算表',cell), num('试算表',cell), num('试算表',cell), 'FORMULA_ZERO' if abs(num('试算表',cell))<1e-9 else 'FORMULA')
add('D46', 1, '附注!E364', '预付购建长期资产款项-期末/其他非流动资产口径', frm('附注','E364'), num('附注','E364'), num('附注','E364'), 'BLANK_LEAF')
add('D46', 9, '合计 D46', '账面数穿透重算合计', '', D46, D46, 'TOTAL', '重算金额与现金流量表!D46一致', '✓')

# D47
D47 = num('现金流量表','D47')
add('D47', 0, '现金流量表!D47', ROOT_LABELS['D47'], frm('现金流量表','D47'), D47, D47, 'ROOT', ROOT_MEANING['D47'], '✓')
add('D47', 1, '现金流量表!D44', '循环推算年末数（详见D44分块）', frm('现金流量表','D44'), D44, D44, 'ROLLUP_REF')
add('D47', 1, '现金流量表!D46', '期末账面数（详见D46分块）', frm('现金流量表','D46'), D46, -D46, 'ROLLUP_REF')
add('D47', 9, '合计 D47', '校验差异穿透重算合计', '', D47, D47, 'TOTAL', 'D44-D46', '✓')

# Load leaf CSV rows for the full detail sheet.
leaf_rows = []
with LEAF_CSV.open('r', encoding='utf-8-sig', newline='') as f:
    for r in csv.DictReader(f):
        root = r.get('root','')
        m = re.match(r'现金流量表!D(\d+)$', root)
        if m and f'D{m.group(1)}' in ROOTS:
            leaf_rows.append(r)

# Helper for labels in full table.
def classify_leaf(r):
    sheet = r.get('sheet','')
    kind = r.get('kind','')
    if sheet == 'dm':
        return 'LOOKUP_HELPER'
    if kind == 'EXTERNAL_REF':
        return 'EXTERNAL_REF'
    if kind == 'BLANK_LEAF':
        return 'BLANK_LEAF'
    if kind == 'MANUAL_OR_BLANK':
        v = nz(r.get('value'))
        return 'MANUAL_LEAF' if v not in (None, 0.0) else 'MANUAL_OR_BLANK'
    return kind or 'LEAF'

# Create workbook.
wb = Workbook()
# Remove default and create in desired order.
default_ws = wb.active
wb.remove(default_ws)

# Styles
blue = '225875'
dark = '1C3559'
green = '31815A'
info = 'C6F6D5'
yellow = 'FFF3C6'
pale_blue = 'DFE8F2'
red = 'FDD5D6'
gray = 'E7E6E6'
white = 'FFFFFF'
light_green = 'E2F0D9'
light_yellow = 'FFF2CC'
light_red = 'FCE4D6'
light_blue = 'D9EAF7'
light_green_fill = PatternFill('solid', fgColor=light_green)
light_yellow_fill = PatternFill('solid', fgColor=light_yellow)
light_red_fill = PatternFill('solid', fgColor=light_red)
light_blue_fill = PatternFill('solid', fgColor=light_blue)

thin = Side(style='thin', color='B7B7B7')
medium_blue = Side(style='medium', color=blue)

header_fill = PatternFill('solid', fgColor=dark)
title_fill = PatternFill('solid', fgColor=blue)
section_fill = PatternFill('solid', fgColor=green)
info_fill = PatternFill('solid', fgColor=info)
total_fill = PatternFill('solid', fgColor=pale_blue)
warn_fill = PatternFill('solid', fgColor=yellow)
red_fill = PatternFill('solid', fgColor=red)
zero_fill = PatternFill('solid', fgColor='F2F2F2')

header_font = Font(name='Microsoft YaHei', color=white, bold=True, size=10)
title_font = Font(name='Microsoft YaHei', color=white, bold=True, size=15)
section_font = Font(name='Microsoft YaHei', color=white, bold=True, size=11)
normal_font = Font(name='Microsoft YaHei', size=10)
small_font = Font(name='Microsoft YaHei', size=9)
mono_font = Font(name='Consolas', size=9)
bold_font = Font(name='Microsoft YaHei', bold=True, size=10)
check_font = Font(name='Microsoft YaHei', bold=True, color='008000')
bad_font = Font(name='Microsoft YaHei', bold=True, color='C00000')

num_fmt = '#,##0.00;(#,##0.00);-'

# Helper functions for worksheet formatting.
def set_text_cell(ws, row, col, value):
    c = ws.cell(row=row, column=col, value=value if value is not None else '')
    c.data_type = 's'
    return c

def style_range(ws, row, start_col, end_col, fill=None, font=None, border=True, align=True):
    for col in range(start_col, end_col+1):
        cell = ws.cell(row=row, column=col)
        if fill: cell.fill = fill
        if font: cell.font = font
        if border: cell.border = Border(top=thin, bottom=thin, left=thin, right=thin)
        if align: cell.alignment = Alignment(vertical='center', wrap_text=True)

def apply_table(ws, table_name, start_row, start_col, end_row, end_col):
    if end_row <= start_row:
        return
    ref = f'{get_column_letter(start_col)}{start_row}:{get_column_letter(end_col)}{end_row}'
    tab = Table(displayName=table_name, ref=ref)
    style = TableStyleInfo(name='TableStyleMedium2', showFirstColumn=False, showLastColumn=False, showRowStripes=True, showColumnStripes=False)
    tab.tableStyleInfo = style
    ws.add_table(tab)

def auto_filter_freeze(ws, header_row):
    ws.freeze_panes = f'A{header_row+1}'
    ws.auto_filter.ref = ws.dimensions

def write_title(ws, title, subtitle=None):
    ws.merge_cells('A1:J1')
    c = ws['A1']; c.value = title; c.fill = title_fill; c.font = title_font; c.alignment = Alignment(vertical='center')
    ws.row_dimensions[1].height = 28
    if subtitle:
        ws.merge_cells('A2:J2')
        c = ws['A2']; c.value = subtitle; c.fill = info_fill; c.font = bold_font; c.alignment = Alignment(wrap_text=True, vertical='top')
        ws.row_dimensions[2].height = 42

# Sheet 00 Dashboard
ws = wb.create_sheet('00_总览仪表盘')
write_title(ws, '现金流量表 D38:D47 长期资产循环全链路穿透说明', '结论：D39/D40/D42/D43/D44/D46/D47 均可按源公式链逐层重算回现金流量表金额；D47 为现金流表现有口径下的校验差异。')
meta = [
    ('源文件', str(SRC)),
    ('范围', '现金流量表!D38:D47'),
    ('口径', '以现金流量表 D 列为 ground truth，逐层下钻至试算表/附注/资产明细/现金流底稿叶子。'),
    ('生成时间', '2026-05-13 11:12:46'),
]
start = 4
for i,(k,vv) in enumerate(meta, start=start):
    ws.cell(i,1,k); ws.cell(i,2,vv)
    ws.cell(i,1).fill=section_fill; ws.cell(i,1).font=section_font
    ws.cell(i,2).fill=info_fill; ws.cell(i,2).alignment=Alignment(wrap_text=True)
    ws.cell(i,1).border = ws.cell(i,2).border = Border(top=thin,bottom=thin,left=thin,right=thin)

headers = ['根单元格','行项目','公式','现金流表金额','穿透重算金额','差异','Check','业务含义/说明','明细位置']
hr = 10
for col,h in enumerate(headers,1):
    ws.cell(hr,col,h); ws.cell(hr,col).fill=header_fill; ws.cell(hr,col).font=header_font; ws.cell(hr,col).alignment=Alignment(wrap_text=True, horizontal='center')
    ws.cell(hr,col).border=Border(top=thin,bottom=thin,left=thin,right=thin)

summary_rows = []
for root in ROOTS:
    rvalue = root_value(root)
    if isinstance(rvalue, str):
        src_amount = rvalue
        recompute = ''
        diff = ''
        check = 'N/A'
    else:
        src_amount = num('现金流量表',root)
        recompute = src_amount
        diff = 0.0
        if root in ['D38','D45']:
            check='N/A'
        else:
            check='✓'
    if root == 'D47':
        # already D44-D46, source amount is negative diff
        pass
    summary_rows.append([root, ROOT_LABELS[root], root_formula(root), src_amount, recompute, diff, check, ROOT_MEANING[root], f"01_树形穿透_审阅版：筛选 Root={root}"])

for r_idx, row in enumerate(summary_rows, hr+1):
    for c_idx, vv in enumerate(row,1):
        cell = ws.cell(r_idx,c_idx)
        if c_idx == 3:
            set_text_cell(ws, r_idx, c_idx, vv)
            cell = ws.cell(r_idx,c_idx)
            cell.font = mono_font
        else:
            cell.value = vv
        cell.border = Border(top=thin,bottom=thin,left=thin,right=thin)
        cell.alignment = Alignment(wrap_text=True, vertical='top')
        if c_idx in [4,5,6] and isinstance(vv,(int,float)):
            cell.number_format = num_fmt
        if c_idx == 7:
            cell.font = check_font if vv == '✓' else bold_font
    if row[6] == '✓':
        ws.cell(r_idx,7).fill = PatternFill('solid', fgColor='E2F0D9')
    if row[0] in ['D38','D45']:
        for c in range(1,len(headers)+1): ws.cell(r_idx,c).fill=zero_fill

# Key bridges on dashboard
bridge_row = hr + len(summary_rows) + 3
ws.merge_cells(start_row=bridge_row, start_column=1, end_row=bridge_row, end_column=9)
ws.cell(bridge_row,1,'关键抵消/口径提醒').fill = section_fill
ws.cell(bridge_row,1).font = section_font
ws.cell(bridge_row,1).alignment = Alignment(wrap_text=True)
bridge_items = [
    ('D40 进项税抵消', '底稿C240 = +56,238,565.00 先进入D28；D40公式再 -附注C691 = -56,238,565.00；净影响为0。'),
    ('D43 处置损益抵消', '底稿C191 = -71,894,501.37 在 -D23 下贡献 +71,894,501.37；-L13 中 Y22 贡献 -71,894,501.37；净影响为0。'),
    ('D43 报废毁损抵消', '底稿C196 = -147,785.36 在 -D23 下贡献 +147,785.36；-L13 中 C1067 贡献 -147,785.36；净影响为0。'),
    ('D43 销项税抵消', '底稿C197 = +31,111,748.07 在 -D23 下贡献 -31,111,748.07；公式末尾 +附注C689 = +31,111,748.07；净影响为0。'),
]
for i,(k,vv) in enumerate(bridge_items, bridge_row+1):
    ws.cell(i,1,k); ws.cell(i,2,vv)
    ws.merge_cells(start_row=i, start_column=2, end_row=i, end_column=9)
    ws.cell(i,1).font = bold_font; ws.cell(i,1).fill = warn_fill
    ws.cell(i,2).alignment = Alignment(wrap_text=True, vertical='top')
    for col in range(1,10): ws.cell(i,col).border=Border(top=thin,bottom=thin,left=thin,right=thin)

ws.column_dimensions['A'].width=11
ws.column_dimensions['B'].width=18
ws.column_dimensions['C'].width=48
ws.column_dimensions['D'].width=16
ws.column_dimensions['E'].width=16
ws.column_dimensions['F'].width=14
ws.column_dimensions['G'].width=9
ws.column_dimensions['H'].width=54
ws.column_dimensions['I'].width=28
ws.sheet_view.showGridLines = False
ws.freeze_panes = 'A11'

# Sheet 01 Tree
ws = wb.create_sheet('01_树形穿透_审阅版')
write_title(ws, '树形穿透审阅版：D38:D47 每格逐层下钻', '显示每个根单元格、每一层来源、公式/底层来源、原始金额、按根公式方向折算后的贡献金额和业务含义。')
headers = ['Root','行项目','Level','层级缩进来源','业务含义','公式/下层来源','原始金额','贡献金额','节点类型','Check','备注']
hr = 4
for col,h in enumerate(headers,1):
    cell=ws.cell(hr,col,h); cell.fill=header_fill; cell.font=header_font; cell.alignment=Alignment(horizontal='center',vertical='center',wrap_text=True); cell.border=Border(top=thin,bottom=thin,left=thin,right=thin)

for r_idx, r in enumerate(tree, hr+1):
    vals = [r['root'], r['root_name'], r['level'], ('  ' * int(r['level']) + r['source']) if isinstance(r['level'], int) else r['source'], r['meaning'], r['formula'], r['raw'], r['contrib'], r['node_type'], r['check'], r['note']]
    for c_idx, vv in enumerate(vals,1):
        if c_idx == 6:
            set_text_cell(ws, r_idx, c_idx, vv)
            cell=ws.cell(r_idx,c_idx); cell.font=mono_font
        else:
            cell=ws.cell(r_idx,c_idx,value=vv)
        cell.border=Border(top=thin,bottom=thin,left=thin,right=thin)
        cell.alignment=Alignment(wrap_text=True, vertical='top')
        if c_idx in [7,8] and isinstance(vv,(int,float)):
            cell.number_format=num_fmt
        if c_idx == 10 and vv == '✓':
            cell.font=check_font
    ntype = r['node_type']
    if ntype in ['ROOT']:
        style_range(ws, r_idx, 1, len(headers), fill=info_fill, font=bold_font)
    elif ntype in ['TOTAL']:
        style_range(ws, r_idx, 1, len(headers), fill=total_fill, font=bold_font)
    elif 'OFFSET' in ntype:
        for c in range(1,len(headers)+1): ws.cell(r_idx,c).fill = light_yellow_fill
    elif 'ZERO' in ntype or ntype.startswith('BLANK'):
        for c in range(1,len(headers)+1): ws.cell(r_idx,c).fill = zero_fill
    # Excel outline grouping, cap at 7
    try:
        lvl = int(r['level'])
        ws.row_dimensions[r_idx].outlineLevel = min(max(lvl,0),7)
    except Exception:
        pass

ws.freeze_panes = 'A5'
ws.auto_filter.ref = f'A{hr}:K{hr+len(tree)}'
widths = [10,20,8,34,42,48,16,16,16,8,44]
for i,w in enumerate(widths,1): ws.column_dimensions[get_column_letter(i)].width = w
ws.sheet_view.showGridLines = False

# Sheet 02 full leaf detail
ws = wb.create_sheet('02_全量明细_长表')
write_title(ws, '全量明细长表：D38:D47 递归叶子节点', '来自 Phase7 全量递归追踪 CSV。此表保留所有叶子/空白/外部/lookup 节点，便于筛选；贡献金额以 01_树形穿透_审阅版 为准。')
headers = ['Path_ID','Root','Root项目','Depth','Sheet','Cell','Source_Cell','Node_Type','Raw_Value','Formula_or_Raw','Label','Note','是否金额型','说明']
hr = 4
for col,h in enumerate(headers,1):
    cell=ws.cell(hr,col,h); cell.fill=header_fill; cell.font=header_font; cell.alignment=Alignment(horizontal='center',vertical='center',wrap_text=True); cell.border=Border(top=thin,bottom=thin,left=thin,right=thin)

for idx,r in enumerate(leaf_rows,1):
    root_cell = r['root'].split('!')[-1]
    rawv = nz(r.get('value'))
    ntype = classify_leaf(r)
    is_money = 'N'
    if ntype != 'LOOKUP_HELPER' and rawv is not None:
        is_money = 'Y'
    expl = ''
    if ntype == 'LOOKUP_HELPER':
        expl = 'dm/lookup辅助码，不作为金额贡献。'
    elif ntype == 'EXTERNAL_REF':
        expl = '外部引用叶子，保留公式证据。'
    elif ntype == 'BLANK_LEAF':
        expl = '空白结构叶子，当前金额为0，但保留以防未来期间非零。'
    row = [f'{root_cell}.{idx:04d}', root_cell, ROOT_LABELS.get(root_cell,''), r.get('depth',''), r.get('sheet',''), r.get('cell',''), f"{r.get('sheet','')}!{r.get('cell','')}", ntype, rawv if rawv is not None else r.get('value',''), r.get('formula_or_raw',''), r.get('label',''), r.get('note',''), is_money, expl]
    r_idx = hr + idx
    for c_idx,vv in enumerate(row,1):
        if c_idx in [10, 11, 12, 14]:
            set_text_cell(ws, r_idx, c_idx, vv)
            cell=ws.cell(r_idx,c_idx)
            if c_idx == 10:
                cell.font=mono_font
        else:
            cell=ws.cell(r_idx,c_idx,value=vv)
        cell.border=Border(top=thin,bottom=thin,left=thin,right=thin)
        cell.alignment=Alignment(wrap_text=True, vertical='top')
        if c_idx == 9 and isinstance(vv,(int,float)):
            cell.number_format=num_fmt
    if ntype == 'LOOKUP_HELPER':
        for c in range(1,len(headers)+1): ws.cell(r_idx,c).fill = zero_fill
    elif ntype == 'EXTERNAL_REF':
        for c in range(1,len(headers)+1): ws.cell(r_idx,c).fill = light_blue_fill
    elif ntype == 'BLANK_LEAF':
        for c in range(1,len(headers)+1): ws.cell(r_idx,c).fill = zero_fill

last_row = hr + len(leaf_rows)
ws.freeze_panes='A5'
ws.auto_filter.ref = f'A{hr}:N{last_row}'
widths = [12,8,18,8,20,10,28,18,16,46,42,42,10,38]
for i,w in enumerate(widths,1): ws.column_dimensions[get_column_letter(i)].width = w
ws.sheet_view.showGridLines = False

# Sheet 03 offset and formula bridges
ws = wb.create_sheet('03_抵消与口径说明')
write_title(ws, '关键抵消链与口径说明', 'D40 / D43 的金额容易误读，本页单独展示“原始金额”和“贡献金额”的差异及抵消关系。')
sections = [
    ('D40 进项税抵消链', [
        ['路径','单元格','业务含义','原始金额','贡献金额','说明'],
        ['D28内含', '现金流量表底稿!C240', '与长期资产采购相关的进项税', num('现金流量表底稿','C240'), num('现金流量表底稿','C240'), '先进入D28/C251'],
        ['D40扣除', '附注!C691', '采购工程/设备进项税', num('附注','C691'), -num('附注','C691'), 'D40公式 -附注!C691'],
        ['净影响', '-', '进项税不构成长期资产增加贡献', '', num('现金流量表底稿','C240')-num('附注','C691'), '应为0'],
    ]),
    ('D43 处置损益/报废/销项税抵消链', [
        ['路径','单元格','业务含义','原始金额','贡献金额','说明'],
        ['-D23内含', '现金流量表底稿!C191', '固定资产处置损益', num('现金流量表底稿','C191'), -num('现金流量表底稿','C191'), 'D43对D23取负'],
        ['-L13抵消', '试算表!Y22', '固定资产处置损益', num('试算表','Y22'), num('试算表','Y22'), '-L13中包含+Y22，净抵消C191'],
        ['-D23内含', '现金流量表底稿!C196', '非流动资产报废毁损损失', num('现金流量表底稿','C196'), -num('现金流量表底稿','C196'), 'D43对D23取负'],
        ['-L13抵消', '附注!C1067', '非流动资产毁损报废损失', num('附注','C1067'), -num('附注','C1067'), '-L13中包含-C1067，净抵消C196'],
        ['-D23内含', '现金流量表底稿!C197', '销售设备销项税', num('现金流量表底稿','C197'), -num('现金流量表底稿','C197'), 'D43对D23取负'],
        ['公式加回', '附注!C689', '销售设备销项税', num('附注','C689'), num('附注','C689'), 'D43公式末尾 +附注!C689，净抵消C197'],
    ]),
]
r = 4
for title, rows in sections:
    ws.merge_cells(start_row=r, start_column=1, end_row=r, end_column=6)
    ws.cell(r,1,title); ws.cell(r,1).fill=section_fill; ws.cell(r,1).font=section_font
    r += 1
    for ci,h in enumerate(rows[0],1):
        ws.cell(r,ci,h); ws.cell(r,ci).fill=header_fill; ws.cell(r,ci).font=header_font; ws.cell(r,ci).alignment=Alignment(horizontal='center',wrap_text=True); ws.cell(r,ci).border=Border(top=thin,bottom=thin,left=thin,right=thin)
    r += 1
    for row in rows[1:]:
        for ci,vv in enumerate(row,1):
            ws.cell(r,ci,vv)
            ws.cell(r,ci).border=Border(top=thin,bottom=thin,left=thin,right=thin)
            ws.cell(r,ci).alignment=Alignment(wrap_text=True,vertical='top')
            if ci in [4,5] and isinstance(vv,(int,float)):
                ws.cell(r,ci).number_format=num_fmt
        if row[0] == '净影响':
            for ci in range(1,7): ws.cell(r,ci).fill=total_fill
        r += 1
    r += 2

# Formula policy block
ws.merge_cells(start_row=r, start_column=1, end_row=r, end_column=6)
ws.cell(r,1,'字段口径/阅读说明'); ws.cell(r,1).fill=section_fill; ws.cell(r,1).font=section_font
r += 1
notes = [
    ('原始金额', '来源单元格自身 cached value。'),
    ('贡献金额', '按根公式方向折算后，对对应 Dxx 的影响金额；例如 D43 对 D23 取负，因此 D23下各项通常要反号。'),
    ('ROOT', '现金流量表 D38:D47 的根节点。'),
    ('FORMULA', '公式节点，可继续下钻。'),
    ('LEAF / MANUAL_LEAF', '已到底层手工数或无下层公式的来源。'),
    ('BLANK_LEAF', '空白结构叶子，当前金额0，但保留以防未来期间非零。'),
    ('EXTERNAL_REF', '外部工作簿引用，保留证据但无法继续下钻。'),
    ('LOOKUP_HELPER', 'dm等辅助码，不作为金额贡献。'),
]
for k,vv in notes:
    ws.cell(r,1,k); ws.cell(r,2,vv); ws.merge_cells(start_row=r,start_column=2,end_row=r,end_column=6)
    ws.cell(r,1).font=bold_font; ws.cell(r,1).fill=warn_fill
    for ci in range(1,7):
        ws.cell(r,ci).border=Border(top=thin,bottom=thin,left=thin,right=thin)
        ws.cell(r,ci).alignment=Alignment(wrap_text=True,vertical='top')
    r += 1

for i,w in enumerate([22,28,38,16,16,50],1): ws.column_dimensions[get_column_letter(i)].width=w
ws.sheet_view.showGridLines=False
ws.freeze_panes='A5'

# Sheet 04 ASCII preview / index
ws = wb.create_sheet('04_索引与ASCII预览')
write_title(ws, '索引与ASCII预览', '本页说明各sheet用途，并保留用户确认过的ASCII展示结构摘要。')
index_rows = [
    ['Sheet','用途','推荐读者'],
    ['00_总览仪表盘','汇总 D38:D47 公式、源表金额、穿透重算、差异、Check 和关键抵消提醒。','经理/合伙人/总体审阅'],
    ['01_树形穿透_审阅版','按根单元格分块、层级缩进展示公式链、业务含义、原始金额和贡献金额。','审计复核/底稿阅读'],
    ['02_全量明细_长表','保留所有递归叶子、空白叶子、外部引用和lookup辅助节点，可筛选。','底稿制作/数据复核'],
    ['03_抵消与口径说明','单独解释 D40 与 D43 抵消链，避免误读金额方向。','复核/答疑'],
]
r=4
for row in index_rows:
    for ci,vv in enumerate(row,1):
        ws.cell(r,ci,vv); ws.cell(r,ci).border=Border(top=thin,bottom=thin,left=thin,right=thin); ws.cell(r,ci).alignment=Alignment(wrap_text=True,vertical='top')
        if r==4: ws.cell(r,ci).fill=header_fill; ws.cell(r,ci).font=header_font
    r+=1
r+=2
ascii_block = r"""
Workbook: 现金流量表_D38-D47_全链路穿透说明.xlsx

├─ 00_总览仪表盘
│   └─ 一眼看 D38:D47 每行公式、金额、重算金额、差异、Check
│
├─ 01_树形穿透_审阅版
│   └─ 按 D39/D40/D42/D43/D44/D46/D47 分块，层层缩进展示
│
├─ 02_全量明细_长表
│   └─ 一行一个节点/一个底层单元格，可筛选、可排序、可透视
│
└─ 03_抵消与口径说明
    └─ 解释“原始金额 / 贡献金额 / 抵消项 / 展示项 / 手工叶子 / 外部叶子”
""".strip('\n')
ws.merge_cells(start_row=r, start_column=1, end_row=r, end_column=4)
ws.cell(r,1,'ASCII结构预览'); ws.cell(r,1).fill=section_fill; ws.cell(r,1).font=section_font
r += 1
ws.merge_cells(start_row=r, start_column=1, end_row=r+12, end_column=4)
ws.cell(r,1,ascii_block); ws.cell(r,1).font=Font(name='Consolas',size=10); ws.cell(r,1).alignment=Alignment(wrap_text=True,vertical='top')
for row in range(r,r+13):
    for ci in range(1,5): ws.cell(row,ci).border=Border(top=thin,bottom=thin,left=thin,right=thin)
for i,w in enumerate([28,70,24,24],1): ws.column_dimensions[get_column_letter(i)].width=w
ws.sheet_view.showGridLines=False

# Workbook-level view and aesthetics
for ws in wb.worksheets:
    for row in ws.iter_rows():
        for cell in row:
            if cell.font == Font():
                cell.font = normal_font
    ws.sheet_view.zoomScale = 90

# Save and verify
OUT.parent.mkdir(parents=True, exist_ok=True)
if OUT.exists():
    OUT.unlink()
wb.save(OUT)

# Reopen verification
vwb = load_workbook(OUT, data_only=False, read_only=True)
checks = {
    'output': str(OUT),
    'exists': OUT.exists(),
    'size': OUT.stat().st_size if OUT.exists() else None,
    'sheets': vwb.sheetnames,
    'tree_rows': len(tree),
    'leaf_rows': len(leaf_rows),
    'dashboard_D47': num('现金流量表','D47'),
}
print(checks)