DelphiのXLS保存で数式が黙って再計算されないようにする

Excel が 37 と示す総合計が、プログラムが .xls ファイルを開いて1つもセルを編集せずに保存しただけで、67 に変わって返ってくることがあります。増えた 30 は無作為ではありません。総合計がカバーする範囲の中に座る 10 と 20 の2つのグループ小計の合計に、ぴったり一致します。この数字が動くには、2つの問題が揃わなければなりません。保存が出力の道で全部の数式を再計算すること、そして再計算するものが、SUBTOTAL は他の SUBTOTAL セルをスキップすることを忘れること。どちらか1つを取り除けばファイルは無事です。だから1つ目の問題は、2つ目の背後に気づかれず座っているのです

XLSファイルの保存で数式の値が変わる理由

保存が数式の値を変えるのは、ライターが各数式の隣に格納する数を、ロードした値のコピーではなく自分で計算するときです。すべての BIFF8 Formula レコードは、そのセルについて Excel が最後に表示した結果を運ぶ8バイトの FormulaValue フィールド（[MS-XLS] §2.4.127、値のレイアウトは §2.5.133）を持っています。ライターはこの8バイトに何かを入れなければならず、最短のコード経路は、すべての数式を計算エンジンに渡すことです。一度そうなると、保存のたびに、誰も頼んでいない完全な再計算が走り、そのエンジンと Excel の意見が割れる場所すべてが、触れていないとユーザーが信じているファイルの中の、変わった数字になります

SUBTOTAL の意見の割れは良い例になります。スプレッドシートの規則は単純で、罠は Delphi の Variant 型付けにあるからです。Excel はすべての SUBTOTAL 関数番号を、自分の数式が別の SUBTOTAL であるセルを無視するものと定義しています。明細行の合計が 37 のシート（10 と 20 に小計される2つのグループの下の行と、どのグループにも入らない 7）では、総合計は 37 です。入れ子の小計を見つけられないエンジンは、10 と 20 をもう一度数えて 67 に着地します。見つけるには、たいてい数式テキストへの短いチェックで足りるのですが、素直な Delphi 版はコンパイルも走りもするのに、何にも一致しません

//  自分で書くヘルパー。たとえば、外側の SUBTOTAL が
//  スキップすべき数式をシートから探すとき用
function IsNestedSubtotal(const F: Variant): Boolean;
begin
  //  この誘惑的なテストは Unicode Delphi では決して成功しない。
  //  Variant に代入された String は varUString であり、varOleStr ではない
  //    Result := (VarType(F) = varOleStr) and ...

  Result := False;
  if VarIsStr(F) then  //  varString、varOleStr、varUString を受け入れる
    Result := SameText(Copy(F, 1, 9), 'SUBTOTAL(') or
      SameText(Copy(F, 1, 10), '=SUBTOTAL(') or
      SameText(Copy(F, 1, 10), 'AGGREGATE(') or
      SameText(Copy(F, 1, 11), '=AGGREGATE(');
end;

//  HotXLS での使い方（シート・行・列は 1 起点）：
//    if IsNestedSubtotal(Book.Sheets[1].Cells[12, 4].Formula) then ...

VarIsStr は、同じ問いに対して HotXLS の評価器が適用するテストです。さらに AGGREGATE セルも、Excel と同じく、外側の小計から除外されるものとして扱います。ただし、述語を直すだけでは話が終わりません。正しいチェックを持った再計算する保存が 37 を書くのは、そのエンジンがこの特定のファイルでたまたま Excel に同意しているからです。エンジンが少し違う扱いをする次の数式は、また動きます。SUBTOTAL と AGGREGATE がどのセルをスキップするかの規則は、隠し行も含め、SUBTOTAL と AGGREGATE の隠し行の記事で扱っています。キャッシュ優先の保存なら、計算を頼んだことのないファイルは、エンジンがどう判断しようと、セルに 37 を入れたままディスクへ戻ります

クラシックXLSの保存が各Formulaレコードに書き込むもの

HotXLS は BIFF8 ワークブックをキャッシュ優先で保存します。数式セルごとに、TXLSWorkbook.SaveAs は TryGetCachedFormulaValue が値を報告できるときはキャッシュ値を書き、できないときにだけ評価器を走らせます。決めるのはキャッシュの状態です。状態が xlfcsLoaded（値はソースファイルから来た）か xlfcsCalculated（HotXLS がこのセッションで計算した）のセルは、CacheInfo.Value がバイト単位で書かれます。状態が xlfcsMissing か xlfcsInvalidated のセルは保存時に評価されます。これは Excel が保存を扱うやり方と同じです。各セルが表示しているもののスナップショットであり、手動計算モードではひどく古くなっていることもありますが、それでも忠実に書きます

