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

技術記事

DelphiでのHotXLS数式エンジンとカスタム関数

HotXLS Component

数式文字列のみを保存するスプレッドシートライブラリと、動作する数式エンジンを備えたライブラリは、数値を要求する瞬間まではまったく同じに見える異なる2つの製品です。多くのDelphiスプレッドシートコードはこの違いに気付きません。なぜなら、Excelがそれをカバーしてくれるからです。セルに SUM(B2:B501) と書き込んで保存すれば、人間がファイルを開いた瞬間にExcelが合計値を再計算します。しかし、人間をプロセスから排除し、その同じワークブックをサーバーのパイプラインに通してCSVに直接エクスポートすると、その違いは学術的なものではなくなります。CSVには数値の代わりに =SUM(B2:B501) というテキストがそのまま出力されます。なぜなら、それまでのどの段階でも数式が実際に評価されなかったからです

HotXLSはまさにこの問題の正しい側に位置しています。HotXLSはファイルフォーマットと同様に数式を「保存されたテキストとオプション of キャッシュされた結果」として扱うため、単純なCSVエクスポートでは結果の数値ではなく数式そのものが再現されてしまいます。しかし、HotXLSは直接呼び出すことのできる計算エンジンも搭載しています。これはXLSおよびXLSXの両方のファサードで共通のエンジンであり、エンジンが認識していない関数名を解決するためのフックも備えています。HotXLSは、Excelのオートメーションを使用せずにDelphiおよびC++BuilderからXLSおよびXLSXの読み書きを行うネイティブなObject Pascalライブラリであり、その計算エンジンが、要求に応じて保存された数式を値に戻します

数式は保存され、即時評価はされない

セルに数式を書き込んでも、何も計算されません。保存時にワークブックは数式テキストを記録します。XLS側では、Excelにファイルを開く際再計算するよう指示する RecalcOnSave （デフォルトは True）によって制御されるフラグも記録されます。そのモデルは、Excelで開かれるファイルには適していますが、CSVエクスポート、HTMLエクスポート、またはコードによるセルの読み戻しなど、セルの値を直接消費するパイプラインには適していません。それらのケースでは、Calculate を使用して明示的に評価します。この関数は、4つのエントリポイント（TXLSWorkbook、IXLSWorksheet、TXLSXWorkbook、TXLSXWorksheet）すべてで function Calculate(const Formula: WideString): Variant として公開されています

// evaluate in-process, then ship the value rather than the recipe
Total := Book.Calculate('SUM(Sales!B2:B501)');
Sheet.Cells[502, 2].Value := Total;
Book.SaveAsCSV('sales.csv', 0, ',');   // the CSV now carries the number

Calculate に渡される式は、通常のExcelの数式テキストです。シートをまたぐ参照、定義された名前、およびネストされた関数はすべて、現在のメモリ内ワークブックに対して解決されます。これは、CSVエクスポートの対応を超えて非常に役立ちます。これをアサーションの仕組みとして活用してください。500行の明細データを書き込んだ後に、ワークブックにその総合計を要求し、Pascal側で個別に計算した数値と比較することで、顧客の監査人が見つける前に範囲指定のズレ（オフバイワンエラー）を検出できます

これは、数式を多用する出力に対する適切なテスト戦略も提供します。Excelは数式言語の参照実装であるため、ビジネス上の影響が大きい数式については、Excel自身が生成した期待値を保持する承認済みのテスト用ファイルを維持し、ビルドパイプラインで生成されたワークブックの数式を Calculate を使用してそれらの期待値に対して検証します。これにより、相違点は顧客がレポートを比較して見つけるのではなく、Delphiでのテスト失敗として表面化します

OnUserFunctionによるビジネス関数の追加

エンジンが認識できない関数名に遭遇した場合、即座に失敗するのではなく、イベントを発生させます。どちらのワークブッククラスでも OnUserFunction を割り当てることで、呼び出しを自分で解決できます

procedure TReportBuilder.HandleUserFunction(Sender: TObject;
  const FunctionName: WideString; const Args: Variant;
  var Value: Variant; var Handled: Boolean);
begin
  if SameText(FunctionName, 'DISCOUNT') then
  begin
    Value := Args[0] * 0.9;   // Args arrives as a Variant array
    Handled := True;
  end;
end;

// wiring and use
Book.OnUserFunction := HandleUserFunction;
Sheet.Cells[1, 1].Value := 200;
Sheet.Cells[1, 2].Formula := 'DISCOUNT(A1)';
Net := Book.Calculate('DISCOUNT(A1) + SUM(A1:A1)');

