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

技術記事

Excel がキャッシュ済み #SPILL! と #CALC! を無視する理由

HotXLS Delphi Component

Excel がキャッシュ済みの #SPILL! や #CALC! を信用するのは、ワークブックが近代的エラーの契約一式を運んでいるときだけです。XLRICHVALUE 拡張 URI が小文字の GUID である Rich Data メタデータ、7 の fileVersion、最低 191029 の calcId、そして microsoft.com:RD 計算機能です。ネイティブ Delphi XLSX コンポーネントである HotXLS は、保存されるセルがこれらのエラーを保持していれば、4 つすべてを出力します

どれか 1 つでも欠けると、失敗はめったに派手ではありません。時々 Excel 16 はファイルのオープンをまったく拒否します。たいていは普通に開き、自分の計算エンジンが #SPILL! を生んだ場所に #VALUE! を表示します。計算のバグに見え、完璧に正しい数式コードを延々と探すことになります。F9 を押せば正しいエラーが出るので、問題は「自分のマシンでは動く」として閉じられがちです。顧客が自動計算オフでレポートを開くまで。本記事では、各要素が何をするか、どう考えても効くはずのない GUID 大文字小文字の罠、そしてまったく同じ静かなやり方で失敗する配列テキストキャッシュのルールをたどります

Excel がキャッシュ済み #SPILL! の代わりに #VALUE! を示すのはなぜか

Excel が #VALUE! を示すのは、文字通りそれがセルに格納されているものだからです。ECMA-376 SpreadsheetML のレガシーエラータイプにはエラーリテラルの固定リストがあり、#SPILL! と #CALC! はそこに載っていません。t="e" のセルが言えるのは #NULL!、#DIV/0!、#VALUE!、#REF!、#NAME?、#NUM!、#N/A だけで、新しい Excel バージョンは後方互換フォールバックとして #VALUE! を書き、本物のエラータイプを value-metadata インデックスに吊るします

このインデックス、vm 属性は xl/metadata.xml を指し、それは実際のエラーを記述する Rich Data 値を指します。旧いリーダーは #VALUE! を見て先へ進みます。現行の Excel はチェーンをたどって近代的エラーを表示しますが、チェーンが信用できると判断したときに限ります。ディスク上の、あふれて塞がれた数式はこう見えます

<!-- sheet1.xml: レガシーリテラルと value-metadata ポインタ -->
<c r="A1" t="e" cm="1" vm="1">
  <f t="array" ref="A1">_xlfn.SEQUENCE(3)</f>
  <v>#VALUE!</v>
</c>

<!-- xl/richData/rdrichvaluestructure.xml -->
<s t="_error">
  <k n="errorType" t="i"/>
  <k n="colOffset" t="i"/>
  <k n="rwOffset" t="i"/>
</s>

<!-- xl/richData/rdrichvalue.xml: errorType 8 が #SPILL! -->
<rv s="0"><v>8</v><v>0</v><v>2</v></rv>

HotXLS は #SPILL! のために列と行のオフセット付きでエラータイプ 8 を書き、#CALC! のためにオフセットなしでエラータイプ 13 を書きます。レガシーの <v> はどちらの場合も意図的に #VALUE! のままです。Rich Data を無視するリーダーが目にすべき値はこれだからです

Excel 16 が実際に確認する小文字の GUID

XLRICHVALUE future-metadata ブロックは、拡張 URI として {3e2802c4-a4d2-4d8b-9148-e3be6c30e623} を、1 文字も違えず小文字で使わねばなりません。16 進の文字を大文字にすると、手元の Excel 16 ビルドはワークブックの通常のオープンを拒否しました。すべての XML パートがパースされ、すべてのリレーションシップが解決され、すべてのカウントとインデックスが揃っていても、です

<futureMetadata name="XLRICHVALUE" count="1">
  <bk>
    <extLst>
      <ext uri="{3e2802c4-a4d2-4d8b-9148-e3be6c30e623}">
        <xlrd:rvb i="0"/>
      </ext>
    </extLst>
  </bk>
</futureMetadata>
<valueMetadata count="1">
  <bk><rc t="2" v="0"/></bk>
</valueMetadata>

