LlosLab Software 開発ブログPDF、Delphi、およびドキュメントソフトウェア開発者向けのエンジニアリングノート。

技術記事

Delphiでの定義された名前とシートをまたぐ数式 (HotXLS)

HotXLS Component

定義された名前（Defined Name）とは、定数、セル範囲、または数式を代表するラベルのことであり、ワークブックに一度だけ保存され、必要に応じてどこからでもシンボルとして参照されます。数式に TaxRate と記述すれば、エンジンはその定義が指す内容（リテラルの 0.08 や、範囲の Data!$A$2:$D$100 など）を解決します。シート間参照（Cross-Sheet Reference）はこれと直交する概念です。Data!D2 はアドレスの前にシート名を修飾することで、他のシートのセルを参照します。この2つの目を組み合わせることで、サマリーシートはリテラルアドレスを直接記述することなく、定義された名前を介して明細シートを集計できます。これは、ジェネレーターが構築し、後に会計士が監査するワークブックにおいてまさに必要とされる構成です

losLabが提供するXLSおよびXLSXファイル用のネイティブDelphiライブラリであるHotXLSは、両フォーマットの名前テーブル（Name Table）へのアクセス（作成、検索、および削除）を公開しており、プロセス内で名前やシート間参照を解決する数式エンジンも備えています。これら2つのフォーマットは独立したクラス階層を維持しており、その名前APIの違いが、一方から他方へ移植されたコードでよく問題を引き起こします

インターフェースを共有しない2つの名前ストア

XLS側では、TXLSWorkbook.GetNames が IXLSNames コレクションを返し、その Add(Name, RefersTo, Visible) オーバーロードを介して BIFF 名前テーブルに名前を書き込みます。個々のエントリは、Name、RefersTo、解決された RefersToRange、および Delete メソッドを保持する IXLSName オブジェクトとして返されます。XLSX側では、TXLSXWorkbook.DefinedNames が、Add、FindByName、および DeleteByName メソッドを備えた TXLSXDefinedNames コレクションです

名前のルックアップ仕様は、コンパイル時ではなく、移植後の実行時に表面化する形で異なっています。XLSコレクションのデフォルトプロパティである Item は Variant を受け取るため、Names[0] と Names['TaxRate'] の両方を解決できます。XLSXコレクションにはそのようなデフォルトプロパティはありません。名前が存在しない場合は nil を返す FindByName('TaxRate') を呼び出す必要があります。一方のファサード用に作成されたコードがもう一方のファサードに対してコンパイルできたとしても、それは偶然に過ぎず、実行時に nil 参照エラーとして顕在化する傾向があります

スコープは最初に決定すべき設計事項

定義された名前は、すべてのシートの数式から参照できるブックレベル（Workbook-Scoped）か、またはその名前を所有するシートの数式からのみ参照できるシートレベル（Sheet-Scoped）のいずれかになります。XLSX APIでは、この違いは1つのオプション引数で決定されます。DefinedNames.Add(AName, AFormula) はブックレベルの名前を作成し、Add(AName, AFormula, ASheetIndex) は指定されたシートに名前を紐付けます。名前の読み出しでは、TXLSXDefinedName.SheetIndex がブックレベルの場合は -1 を返し、シートレベルの場合は0から始まるシートインデックスを返します

スコープは名前の衝突対策も兼ねているため、最初の名前を書き出す前に方針を決めておく必要があります。Excelでは、各シートにローカルな Total という名前を定義できると同時に、ブック全体に対して Total を定義することも可能です。特定のシート内の数式は、ローカルな定義を優先して解決します。生成されるワークブックはこの仕組みを意図的に活用すべきです。複数のシートが消費するビジネス上の前提（税率、為替レート、報告期間など）は、ブックレベルのスコープに属します。一方、特定のシートの数式だけが参照する補助的な範囲は、他をシャドウイング（隠蔽）せず、他からもシャドウイングされないシートレベルのスコープにするのが安全です

