Delphiで再計算せずExcelの数式キャッシュ値を読む

HotXLSは、ネイティブなDelphiとC++Builder向けExcelライブラリとして、数式の脇にExcelがすでに格納した値をTryGetCachedFormulaValueとIXLSFormulaCacheReaderを通じて読みます。どちらのエントリポイントも計算機を呼び出さず、数式トークンを逆コンパイルせず、ダーティ状態を更新せず、モデルへ何かを書き戻すこともありません。読むだけのブックは、開いたときのまま残ります

これを動かすシナリオは地味で、極めてよくあります。夜間ジョブが他人が作った数百のブックを開き、それぞれから合計の1列を引き出し、その数値をウェアハウスへ流し込みます。合計はすでにファイルの中にあります。Excelが計算して保存したのです。ところがジョブが数式セルに値を問うた瞬間、その問いに答えを1つしか持たないライブラリは依存グラフを構築してシート全体を評価し始め、I/Oバウンドであるべきジョブが計算ベンチマークに変わります

数式セルを読むだけで全体再計算が起きるのはなぜか

数式セル上の値ゲッターは値を生成せよという要求であり、生成の唯一の普遍的に正しい方法が数式の評価だからです。ブックを編集するアプリケーションにはそれが正しいデフォルトで、抽出するパイプラインには間違ったデフォルトです。さらに悪いことに評価は副作用を免れません。結果をセルへ書き込み、ダーティフラグを立て、関数が未対応だったり外部参照が壊れていたりすると生成元アプリケーションと異なる解決をします。運用チームに読み取り専用だと説明したジョブが、ディスク上のものと一致しないブックを静かに生み出し、後から何かがそれを保存すればディスク上のファイルまで変わります

キャッシュ値の読み取りは契約のもう半分です。より狭い問い——生成元アプリケーションはここに何を格納したか——に答え、それ以外には答えません。本当に新しい数値が欲しいとき、HotXLSは依存グラフ駆動のインクリメンタル再計算を引き続き提供します。要点は、抽出と評価は2つの別個の呼び出しであるべきで、2つの気分を持つ1つの呼び出しであるべきではないということです

1つのセルについての3つの直交する事実

結論から言うと、キャッシュされた数式値は3つの独立した事実を運び、それを単一のVariantへ畳み込むと必要な情報が失われます。TXLSFormulaCacheInfoはこれをState、Kind、Valueとして分けて保持します。TXLSFormulaCacheStateは5つのケース——xlfcsNotFormula、xlfcsMissing、xlfcsLoaded、xlfcsCalculated、xlfcsInvalidated——にわたる由来を記録し、TXLSFormulaCacheValueKindはペイロードをxlfcvBlank、xlfcvNumber、xlfcvDateTime、xlfcvString、xlfcvBoolean、xlfcvErrorに分類します。この分離があってこそ存在を正直に報告できます。キャッシュされた空白、キャッシュされた空文字列、キャッシュされたFalse、キャッシュされたゼロ、キャッシュされたエラーはすべて実値であり、存在はVarIsEmptyやVarIsNullから推論できません。TryGetCachedFormulaValueがTrueを返すのはxlfcsLoadedとxlfcsCalculatedだけです。そしてFalseを返すときも診断可能な状態を埋めて返します

var
  Book: TXLSXWorkbook;
  Info: TXLSFormulaCacheInfo;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('quarterly-model.xlsx');
    // SheetIndex、Row、Col はすべてここでは1始まり
    if Book.TryGetCachedFormulaValue(1, 12, 5, Info) then
      Writeln('cached value: ', VarToStr(Info.Value))
    else
      Writeln('no usable cache, state ordinal ', Ord(Info.State));
  finally
    Book.Free;
  end;
end;

キャッシュ値が欠けているのはなぜか

TryGetCachedFormulaValueがFalseを返す理由は正確に4つあり、どれが当てはまるかは状態が教えてくれます。xlfcsNotFormulaはセルがリテラルか何も持っていないことを意味し、座標が範囲外の場合も同じ答えに畳み込まれます。xlfcsMissingはセルが本当に数式なのに生成元が値ペイロードを格納しなかったことを意味します。数式を書いて結果は初回オープン時にExcelへ任せるジェネレータでよく起こる帰結です。xlfcsInvalidatedはロード後に数式テキストが置き換えられたことを意味し、そこにあった値はもはや存在しない式を記述します。対照的にxlfcsCalculatedは成功ケースです。自分のコードまたはHotXLS評価器がこのセッションで生んだ値を示し、ファイル由来のxlfcsLoadedと対になります

欠けたキャッシュを誠実に扱うことは、覆い隠すことより重要です。HotXLSは値をでっち上げません。保存時も同じく厳格で、xlfcsLoadedとxlfcsCalculatedだけがキャッシュ値を出力し、xlfcsMissingとxlfcsInvalidatedは古い数値をファイルに凍結する代わりに数式だけを書きます。パイプラインでの健全な対応は3つ残されます。行を飛ばして欠落を記録するか、その1ブックだけ意図的に再計算してコストを受け入れるか、評価して突き合わせるかです。評価した数値が生成元アプリケーションが書いたはずのものと食い違うなら、結果から当て推量するのではなく数式評価トレーサが2つの計算がどこで分岐したかを見つける道具です

クラシック、OOXML、ODFの各エンジンにまたがる1つのリーダ

