LlosLab Software Development Blog面向 PDF、Delphi 和文档软件开发者的工程笔记

技术文章

Delphi 里按值排透视表项：autoSortScope 与 -2

HotXLS Delphi Component

HotXLS 在 Delphi 里经 TXLSPivotField 上的两个属性按值排 PivotTable 项：SortType 选升序或降序，SortDataField 指名驱动排序的从零起计数据字段。HotXLS 打开 Excel 制作的 XLSX 时，会从 autoSortScope 元素恢复那个数据字段——Excel 在那里把 Values 字段存成 field="4294967294"，即 -2 的无符号写法

按值排序是每份销售报表迟早会提的需求："把地区从大到小给我看"。Excel 界面里是点两下的事。文件格式里，它是一个属性、一个嵌套的 pivot 区域、一个保留字段索引和一个选中的数据字段序号；少读其中任何一件的解析器照样若无其事地打开文件——只是按错误的东西排了序

Excel 把按值排序的度量存在哪？

Excel 只把排序度量存在 autoSortScope 里，不放在字段自身。<pivotField> 上的 sortType 属性（ECMA-376 Part 1 的 ST_FieldSortType 值：manual、ascending 或 descending）说明往哪个方向排。按哪个度量排则住在子元素 <autoSortScope> 里——它包着一个 <pivotArea>，其数据轴引用选中一个数据字段

<pivotField axis="axisRow" showAll="0" sortType="descending">
  <items count="5"> ... </items>
  <autoSortScope>
    <pivotArea dataOnly="0" outlineLevel="all" fieldPosition="0">
      <references count="1">
        <reference field="4294967294" count="1" selected="0">
          <x v="0"/>
        </reference>
      </references>
    </pivotArea>
  </autoSortScope>
</pivotField>

reference 元素通常按索引指向透视字段。字段索引 -2 在 ECMA-376 的 PivotTable 部件里保留给 Values 伪字段——两个以上度量同处一条轴时 Excel 显示为 "Values" 的那个。该属性类型是无符号 32 位整数，所以 -2 上线时是 4294967294。其下的 <x v="0"/> 选中数据字段 0，即 <dataFields> 的第一项。<pivotField> 上还有个 rankBy 属性，但在我们比对的文件里 Excel 都没写它，只靠 scope

Excel 把透视表按值排序存在 autoSortScope 的 pivotArea reference 里，字段 4294967294 是 Values 伪字段 -2 的无符号 32 位写法，其 x 子元素选中 HotXLS 排序所用的数据字段
嵌套的 reference 把抽象的排序方向变成具体的度量：x v=0 指向数据字段 0，即 HotXLS 在 TXLSPivotField 上暴露为 SortDataField 的那个序号

排序度量为什么读回来成了 -1？

HotXLS 读取器最初丢了排序度量，因为 4294967294 塞不进有符号 32 位整数。属性辅助函数是 StrToIntDef 的薄封装，超范围值静默返回默认值，于是数据轴测试永远不匹配，SortDataField 停在 -1，字段只剩 sortType="descending"、没挂度量

这个失败安静得可怕。没有度量的降序字段照样排——只是按项标签倒着排——于是 Excel 按总额排好的报表从 HotXLS 回来变成了项顺序反转。没有任何异常，每个单元格的数字全对。只有行序错了，而且只在 Excel 制作的文件上错

lxPivotXml.pas 里的修复在任何整数转换之前先按字符串比较原始属性 token。若 rankBy 已设了度量则以它为准；否则 field token 恰为 '4294967294' 的 reference 从其选中的 x 值解析 SortDataField。求值侧不需要改：比较器早已在行轴与列轴上敲定每成员总额、处理降序并打破并列。只是数据从没送到它手里

HotXLS 透视表读取器丢了 Excel 的按值排序，因为 4294967294 溢出有符号 32 位 StrToIntDef、SortDataField 停在 -1；修复在任何整数转换前先比较原始 token 字符串
旧路径把降序的按值排序无声退化成倒序标签排序；lxPivotXml.pas 里的新路径只要 field token 恰为 4294967294 就解析出度量

普遍教训适用于任何 Delphi 写的 OOXML 读取器。ECMA-376 把多个索引属性类型定为 xsd:unsignedInt，又拿范围顶端当哨兵，于是带默认回退的有符号读取器把保留值变成"属性缺失"。凡字段索引可能意为 Values 的地方，比较 token，或解析进 Int64 或 Cardinal

从 Delphi 代码按度量排透视项

