pivotField要素のaxis="rowAxis"という属性は、まったくもっともらしいXMLに読めます。それを行フィールドへマップするリーダーなら、すべてのフィールドが正しい軸に乗ったピボットをロードします。ところが同じパーツを、ECMA-376 Part 1 §18.10スキーマから作ったバリデーターに通すと、最初の属性で失敗します。ST_AxisはaxisRow、axisCol、axisPage、axisValuesのちょうど4トークンであり、rowAxisはそのうちの1つではありません。ピボットライターが満たすべきはスキーマであって、自分のリーダーではありません。ファイルを開く人々はExcelと、それぞれのパーサーを使うのです
ピボットフィールド定義は、こういうルールだらけです。ラウンドトリップテストをすり抜けやすいのが3つ。列挙トークン、items要素が決して空でないという要件、そしてExcelがデータアイテムの後に列挙する末尾の小計エントリです。さらに2つは読み手側にあり、スキーマ既定値を保持するためにExcelが省く属性にあります。この記事の残りでは、各ルールを歩き、XMLがどのような形をしているべきかを見せ、それを生み出すDelphiの呼び出しを見せます
ラウンドトリップでは間違ったaxisトークンを捉えられない理由
ラウンドトリップが証明するのは、ライターとリーダーが互いに合意していることだけです。リーダーがaxisRowとrowAxisの両方を受け付けるなら、間違った方を出すライターは、書けるすべての保存してロードするチェックを通ります。帰り道でリーダーがミスを修復するからです。フォルトが現れるのは、より厳格なコンシューマーがパーツを開いたときです。スキーマバリデーター、XSDから生成されたパーサー、またはパーツに修復が必要と判断したExcelです
HotXLSは2つの側を意図的に分けています。XLSXライターは4つのST_Axisトークンだけを出します。一方ピボットリーダーは、英語風の綴りrowAxis、colAxis、pageAxis、dataAxisも、対応する軸へマップします。それを使うプロデューサーのファイルも、レイアウトを保ったままロードできるようにするためです。出力はスキーマに縛られ、入力は認識できさえすればよい。逆——厳格なリーダーとずさんなライターのペア——なら、少なくとも自分のテストで大声で失敗します。寛容なリーダーとずさんなライターのペアは、あなたの見えるどこでも失敗しません
<!-- 無効:rowAxisはST_Axis値ではなく、CT_Itemsは空にできない -->
<pivotField axis="rowAxis" defaultSubtotal="1"><items count="0"></items></pivotField>
<!-- 有効:ST_Axisトークン、実在するitems、末尾のデフォルト小計 -->
<pivotField axis="axisRow" defaultSubtotal="1">
<items count="4"><item x="0"/><item x="1"/><item x="2" h="1"/><item t="default"/></items>
</pivotField>
axisを超えてCT_PivotFieldが期待するもの
CT_PivotFieldには、落としやすい要件がもう3つあります。値領域で集計されるフィールドは、<dataFields>リストがすでに参照しているにもかかわらず、自分の定義上でdataField="1"と述べなければなりません。HotXLSは、DataFieldsのエントリが指すすべてのピボットフィールドにそのフラグを立てるため、AddDataFieldByNameで足されたフィールドは両方の場所でマークされます
2つ目のルールは構造的なものです。CT_Itemsはitemの子を少なくとも1つ要求するため、<items count="0"></items>は整形式のXMLでどこでもパースされるにもかかわらず無効です。フィールドにアイテムがないとき、HotXLSはitems要素全体を省きます。3つ目のルールは各アイテムの状態についてです。非表示のメンバーはh="1"を運び、詳細が折りたたまれたメンバーはsd="0"を運びます。どちらもTXLSPivotItem.IsHiddenとTXLSPivotItem.IsDetailHiddenから来て、毎回の保存で書かれるため、コードで設定したフィルターはExcelまで生き残ります
末尾の小計アイテムとcountの罠
フィールドがアイテムを持つとき、Excelはデータアイテムの後に、小計関数ごとに1つの追加のitemを続けます。ST_ItemTypeで型付けされたものです。自動小計は<item t="default"/>で、明示的な関数はsum、countA、avg、max、min、product、count、stdDev、stdDevP、var、varPを使います。これらのエントリはキャッシュへのxインデックスを運ばず、itemsのcount属性に算入されます。これらを省くライターは、同じ要素の小計フラグともう一致しないitemsリストを作ります
HotXLSは、保存時にTXLSPivotField.Subtotalsから末尾を導出します。セットの中の関数ごとに1つのt="…"アイテムを列挙順に足し、countへ加え、フィールドには対応するsumSubtotal="1"式の属性を付けます。AddPivotTableが作るフィールドは空のセットから始まり、それはdefaultSubtotal="0"と、末尾アイテムなしを書きます。生成されるレポートでは、セットを空のままにしてください。小計行はピボットの高さを変え、その下に置かれたものをずらします。Subtotalsへ関数を足すのは、リーダーに追加の行が必要なフィールドだけにしてください
関数を選ぶ前に、命名はもう一度見る価値があります。xlpsCountはcountAへ対応し、すべての非空エントリを数えます。xlpsCountNumsはcountへ対応し、数値だけを数えます。XMLの中の「count」を読んでxlpsCountに手を伸ばす人は、もう一方の関数を手に入れます
下のプログラムは、既存のデータシートから、行フィールド、列フィールド、1つの値フィールドを持つピボットを組み立てます。Regionフィールドは自動小計に平均を加え、データアイテムの後に2つの末尾アイテムを作り、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はキャッシュ値を指します
欠落属性がもう半分です。スキーマでは、CT_PivotFieldのdefaultSubtotalとCT_SharedItemsのcontainsStringはどちらも既定がtrueであり、Excelはその値を保持するとき省きます。HotXLSは、存在しないdefaultSubtotalをtrueと読み、フィールドに明示的な小計フラグがなければxlpsDefaultをSubtotalsへ足します。Excel自身がそのようなフィールドを表示するやり方です。存在しないcontainsStringもtrueと読まれるため、プレーンテキストのキャッシュフィールドは文字列データとして分類されます。どちらかの属性をfalseと読むと、Excelが保存したすべてのピボットがロード時に自動小計を失い、テキストフィールドは別の型に変わります。すべての属性を必ずフルスペルで書くライターはこの経路に触れません。別のプロデューサーのファイルだけがこれを試します
ここでの正しいテスト入力は、ロードされたExcelワークブックです。それらの属性を省く唯一の種類のファイルだからです。下のチェックは1つを開いて行フィールドを歩きます:
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が符号なし整数だからです。セルの書式設定ダイアログでユーザーが見る名前を借りたくなるのは自然ですが。スキーマから属性を型付けするパーサーは名前を丸ごと拒否し、この種の失敗はまさにExcelで修復ダイアログに変わるものです。この対話のトリガーの仕組みは、Excel修復プロンプトの背後にあるOPCとマークアップのルールの記事が扱っています。HotXLSはすべてのcacheFieldを、キャッシュフィールドのNumberFormatから取った数値のnumFmtIdで書きます。何も設定していなければ、これは組み込みのGeneral形式である0です
行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ライターだけです。TXLSXWorksheet.AddPivotTableは、1..1048576 × 1..16384の外のアンカーに対して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を引きます。クラシックのAddPivotTableは、1..65536 × 1..256の外のアンカーに対してnilを返すため、ピボットは(0, 0)ではなく(1, 1)にアンカーしてください。レコードレイアウトそのものは、クラシック.xlsピボットテーブルの背後のBIFF8 SXレコードで説明しています
自分のリーダーではなくスキーマで検証する
寛容な側と誤った側が同じライブラリに住んでいるとき、上のすべての罠はラウンドトリップを生き延びます。それらを捉えるチェックが3種類あります。生成されたpivotTableN.xmlとpivotCacheDefinitionN.xmlパーツのスキーマ検証は、創作されたトークン、空のitems要素、名前付きフォーマットIDを捉えます。Excelが保存したファイルを自分のリーダーに通すことは、Excelが既定値で省く属性を試します。書かれたXMLの中の正確なトークンを固定するアサーション、たとえばaxis="axisRow"は、自分のリーダーしか理解しない綴りへ向かうライターを捉えます。パース済みの軸へのアサーションでは捉えられません
APIを通して組み立てられるピボットには、計算フィールド付きXLSXピボットテーブルの構築と更新で見せる計算フィールド、計算アイテム、合計に対するパーセントレイアウトを含め、これらのルールが適用されて出てきます。あなたの側のコードは増えません。あなたに残された仕事は、どのフィールドに小計を付けるか、そしてどのcount関数を意図しているかを選ぶことです
HotXLS Delphi spreadsheet componentは、DelphiとC++BuilderコードからXLSとXLSXのピボットテーブルを組み立て、読み、保存します。それを実行するマシンに、ExcelのインストールもCOMオートメーションも必要ありません