評価の経路には、残す正当な理由があります。コードから Cells[Row, Col].Formula で代入した数式はまだキャッシュを持たず、ロード済みセルへの新しい数式の代入は xlfcsInvalidated へ移します。古い結果は古い式のものだからです。どちらも保存の間に計算されるので、ゼロから生成したワークブックは、Excel でゼロではなく計算された数字を表示して開きます。評価器でも値が出せないとき（未対応の関数、壊れた外部リンク）は、ライターがゼロを格納し、§2.4.127 のレコードフラグのビット0である fAlwaysCalc を立てます。Excel はプレースホルダを信用する代わりに、オープン時にそのセルを再計算します

関係ありそうで、そうではないプロパティが1つあります。TXLSWorkbook.RecalcOnSave は、CalcSaveRecalc レコード（[MS-XLS] §2.4.37）にある Excel の「保存前にワークブックを再計算する」オプションの写しです。これが意味を持つのは Excel に対してのみ、しかも手動計算モードだけなので、HotXLS は単純にファイルへ書き込みます。SaveAs が FormulaValue に何を入れるかを決めるとき、これを参照しません。下のラウンドトリップが、自分のファイルでこの契約を見る最短の道です

procedure CheckRoundTrip(const Source, Target: string;
  Row, Col: Integer);
var
  Book: TXLSWorkbook;
  Before, After: TXLSFormulaCacheInfo;
begin
  Book := TXLSWorkbook.Create;
  try
    if Book.Open(Source) <> 1 then
      raise Exception.Create('Cannot open ' + Source);
    //  シート・行・列は 1 起点
    if not Book.TryGetCachedFormulaValue(1, Row, Col, Before) then
      raise Exception.Create('Cell carries no usable cached value');
    if Book.SaveAs(Target) <> 1 then   //  キャッシュ済みセルは格納どおりに書かれる
      raise Exception.Create('Save failed');
  finally
    Book.Free;
  end;

  Book := TXLSWorkbook.Create;
  try
    Book.Open(Target);
    Book.TryGetCachedFormulaValue(1, Row, Col, After);
    //  期待：After.Value は Before.Value と等しく、After.State は
    //  保存ファイルから来たのだから再び xlfcsLoaded
    Writeln(VarToStr(Before.Value), ' -> ', VarToStr(After.Value));
  finally
    Book.Free;
  end;
end;

この契約の読み側は、各状態の意味や、キャッシュされた空白や False がなぜ値として数えられるのかを含め、Read Excel Cached Formula Values in Delphi Without Recalc で説明しています。入れ子の小計があるワークブックの総合計セルで上のチェックを走らせれば、2行とも同じ数字を印字します

BIFFの共有数式ルートはキャッシュ値をどこに持つのか

ほかの数式セルと同じく、自分自身の Formula レコードの中です。ただし式は1レコード後に届き、この隙間こそ、不用心なリーダーがキャッシュを失う場所です。BIFF8 は共有数式を、グループの左上セルの Formula レコードの直後に置かれる ShrFmla レコード（[MS-XLS] §2.4.260）として格納します。ルートを含むすべてのメンバーセルは、単一の PtgExp トークン（§2.5.198）からなる rgce を運びます。バイト $01 の後に、ルートの行と列が続く形です。フォロワーセルは簡単です。姿を現すときにはルートの式がすでに存在し、自分の FormulaValue は自分のレコードに入っているからです。ルートは簡単ではありません。Formula レコードが読まれた時点では、キャッシュ値 37 を持ち、式をまだ持っていないのです

セルへの式の取り付けはキャッシュをクリアします。そしてこれは真のままであるべきです。置き換えられた数式は、本当に古い結果を無意味にするからです。ただし、同じ規則を ShrFmla レコードに盲目的に適用すると、式が届いた瞬間にルートの 37 が捨てられ、TryGetCachedFormulaValue はルートを xlfcsMissing と報告し、保存は、ユーザーが最も見そうなセルに対して正確に評価器へフォールバックします。HotXLS のリーダーは、ShrFmla レコードを待つ間、ルートのデコード済み値を退避させ、式を取り付け、それからロード済みの値を状態 xlfcsLoaded で戻します。配列数式（Array レコード、§2.4.4）は同じレコード順序をたどり、同じ扱いを受けます。キャッシュの String 形式は、この問題を最初から抱えていません。テキストが別の String レコードで届き、行と列でセルに突き合わせられるので、ストリーム内の位置が意味を持たないからです

OOXML 側も扱うなら、XLSX shared formula si expansion article が、パッケージ形式にはレコード順序の隙間はないものの、独自の展開の落とし穴があることを説明しています

共有数式のフォロワーは式のシフト済みコピーを所有する