注意すべき詳細が3つあります。第一に、実際に名前を認識した場合にのみ Handled := True を設定してください。False のままにしておくと、エンジンは未知の関数に対する通常の処理を継続するため、1つのハンドラーがすべてを要求することなく複数のワークブックに対応できます。第二に、数式の作成者は discount( や DISCOUNT( を区別なく入力するため、SameText を使用して大文字小文字を区別せず名前を比較してください。第三に、引数は評価された状態で渡されます。DISCOUNT(A1) は参照ではなくA1の値を渡すため、関数はその入力がどこから来たのかを検知できません。この最後のポイントが、次のセクションで説明する制限につながります

ハンドラーの本体は、他の外部エントリポイントと同様に防御的に扱ってください。Args 配列は数式の作成者が入力した内容を反映するため、インデックスを指定する前に引数の数と型を検証し、無効な呼び出しがエラー値（Variant Error）を返すか、例外を発生させるかを事前に決定してください。この選択は重要です。ハンドラー内でスローされた例外は、評価をトリガーした Calculate 呼び出しを通じて外部に伝播するためです。これは厳密に管理されたジェネレーターでは許容されますが、ユーザーが作成したワークブックを評価するサービスでは不適切であり、1つの不正な数式がリクエスト全体を停止させてしまいます。そのような環境では、ハンドラー内で例外をキャッチし、呼び出し元のワークフローが認識してログに記録できるシグナル値を返します

セルの位置を意識する関数にはExバリアントが必要

一部の関数は、評価される場所（セル位置）に依存します。シートごとに異なるレート、行相対のルックアップ、地域別のシートにのみ適用される地域ごとの乗数など、これらは引数の値だけでは判断できません。通常のイベントではこれを表現できないため、エンジンは OnUserFunctionEx を提供しています。これは1つの追加パラメータを除いて、通常のイベントと同じです

procedure TReportBuilder.HandleUserFunctionEx(Sender: TObject;
  const FunctionName: WideString; const Args: Variant;
  const Context: TXLSUserFunctionContext;
  var Value: Variant; var Handled: Boolean);
begin
  if SameText(FunctionName, 'REGIONRATE') then
  begin
    // the same formula yields a different rate on each regional sheet
    Value := RateForSheet(Context.SheetIndex) * Args[0];
    Handled := True;
  end;
end;

TXLSUserFunctionContext は、評価中のセルの SheetIndex、Row、および Col を保持します。関数の結果がその配置場所に少しでも依存する場合は、最初からExイベントを使用してください。すでに30個の数式が呼び出しているハンドラーに後からコンテキストを組み込むのは、初日に適切なシグネチャを選択するよりもはるかに煩雑です。また、2つのイベントはシグネチャ以外は非常に似ているため、範囲の狭い方から始める理由はほとんどありません

カスタム関数はExcelに引き継がれない

カスタム関数は完全にプロセス内部でのみ動作します。DISCOUNT という名前は、Delphiのコードとそのイベントハンドラーが実行されている間だけ意味を持ちます。保存されたファイルをExcelで開くと、DISCOUNT は単に認識されない名前になり、ユーザーのマシンに対応するVBA関数やアドインが存在しない限り、セルには #NAME? が表示されます。これが、デモと出荷可能な製品を分ける設計上の事実であり、後から気付くのではなく意図的に選択しなければならない点です

セルごとに、2つのコントラクトのどちらで出荷するかを決めてください。ユーザーがExcel内で再計算させることを想定しているセルは、Excel独自の関数ボキャブラリのみを使用して構築する必要があります。ロジックが独自仕様であるセルは、プロセス内で Calculate を使用して評価し、プレーンな値として永続化する必要があります。これにより、カスタム関数はファイルコンテンツではなく、内部の計算ルールとして動作します。サポートチケットを確実に発生させてしまう失敗パターンは、その中間に位置する「カスタム関数の数式を永続化し、Excelがそれを処理することを期待する」というものです

値のみを永続化するコントラクトには、知的財産を保護できるという隠れたメリットがあります。Delphiプロセス内で評価され、数値として出力された価格ルールは、可視の数式のようにワークブックから逆コンパイルされることがなく、ユーザーが中間のセルを編集して壊すこともありません。請求書ジェネレーター、コミッション計算書、および料金表は、ほぼ常にこのカテゴリに属します。ライブな数式が本当に必要なのは、顧客が入力値を変更して合計値が動くのを確認するインタラクティブなシミュレーションモデルであり、それらはExcel独自のボキャブラリと定義された名前を使用して構築する必要があります

計算モード、反復計算、およびR1C1：XLSファサードの設定

XLSファサードは、Excelがファイルから読み取るBIFFレベルの計算設定を公開します。CalculationMode は xlCalcManual、xlCalcAutomatic （デフォルト）、または xlCalcAutomaticExceptTables を受け入れ、ファイルを開いた後のExcelの動作を決定します。数千の数式を含む大規模なワークブックは、手動計算モードで配信する方が親切な場合があり、受信者が再計算のタイミングを決定できます。EnableIteration （デフォルトは False）は、MaxIterations （デフォルトは100）および MaxIterationChange （デフォルトは0.001）と組み合わせて、一部の財務モデルに登場する意図的な循環参照（収束計算）を可能にします。ReferenceStyle は A1 と R1C1 の表示を切り替え、UseFullPrecision はExcelの「表示桁数で計算する」オプションを反映します

これらのプロパティはBIFFレコードにマッピングされるため、XLSファサードに存在します。.xlsx を生成する際は、数式が反復計算の設定に依存しないように設計するか、Delphi側で収束値を計算して結果を書き込んでください

配列数式：公開されているエントリポイントはXLSX

従来のCSEスタイル（Ctrl+Shift+Enter）の配列数式は、TXLSXRange.SetArrayFormula を介して作成されます

// one array formula spanning A2:A4
Sheet.RCRange[2, 1, 4, 1].SetArrayFormula('A1*{1;2;3}');

同等のメソッドはXLSのクラス階層にも存在しますが、privateセクションに配置されているため、.xls ファイルに対して新しい配列数式を作成するサポートされた方法はありません。開かれたファイル内に存在する既存 of 配列数式はそのまま往復（ラウンドトリップ）しますが、新規作成はできません。したがって、仕様として配列セマンティクスが必要な場合は、.xlsx を対象にするというシンプルなルールに従ってください。レガシーな .xls で配列動作がどうしても必要な場合の現実的なアプローチは、Delphi側で配列結果を計算し、個々の値をセルに書き込むことです

関連する記事として、エンジンが実行する名前解決について説明した定義された名前とシートをまたぐ数式、および明示的な計算が必要となるエクスポートの動作について詳しく説明したCSVおよびTSVエクスポートに関する記事があります。サポートされている関数セットを含む完全なエンジンのリファレンスは、HotXLS Componentに同梱されています