要按值排 HotXLS 透视字段，把 SortType 设为 xlpfstAscending 或 xlpfstDescending，把 SortDataField 设为该度量在表 DataFields 列表里的从零起计索引。SortType = xlpfstManual（默认值）时 SortDataField 被忽略，项保持缓存或程序设定的顺序

uses
  SysUtils, lxHandleX, lxPivot;

var
  Book: TXLSXWorkbook;
  Data, Report: TXLSXWorksheet;
  Pivot: TXLSPivotTable;
  Region: TXLSPivotField;
begin
  Book := TXLSXWorkbook.Create;
  try
    Data := Book.Sheets.Add('Data');
    Data.Cells[1, 1].Value := 'Region';
    Data.Cells[1, 2].Value := 'Product';
    Data.Cells[1, 3].Value := 'Units';
    // ... 填充第 2..41 行 ...
    Report := Book.Sheets.Add('Report');

    Pivot := Report.AddPivotTable('Data!A1:C41', 3, 1, 'UnitsByRegion');
    Region := Pivot.AddRowField('Region');
    Pivot.AddDataFieldByName('Units', xlpaSum);   // 数据字段 0

    Region.SortType := xlpfstDescending;  // 大区在前
    Region.SortDataField := 0;            // 按 Units 求和排
    Region.AutoSortScope := '0';          // 同时输出 Excel 自己的 autoSortScope

    if Book.MaterializeNativePivotTable(Pivot) <> 1 then
      raise Exception.Create('Pivot layout was rejected');
    Book.SaveAs('units-by-region.xlsx');
  finally
    Book.Free;
  end;
end;

两个细节值得记一笔。HotXLS 写入器把 SortDataField 写成 rankBy，且 AutoSortScope 非空时还会写一个带 4294967294 数据轴引用的 autoSortScope 块，把属性字符串放进 x v 槽位。加载时 AutoSortScope 收到同一个 x v token，所以按值排序时属性里装着作为文本的数据字段序号。设成 '0' 因此产出在两处都带度量的文件——最接近 Excel 自己写法的选择。MaterializeNativePivotTable 随后把排序后的网格与布局元数据一起暂存，成功返回 1，失败返回 -1 并带诊断

被排的不只是项行。列字段过同一个比较器，只是用每列总额代替每行总额，所以带 SortType = xlpfstDescending 和有效 SortDataField 的列字段同样按值给列排序

检查 Excel 工作簿已带的排序

对 Excel 制作的文件，HotXLS 在加载透视定义时就填好 SortType、SortDataField 和 AutoSortScope，所以审计或修改报表排序不必重建表。下面的代码段列出每个按度量排序的字段，并按 DisplayName 报出度量名

var
  Book: TXLSXWorkbook;
  Pivot: TXLSPivotTable;
  Field: TXLSPivotField;
  I: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('sales-report.xlsx');
    Pivot := Book.Sheets.SheetByName('Report').PivotTables[0];
    for I := 0 to Pivot.FieldCount - 1 do
    begin
      Field := Pivot.Fields[I];
      if (Field.SortType <> xlpfstManual) and (Field.SortDataField >= 0) then
        Writeln(Field.Name, ' sorted by ',
          Pivot.DataFields[Field.SortDataField].DisplayName);
    end;

    // 把 Region 排序翻成升序并重建既有网格
    Field := Pivot.FindFieldByName('Region');
    if Field <> nil then
      Field.SortType := xlpfstAscending;
    if Book.MaterializeNativePivotTable(Pivot, True) <> 1 then
      raise Exception.Create('Could not re-materialize the pivot');
    Book.SaveAs('sales-report-asc.xlsx');
  finally
    Book.Free;
  end;
end;

MaterializeNativePivotTable 的第二个参数告诉 HotXLS 采用透视表现有的输出范围，而不是把它当冲突。HotXLS 的 Show Values As 排名与累计汇总指南里出现同一个模式——排序次序在那里要紧，因为累计汇总按显示顺序累加

两个项值相同时 HotXLS 怎么打破并列？

HotXLS 按项标签确定性地打破并列——这是与 Excel 的已知差异。两个成员总额相同时，比较器回退去比较它们的 caption，降序标志连这个比较也反转。度量不是有效数字的项在升序里排在有效项之后，降序标志反转整个比较，包括这个分组

