LlosLab 软件开发博客面向 PDF、Delphi 和文档软件开发人员的技术笔记

技术文章

使用 HotXLS 在 Delphi 中构建和刷新 Excel 数据透视表

HotXLS 组件

HotXLS 在没有安装 Excel 且没有 COM 自动化的机器上，从 Delphi 和 C++Builder 构建并刷新原生的 XLSX 数据透视表。您通过源范围调用 AddPivotTable，将字段拖放到行、列、页和数据轴上，层叠上计算项或总计百分比显示，然后该组件写入 pivotCacheDefinition 和 pivotTableDefinition 部分，Excel 打开它们即可作为活动的、可刷新的数据透视表

让这些努力变得有价值的场景是报表服务器。您每晚生成数百个工作簿，每个工作簿都带有一个汇总一个账户的数据透视表，下个月数据变动，每个文件都必须反映新的源数据行。从 Windows 服务驱动 Excel 是脆弱的，且未经服务器使用许可，而手写透视表的 XML 是一项永远不会结束的规范考古工程。HotXLS 处于这两个死胡同之间：一个针对 OOXML 数据透视表部分的类型化对象模型，因此填充单元格的同一 Pascal 也可以在同一进程中声明数据透视表并重建其缓存

如何在没有 Excel 的情况下在 Delphi 中创建数据透视表？

您只需一次调用即可创建它，然后将字段放置在轴上。AddPivotTable 接受 A1 表示法的源范围、表格锚定的目标单元格以及名称；它解析该范围，扫描每一列以推断其数据类型，构建透视缓存（或重用已绑定到相同范围的缓存），并返回一个 TXLSPivotTable，其字段最初都处于轴外。从此，便捷方法 AddRowField, AddColumnField, AddPageField, 和 AddDataFieldByName 连接布局，并且每个数据字段都从 TXLSPivotAggregation 枚举中获取 11 种聚合之一（xlpaSum, xlpaCount, xlpaAverage, xlpaMax, xlpaMin, xlpaProduct, xlpaCountNums, xlpaStdDev, xlpaStdDevP, xlpaVar, xlpaVarP）

uses
  lxHandleX, lxPivot;

var
  Book : TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  Pivot: TXLSPivotTable;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('sales.xlsx');
    Sheet := Book.Sheets[1];                 // 报表工作表（在 XLSX 引擎中基于 1）

    // 'Data' 工作表上的源 A1:E500；将数据透视表锚定在第 3 行第 1 列
    Pivot := Sheet.AddPivotTable('Data!$A$1:$E$500', 3, 1, 'SalesByRegion');
    if Pivot <> nil then
    begin
      Pivot.AddRowField('Region');
      Pivot.AddColumnField('Quarter');
      Pivot.AddPageField('Year');
      Pivot.AddDataFieldByName('Revenue', xlpaSum);
      Pivot.AddDataFieldByName('Units', xlpaAverage);
      Book.SaveAs('sales-pivot.xlsx');
    end;
  finally
    Book.Free;
  end;
end;

为什么缓存和数据透视表是两个独立的部分

数据透视表实际上是互相引用的两个制品，理解这种拆分是理清其余内容的关键。pivotCacheDefinition 是数据快照：指向源范围的 worksheetSource，以及每列一个用于保存该列相异值（其 sharedItems）及派生边界的 cacheField。pivotTableDefinition 是视图：哪个缓存字段位于哪个轴上、数据字段及其聚合、布局切换。视图通过工作簿的 pivotCaches 关系，通过 cacheId 绑定到缓存，这正如 ECMA-376 Part 1 §18.10 和 [MS-XLSX] 所规划的那样

这种间接性并不是官僚作风，它带来了两个好处。一个缓存可以支持多个表格，因此刷新一次缓存就会更新从中绘制的每个视图。并且缓存使用每条记录索引表而不是原始网格来去重存储每列的值，这就是为什么构建透视表意味着扫描源，而不是复制单元格。HotXLS 对两个引擎的建模方式相同，因此上面的代码与 经典 .xls 数据透视表背后的二进制 SX 记录布局 旁边记载 of 经典路径几乎完全相同。如果您的源存在于另一个工作表上，或者您通过名称寻址它，范围前缀和 已定义名称和跨工作表引用 遵循通常的 A1 规则，允许对包含空格的名称使用带引号的工作表名称

