pivotField 元素上的 axis="rowAxis" 属性读起来是通情达理的 XML,把它映射成「行字段」的读取器也能让数据透视表带着轴上每一个字段加载成功。可把同一个部件丢进按 ECMA-376 第 1 部分 §18.10 schema 造的校验器,它在第一个属性上就挂了:ST_Axis 恰好四个令牌——axisRow、axisCol、axisPage、axisValues——rowAxis 不在其中。数据透视表写出器得让 schema 满意,而不是让自己人满意,因为打开文件的人手里是 Excel 和他们自己的解析器
透视字段的定义里满是这类规矩。有三条最容易从往返测试眼皮底下溜走:枚举令牌、items 元素不许为空的要求,以及 Excel 排在数据项后面的那些尾部小计条目。还有两条蹲在读取侧,藏在 Excel 因为取的是 schema 默认值而干脆不写的属性里。本文余下部分逐条拆解,展示 XML 该长什么样,以及产出它的 Delphi 调用
为什么往返测试抓不住错的轴令牌
往返测试只证明写出器和读取器彼此合拍,仅此而已。若读取器对 axisRow 和 rowAxis 来者不拒,写错令牌的写出器能通过你写得出的每一次保存-重载检查,因为错误在读取回来的路上就被修好了。故障只在更严的消费者打开部件时现形:schema 校验器、从 XSD 生成的解析器,或者 Excel 判定部件需要修复的那一刻
HotXLS 有意把两侧分开对待。XLSX 写出器只发那四个 ST_Axis 令牌;透视读取器则额外把长得像英语的 rowAxis、colAxis、pageAxis、dataAxis 也映射到对应轴,用这些拼写的生产方产出的文件照样带着完整布局加载。输出按 schema 从严;输入只要求认得出来。反过来——严读取器配马虎写出器——至少在你自己的测试里会炸得很响;宽容读取器配马虎写出器,则在你看得见的任何地方都不炸
<!-- 非法:rowAxis 不是 ST_Axis 的取值,且 CT_Items 不允许为空 -->
<pivotField axis="rowAxis" defaultSubtotal="1"><items count="0"></items></pivotField>
<!-- 合法:ST_Axis 令牌、真实条目、末尾默认小计 -->
<pivotField axis="axisRow" defaultSubtotal="1">
<items count="4"><item x="0"/><item x="1"/><item x="2" h="1"/><item t="default"/></items>
</pivotField>
轴之外 CT_PivotField 还要求什么
CT_PivotField 还有三条容易掉的要求。在值区参与聚合的字段必须在自己的定义上用 dataField="1" 说明这一点——哪怕 <dataFields> 列表已经引用了它。HotXLS 给 DataFields 里每个条目指向的透视字段都设这面旗,所以用 AddDataFieldByName 加进来的字段两处都有标记
第二条是结构规则。CT_Items 要求至少一个 item 子元素,于是 <items count="0"></items> 无效——尽管它是良构的 XML,哪儿都解析得动。字段没有条目时,HotXLS 干脆整个省掉 items 元素。第三条关乎每个条目上的状态:隐藏成员带 h="1",折叠了明细的成员带 sd="0"。两者都来自 TXLSPivotItem.IsHidden 和 TXLSPivotItem.IsDetailHidden,每次保存都写,代码里设的筛选因此能活着进 Excel
尾部小计条目与 count 陷阱
字段有条目时,Excel 会在数据项后面为每个小计函数跟一个额外的 item,用 ST_ItemType 标类型。自动小计是 <item t="default"/>;显式函数用 sum、countA、avg、max、min、product、count、stdDev、stdDevP、var 和 varP。这些条目不带指向缓存的 x 索引,但计入 items 的 count 属性。省略它们的写出器,产出的 items 列表与同一元素上的小计标志对不上号
HotXLS 保存时从 TXLSPivotField.Subtotals 推导这条尾巴:集合里每个函数对应一个 t="…" 条目,按枚举顺序排,计入 count,另在字段上写对应的 sumSubtotal="1" 风格属性。AddPivotTable 建出的字段初始集合为空,写出的是 defaultSubtotal="0",尾部一个条目都没有。生成报表时保持空集合就好:小计行改变透视表的高度,把摆在下面的东西往下顶。只在读者确实需要那几行的字段上往 Subtotals 里加函数
挑函数之前,命名值得多看一眼。xlpsCount 映射到 countA——数所有非空条目;xlpsCountNums 映射到 count——只数数字。谁在 XML 里看到「count」就伸手拿 xlpsCount,谁就拿走了另一个函数
下面的程序在既有数据表上建一个透视表:一个行字段、一个列字段、一个值字段。Region 字段拿了自动小计加平均值,数据项后面于是跟两条尾部条目,Revenue 则带上 dataField="1"
uses
SysUtils, lxHandleX, lxPivot;
var
Book : TXLSXWorkbook;
Sheet : TXLSXWorksheet;
Pivot : TXLSPivotTable;
Region: TXLSPivotField;
begin
Book := TXLSXWorkbook.Create;
try
Book.Open('orders.xlsx');
Sheet := Book.Sheets[1]; // Sheets 从 1 数起
Pivot := Sheet.AddPivotTable('Data!$A$1:$D$800', 3, 6, 'RegionTotals');
if Pivot = nil then
raise Exception.Create('Bad source range or anchor');
Region := Pivot.AddRowField('Region'); // 没有该字段时为 nil
if Region <> nil then
Region.Subtotals := [xlpsDefault, xlpsAverage]; // -> t="default"、t="avg"
Pivot.AddColumnField('Quarter');
Pivot.AddDataFieldByName('Revenue', xlpaSum); // Revenue 会拿到 dataField="1"
Book.SaveAs('orders-pivot.xlsx');
finally
Book.Free;
end;
end;
读取 Excel 省略的东西
读取器面对的是镜像问题。Excel 造的透视字段把小计条目也列在 items 里,当普通成员加载就会给字段凭空造出指向虚无的幽灵条目。HotXLS 跳过任何带了 t 属性且不为 data 的 item——小计、总计和空白条目一网打尽。小计函数经字段自身的标志进来,落进 Subtotals,于是遍历 Items 的代码只见真成员,每个 CacheItemIndex 都指向一个缓存值
缺席属性是另一半。schema 里,CT_PivotField 的 defaultSubtotal 和 CT_SharedItems 的 containsString 默认都是 true,取默认值时 Excel 干脆不写。HotXLS 把缺席的 defaultSubtotal 读作 true,字段没有显式小计标志时往 Subtotals 里加 xlpsDefault——Excel 自己显示这类字段就是这么干的。缺席的 containsString 同样读作 true,纯文本缓存字段因此归为字符串数据。把任一属性读成 false,Excel 存的每个透视表加载时就丢了自动小计,文本字段也会变成别的类型。总是全量拼出每个属性的写出器碰不到这条路径;只有别人家的文件才会踩上去
加载过的 Excel 工作簿正是合适的测试输入,因为只有这种文件会省略那些属性。下面的检查打开一份并遍历一个行字段:
var
Book : TXLSXWorkbook;
Pivot: TXLSPivotTable;
Fld : TXLSPivotField;
I, J : Integer;
begin
Book := TXLSXWorkbook.Create;
try
Book.Open('saved-by-excel.xlsx');
Pivot := Book.Sheets[1].PivotTables.FindByName('PivotTable1');
if Pivot = nil then
Exit;
for I := 0 to Pivot.FieldCount - 1 do
begin
Fld := Pivot.Fields[I];
if Fld.Axis <> xlpfaRowField then
Continue;
// 没有小计属性的 Excel 字段照样报出默认小计
if xlpsDefault in Fld.Subtotals then
Writeln(Fld.Name, ': automatic subtotal');
// 小计条目已被跳过,每个 item 都对应一个缓存值
for J := 0 to Fld.ItemCount - 1 do
Assert(Fld.Items[J].CacheItemIndex >= 0); // 建议放进你自己的测试
end;
finally
Book.Free;
end;
end;
为什么 numFmtId="General" 在缓存字段上非法
numFmtId="General" 之所以非法,是因为 ST_NumFmtId 是无符号整数——哪怕借用户在设置单元格格式对话框里见的那个名字再自然不过。按 schema 给属性定型的解析器直接拒收这个名字,而这类失败恰恰就是 Excel 修复对话框的来源;Excel 修复提示背后的 OPC 与标记规则一文讲了那些对话框如何被触发。HotXLS 写每个 cacheField 时都带数值 numFmtId,取自缓存字段的 NumberFormat——没人设置过就是 0,即内置的 General 格式
放到 65535 行之后的透视表需要 32 位坐标
XLS 与 XLSX 引擎共用的透视模型,很容易动心思从 BIFF8 的 SxView 记录借字段宽度——那里 16 位就够盖住经典工作表的所有行。可 XLSX 工作表有 1,048,576 行。把 FirstRow、LastRow、FirstHeaderRow、FirstDataRow 和列方向的对应属性存成 Word,再拿 Min(Value, High(Word)) 一钳,锚在第 70001 行的透视表就被削到 65535
在 TXLSPivotTable 上这些属性是 Integer,只有 BIFF8 写出器在记录边界收窄它们。锚点落在 1..1048576 乘 1..16384 之外时,TXLSXWorksheet.AddPivotTable 返回 nil;复制的范围会跑出网格时,AddPivotTableCopy 返回 nil
var
Book, Check: TXLSXWorkbook;
Pivot : TXLSPivotTable;
begin
Book := TXLSXWorkbook.Create;
try
Book.Open('orders.xlsx');
// 第 70001 行超出 16 位范围
Pivot := Book.Sheets[1].AddPivotTable('Data!$A$1:$D$800', 70001, 1, 'LateTotals');
if Pivot = nil then
Exit; // 锚点在表外或源区域解析不了
Pivot.AddRowField('Region');
Pivot.AddDataFieldByName('Revenue', xlpaSum);
Book.SaveAs('late.xlsx');
finally
Book.Free;
end;
Check := TXLSXWorkbook.Create;
try
Check.Open('late.xlsx');
Pivot := Check.Sheets[1].PivotTables.FindByName('LateTotals');
// Assert 是建议放进你自己测试的检查
Assert((Pivot <> nil) and (Pivot.FirstRow = 70001));
finally
Check.Free;
end;
end;
经典 XLS 引擎遵守同样的从 1 数起的约定。SxView 和 DConRef 记录里存的是从 0 数起的值,而模型——与 XLSX 引擎和 Cells[Row, Col] 一致——保持从 1 数起的坐标:BIFF8 读取器在记录边界加 1,写出器减 1。锚点落在 1..65536 乘 1..256 之外时,经典 AddPivotTable 返回 nil,所以锚点请用 (1, 1) 而不是 (0, 0)。记录布局本身见经典 .xls 透视表背后的 BIFF8 SX 记录
对着 schema 测,而不是对着自家的读取器测
宽容的一侧和出错的一侧住在同一个库里时,上面每个陷阱都能活着通过往返。三类检查能抓住它们。对生成的 pivotTableN.xml 和 pivotCacheDefinitionN.xml 部件做 schema 校验,能逮住编造的令牌、空的 items 元素和带名字的格式 ID。拿 Excel 存的文件喂给自己的读取器,才能覆盖 Excel 在默认值处省略的那些属性。在写出的 XML 里钉死精确令牌的断言——比如 axis="axisRow"——能抓住写出器向「只有自家读取器认识的拼写」漂移;对解析后的轴做断言则抓不住
走 API 建的透视表——包括用计算字段构建与刷新 XLSX 透视表展示的计算字段、计算项和占总数百分比布局——产出时这些规则已自动生效,你这边不用加一行代码。留给你的活只有两件:选哪些字段带小计,以及你说的到底是哪个 count 函数
HotXLS Delphi 电子表格组件用 Delphi 与 C++Builder 代码构建、读取并保存 XLS 和 XLSX 透视表,运行它的机器上不需要装 Excel,也没有 COM 自动化