ShrFmla レコード内の式はルートセルとの相対で書かれています。だから各フォロワーには、ルートからの距離ぶん変位した自分のコピーが要ります。そのまま写すと、B1 をルートに持つ =A1*3 のグループは、B2 と B3 に同じ =A1*3 を渡します。HotXLS が各フォロワーに渡すのは、相対参照がフォロワーの行と列の距離ぶんオフセットされたコピーで、B2 は本物の =A2*3 を、B3 は =A3*3 を所有します

だからこそ、素のラウンドトリップではフォロワーの式が正しいか分かりません。フォロワーはロード値を保存の間も保つので、そのままのコピーでも通ってしまいます。何かを証明するチェックは、入力を変えて再計算しなければなりません。下のルーチンは UseSharedFormulas をオンにして（既定は False で、その場合はセルごとに1つの Formula レコードを書く）共有グループを書き出し、ファイルを開き直し、A2 を編集して B2 を見張ります

procedure CheckSharedFollower(const FileName: string);
var
  Book: TXLSWorkbook;
  Sheet: TXLSWorksheet;
  Info: TXLSFormulaCacheInfo;
  Row: Integer;
begin
  Book := TXLSWorkbook.Create;
  try
    Sheet := Book.Sheets.Add;
    for Row := 1 to 3 do
    begin
      Sheet.Cells[Row, 1].Value := Row * 2;                 //  2, 4, 6
      Sheet.Cells[Row, 2].Formula := '=A' + IntToStr(Row) + '*3';
    end;
    Book.UseSharedFormulas := True;   //  B1:B3 が1つの ShrFmla グループになる
    Book.SaveAs(FileName);            //  まだキャッシュなし：6, 12, 18 に評価される
  finally
    Book.Free;
  end;

  Book := TXLSWorkbook.Create;
  try
    Book.Open(FileName);
    Book.Sheets[1].Cells[2, 1].Value := 10;   //  フォロワーの入力を編集する

    Book.TryGetCachedFormulaValue(1, 2, 2, Info);
    Writeln('B2 before: ', VarToStr(Info.Value));   //  期待：12、まだロード値

    Book.Recalculate;
    Book.TryGetCachedFormulaValue(1, 2, 2, Info);
    Writeln('B2 after: ', VarToStr(Info.Value));    //  期待：30、=A2*3 より
  finally
    Book.Free;
  end;
end;

=A1*3 を再利用したフォロワーなら、2行目は 6 を印字します。1行目も同じくらい大事です。リテラルの編集は、何かが再計算するまで、依存する数式のロード値を元の場所に残すと示しているからです

キャッシュ優先の保存がこちらに委ねるもの

キャッシュ優先の保存は、ロードされたものを保存します。ロードされたものがまだ真であるかどうかは追跡しません。数式が依存するセルを変えると、依存グラフは次の再計算のために印を付けられますが、依存セルは xlfcsLoaded の値を保ち続け、SaveAs はその古い数字を書きます。先に Recalculate を呼ぶか、セルの Value を読む（これは数式を計算して状態を xlfcsCalculated へ動かします）のでない限り、です。Recalculate は評価できなかった数式セルの数を返すので、ゼロでない結果は保存の前に記録する価値があります

よくあるパイプラインでは、プログラムは他人が作ったワークブックを開き、いくつかのラベルやヘッダーを書き込み、保存します。そのファイルの中で値が変わるべきものは何もなく、キャッシュ優先の保存では実際に何も変わりません。逆に入力を編集するプログラムは、再計算ステップを所有します。そのステップは、編集の隣にある見える1つの Recalculate 呼び出しとして、コードを読む人誰の目にも見える場所に置くべきものです。Excel は手動計算モードで同じ取引をします。XLSX ライターには独自のポリシー、XLSX ワークブッククラスに設定する xrmManual、xrmIfDirty、xrmAlways の各モードを持つ RecalcBeforeSave があり、上のどれとも独立しています

この設計から2つの境界が続きます。キャッシュ経路が助けるのは、状態が xlfcsLoaded か xlfcsCalculated のセルだけなので、何千もの新しい数式を書くだけで評価しないジェネレータは、保存の間もセルごとに1回の評価を払います。そして契約が支配するのは、保存が書くものであって、明示的な再計算が計算するものではありません。触れていないファイルは Excel が格納した数字を保ち、Recalculate は HotXLS 評価器の結果を書きます。パイプラインを再計算つきの保存へ切り替える前に、代表的なワークブックで両者を比べてください

HotXLS Delphi Spreadsheet Component は、Excel も OLE オートメーションもインストールなしに、Delphi と C++Builder からクラシック BIFF8 と XLSX のワークブックを読み書きします。製品ページには、これらの例で使ったワークブック・キャッシュ値・再計算の各呼び出しのドキュメントがあります