技術記事

HotXLSでDelphiからExcel構造化テーブル参照を評価

Amount列に=[@Qty]*[@Price]を保持するExcelテーブルは、保存されたファイルのどこにも[@Qty]を格納していません。ワークブックをunzipすると、シートXMLの<f>要素に、Excelが実際に書いたものが現れます。長い、テーブル修飾の綴り、Lines[[#This Row],[Qty]]*Lines[[#This Row],[Price]]です。ECMA-376 Part 1 §18.17の数式文法には#This Rowキーワードがあり、@はないからです。少し下のサマリーセルはSUM(Lines[Amount])を保持します。どちらの文字列にも単一のセルアドレスは含まれません。したがってA1参照しかパースしないエンジンは、テーブルがどこに座り、どの行がヘッダーを運び、数式自身がどの行に居るかを知るまで、計算すべき何も持ちません

最後の情報こそが、厄介なピースです。列参照はテーブルごとに1回、矩形へ変換できます。現在行参照はそうはいきません。コピーされる行ごとに、別のセルを意味するのです

構造化参照が単なる「わかりやすい名前の付いた範囲」ではないのはなぜか

定義名はアドレスを凍結し、テーブル参照はしません。名前DataBlockをSheet1!$A$2:$D$100に向ければ、何かが書き換えるまでその矩形のままです。Sales[Amount]は「SalesテーブルのAmount列」を意味し、数式が評価されるときにテーブルの範囲がどうなっていようと構いません。テーブルに20行追加すれば、合計はそれを覆います。調整すべき参照が存在しないのです。数式はそもそもアドレスを保持していなかったのですから

HotXLSは、評価用に数式をコンパイルするたびに、これらの参照を解決します。TXLSXWorkbookは、ワークブックのテーブルモデル(TXLSXTable.NameまたはDisplayName、大文字小文字を無視して比較)の中からテーブルを名前で探し、TXLSXTable.Columnsに保存されたヘッダーテキストに対して列を照合し、項目指定子を適用して行バンドを選び、'Sales'!$D$2:$D$25のような絶対範囲をコンパイラへ渡します。書き換えられたテキストは、そのコンパイルのためだけに存在します。セルは書かれたままの数式を正確に保持し続けます。そしてルックアップは再計算時点のTXLSXTable.Rangeに対して走るため、テーブルが成長する前に書かれた数式も、大きくなったテーブルに対して評価されます

HotXLS が名前でテーブルを見つけ、Amount 列ヘッダーを照合し、#Data 指定子を適用して具体的な範囲を得ることで、Delphi で構造化参照 SalesTable[Amount] を解決する様子の図
HotXLS はパース時にテーブルモデルに対して Table[Column] 参照をコンパイルし、現在の具体的な範囲へ変換するため、数式はテーブルの成長に追従し続けます

矩形の文法:列、スパン、項目指定子

HotXLSが評価するテーブル全体の形式は、すべて1つの連続ブロックを生みます。受け付ける綴りは、[Col]とその括弧双子の[[Col]]、素の項目指定子[#Data]、[#All]、[#Headers]、[#Totals]、結合形式の[[#Data],[Col]]、[[#Data],[Col1]:[Col2]]のような項目指定子の内側のスパン、そして素のスパン[[Col1]:[Col2]]です。スパンは右から左に書いても構いません。[[Q2]:[Q1]]は[[Q1]:[Q2]]と同じ2列に解決されます

各指定子が選ぶ行バンドは、テーブル上の2つのフラグに従います。TXLSXTable.HasHeaderRowがRangeの最初の行がヘッダーかを決め、TXLSXTable.TotalsRowShownが最後の行が合計行かを決めます。素の列参照と[#Data]は、その2つの間の行を覆います。[#All]はヘッダーと合計込みでRangeの全体を取ります。ヘッダー行のないテーブルに[#Headers]を、合計行のないテーブルに[#Totals]を要求したとき、またボディに1行もないテーブルの列を要求したとき、参照は#REF!になります。同じ要求に対してExcelが示すものと一致し、実現不可能な参照が、合計ゼロの空範囲へ静かに化けることを防ぎます

括弧、ハッシュ、アットマーク、アポストロフィを含む列名は、Excelのエスケープ規則に従います。その種の文字は、参照の内側でアポストロフィを前置されるため、Unit [USD]というヘッダーはSales[Unit '[USD']]と書かれます。単一列形式と現在行形式は、照合の前にそれらのエスケープをデコードします。スパンと[#Data],[Col]の結合形式は、数式自身のシート上のテーブルに対して解決されるため、別シートのテーブルにスパンを掛けたいサマリーシートは、列ごとの素の列参照のほうが適しています

HotXLS が Delphi で受け付ける構造化参照の文法の地図。#Data、#Headers、#Totals、#All の各形式がどのテーブル領域を覆うか、括弧付きの綴りが同じ矩形へどう対応するかを示す
テーブル全体のすべての形式は、テーブルの 1 つの連続バンドを覆い、括弧付きの変種は素の綴りとまったく同じように解決されます

下のサンプルは、24行の売上テーブルを組み立て、その隣のF2からF6に5つのサマリー数式を、テーブルと同じシート上へ置きます。行1はヘッダー行、行2から25がボディで、合計行はありません。したがってSalesTable[#All]は25行に及び、SalesTable[Amount]は24行に及びます

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  Cols: TStringList;
begin
  Book := TXLSXWorkbook.Create;
  Cols := TStringList.Create;
  try
    Sheet := Book.Sheets.Add('Sales');
    Cols.Add('Region');
    Cols.Add('Q1');
    Cols.Add('Q2');
    Cols.Add('Amount');
    Sheet.Tables.Add('SalesTable', 'A1:D25', Cols);
    // ... ヘッダー行と24データ行を書き込む ...

    Sheet.Cells[2, 6].Formula := 'SUM(SalesTable[Amount])';
    Sheet.Cells[3, 6].Formula := 'SUM(SalesTable[[Q1]:[Q2]])';
    Sheet.Cells[4, 6].Formula := 'COUNTA(SalesTable[[#Data],[Region]])';
    Sheet.Cells[5, 6].Formula := 'ROWS(SalesTable[#All])';
    Sheet.Cells[6, 6].Formula := 'COUNTA(SalesTable[#Headers])';

    Book.Recalculate;
    Book.SaveAs('sales.xlsx');
  finally
    Cols.Free;
    Book.Free;
  end;
end;

このレイアウトでの期待値は、F5が25、F6が4、つまりヘッダーセルごとに1のカウントです。F2からF4は、書き込むデータ次第です

[@Column]はどの行を読めばいいかをどう知るのか

現在行参照は、評価中のセルから行を受け取ります。Recalculateが7行目のLines[@Qty]を含む数式に届くと、HotXLSは7行目のQty列へ解決します。同じ数式テキストが8行目にあれば8行目を読みます。Excelがディスクへ書く長い綴り、Lines[[#This Row],[Qty]]は同じ参照として扱われ、空白を含むヘッダーのためのLines[@[Unit Price]]も同様です

テーブル名を前に持たない非修飾形式の[@Qty]には、もう1つの事実が要ります。どのテーブルに属するかです。HotXLSはそれを位置で答えます。評価中のセルが、ボディ行の内側かつ同じシートのテーブルの列スパンの内側に座るなら、そのテーブルが列を供給し、その種のテーブルの内側では[@[Q1]:[Q2]]が、それらの列にわたる1行の範囲になります。それ以外の場所では、参照は未解決のまま置かれます。現在行参照を使う数式を持つ定義名は、その名前を参照しているセルから行を受け取ります。数式がインラインで得るのと同じ挙動です

現在行の数式は、テーブル自身のボディ行の上に置き、コードが生成するときはテーブル名で修飾してください。修飾された参照は移動に耐え、Excelが保存するのと同じ読み方をし、テーブル自身の行上の数式こそ、Excelとファイルの他のどの消費者も@の意味について一致する唯一のケースです

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  Cols: TStringList;
  Row: Integer;
begin
  Book := TXLSXWorkbook.Create;
  Cols := TStringList.Create;
  try
    Sheet := Book.Sheets.Add('Orders');
    Cols.Add('Item');
    Cols.Add('Qty');
    Cols.Add('Price');
    Cols.Add('Amount');
    Sheet.Tables.Add('Lines', 'A1:D13', Cols);
    // ... 行2..13にヘッダー行と商品、数量、単価を書き込む ...

    // 1つの数式テキスト、12の異なる結果。各セルは自分の行を読む
    for Row := 2 to 13 do
      Sheet.Cells[Row, 4].Formula := 'Lines[@Qty]*Lines[@Price]';

    // テーブルの下でクロスチェック:列合計と再計算した積の突き合わせ
    Sheet.Cells[15, 4].Formula :=
      'SUM(Lines[Amount])-SUMPRODUCT(Lines[Qty],Lines[Price])';

    Book.Recalculate;
    // 期待値:すべてのAmountセルがQty * Priceと一致すれば、D15は0に評価される
    Book.SaveAs('orders.xlsx');
  finally
    Cols.Free;
    Book.Free;
  end;
end;

D15の検査は安価で、組み立て中の生成レポートに残しておく価値があります。間違った行を拾った行ごとの数式は、2つの合計を食い違わせ、その差は誰でも読めるセルの中でゼロでない数字として現れます

HotXLS が評価中のセルの行に対して Delphi で現在行形式 [@Amount] を解決しつつ、数式テキストは無変更でラウンドトリップし、保存後も Excel が同じ参照を読める様子のフローチャート
現在行参照は、数式が座る行に対してセルごとに解決され、保存されたファイルは Excel が読める元の参照テキストを運び続けます

テーブルの形が変わったときに何が起こるか

成長は簡単なケースです。テーブル定義が実際に成長する限りは。テーブルがA1:D25というRangeを持つとき、行26への値の書き込みは、行26をテーブルの一部にはしません。自動拡張は、誰かがテーブルの隣にタイプしたときに発火するExcelの編集機能であり、HotXLS経由のセル書き込みはそれを作動させません。インポートの後にTXLSXTable.Rangeを新しい範囲へ設定するか、TXLSXWorksheet.InsertRowsでテーブルの内側へ行を挿入してください。後者は、シート上の他のすべての参照と一緒にTables[i].Rangeをずらします。どちらの道でも、[#Data]が広がり、1つの数式も変わりません

縮小こそ、参照が壊れうる場所であり、HotXLSはExcelの規則に従います。テーブル列を横切るDeleteColsは、その列を名指すすべての構造化参照を#REF!へ書き直します。セル数式、条件付き書式、データ入力規則、グラフ系列、定義名においてです。ヘッダー行を取り除くDeleteRowsはテーブルを削除し、その参照を同じように無効化します。合計行を取り除くものはTotalsRowShownをクリアし、その後[#Totals]には指すべき行がなくなります。素のA1参照も同じ調整を通ります。挿入と削除における数式参照の調整で説明しています

TXLSXTables.DeleteByNameとTXLSXTable.Name経由のリネームは、テーブルモデルにだけ作用します。古い名前を口にする数式は自分のテキストを保つため、コードがテーブルをリネームするときは、同じパスでそれらの数式もリネームしてください

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  Table: TXLSXTable;
  Row: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    if Book.Open('sales.xlsx') <> 1 then Exit;
    Sheet := Book.Sheets[1];  // ワークブックのシートは1始まり

    Table := Sheet.Tables.FindByName('SalesTable');
    if Table = nil then Exit;

    // ... インポートした20行をA26:D45へ書き込む ...
    Table.Range := 'A1:D45';  // ボディは行2から行45まで走る

    Book.Recalculate;  // F2のSUM(SalesTable[Amount])は今や44行を覆う

    // テーブルボディ上のレコードセット風ルックアップ。行結果は1始まり、0は無し
    Row := Table.FindFirst(Sheet, 'Region', 'EMEA');
    while Row > 0 do
    begin
      Log(VarToStr(Sheet.Cells[Row, 4].Value));  // Logは独自ルーチン
      Row := Table.FindNext(Sheet, 'Region', 'EMEA', Row);
    end;

    Book.SaveAs('sales.xlsx');
  finally
    Book.Free;
  end;
end;

FindFirstとFindNextは文字列を大文字小文字を無視して、数値を値で比較し、ボディ行だけを走査するため、ヘッダーテキストのRegionが一致として返されることはありません

ラウンドトリップの規律

SUM(SalesTable[Amount])でロードされたワークブックは、SUM(SalesTable[Amount])で保存され、Lines[[#This Row],[Qty]]を保持するセルは、そのテキストを無傷で出ていきます。解決済みのアドレスが、ユーザーの書いたものを置き換えることは決してありません。HotXLSが代わりにSUM($D$2:$D$25)を保存するとしたら、そのファイルはExcelで問題なく開き、誰かが最初にテーブルを伸ばしたとき、新しい行をカバーしなくなります。原因への逆追跡が最も遅い種類の壊れ方です

テーブル定義そのものも、ヘッダーなしテーブルやテーブルごとのコメントを含め、データ入力規則、オートフィルタ、Excelテーブルで扱うテーブルモデルを通してラウンドトリップします。多くのセルが1つの数式パターンを共有するとき、XLSXはそれを共有数式として1回だけ格納でき、それらのセルの展開は共有数式siの展開で扱われます。Excelが保存した計算列はしばしばその形で届くため、テーブルボディの中の構造化参照は、評価される前に両方の経路を通ります。共有数式がまずセルごとのテキストへ展開され、それから各セルが自分の行を解決します

ここで使ったテーブルモデル、Recalculateエンジン、ワークシートの挿入と削除の呼び出しは、HotXLSの一部です。HotXLSは、Excelのインストールなしに、Delphiと C++Builder からXLS、XLSX、ODSを読み書きします。完全なAPIリファレンスは、HotXLS Delphiスプレッドシートコンポーネントページにあります