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が評価するテーブル全体の形式は、すべて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]の結合形式は、数式自身のシート上のテーブルに対して解決されるため、別シートのテーブルにスパンを掛けたいサマリーシートは、列ごとの素の列参照のほうが適しています
下のサンプルは、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つの合計を食い違わせ、その差は誰でも読めるセルの中でゼロでない数字として現れます
テーブルの形が変わったときに何が起こるか
成長は簡単なケースです。テーブル定義が実際に成長する限りは。テーブルが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スプレッドシートコンポーネントページにあります