计算项不是计算字段

这三个术语指代三种不同的东西，混淆它们是经典的透视表错误。计算项（Calculated Item）存在于单个字段内部，并通过名称组合该字段自身的项，因此在 Region 字段内，您可以定义一个等于 North 加 South 的合成 CoreMarkets 行；HotXLS 将其暴露为 TXLSPivotField.AddCalculatedItem，并在该字段的 <calculatedItems> 下写入 <calculatedItem>。计算字段（Calculated Field）则不同：它是从其他列派生的新值，例如从 Revenue 和 Cost 派生的 Margin，它通过 TXLSPivotCacheField.Formula 作为公式承载在缓存字段上。通过 TXLSPivotTable.AddCalculatedMember 添加的计算成员（Calculated Member）是表格级的自定义成员，可以作为度量（成员类型为 data）或维度成员，主要对 OLAP 形状的数据透视表有用

var
  Region: TXLSPivotField;
  Member: TXLSPivotCalculatedMember;
begin
  // 计算项通过名称合并单个字段的项
  Region := Pivot.AddRowField('Region');
  Region.AddCalculatedItem('CoreMarkets', '=North+South');

  // 计算成员在表格级声明。成员类型
  // 'data' 将其标记为度量；空类型是维度成员
  Member := Pivot.AddCalculatedMember('AvgTicket', '=Revenue/Units');
  Member.MemberType := 'data';
end;

一个客观的边界适用于所有这三者。HotXLS 将公式文本发射到定义 XML 中，并不评估它。Excel 在打开文件时计算计算项、字段或成员，这与它计算网格中每个聚合的方式相同。HotXLS 正在编写指令，而不是结果，因此您提供的公式必须是 Excel 自身方言中的有效透视公式，像 Excel 那样引用字段和项的名称

如何将值显示为总计的百分比？

您在数据字段上设置值显示模式，而不是自己转换数字。TXLSPivotDataField.ShowDataAs 接受 TXLSPivotShowDataAs 枚举，该枚举镜像了 OOXML 的 ST_ShowDataAs 值：xlpsdaNormal, xlpsdaDifference, xlpsdaPercent, xlpsdaPercentDiff, xlpsdaRunTotal, xlpsdaPercentOfRow, xlpsdaPercentOfCol, xlpsdaPercentOfTotal, 和 xlpsdaIndex。常见的技巧是在数据轴上放置相同的源列两次，一次作为原始和，另一次作为总计的份额，这样报告就能同时显示数字及其权重

var
  Rev, Share: TXLSPivotDataField;
begin
  Rev := Pivot.AddDataFieldByName('Revenue', xlpaSum);

  Share := Pivot.AddDataFieldByName('Revenue', xlpaSum);
  Share.DisplayName := 'Share of total';
  Share.ShowDataAs  := xlpsdaPercentOfTotal;

  // 项相对模式需要一个与之对比的基准。一个沿着
  // 'Quarter'（缓存字段索引 3）向下的累计值读取为
  // Share.ShowDataAs := xlpsdaRunTotal;
  // Share.BaseField  := 3;         // 转换运行的缓存字段索引
  // Share.BaseItem   := $7FFD;     // $7FFD = "(All)"
end;

xlpsdaPercentOfTotal 不需要基准，因为它是相对于总计的，但项相对模式需要。xlpsdaDifference、xlpsdaPercentDiff 和 xlpsdaRunTotal 需要 BaseField（比较运行的缓存字段索引），对于项锚定形式，还需要一个 BaseItem 索引，其中 $7FFD 代表 (All) 哨兵。HotXLS 将这些写入为 <dataField showDataAs="percentOfTotal" baseField="N" baseItem="M"/> 属性，并且与计算公式一样，将算术留给 Excel

分组日期和数字，以及页字段过滤器

