技術記事

HotXLSが書くXLSXピボットフィールドのスキーマ妥当性

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>
無効と有効なpivotField XMLの対比。創作されたaxis値rowAxisと空のitems要素はCT_PivotFieldに違反する一方、HotXLSは、実在のitemエントリ、保持された非表示フラグ、スキーマが受け入れる末尾のデフォルト小計とともに、axisRowのようなST_Axisトークンを出力する
両方の綴りを受け付ける寛容なリーダーはすべてのラウンドトリップを通すのに、ファイルはどんな厳格なスキーマ検査も破ります。書くのは4つのST_Axisトークンだけ、CT_Itemsにはitemを1つ以上運ばせてください

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に手を伸ばす人は、もう一方の関数を手に入れます

HotXLSのピボットitemsリストの構造図。データitemエントリの後ろに、TXLSPivotField.Subtotalsから導出されたt=defaultやt=avgなどの末尾小計アイテムが続き、items countへ算入されます。xlpsCountがcountAへ、xlpsCountNumsがcountへ対応する命名の罠も書き添えます
AddPivotTableが作るフィールドは空のSubtotals集合から始まり、defaultSubtotal=0と末尾アイテムなしに書かれます。欲しい関数を要求すれば、ライターは関数ごとに1つのitemを導出してcountへ入れます

下のプログラムは、既存のデータシートから、行フィールド、列フィールド、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を返します

行70001のピボットアンカーと16ビット上限の対比。FirstRowとLastRowがWordとして保存されHigh(Word)の65535を上限にMinでクランプされる方式では、ライン以下のピボットが切り捨てられた。HotXLSはこれらをIntegerフィールドとして保ち、グリッドの外ではnilを返す
Word幅の行フィールドはBIFF8のSxViewレコードには収まるが、1048576行まで走るシートには収まりません。それらを使うと、行65535を超えるアンカーは65535へクランプされ、ピボットは置かれた場所に座らなくなります
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オートメーションも必要ありません