HotXLS 从 Delphi 设置 Excel 透视表的 “Show Values As” 模式，靠的是 TXLSPivotDataField 上的两个属性：ShowDataAs 负责经典模式（累积总计、差异、行百分比等），ExtendedShowDataAs 负责排名、父级百分比和累积总计百分比。TXLSXWorkbook.MaterializeNativePivotTable 随后按 Excel 的方式写出计算好的网格。两个属性互斥，扩展那组还依赖一个 Excel 区分大小写匹配的扩展 URI
最后这个细节正是本文存在的原因。生成一个带排名列的透视表看着是小事，第一次打开通常也确实是。麻烦从用户在 Excel 里点“刷新”开始：排名列悄悄变回普通的求和，没有任何警告。把 Show Values As 做对，意味着两套 XML 词汇都要做对，外加一堆 Excel 从不在界面上说明的数值规则：并列时怎么排名、平均值的累积百分比是什么含义、哪个空单元格该是空白还是错误
一个 Excel 菜单为什么对应两个属性
Excel 显示的是一个 “Show Values As” 菜单，但文件把它存在两个不同的地方，所以 HotXLS 暴露两个属性。Excel 2007 的原始模式存在 pivotTableDefinition 内 dataField 元素的 showDataAs 属性里，用的是 ECMA-376 的 ST_ShowDataAs 枚举。Excel 2010 新增的模式——排名、父级百分比和累积百分比——塞不进那个枚举，于是进了带 pivotShowAs 属性的 x14:dataField 元素，包在同一个数据字段的 extLst 扩展块里
TXLSPivotShowDataAs 对应第一组：xlpsdaNormal、xlpsdaDifference、xlpsdaPercent、xlpsdaPercentDiff、xlpsdaRunTotal、xlpsdaPercentOfRow、xlpsdaPercentOfCol、xlpsdaPercentOfTotal 和 xlpsdaIndex。TXLSPivotExtendedShowDataAs 覆盖第二组：xlpesdaPercentOfParent、xlpesdaPercentOfParentRow、xlpesdaPercentOfParentColumn、xlpesdaPercentOfRunningTotal、xlpesdaRankAscending 和 xlpesdaRankDescending，另有 xlpesdaNone 作为中性取值。一个数据字段要么用这一组，要么用那一组，绝不同时用。如果你把 ShowDataAs 留在非 normal 值上、又设了扩展模式，MaterializeNativePivotTable 会返回 -1，现有输出原封不动——单元格和透视表边界都算在内
BaseField 和 BaseItem 携带比较目标。BaseField 是透视表字段列表里的零起索引，不是源列标题名的索引，它驱动差异比较、累积总计、对指定父级的百分比和排名。BaseItem 是该字段内零起的条目位置，或者是表示“与相邻项比较”的常量 xlPivotBaseItemPrevious 和 xlPivotBaseItemNext。父行和父列百分比完全无视 BaseField；它们取匹配轴上最内层的字段
在 Delphi 里构建带排名的透视表
最短可用路径是：构建透视表，在数据字段上设显示模式，保存前实体化。下面的例子把各大区金额按类别降序排名，每行最大的金额是第 1 名
uses
SysUtils, lxHandleX, lxPivot;
var
Book: TXLSXWorkbook;
Data, Report: TXLSXWorksheet;
Pivot: TXLSPivotTable;
Measure: TXLSPivotDataField;
begin
Book := TXLSXWorkbook.Create;
try
Data := Book.Sheets.Add('Data');
Data.Cells[1, 1].Value := 'Category';
Data.Cells[1, 2].Value := 'Region';
Data.Cells[1, 3].Value := 'Amount';
// ... 填充第 2..7 行 ...
Report := Book.Sheets.Add('Report');
Pivot := Report.AddPivotTable('Data!A1:C7', 4, 3, 'RegionRank');
Pivot.AddRowField('Category'); // 透视表字段 0
Pivot.AddColumnField('Region'); // 透视表字段 1
Measure := Pivot.AddDataFieldByName('Amount', xlpaSum);
Measure.DisplayName := 'Rank by Region';
Measure.ShowDataAs := xlpsdaNormal; // 基础模式保持关闭
Measure.ExtendedShowDataAs := xlpesdaRankDescending;
Measure.BaseField := 1; // 跨 Region 排名
if Book.MaterializeNativePivotTable(Pivot) <> 1 then
raise Exception.Create('Pivot display mode was rejected');
Book.SaveAs('region-rank.xlsx');
finally
Book.Free;
end;
end;这里有两个习惯值得养成。其一，选了扩展模式就显式设 ShowDataAs := xlpsdaNormal，哪怕字段是全新的，因为同一段代码常常被拿去处理导入的透视表，而它的数据字段可能已带基础模式。其二，检查返回值。被拒绝的实体化是原子的，这很好，但也意味着漏了检查就会发出一份带着旧网格的文件
就算从不调用 MaterializeNativePivotTable，BaseField 也会随保存留存。扩展模式一设，HotXLS 就会为父级百分比、累积总计百分比和两种排名模式写出 baseField 属性，所以配置完直接保存的工作簿，在 Excel 刷新时也知道该跨哪个字段排名
扩展 URI 会出什么岔子
携带 pivotShowAs 的扩展块由 URI {E15A36E0-9728-4e99-A89B-3F7291B0FE68} 标识，Excel 刷新时区分大小写地比较这个字符串。注意 4e99 里的小写 e。GUID 通常大小写不敏感，很多 XML 写入器会把它们转成大写——这恰好就是故障模式：带 4E99 的文件在 Excel 里打开，所有缓存单元格都正确，因为 Excel 只是照着存好的值画。可一旦点了 RefreshTable，Excel 就不再认识这个扩展，丢掉扩展模式，把那一列按普通求和重算。没有修复提示，没有报错，只有一个悄悄变成合计的排名列
HotXLS 按 Excel 期望的原样写出这个 URI，所以只有下游有东西重写了包——比如把 GUID 大小写规范化的后处理步骤、或重建 extLst 元素的步骤——才会踩到它。能抓住它的测试很便宜：在 Excel 里打开生成的文件，刷新透视表，确认扩展列显示的仍是排名或百分比而不是原始聚合。只读缓存单元格的测试，对着坏文件每次都会通过
Excel 如何处理并列排名与累积总计的顺序
Excel 的透视表排名是密集排名（dense rank），HotXLS 如实复刻。金额降序 30、30、20，结果是 1、1、2，而不是工作表里 RANK.EQ 那种竞赛式的 1、1、3。并列算作一个不同的前序值。如果报表需要竞赛式排名，请在源数据或透视表旁的工作表公式里算，因为没有任何透视表显示模式能产出它
累积总计、累积总计百分比和前一项/后一项比较，跟随成员实际出现的顺序，而不是字段条目列表的顺序。缓存建好之后有人改了排序，这个区别就开始咬人
// 行按降序排：累积总计按显示顺序累加
Pivot.Fields[0].SortType := xlpfstDescending;
Measure.ExtendedShowDataAs := xlpesdaNone;
Measure.ShowDataAs := xlpsdaPercentDiff; // 与……的百分比差异
Measure.BaseField := 0; // ……对 Category 字段
Measure.BaseItem := xlPivotBaseItemPrevious; // ……前一个可见成员
if Book.MaterializeNativePivotTable(Pivot) <> 1 then
raise Exception.Create('Pivot display mode was rejected');SortType 改成 xlpfstDescending 后，“前一个”指显示在上方那个成员，累积总计从排序列表顶端开始。改为遍历存储条目列表的实现，产出的数字看着合理，但只要有人一刷新就和 Excel 对不上。边缘成员同样要小心：第一个成员没有“前一个”，且只有百分比模式让边缘成员与自己比较；差异模式把那个单元格留给 Excel 自己的边缘行为，而不是发明一个 0
累积百分比为什么会超过 100%
对平均值做累积总计百分比，超过 100% 是合法的，因为 Excel 累加的是各成员显示出来的聚合值，再除以一个并不等于它们之和的聚合。对求和来说两者是一回事，所以最后一个成员恰好落在 100%；对平均值来说，三个类别平均值相加再除以总体平均值，不是任何东西的份额，末行可以远超 100%。HotXLS 在这里刻意与 Excel 保持一致而不是把值夹住，因为夹住的数字会和刷新后的文件对不上
除数还取决于基础字段坐在哪。基础字段是其轴上最内层字段时，除数是该轴位置上的原始聚合；嵌套布局里基础字段是外层字段时，除数是其余轴上匹配成员之和。两个位置都独立对照过 Excel 输出，因为只在一个嵌套层级成立、在另一层级悄悄失效的规则，正是透视表 bug 的经典长相
简单百分比与累积百分比行为不同。行、列和总计百分比在相关范围内除以原始聚合，对平均值这类不可加聚合也一样，所以平均值上的总计百分比是单元格平均值除以总体平均值，而不是某个加总池子的份额
比较模式里的空白、#NULL! 与 #DIV/0!
对基百分比比较区分三种空的情况，每种产出不同的单元格。分子交集没有源行时，单元格显示 #NULL!；基础条目在该交集没有数据时，单元格是空白；基础条目存在且聚合为零时，单元格显示 #DIV/0!。读取实体化网格的代码应该用 VarIsEmpty 和 VarIsError 测试，而不是假设每个值单元格装的都是数字
简单百分比宽容些：分子缺席按零算。父行和父列百分比多一种情况——透视表在对应轴上没有字段时就没有父级可除，所以结果为空白。总计同样遵循各模式自己的语义，包括比较或累积总计所走轴上的空白总计；累积总计下面一整行空白是正确的，不是实体化失败
在导入的透视表上切换模式
改一个来自既有工作簿的透视表的显示模式，流程相同，只多一个标志。把 True 传给 AAdoptExisting，MaterializeNativePivotTable 就可以接管 Excel 已为该透视表写好的单元格
Pivot := Book.Sheets.SheetByName('Sales').PivotTables[0];
with Pivot.DataFields[0] do
begin
ShowDataAs := xlpsdaNormal; // 清掉基础模式
ExtendedShowDataAs := xlpesdaPercentOfParentRow; // 无需 BaseField
end;
if Book.MaterializeNativePivotTable(Pivot, True) <> 1 then
raise Exception.Create('Could not re-materialize the pivot');模式变化时，HotXLS 会从暂存的定义里移除过时的 showDataAs、pivotShowAs 和 baseItem 属性，保存的文件绝不会在新的扩展模式旁边带着陈旧的基础模式。同一个暂存步骤还让数据和非活动字段上的紧凑与分级标志与报表布局保持同步，这一点很重要，因为 Excel 刷新后的数据标题是从这些标志推导的，不只看行字段和列字段。非法枚举值同样被拒：把 255 这个字节 cast 进任一枚举，在 Delphi 里可能符号扩展成 -1，所以 HotXLS 上下界都查，返回 -1 且不碰网格
如果还要往同一张报表里加计算字段或计算项，先读在 HotXLS 中构建并刷新带计算字段的透视表；想知道哪些字段级 XML 规则能避免 Excel 修复文件，见Delphi 中的 XLSX 透视表字段模式有效性
FAQ
HotXLS 数据字段能同时用 ShowDataAs 和 ExtendedShowDataAs 吗
不能。TXLSPivotDataField.ShowDataAs 和 ExtendedShowDataAs 互斥；两者都设时，MaterializeNativePivotTable 返回 -1，先前的输出原样保留
为什么我的透视表排名在 Excel 刷新后变成了求和
扩展 URI 多半被改写了大小写。Excel 区分大小写地匹配 {E15A36E0-9728-4e99-A89B-3F7291B0FE68}，匹配不上时 RefreshTable 之后退回普通聚合
HotXLS 透视表排名在并列后会跳号吗
不会。HotXLS 遵循 Excel 的密集透视表排名，30、30、20 排为 1、1、2
Show Values As 支持、原生透视表实体化以及其余 XLSX 和 XLS 功能集，都随面向 Delphi 和 C++Builder 的 HotXLS Delphi spreadsheet component 提供