分组是在缓存字段上配置的，而不是在透视字段上，因为它改变了源域的分桶方式。在 Cache.FindFieldByName 返回的缓存字段上设置 HasGroup := True，然后选择数值范围调节（GroupStartNum、GroupEndNum、GroupInterval）或日期层次结构标志（GroupByDate 配合 GroupMonths、GroupQuarters、GroupYears，以及 GroupStartDate / GroupEndDate 跨度）。HotXLS 发射慢速匹配的 <fieldGroup><rangePr groupBy="months"/> 元素，因此按月或按 1000 宽的数值段分组的字段会以 Excel 相同的方式进行分组

页字段是表格上方的过滤器下拉菜单。AddPageField 将字段放置在页轴上，并且 PageItemIndex 预先选择单个缓存项（默认值为 xlPageItemAll，即 $7FFD 代表 (All)）。为了让读者一次勾选多个项目，设置 MultipleItemSelectionAllowed := True，HotXLS 将其写入为 <pivotField multipleItemSelectionAllowed="1"/>。对于超出手动选择的条件，每个字段都携带一个类型化的 Filters 集合，涵盖了 OOXML 的 ST_FilterType 系列（数量、百分比、求和、标题、值和日期过滤器），每个条目都将过滤器类型与其比较值配对

当源改变时，如何刷新透视表缓存？

TXLSXWorkbook 上的 RefreshPivotCache 会重新扫描源范围并在原地重建缓存，这正是批处理管道在编辑底层行之后所需要的。传递缓存 ID，该方法将重新读取源范围中的每个单元格（第一行为表头，第二行开始为数据），通过重新去重值并重新派生每个类型的边界来重建每个字段的共享项域，并重写每条记录的项索引。它在成功时返回 1，在缓存 ID 未知或源工作表缺失时返回 -1

var
  Rc: Integer;
begin
  // ...数据透视表构建后源数据已发生改变...
  Sheet := Book.Sheets[1];
  Sheet.Cells[2, 5].Value := 128000;             // 一个更正的 Revenue 数字

  // 从源重建缓存的共享项和记录索引
  Rc := Book.RefreshPivotCache(Pivot.CacheId);   // 1 = 已刷新，-1 = 缓存/源缺失
  if Rc = 1 then
    Book.SaveAs('sales-pivot-refreshed.xlsx');
end;

这里的语义边界值得明确说明。在此方法存在之前，HotXLS 依赖于 refreshOnLoad="1" 标志，并将所有的刷新工作留给下次打开时的 Excel，这在人类打开文件时没有问题，但在必须交付正确数据的无头管道中是无用的。RefreshPivotCache 在进程内使保存的缓存保持最新，并且由于表格通过 ID 绑定 to 缓存，从该缓存绘制的每个透视表都会看到该更新。它不做的是布局或聚合可见网格——当文件打开时，Excel 仍会从刷新后的缓存中重新计算渲染的表格

HotXLS 写入的内容与 Excel 计算的内容

牢记这种分工，就没有什么会令您惊讶。HotXLS 是一个定义写入器：它产生 pivotCacheDefinition、缓存记录和 pivotTableDefinition，连同轴、聚合、计算项和成员、值显示、分组和过滤器。Excel 是计算器：在打开时，它具体化分组桶、评估计算公式、应用 showDataAs 转换并聚合正文。用户看到的值是 Excel 从 HotXLS 编写的指令中产生的，这就是为什么每个公式和基础索引在写入时必须正确，而不是在渲染时检查的原因

在围绕此功能进行设计之前，有两个局限值得了解。AddPivotTable 解析带有一个可选工作表前缀的矩形 A1 范围；命名范围和外部工作簿源在构建器解析范围之外，但从 Excel 编写的文件中读取的缓存将在往返中保留其命名范围源。并且 HotXLS 从磁盘读取的 Excel 创建的数据透视表在保存时会按字节进行重放，因此这里描述的类型化编辑可以干净地适用于您在代码中构建的透视表，而现有的透视表则保持无损。对于报告的输入端（透视表汇总的经过验证的单元格和过滤后的表），请参见 数据验证、自动筛选和结构化表格

这里展示的数据透视表模型是适用于 Delphi 和 C++Builder 的标准 HotXLS Delphi Excel Component 的一部分，它在同一个对象模型中读写 XLSX 类型化透视表部分和经典的 BIFF8 记录