パイプラインは、開いたばかりのファイルがBIFFかOOXMLかODFかを気にするべきではありません。IXLSFormulaCacheReaderは3者すべてに対する単一の読み取り専用エントリポイントです。TXLSWorkbook.CreateFormulaCacheReaderもTXLSXWorkbook.CreateFormulaCacheReaderも、各エンジンがすでに使っている疎なセル検索の上の軽量アダプタを返し、シート、行、列の座標は同じく1始まりです。ブッククラス自身は意図的にこのインターフェースを実装しません。ブックへのインターフェース参照は所有権の意味論を変え、呼び出し側にライフタイムリースの裏をかかせてしまうからです。代わりに、ブックを破棄するとそのリース内の生ポインタがクリアされ、コードがまだ保持しているリーダは解放済みメモリを参照する代わりに次の問い合わせでEXLSFormulaCacheReaderInvalidatedを投げます。フェイルファストのライフタイム検査であって、並行性の保証ではありません

var
  Reader: IXLSFormulaCacheReader;
  Info: TXLSFormulaCacheInfo;
  Row, Missing, Errors: Integer;
  Total: Double;
begin
  Reader := Book.CreateFormulaCacheReader;
  Total := 0;
  Missing := 0;
  Errors := 0;
  for Row := 2 to LastRow do
    if Reader.TryGetCachedFormulaValue(1, Row, 7, Info) then
    begin
      case Info.Kind of
        xlfcvNumber: Total := Total + Double(Info.Value);
        xlfcvError:  Inc(Errors);
      end;
    end
    else if Info.State = xlfcsMissing then
      Inc(Missing);
  // 計算機は走らず、ダーティフラグも動かず、Book は無変更のまま
end;

キャッシュバイトが実際に眠る場所

クラシックな.xlsファイルでは、キャッシュはFormulaレコードのFormulaValueフィールド、[MS-XLS] §2.5.133が記述する8バイトです。上位ワードが$FFFFと等しいとき、ペイロードはIEEE 754のdoubleではなくタグ付きバリアントで、レイアウトは微妙に間違えやすいものです。バリアント型はval[0]にあり、booleanまたはBErrペイロードはval[2]にあり、val[1]は未定義です。HotXLSは以前、ペイロードをval[1]から読んでいました。数値ではなくbooleanやエラーをキャッシュする特定のファイルでのみ表面化する種類のオフバイワンです。リーダと共有数式ライタは今や同じオフセットで合意し、キャッシュされたTRUEはロードとセーブを無傷で通過し、ノイズへは劣化しません

パッケージ形式での型忠実度は別の問題で、専用の罠を持ちます。OOXMLではキャッシュ値はc要素の<v>としてぶら下がり、t属性がECMA-376 Part 1 §18.3.1.4に従って型を名指します。HotXLSはt="e"を直接varErrorのVariantへ読み、保存時に標準のエラーテキストへ写し戻すので、エラーが普通の整数に化けることはありません。ただしDelphi RTLはここで助けになりません。VarAsType(Integer, varError)は変換例外を投げるからです。動く構成はTVarData.VTypeとTVarData.VErrorを直接設定するものです。日付は逆方向に同じ規律に従います。t="d"とODFの日付値型は明示的な型宣言でありvarDateになります。一方BIFFの数値キャッシュは日付フラグを一切運ばないのでDoubleのままです。HotXLSがセルの表示形式から日付を推測することはありません。表示形式はプレゼンテーションであり、キャッシュはデータだからです。ODFはもう1つ知っておく価値のあるケースを加えます。office:value-type="void"は存在するが値を運ばないキャッシュを表現します。ODFにエラー値型はないので、エラーに見えるテキストはエラーへ昇格されずテキストとして保持されます

function DescribeCache(const Info: TXLSFormulaCacheInfo): string;
begin
  case Info.State of
    xlfcsNotFormula:  Result := 'not a formula cell';
    xlfcsMissing:     Result := 'formula stored with no cached value';
    xlfcsInvalidated: Result := 'formula replaced since load';
  else
    case Info.Kind of
      xlfcvError:    Result := 'error code ' + IntToStr(TVarData(Info.Value).VError);
      xlfcvDateTime: Result := DateTimeToStr(VarToDateTime(Info.Value));
      xlfcvBoolean:  Result := BoolToStr(Info.Value, True);
      xlfcvNumber:   Result := FloatToStr(Double(Info.Value));
      xlfcvString:   Result := VarToStr(Info.Value);
    else
      Result := 'present but blank';
    end;
  end;
end;

共有数式はキャッシュ値を共有するか

共有しません。そう仮定することが、一括処理が列全体に同じ数値を報告する末路です。OOXMLの共有数式は数式式とストレージの最適化だけを共有し、各メンバーセルは自分の<v>を所有します。したがってHotXLSはルートメンバーのキャッシュを、値なしで到着したフォロワーへ伝播させません。xlfcsMissingとしてロードされたフォロワーは、保存と再オープンの後もxlfcsMissingを報告し続けます。グループがそもそもどう格納され展開されるかを検討中なら、共有数式の si 属性とその展開の仕組みは別途扱っています。キャッシュ読み取りに関して、ルールは1行に要約されます。全セルに問え、問うて得ていないものを信用するな

キャッシュ値の読み取り、エンジン横断の統一リーダ、そして呼ばないことを選べる再計算エンジンは、DelphiとC++Builder向けの標準HotXLS Delphi Spreadsheet Componentに含まれて出荷されます。ExcelにもOLEオートメーションサーバにも依存しません。製品ページに、ここで示したブックとリーダのエントリポイントの完全なAPIリファレンスがあります