HotXLS は、TXLSPivotField の 2 つのプロパティを通じて、Delphi でピボットテーブルのアイテムを値で並べ替えます。SortType が昇順か降順を選び、SortDataField が順序を決める 0 起点のデータフィールドを名指します。HotXLS が Excel 作成の XLSX を開くとき、そのデータフィールドを autoSortScope 要素から回復します。そこでは Excel が Values フィールドを field="4294967294"、つまり -2 の符号なし綴りで保存しています
値での並べ替えは、どんな売上レポートもいずれ受ける要求です。「地域を大きい順に見せて」。Excel の UI では 2 クリックです。ファイル形式では、属性、ネストしたピボット領域、予約済みフィールドインデックス、選択されたデータフィールドの序数であり、そのどれか 1 つでも読み落とすと、リーダーは文句も言わずにファイルを開けます。ただ間違ったものの順に並べるだけです
Excel は値順のメジャーをどこに保存するのか
Excel はソートのメジャーを autoSortScope の中だけに保存し、フィールド自体には保存しません。<pivotField> の sortType 属性（ECMA-376 Part 1 の ST_FieldSortType 値：manual、ascending、descending）がどちら向きに並べるかを言い、どのメジャーで並べるかは子の <autoSortScope> に住んでいます。それは <pivotArea> を包み、そのデータ軸リファレンスが 1 つのデータフィールドを選びます
<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 疑似フィールド用に予約されており、2 つ以上のメジャーが軸に乗るとき Excel が "Values" として表示するあれです。属性は符号なし 32 ビット整数として型付けされているため、-2 は 4294967294 として回線を渡ります。その下の <x v="0"/> がデータフィールド 0、<dataFields> の最初のエントリを選びます。<pivotField> には rankBy 属性もありますが、比較したファイルでは Excel はそれを省き、スコープだけに頼っていました
ソートのメジャーが -1 として返ってきた理由
HotXLS のリーダーは元来、ソートのメジャーを失っていました。4294967294 が符号付き 32 ビット整数に収まらないからです。属性ヘルパーは StrToIntDef の薄いラッパーで、範囲外の値は静かに既定値を返すため、データ軸のテストは一度も一致せず、SortDataField は -1 のまま、フィールドはメジャーなしの sortType="descending" だけを保持していました
この失敗は最悪の意味で静かでした。メジャーのない降順フィールドも並べ替えはします。ただ項目ラベルの逆順です。Excel が合計順に並べたレポートが、HotXLS からは項目順を逆にしただけで返ってきました。例外も上がらず、各セルの数字はすべて正しい。間違っているのは行順だけで、しかも Excel が作成したファイルだけです
lxPivotXml.pas の修正は、整数変換の前に生の属性トークンを文字列として比較します。rankBy がすでにメジャーを設定していればそちらが勝ち、そうでなければ field トークンが正確に '4294967294' のリファレンスが、選択した x 値から SortDataField を解決します。評価側の変更は不要でした。比較器はすでに行軸と列軸でメンバーごとの合計を確定し、降順を扱い、同着を処理していました。データが単純に届いていなかっただけです
一般教訓は、Delphi で書かれたどんな OOXML リーダーにも当てはまります。ECMA-376 はいくつかのインデックス属性を xsd:unsignedInt として型付けし、範囲の上限をセンチネルとして使うため、既定値フォールバック付きの符号付きリーダーは、予約値を「属性なし」に変えてしまいます。フィールドインデックスが Values を意味し得る場所では、トークンを比較するか、Int64 や Cardinal にパースしてください
Delphi コードからメジャー順にピボットアイテムを並べる
HotXLS のピボットフィールドを値で並べるには、SortType を xlpfstAscending か xlpfstDescending に設定し、SortDataField をテーブルの DataFields リスト内のメジャーの 0 起点インデックスに設定します。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; // 「Sum of 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;2 つの細部に触れておきます。HotXLS のライターは SortDataField を rankBy として出力し、AutoSortScope が空でないときは 4294967294 のデータ軸リファレンス付きの autoSortScope ブロックも書き、プロパティ文字列を x v スロットに入れます。ロード時、AutoSortScope は同じ x v トークンを受け取るため、値ソートではプロパティがテキストとしてデータフィールドの序数を保持します。したがって '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 の第 2 引数は、衝突として扱う代わりに、ピボットの既存の出力範囲を採用するよう HotXLS に命じます。同じパターンはHotXLS の Show Values As のランクと累計のガイドにも現れます。累計は表示順に積み上がるため、そちらではソート順が重要です
2 つのアイテムが同じ値のとき HotXLS はどう同着を処理するのか
HotXLS は同着を項目ラベルで決定論的に処理します。これは Excel との既知の差です。2 つのメンバーが同一の合計を持つとき、比較器はキャプションの比較へフォールバックし、降順フラグはその比較も反転します。メトリックが有効な数でないアイテムは、昇順では有効なものの後に並び、降順フラグはそのグルーピングごと比較全体を反転します
Excel は同着の順序について何も約束しません。4 つのメンバー A、B、C、D がすべて同じ合計を持つテストフィクスチャで、Excel の昇順ソートは B、C、A、D を生みました。保存されたメタデータのどこにもその順列を予言するものはなく、不安定ソートの副産物であり、ファイル間で変わり得ます。HotXLS は同じ入力に A、B、C、D を返します。このケースの当方の検証は、順列ではなくメンバー集合とメンバーごとの値を確認し、15/15 の同着を持つ降順フィクスチャは Excel とセル単位で一致しました。下流のコードが同着行の正確な位置に依存するなら、2 番目のメジャーで並べるか、値を区別可能にしてください
折りたたまれたメンバーと最も内側のフィールド
折りたたまれたメンバーは、HotXLS では折りたたまれたアイテムごとの 1 つの集計行として実体化します。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> とそのアイテムにスキーマが何を要求するかを扱います
Excel 自動化と突き合わせるときの罠
HotXLS のソートを COM 自動化経由で Excel と突き合わせるとき、AutoSort にはメジャーのローカライズされたキャプションを、Excel が表示するとおりに綴って渡してください。中国語の Excel では、合計した Units フィールドの既定キャプションは '求和项:Units' で、そのマシンで 'Sum of Units' を渡してもエラーは上がらず何も変わりません。英語の Excel で動く同じスクリプトが、他では静かに no-op になるため、キャプションはハードコードせずデータフィールドから読んでください
- テストした Excel ビルドでは
PivotField.SortTypeを COM 経由で読み戻せなかったため、ソートは保存された XML か描かれたセルから確認してください - 同じビルドで、ソートするフィールドと同じ軸に手動グループの親フィールドを置くと、値によるメンバーソートが抑止されました。手動グループの親の下でのソート済み描画は検証されていません
PivotField.OutlineとPivotField.LayoutFormへの書き込みもそのビルドでは拒否されたため、フィクスチャのアウトラインレイアウトはPivotTable.RowAxisLayoutで設定してください
これらの癖はファイル形式を変えませんが、リグレッションテストが何かを証明するかを決めます。間違ったキャプションで AutoSort を呼ぶテストは、一度も並べ替えられていないピボットに対しても通ります
FAQ
PivotTable XML ファイルのフィールド 4294967294 は何を意味するか
フィールド 4294967294 は、フィールドインデックス -2 の符号なし 32 ビットエンコーディングで、ECMA-376 が Values 疑似フィールド用に予約しています。autoSortScope の内側ではデータ軸リファレンスを示し、その x の子がアイテムを並べ替えるデータフィールドを選びます
HotXLS は Excel をインストールせずにピボットアイテムを値で並べ替えるか
はい。HotXLS はピボットグリッドを実体化するときに、選ばれたデータフィールドのメンバーごとの合計を使ってソートを自前で評価し、Excel が更新後も同じ順序を保つよう sortType、rankBy、任意で autoSortScope を書き込みます。同着の順序だけが Excel と変わり得ます
ピボットアイテムが値順ではなくアルファベット順に並ぶ理由
たいてい、フィールドにソート方向はあるがメジャーがないせいです。SortType が xlpfstManual でなく、SortDataField が 0 から DataFieldCount - 1 の間にあるか確認してください。-1 のままでは、HotXLS は要求された方向でラベル順に並べます
値ソート、折りたたまれたメンバー、ネイティブピボットパイプラインの残りは、Excel をインストールせずに Excel ブックを読み、編集し、書く HotXLS（XLS と XLSX 向け Delphi スプレッドシートコンポーネント）に同梱されています。ピボットをゼロから組み立てる段階なら、HotXLS のピボットテーブル構築と更新のチュートリアルが、順序を扱う前にキャッシュ、フィールド、更新をカバーします