Excel 根本不承诺任何并列次序。在一个四个成员 A、B、C、D 总额全同的测试夹具里，Excel 的升序排出 B、C、A、D。存储的元数据里没有任何东西预测这个排列；它是不稳定排序的副产品，文件与文件之间还可以不同。同样的输入 HotXLS 返回 A、B、C、D。这个用例的验证核对成员集合与每成员的值而不是序列，15/15 并列的降序夹具与 Excel 逐单元格一致。下游代码依赖并列行确切位置的话，按第二个度量排序，或让值互不相同

HotXLS 在 Delphi 里按项 caption 确定性地打破 Excel 透视表按值排序的并列：四成员夹具上返回 A、B、C、D，而不稳定的 Excel 排序给出 B、C、A、D
总额相同落进 caption 比较、降序标志同样反转它，所以两个引擎之间只可能差并列行的次序

折叠成员与最内层字段

折叠成员在 HotXLS 里物化为每个折叠项一行聚合。Excel 隐藏某项的细节时会在那个 <item> 上写 sd="0"；HotXLS 把它暴露为 TXLSPivotItem.IsDetailHidden。折叠项以普通标签渲染、替换其整个子树，折叠对该项在每个父级下都生效，而外围块保留自己的小计

var
  Category: TXLSPivotField;
begin
  // 透视表行上有 Category（外层）与 Product（内层）
  Category := Pivot.FindFieldByName('Category');
  if (Category <> nil) and (Category.ItemCount > 0) then
    Category.Items[0].IsDetailHidden := True;   // 折叠第一个类别
  if Book.MaterializeNativePivotTable(Pivot, True) <> 1 then
    raise Exception.Create('Collapse was rejected');
end;

折叠与排序的交互可以预测：折叠行仍是其字段的成员，所以它像其他项一样按总额占位。最内层字段不同：它没有更内层，无细节可折叠，而且自早前一次修复起，HotXLS 也不再为它渲染小计行——哪怕 defaultSubtotal 标志置位。Excel 在单字段与多级轴上行为相同；那次修改之前 HotXLS 可能在最内层字段下吐出幻影 Total 行。这里涉及的字段级属性，HotXLS 走读 CT_PivotField 模式有效性讲了模式对 <pivotField> 及其 items 的要求

对着 Excel 自动化核对时的坑

经 COM 自动化把 HotXLS 排序与 Excel 对照时，调 AutoSort 要用度量的本地化 caption，一字不差照 Excel 显示的写。中文版 Excel 上，求和的 Units 字段默认 caption 是 '求和项:Units'；在那台机器上传 'Sum of Units' 不报错、也不改任何东西。同一段脚本在英文 Excel 上好使，到别处就无声变成 no-op，所以 caption 从数据字段读，别硬编码

  • 我们测的那个 Excel 版本里，PivotField.SortType 经 COM 读不回来，所以从保存的 XML 或渲染后的单元格验证排序
  • 同一版本里，把手动分组父字段放上同一条轴、又在被排序字段之外，会压制按值的成员排序；手动分组父级之下的排序渲染未经验证
  • PivotField.Outline 与 PivotField.LayoutForm 的写入在那个版本同样被拒，所以夹具的大纲布局改用 PivotTable.RowAxisLayout 设置

这些怪癖都不改文件格式，但决定回归测试能不能证明任何东西。用错 caption 调 AutoSort 的测试，对一个从未被排序的透视表照样通过

FAQ

PivotTable XML 文件里的字段 4294967294 是什么意思？

字段 4294967294 是字段索引 -2 的无符号 32 位编码，ECMA-376 把 -2 保留给 Values 伪字段。在 autoSortScope 里它标记一条数据轴引用，其 x 子元素选中项按哪个数据字段排序

没装 Excel 时 HotXLS 能按值排透视项吗？

能。HotXLS 物化透视网格时自己求值排序，用所选数据字段的每成员总额，并写出 sortType、rankBy 和可选的 autoSortScope，让 Excel 刷新后保持同一顺序。只有等值之间的并列次序可能与 Excel 不同

为什么我的透视项按字母排而不是按值排？

通常因为字段有排序方向却没挂度量。检查 SortType 不是 xlpfstManual、SortDataField 落在 0 到 DataFieldCount - 1 之间；是 -1 的话，HotXLS 按标签按请求方向排序

按值排序、折叠成员和原生透视管线的其余部分都随 HotXLS，读写 XLS 与 XLSX 的 Delphi 表格组件发布，不需要安装 Excel 即可读取、编辑和写出 Excel 工作簿。要从零开始建透视表的话，HotXLS 的透视表构建与刷新教程在讲到排序之前先讲了缓存、字段与刷新