var
  Book: TXLSXWorkbook;
  Data, Summary: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Data := Book.Sheets.Add('Data');
    Summary := Book.Sheets.Add('Summary');
    // ... fill Data!A2:D100 with detail rows ...

    Book.DefinedNames.Add('TaxRate', '0.08');                // workbook scope, a constant
    Book.DefinedNames.Add('DataBlock', 'Data!$A$2:$D$100');  // workbook scope, a range
    Book.DefinedNames.Add('LocalNote', 'Summary!$B$1', 1);   // scoped to sheet index 1 only

    // XLSX formulas take no leading '='
    Summary.Cells[2, 2].Formula := 'SUM(Data!D2:D100)*TaxRate';
    Book.SaveAs('model.xlsx');
  finally
    Book.Free;
  end;
end;

定義された名前は、必ずしも範囲を指す必要はありません。上記の TaxRate はプレーンな定数 0.08 を参照しており、これはビジネス上の前提条件を公開する最もスマートな方法です。これはExcelの「名前の管理」に一度だけ表示され、すべての数式がそれをシンボルとして参照します。次の四半期の税率変更の際は、組み立てられた14個 of 数式文字列を検索して書き換えるのではなく、ジェネレーター内の定義を1行修正するだけで済みます

片方の形式にのみ属するイコール記号

数式の入力チャンネルは、移植されたコードが最も頻繁に破損する場所です。なぜなら、2つのファサードでイコール（=）記号の扱いが異なるためです。XLSのセルは、先頭に = を付けた Value を介して数式を受け取ります。XLSXのセルには、式をプレフィックス（=）なしで受け取る専用の Formula プロパティがあります。TXLSXCell.Formula に '=SUM(A1:A10)' と書き込むと、イコール記号は数式の開始マークではなく保存される式テキストの一部になってしまい、ファイルはXLS側での同じ文字列のようには動作しません

var
  Book: IXLSWorkbook;   // interface-counted: do not Free
  Names: IXLSNames;
begin
  Book := TXLSWorkbook.Create;
  // assume a sheet named 'Data' already holds the detail rows
  Names := Book.GetNames;
  Names.Add('TaxRate', '0.08');
  Names.Add('Helper', 'Data!$A$2:$A$100', False);  // False = hidden from the Name Manager

  // XLS formulas go through Value, with the '=' prefix
  Book.Sheets[1].Cells.Item[2, 2].Value := '=SUM(Data!A2:A100)*TaxRate';
  Book.SaveAs('model.xls');
end;

上記のコード断片は、さらに2つのXLS側の独自の仕様を示しています。シートコレクションが1から始まるため、0から始まるXLSXの Sheets[0] に対して、Sheets[1] が最初のシートを指します。また、第3引数を指定して非表示の名前を作成しています。これはファイル内に存在し、数式から使用できるものの、Excelの「名前の管理」には表示されません。非表示の名前は、エンドユーザーが誤って編集したり削除したりしてはならない、ジェネレーターの内部処理用として最適な手段です

シート間参照と、行が移動した際の動作

どちらの数式エンジンも標準的なシート間参照の構文を受け入れます。プレーンなシート名は Data!A1 のように直接修飾します。スペースや記号を含む名前は 'Sheet With Space'!A1 のようにシングルクォーテーションで囲む必要があります。定義された名前の RefersTo テキストの内部では、ほぼ常に Data!$A$2:$D$100 のような絶対参照を使用してください。定義された名前の内部で相対参照を使用すると、その名前を使用しているセルを基準に相対的に解決されます。これはExcelの意図された仕様ですが、誤って動作すると混乱の元になります