COM やレジストリを扱ったことがある人なら、GUID は大文字小文字を区別せず比較されると知っています。ここで噛みつくのはまさにその習慣です。遠回りに切り分けました。Excel が拒否する最小パッケージを組み立て、1 つずつ交換していったのです。コンテンツタイプの置き換えでは直りませんでした。rich value types パートの追加でも直りませんでした。名前空間接頭辞の変更や rich value データの書き直しでも直りませんでした。あの単一の uri 属性を小文字にしたら直り、同じパッケージが #CALC! と #SPILL! の両方を開いて表示しました。拡張 URI は不透明な文字列リテラルとして扱い、フォーマット定義からコピーし、自分で大文字小文字を選ぶ GUID フォーマッタには決して通さないことです

診断パッケージを手で書く人には、実務上の帰結があります。編集は XML パーサで行い、シリアライズ済み XML への全置換では行わないことです。ある 1 トークンを狙った一括置換は、パートの他の場所の &gt; のようなエンティティを壊し得ます。そうなると、元の問いではなく壊れたプローブをデバッグすることになります

キャッシュ済みの近代的エラーを Excel が読むのに他に何が要るのか

メタデータパートとそのリレーションシップは必要ですが十分ではありません。完璧に正当な Rich Data チェーンが揃っていても、Excel はワークブックパートが近代的な計算エンジンから来たと自己宣言するまで、キャッシュ済みエラーを無視し続けました。HotXLS は、保存されるセルが #SPILL! か #CALC! を保持しているときはいつでも、workbook.xml に 3 つの宣言を書き込みます

<fileVersion appName="xl" lastEdited="7" lowestEdited="7"/>
...
<calcPr calcId="191029"/>
<extLst>
  <ext uri="...">
    <calcFeatures>
      <feature name="microsoft.com:RD"/>
    </calcFeatures>
  </ext>
</extLst>

fileVersion 要素は素の ECMA-376 Part 1 ワークブックメタデータであり、7 の lastEdited / lowestEdited は、このファイルを最後に保存したのが Rich Data を理解するアプリケーション世代だと Excel に告げます。calcPr の calcId は計算エンジンのバージョンを識別し、191029 はこれらのキャッシュに対して Excel が受け付ける最小値として見つけたものです。計算機能拡張の中の microsoft.com:RD エントリ——http://schemas.microsoft.com/office/spreadsheetml/2018/calcfeatures 名前空間のもの——は、キャッシュ値を生んだエンジンが Rich Data のセマンティクスで評価したことを宣言します。サブタイプだけ、types パートだけ、あるいは違う接頭辞とリレーションシップパスだけでは、結果は変わりませんでした。3 つの宣言がすべて揃う必要がありました

自分でこれらの値を設定するなら、HotXLS の適用の仕方の細部で知っておく価値のあるものが 3 つあります

  • TXLSXWorkbook.CalcId は上書きされません。ライターが 191029 へ引き上げるのは出力の中だけなので、より高い CalcId を設定した呼び出し側はそれを保ち、メモリ内のプロパティは割り当てた通りのままです
  • 開いたファイルと共に来た既存の計算機能と無関係なワークブック拡張は、置き換えられず保持されます
  • ライターは追加の前に既存の microsoft.com:RD 機能を確認するため、同じワークブックを繰り返し保存しても重複した宣言が積み上がることはありません
var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('forecast.xlsx');
    Sheet := Book.Sheets[1];
    // A1 は 3 行にあふれる。A2 が占有されているため結果は #SPILL!
    Sheet.Range['A1'].SetDynamicArrayFormula('=SEQUENCE(3)');
    Sheet.Cells[2, 1].Value := 'blocker';
    Book.Recalculate;
    // 高い CalcId は生き残る。ライターが 191029 へ引き上げるのは低いときだけ
    Book.CalcId := 191029;
    Book.SaveAs('forecast-out.xlsx');   // rich error パートは自動で書かれる
  finally
    Book.Free;
  end;
end;

Rich Data パートを自分で組み立てることはありません。書き込みの前に、TXLSXWorkbook がすべてのセルを #SPILL! か #CALC! の値で走査し、xl/metadata.xml と 2 つの rich value パートを作成または拡張し、エラーがその後変わったり消えたりしたセルから古い value-metadata リンクを落とします。上の明示的な CalcId の割り当てはオプションで、それが尊重されることを示すために載せただけです

配列テキストキャッシュは t="str" に属し、共有文字列ではない

同じ静かな失敗が、配列の中のテキスト結果にも現れます。HotXLS は数式セルのキャッシュテキストと、CSE または動的配列範囲のすべてのフォロワーセルのキャッシュテキストを、<v> にリテラル文字列を入れた t="str" として保存します。これらのキャッシュを t="s" の共有文字列として書くと、キャッシュ済みファイルを読むとき Excel は配列セルに #VALUE! を表示します

