wps-udf-tree-collections 项目说明

Excel-DNA 自定义函数插件 | 父子维度树引擎 | BI 层级汇总分析

一、项目概述

wps-udf-tree-collections 是一个基于 Excel-DNA 的 Excel/WPS 自定义函数插件，核心能力是将 TreeCollections 开源树集合库 内嵌为 Excel 自定义函数（UDF），让用户在单元格中直接完成父子维度（Parent-Child Hierarchy）的构建、查询、层级汇总全流程。

该项目的灵感来源于 SAP BPC / SAP BW 中父子维度层级建模的真实企业级需求，经过两个大型制造集团的 SAP 报表项目实战验证后，将核心逻辑抽离为通用蓝图。

一句话定位： 用 Excel 公式实现 BI 工具的维度分析和层级汇总，零数据库、零服务器、零 SQL。

二、技术栈

三、核心功能

3.1 维度树函数（DIM_*）

共 16 个维度函数，覆盖父子维度分析的完整生命周期：

3.2 事实聚合函数（FACT_*）

共 3 个事实表函数，实现 BI 工具的核心能力——层级汇总：

支持的汇总方式： sum / count / average / min / max / std（标准差）

筛选条件语法： 类 SQL 风格，支持 =、!=、<>、>、>=、<、<= 运算符和 and 组合。

# 单条件
month='2026-07'

# 多条件组合
month='2026-07' and category='actual'

# 数值比较
amount>1000 and amount<=5000

四、架构设计

4.1 工作簿驱动模型（Workbook-Driven）

用户工作表数据  DIM_BUILD  内存句柄  DIM_* 查询
                    
                     FACT_BUILD  事实句柄  FACT_AGG 汇总

• 维度表和事实表都在工作表中以普通区域的形态存在
• 通过 DIM_BUILD / FACT_BUILD 一次性构建为内存快照
• 后续所有查询/汇总操作都基于句柄复用，无需重新解析

4.2 内存句柄复用机制（核心性能优化）

一次性 O(n) 构建  常驻内存对象  后续查询 O(log n) 甚至 O(1)

• DIM_BUILD 只执行一次，将整个维度表解析为 BiDimensionSnapshot 树结构
• 构建时同步计算所有节点的路径缓存（_pathCodesCache / _pathNamesCache），避免后续重复遍历
• 所有 DIM_* 函数直接操作内存中的同一棵树，不做重复解析
• FACT_BUILD 构建时支持预过滤，减少后续查询的数据量

4.3 一维多树支持

通过 rootNodeId 参数，在同一张维度表中模拟多棵树：

• 维度表中包含多个顶层根节点（如“管理口径”和“法人口径”）
• 查询时通过 rootNodeId 指定当前分析使用哪棵树
• 节点作用域自动限定在指定根节点的子树范围内

4.4 异步计算

• DIM_BUILD、FACT_BUILD、FACT_AGG、FACT_TABLE 均支持异步执行
• 计算期间单元格显示“计算中…”，不阻塞 Excel UI 响应
• 支持 CancellationToken，公式依赖变化时自动取消旧任务

4.5 源码结构

src/TestWpsUdfTreeCollections/
 AddIn.cs                    # 插件生命周期、日志、运行时装配
 Functions.cs                # 全部 DIM_* / FACT_* 业务函数（主入口）
 ExcelWorkbookDataOps.cs     # Excel COM 互操作封装（工作表读写、ListObject 操作）
 HostInteractionGuard.cs     # 宿主忙碌检测与保护
 WpsBlankWorkbookCleaner.cs  # WPS 空白工作簿清理
 Properties/AssemblyInfo.cs  # 程序集信息
 TreeCollections/            # 内嵌 TreeCollections 源码
    Tree/                   # 核心树结构（EntityTree、MutableEntityTree、ReadOnlyEntityTree）
    InternalUtilities/      # 内部工具类
    SerialTreeNode.cs       # 序列化树节点
 TestWpsUdfTreeCollections-AddIn.dna    # x86 配置
 TestWpsUdfTreeCollections-AddIn64.dna  # x64 配置