行や列の挿入・削除などの構造的な編集においても、シート間参照の一貫性が維持されます。XLSX側では、編集の際セル、結合、ハイパーリンク、グラフのアンカーなどと一緒に関連する名前の定義範囲もシフトするため、データブロックの上に行が挿入されても、Data!$A$2:$D$100 を指す名前は依然として正しい範囲をカバーし続けます。数式に関しては、1つの仕様上の注意事項があります。行の挿入は、編集されているシートをターゲットとする参照のみを調整します。Summary シートに記述されている Data!D2:D100 を参照する数式は、Data シートに行が追加された際に書き換えられます。これは通常期待される動作です。計算エンジンを呼び出すことでこれを簡単に確認できます

// the calculation engine resolves names and cross-sheet references in-process
V := Book.Calculate('SUM(Data!D2:D100)*TaxRate');
if VarIsNumeric(V) then
  Log('net total checks out: ' + FloatToStr(V));

Calculate は、保存することなく現在のワークブックの状態に対して任意の式を評価するため、ジェネレーターのテストにおけるアサーションとして最適です。Pascal側でソースデータから期待される集計値を計算し、ワークブック独自の数式を評価して、これら2を比較します。数式エンジンが何を、いつ評価し、どのようにカスタム関数で拡張できるかについては、数式エンジンに関する記事で説明されています

プロパティレイヤーが所有する _xlnm 名前

生成されたファイルの名前テーブルを低レベルのインスペクターで開くと、作成した覚えのない _xlnm.Print_Area や _xlnm.Print_Titles などのエントリが見つかることがあります。これらは OOXML (ECMA-376 / ISO 29500) が印刷範囲や繰り返し印刷するタイトル行を記録する方法であり、予約された識別子を持つ定義された名前として機能します。HotXLSはこれらを専用のワークシートプロパティを通じて管理するため、PrintArea や PrintTitleRows を設定すると、対応する _xlnm.* エントリが自動的に書き出されます

やってはいけないのは、この予約された名前空間に手動でアクセスすることです。PrintArea プロパティを設定しながら、同時に DefinedNames.Add を介して手動で _xlnm.Print_Area を追加すると、ワークブックの内部で1つの予約名に対して2つの矛盾する定義が保持されることになり、Excelはこの競合を開発者が依存すべきでない方法で解決します。_xlnm. で始まるすべての識別子は、プロパティレイヤーに属するものとして扱ってください。印刷設定を検査する際は、名前テーブルではなくプロパティを読み取ります。これについては、保護とページ設定に関する記事を参照してください

設計を決める前に知っておくべき2つの制限

定義された名前は、提供されている XLS から XLSX への変換ブリッジ（便法的な移行処理）ではコピーされません。SaveXLSWorkbookAsXLSX はセルの内容と基本的な書式をコピーしますが、名前テーブルはコピーされません。そのため、定義された名前に依存しているワークブックは、変換の過程で名前を失ってしまいます。変換後に DefinedNames.Add を介して名前を再作成してください。この手順は、元の XLS ファイルが保持していたスコープの歪みを正常化するための良い機会にもなります

もう1つの制限は、数式文字列とシート名とのズレ（ドリフト）です。Excelではユーザーがシート名を変更すると数式や名前の中のシート参照も自動的に書き換えるため、ユーザーがExcel内で編集したファイルは自己整合性を保ちます。一方、ジェネレーター側では、Pascalのコードがシート名のリテラルから数式文字列を組み立てているため、シート名を変更した際に関連する数式文字列の更新を忘れると、存在しないシートへの参照が発生してしまいます。シート名を単一のDelphi定数として保持し、それを Sheets.Add と数式組み立ての両方に提供するようにすれば、両者が矛盾することは決してありません。これは、レポートの出力セルにアドレスをハードコードするのではなく名前を付けるべきであるという考えと同じです。集計セルに名前が付けられていれば、デザイナーがその上に3行挿入してもテンプレートは動作し続けますが、ジェネレーターがリテラルの B17 に書き込んでいる場合は、数値が誤った場所に格納されてしまいます。これについては、テンプレートレポート生成に関する記事で説明されています

両フォーマットに対応する定義された名前API、および数式エンジンの完全なリファレンスは、HotXLS Componentに同梱されています