<!-- 正しい: 配列フォロワー上のリテラルテキストキャッシュ -->
<c r="B3" t="str"><v>Northwind</v></c>

<!-- 誤り: 共有文字列インデックス。Excel はキャッシュから #VALUE! を示す -->
<c r="B3" t="s"><v>17</v></c>

これは間違えやすいです。t="s" は普通の値セルには正しい選択で、ほとんどのライターは既定ですべての文字列を共有文字列テーブルへ回すからです。罠は検証の中にあります。Excel で再計算すれば配列セルは正しいテキストを示します。それは数式が正しいことの証明であって、書いたキャッシュについては何も証明しません。ユーザーが手動計算でレポートを開くなら、あるいは下流ツールが計算なしでキャッシュ値を読むなら、共有文字列版はただ壊れています

数式の保存構文: 先頭の等号、カンマ、_xlws

関連する掃除が同じライターに載ります。OOXML に入っていく数式は、ユーザーがタイプした文法ではなく、ファイルの文法で格納されます。HotXLS は Formula に割り当てたテキストをラウンドトリップ表示のためにそのまま保持しますが、シリアライズされる数式は先頭の = を落とし、関数引数の間にカンマを使い、配列定数の行区切りとクォートされたテキストには手を触れません。FILTER と SORT は、ワークシート専用関数に対して Excel が期待する _xlfn._xlws. 接頭辞付きで格納され、SEQUENCE などの他の future 関数は素の _xlfn. 接頭辞を得ます

// セミコロン区切りで入力。格納は次の通り
//   _xlfn._xlws.FILTER(A2:A50,C2:C50>1000)
Sheet.Range['E2'].SetDynamicArrayFormula('=FILTER(A2:A50;C2:C50>1000)');
Book.Recalculate;   // 1000 を超える行がなければ #CALC!。Rich Data 付きで保存される

ライター内部では順序が重要です。XML エスケープは構文正規化の後に走り、決して前ではありません。診断パッケージにパーサが要るのと同じ理由です。既にエスケープされたテキストに区切りの書き換えを適用すると、比較演算子の中の &gt; のようなエンティティが台無しになります

自分を欺かずにキャッシュ済みエラーを検証する方法

信頼できる唯一のテストは、計算をあらかじめ手動に設定した状態でファイルを Excel で開き、セルを読み、その後で初めて再計算することです。Excel はセッションで最初に開いたワークブックから計算モードを引き継ぐため、アクセプタンスハーネスはまず自分のシードワークブックを作り、手動へ切り替え、それからテスト対象のファイルを開きます。シードのステップを飛ばしたプローブが証明したのは、ファイルが開いて正しく計算されたことだけです。キャッシュとラベルされたものは、すでに再計算済みでした

この区別が、記事全体の縮図です。近代的なエラーキャッシュが正しいのは、どんな計算も走る前に Excel がそれを表示するときであり、小文字の GUID から t="str" のテキストフォロワーまで、上のすべての要件は、再計算後の状態ではなくその状態を確認することで見つかりました。HotXLS がそもそもスピル範囲をどうサイズし所有するかの背景は、HotXLS による Delphi での動的配列スピル数式を参照してください。症状が間違ったエラーではなく修復プロンプトなら、Excel が正当な XLSX を修復する理由のパッケージレベルの規則のほうが良い出発点です

FAQ

#SPILL! と #CALC! を正しく保存するのに HotXLS は何か設定が要るのか

いいえ。HotXLS は保存時に #SPILL! と #CALC! の値を検出し、Rich Data パート、fileVersion、最小の calcId、microsoft.com:RD 機能を自動で書き込みます

セルの XML が依然 #VALUE! を含むのはなぜか

レガシーの SpreadsheetML エラータイプには #SPILL! や #CALC! のリテラルがないため、#VALUE! が後方互換のフォールバックです。現行の Excel は本物のエラーを vm の value-metadata リンクを通じて読みます

自分で設定した CalcId を HotXLS は引き下げるのか

いいえ。TXLSXWorkbook.CalcId が書かれたファイルの中で 191029 に引き上げられるのは、近代的エラーが存在するときだけです。設定したより高い値はそのまま保持されます

HotXLS は、Delphi と C++Builder から、自前の計算エンジンで XLSX、XLSM、レガシーの XLS を読み書きします。動的配列も含みます。詳細とトライアルダウンロードはHotXLS Delphi スプレッドシートコンポーネントのページにあります