五、使用场景

5.1 组织架构树分析

深圳总部(HQ)
 华南大区(HN)
    广东子公司(GD)
       深圳分公司(SZ001)
       广州分公司(GZ001)
    广西子公司(GX)
 华东大区(HD)
     上海子公司(SH)
     浙江子公司(ZJ)

• 快速判断某分公司的层级和完整组织路径
• 按大区汇总所有下属公司的收入/成本/利润
• 组织架构调整后，自动重算跨期对比报表

5.2 财务科目树分析

所有者权益
 净利润
    收入
       主营收入
          产品线A收入
          产品线B收入
       其他业务收入
    成本
        主营成本
        期间费用
 资本公积

• 从最细颗粒度的科目编码，自动汇总到科目大类
• 支持管理口径和法人口径两套科目树的动态切换

5.3 成本中心树

• 集团成本中心 工厂成本中心 车间成本中心 班组
• 按层级汇总各成本中心的费用发生额

5.4 物料 BOM 树

• 成品 半成品 零部件 原材料
• 计算物料需求计划的层级展开
• 成本卷算（从原材料到成品的成本累加）

5.5 通用父子维度场景

任何满足“同一列数据中，不同行代表不同层级，且层级关系通过 id/parentid 定义”的场景，都可以直接使用。

六、BI 分析工作流

典型操作步骤

1. 准备维度表：在工作表里创建维度表，首行表头，至少包含 id 和 parentid 两列，其他列自动视为节点属性
2. 构建维度树：=DIM_BUILD(维度表区域, "id", "parentid", "name", "组织维度")
3. 验证构建结果：=DIM_STATUS(句柄)
4. 维度查询：用 DIM_FIND、DIM_PATH_NAMES、DIM_LEVEL 等函数做节点定位
5. 准备事实表：在工作表里创建事实表，首行表头，至少包含 id 列（与维度节点编码关联）
6. 构建事实缓存：=FACT_BUILD(维度句柄, 事实表区域, "id")
7. 层级汇总：=FACT_AGG(事实句柄, "华南大区", "amount", "sum", TRUE, TRUE, FALSE)
8. 切换筛选条件：在 FACT_AGG 中追加 month='2026-07' 等运行时筛选

推荐公式示例

# 维度构建
=TESTWPSUDFTREECOLLECTIONS_DIM_BUILD(DimData!A1:F24, "id", "parentid", "name", "组织维度")

# 维度状态查询
=TESTWPSUDFTREECOLLECTIONS_DIM_STATUS(A10)
=TESTWPSUDFTREECOLLECTIONS_DIM_STATUS(A10, "ROOT_HQ")

# 关键字搜索
=TESTWPSUDFTREECOLLECTIONS_DIM_FIND(A10, "财务", 20, "ROOT_HQ")

# 路径查询
=TESTWPSUDFTREECOLLECTIONS_DIM_PATH_NAMES(A10, "FIN_AR_01", " > ", "ROOT_HQ")

# 作用域编码
=TESTWPSUDFTREECOLLECTIONS_DIM_SCOPE_IDS(A10, "FIN", TRUE, TRUE, FALSE, ",", "ROOT_HQ")

# 事实表构建（全量）
=TESTWPSUDFTREECOLLECTIONS_FACT_BUILD(A10, FactData!A1:F193, "id", "", "", "ROOT_HQ")

# 事实表构建（预过滤）
=TESTWPSUDFTREECOLLECTIONS_FACT_BUILD(A10, FactData!A1:F193, "id", "month='2026-07'", "region='总部'", "ROOT_HQ")

# 层级汇总（全量）
=TESTWPSUDFTREECOLLECTIONS_FACT_AGG(A13, "FIN", "amount", "sum", TRUE, TRUE, FALSE, "", "", "ROOT_HQ")

# 层级汇总（运行时筛选）
=TESTWPSUDFTREECOLLECTIONS_FACT_AGG(A14, "FIN", "amount", "sum", TRUE, TRUE, FALSE, "month='2026-07'", "region='总部'", "ROOT_HQ")

