HotXLSは今、構造化テーブル参照を評価できるようになり、=SUM(Table1[Amount])はスキップされるのではなく数値を返すようになりました。リゾルバーはTable[Column]、Table[[Column]]、Table[[Q1]:[Q4]]のような列範囲、そして[#Data]、[#All]、[#Headers]、[#Totals]という項目指定子を処理し、パース時にそれぞれをワークブックのテーブルモデルに対して解決する一方、元の数式テキストはそのままの形でラウンドトリップされます
ある1つの形式だけは意図的に非対応であり、しかもそれは人が最初にぶつかる形式です。現在行の省略記法である[@Column]は対応していません。これには、闇雲に回避する前に理解しておく価値のある構造的な理由があります
構造化参照が単なる「わかりやすい名前の付いた範囲」ではないのはなぜか
なぜなら、定義済み名前はアドレスを固定しますが、テーブル参照はそうしないからです。Sheet1!$A$2:$D$100を指す名前としてDataBlockを書けば、何かがそれを書き換えるまでその矩形のままです。Sales[Amount]と書けば、それは「Salesテーブルの、Amount列」を意味し、数式が評価される時点でそのテーブルの範囲がどうであれ、それに従います。テーブルに20行追加すれば、合計はそれらも含みます。調整すべき参照は存在しません。なぜなら、そもそも数式の中にアドレスというものが一度も存在していなかったからです
この記号的な性質こそが、この参照を文字列置換では解決できない理由です。リゾルバーは、ワークブック内で名前によってテーブルを見つけ、ヘッダーテキストによって列を検索し、要求された項目指定子がどの行をカバーするかを決定し、具体的な矩形を生成しなければなりません。HotXLSはこれを、数式コンパイル時にテーブルモデルを通じて行っており、だからこそ、テーブルが大きくなる前に書かれた数式であっても、テーブルの現在の範囲に対して評価されます
HotXLSが解決する文法
対応している仕様の文法は、単一の矩形の結果をカバーするものであり、正確に述べておく価値があります。Excelのドキュメントは、ほとんどのエンジンが実装する範囲よりもはるかに広い範囲を提示しているからです。HotXLSが受け付けるのは、[Col]とその角括弧付きの変種である[[Col]]、素の項目指定子である[#Data]、[#All]、[#Headers]、[#Totals]、組み合わせ形式の[[#Data],[Col]]、項目指定子内の範囲である[[#Data],[Col1]:[Col2]]、そして単純な範囲である[Col1]:[Col2]です
この集合が提供するのは、1つの連続したブロックを生成するあらゆる参照の形、すなわち1つの列、隣接する列の連なり、そのどちらかのボディのみまたはヘッダーを含むスライスです。非隣接の和集合や複数領域にまたがる結果はこの範囲外です。参照を解決できない場合、数式は推測値で代用するのではなく、従来どおりの「値なしでスキップする」挙動を保つため、解決できない参照が、もっともらしい誤った数値になってしまうことは決してありません
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[27, 4].Formula := 'SUM(SalesTable[Amount])';
Sheet.Cells[28, 4].Formula := 'SUM(SalesTable[[Q1]:[Q2]])';
Sheet.Cells[29, 4].Formula := 'COUNTA(SalesTable[[#Data],[Region]])';
Sheet.Cells[30, 4].Formula := 'ROWS(SalesTable[#All])';
Book.Recalculate;
Book.SaveAs('sales.xlsx');
finally
Cols.Free;
Book.Free;
end;
end;現在行形式が意図的に除外されているのはなぜか
[@Column]と[#This Row]は、「この数式が存在する行における、その列のセル」を意味します。したがって、その値はテーブルだけでなく、評価しているセルの位置にも依存します。これは異なる種類の参照です。コンパイラが一度だけ解決できる矩形ではなく、数式が占めるすべての行についてやり直さなければならない、セル単位の解決なのです
HotXLSは、これらの形式に対してテーブル範囲リゾルバーからFalseを返し、それを「値なしでスキップする」経路へ振り分けます。数式テキストは保持され変更されずに書き戻されるため、[@Amount]を使うワークブックは、自分のアプリケーションを経たラウンドトリップの後でもExcelで正しく開けます。欠けているのはHotXLSが計算する値だけです。値が存在しないことと、誤った行に対して計算された値であることの選択肢があるなら、検出できるのは前者です
実用的な回避策は機械的なものです。自分で生成するワークブックでは、テーブルスコープのロジックの大部分についてExcelが内部的にどのみち格納している、等価なA1形式の相対参照を書いてください。単に処理するだけのワークブックでは、数式にはそのまま手をつけず、Excelがすでに格納しているキャッシュ済みの値を読んでください。読み込んでレポートするパイプラインが通常求めているのは、そちらです
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];
Table := Sheet.Tables.FindByName('SalesTable');
if Table <> nil then
begin
// テーブル本体に対するレコードセット形式の検索、行番号は1始まりで返る
Row := Table.FindFirst(Sheet, 'Region', 'EMEA');
while Row > 0 do
begin
Log(VarToStr(Sheet.Cells[Row, 4].Value));
Row := Table.FindNext(Sheet, 'Region', 'EMEA', Row);
end;
end;
finally
Book.Free;
end;
end;テーブルの形が変わったときに何が起こるか
構造化参照は、それが指し示していたものが消えたとき、静かに参照先を付け替えられるのではなく無効化されます。列を削除すると、その列を参照している数式は、Excelが無効化するのと同じ方法で無効化されます。テーブルを削除またはリネームした場合、それへの参照も同じように処理されます。これは正しい挙動であり、挿入・削除時の数式参照調整で説明している通常の参照調整を反映したものです。そこでは、エンジンの仕事は数式を有効に見せかけ続けることではなく、数式を誠実に保つことだとされています
行の追加はその正反対のケースであり、まったく調整を必要としません。参照が矩形ではなくテーブルそのものを名指ししているため、テーブルの範囲内に行を追加すると、数式に一切手を触れずに[#Data]がカバーする範囲が広がります。この性質こそが、テーブルをレポートテンプレートで使う価値を生んでいます。合計行は、インポートが結局何行を生成したとしても、その結果すべてを合計し続けます
ラウンドトリップの規律
HotXLSは元の数式テキストを保持します。SUM(SalesTable[Amount])で読み込まれたワークブックは、解決済みのSUM(D2:D25)ではなく、SUM(SalesTable[Amount])のまま保存されます。これは見た目以上に重要です。自分の出力をExcelで開くユーザーは、自分が書いた数式が見えることを期待しており、解決済みのアドレスにしてしまうと、自己維持型のモデルが、静かに、新しい行をカバーしなくなる壊れやすいモデルへと変わってしまいます
関連する2つの機能が、この全体像を完成させます。ヘッダーなしテーブルやテーブルごとのコメントを含む、テーブル定義そのものは、データ入力規則、AutoFilter、Excelテーブルで説明しているテーブルモデルを通じてラウンドトリップします。そして、多くのセルが1つのパターンを共有している場合、XLSXはそれらを共有数式として1回だけ格納し、共有数式のsi展開で扱っている方法で展開・再出力されます。共有数式の内部にある構造化参照は両方の経路を通るため、両方が正しく振る舞う必要があり、実際にそうなっています
HotXLSは、ExcelのインストールもOffice自動化も使わず、独自のエンジンで数式を評価しながら、DelphiとC++BuilderからXLS、XLSX、ODSを読み書きします。テーブルモデル、数式エンジン、再計算APIはHotXLS Delphiスプレッドシートコンポーネントページで説明されています