# 事实表明细查看
=TESTWPSUDFTREECOLLECTIONS_FACT_TABLE(A13, "FIN", TRUE, TRUE, FALSE, "month='2026-07'", "region='总部'", 20, "ROOT_HQ")

七、功能特色

7.1 零外部依赖的 BI 分析

• 不需要数据库、不需要服务器、不需要网络
• 所有计算在本地内存完成，毫秒级响应
• 完全离线可用

7.2 与 SAP EPM/AO 的对比优势

7.3 零学习成本的 Excel 原生体验

• 全部功能通过 Excel 公式调用，财务用户无需学习新工具
• 函数名和参数命名直观，IntelliSense 智能提示
• 与 VLOOKUP、SUMIF、数据透视表等原生功能无缝配合

7.4 工业级健壮性

• 完善的错误处理：每个函数都有明确的错误提示
• 宿主忙碌检测：HostInteractionGuard 防止在 Excel 编辑状态下执行交互操作
• WPS 兼容处理：WpsBlankWorkbookCleaner 自动清理 WPS 启动时产生的空白工作簿
• 日志系统：AddInLog 记录插件全生命周期和异常信息

7.5 双宿主支持

• 同一套代码同时支持 Microsoft Excel 和 WPS 表格
• ExcelDnaUtil.IsET 自动检测宿主环境
• 分离的安装/卸载脚本（install_excel_addin.bat / install_wps_addin.bat）

7.6 构建与分发

• 一键构建：build_addin.ps1
• 构建产物自动复制到 dist/files/ 目录
• 双击 .bat 即可安装，无需手动配置注册表
• 支持 dist 目录整体分发，用户拿到即可使用

八、维度表与事实表规范

维度表要求

• 首行必须是表头
• 至少 2 列，且必须包含 id 和 parentid
• 其他列自动视为节点属性（如 name、leader、region、costcenter）
• 空 parentid 会被视为顶层根节点
• 如需多棵树，在同一张表里放多个顶层根节点，通过 rootNodeId 选择

事实表要求

• 首行必须是表头
• 至少包含一个 id 列，用于和维度节点编码关联
• 其他指标列按需传给 FACT_AGG
• 推荐保留 month、category、scenario 等筛选列，便于预过滤
• 筛选条件支持 month='2026-07' and category='actual' 格式

九、与其他蓝图的组合使用

wps-udf-tree-collections 是 Excel-DNA Skill 蓝图矩阵中的“专项蓝图”，可与以下蓝图组合：

组合示例： wps-ribbon-ctp-webview + wps-udf-tree-collections + wps-udf-live-cache = 全功能 BI 插件

十、技术要点总结

1. 内存对象跨公式共享：Excel-DNA 的 [ExcelHandle] 特性使树结构常驻内存，所有公式共享同一份快照
2. 路径缓存：构建时预计算所有节点的路径编码和路径名称，避免后续重复遍历
3. 异步构建：大数据量维度表/事实表构建不阻塞 Excel UI
4. SQL 风格筛选：自定义 SqlWhereFilter 解析器，支持字符串和数值比较
5. 一维多树：通过 rootNodeId 参数在同一个维度中表达多套层级体系
6. TreeCollections 内嵌：源码直接集成到项目中，无需额外 NuGet 依赖，编译即用
7. COM 互操作封装：ExcelWorkbookDataOps 完整封装了工作表的读写、ListObject 操作、格式设置等
8. 智能感知：ExcelDna.IntelliSense 为所有自定义函数提供函数名和参数提示

十一、系统要求

• 操作系统：Windows 7 SP1 及以上
• .NET 运行时：.NET 6 Desktop Runtime（x86 或 x64）
• Office 版本：Microsoft Excel 2010+ 或 WPS 2019+
• 位数：x86 或 x64，与 Office/WPS 位数一致

十二、许可说明

• 本项目为 Excel-DNA Skill 蓝图体系的组成部分
• 内嵌 TreeCollections 源码，其许可证见 docs/TreeCollections-LICENSE.txt
• Excel-DNA 运行时使用本地 Patched 版本，详见 runtime/exceldna/README.md