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

技術記事

DelphiにおけるXLSX共有数式のsi展開:落とし穴

HotXLS Component

XLSXの共有数式のフォロワーは数式のテキストを一切運ばない。その<f t="shared" si="N"/>要素はシート内の別の場所にあるマスターセルを指しており、リーダーはマスターの数式を行と列の差分だけシフトすることでテキストを再構築しなければならない。DelphiとC++Builder向けのHotXLS Componentは、これをファイルを開く時点で展開するため、すべてのフォロワーは完全な数式を報告する

もし実世界のXLSXをサードパーティのライブラリで読み込んで、千個の数式が並ぶ列のうちちょうど1つのセルにだけテキストがあり、残りの999個が空文字列だったという経験があるなら、あなたはこの機能に間違った側から出会っている。何も壊れていない。ファイルはECMA-376が許していることをしているだけであり、リーダーが単にXMLが止まったところで止まってしまっただけだ

なぜ共有数式のセルは空なのか

フォーマットが意図的に数式を一度だけ保存しているからだ。ECMA-376 Part 1(ISO/IEC 29500-1)では、<f>要素(§18.3.1.40)はST_CellFormulaType型のt属性を運び、値sharedは、このセルがsi属性で識別されるグループに参加していることを意味する。そのグループのちょうど1つのセル、すなわちマスターだけが、そのグループが適用される範囲を示すref属性も運び、要素の内容として数式テキストを運ぶのもそのセルだけである。グループ内の他のすべてのセルはフォロワーであり、t="shared"と同じsiを繰り返すが、その要素の内容は空である。Excelはこのグループを積極的に書き出す。20万行の列に対するフィルダウンは、20万個の数式文字列から、1つの文字列とその他199,999個の小さなプレースホルダー要素へと圧縮されるからだ。この節約は本物であり、そのコストは丸ごとリーダーに降りかかる:展開なしでは、フォロワーはそれ単独では何の意味も持たない

このシフトはテキストのコピーではなく変換である

HotXLSは同じsiのもとに登録されたマスターを見つけ出し、マスターのアンカーから現在のセルまでの行と列の差分を計算し、マスターの数式内のすべての参照をその差分だけ変換することでフォロワーを解決する。相対的な次元は動き、絶対的な次元は動かず、混合参照はその非絶対的な半分だけが動く。文字列リテラルは完全にスキップされるため、たまたま"A1"というテキストを含む数式は、どのフォロワーでもそのテキストを変えないまま保持する

const
  // xl/worksheets/sheet1.xml, trimmed to the interesting cells
  SheetXml: WideString=
    '<row r="1"><c r="A1"><v>1</v></c>'+
    '<c r="B1"><f t="shared" si="4" ref="B1:B3">'+
    'A1+$A$1+A$1+$A1+&quot;A1&quot;+SUM(A1:A2)</f><v>7</v></c></row>'+
    '<row r="2"><c r="B2"><f t="shared" si="4"/><v>8</v></c></row>'+
    '<row r="3"><c r="B3"><f t="shared" si="4"></f><v>9</v></c></row>';

var
  Wb: TXLSXWorkbook;
  Sh: TXLSXWorksheet;
begin
  Wb:= TXLSXWorkbook.Create;
  try
    Wb.Open(FileName);
    Sh:= Wb.Sheets[1];
    // Master, verbatim
    // B1 -> A1+$A$1+A$1+$A1+"A1"+SUM(A1:A2)
    // Follower one row down: relative row moves, absolute row frozen,
    // the mixed A$1 keeps its row, and the literal stays a literal
    // B2 -> A2+$A$1+A$1+$A2+"A1"+SUM(A2:A3)
    ShowMessage(Sh.Cells[2, 2].Formula);
  finally
    Wb.Free;
  end;
end;

ref属性は飾りではなくゲートである。座標がマスターの適用範囲の外に落ちるフォロワーは展開されない。その場合、ファイルはグループがサポートしていない主張をしていることになるからだ。同様に、シフトによって参照が1行目より上、あるいはA列より左に押し出されてしまう場合、HotXLSはそれを静かにクランプするのではなく、そのトークンに対して#REF!を出力する。これはExcel自身が同じ編集に対して生成するのと同じ結果である。この変換は、行の挿入や削除のときに起こる参照の書き換えの近縁ではあるが、同じものではない。その経路は、編集が範囲を切り裂くときに範囲がどうなるかについて独自の規則を持っており、それについては挿入・削除時の数式参照調整に関する記事で別途説明している。共有展開はもっと単純だ:既知のアンカーからの純粋なオフセットであり、パース時に一度だけ適用される

シフター(shifter)がカバーしなければならない参照の形はどれか

そのすべてであり、そうでなければこの展開はデータ損失バグの変装にすぎない。A1とA1:B2だけを理解する素朴なシフターは、もっと変わった形式を破損させるか落としてしまう。そして実際のワークブックはそうした形式で溢れている。HotXLSの共有数式トランスレータは、何を動かすかを決める前にA1系の全体を認識する。[Book.xlsx]Sheet1!A1のような外部ワークブック参照やSheet1:Sheet3!A1のような3D参照は、末尾のセル参照がシフトする間もそのプレフィックスをそのまま保つ。引用符付きのシート名は生き残り、シートが文字どおりA1という名前であるという嫌なケースも含まれる。したがって'A1'!A1は感嘆符の後の部分だけがシフトする。列全体を表すA:Aはその列の次元だけを動かし他は動かさない。行全体を表す1:1はその行の次元だけを動かし他は動かさない。$A:$Aはまったく動かない。Table[A1]のような構造化テーブル参照は手を触れないまま残される。角括弧の中身は座標ではなく列名だからだ

// One master, expanded two columns to the right and zero rows down.
// Master D1: A1+A:A+$A:$A
// F1       : C1+C:C+$A:$A
//
// One master, expanded three rows down and zero columns across.
// Master A1: B1+$C$1+"A1"+A:A+1:1+'Data'!A1+LOG10(A1)+Table[A1]+'A1'!A1
// A3       : B3+$C$1+"A1"+A:A+3:3+'Data'!A3+LOG10(A3)+Table[A1]+'A1'!A3
//
// Note what did NOT move in the second line: the absolute $C$1, the
// string literal "A1", the whole column A:A under a pure row delta,
// the function name LOG10, and the structured reference Table[A1]

関数名はここでの静かな罠だ。数字が続く文字を掴むだけのトークンスキャナは、LOG10を1行下がっただけで喜んでLOG11に書き換えてしまう。HotXLSは候補トークンの前後に参照の境界を要求するため、文字、数字、アンダースコア、ドット、あるいは開き括弧へと続く識別子はセル参照ではないと判断される。もう一方の記法系で作業しているなら、同じ境界の問題は違った形で現れる。R1C1記法に関する記事では、この2つのモデルがどこで分岐するかを扱っている

なぜ自己終端のf要素は次の値を飲み込んでしまうのか

自己終端の要素は終了要素イベントを一切発生させないからだ。これはこの機能全体の中で最もコストのかかるバグであり、特定のXMLパーサーに固有のものではない。TXMLReaderでは、<f t="shared" si="4"/>はIsEmptyElementがTrueにセットされたElementイベントをちょうど1回だけ発生させ、対応するEndElementは決して発生させない。数式を捕捉している状態をEndElementでしか閉じないパーサーは、そのため数式の内側に留まり続け、次に目にするテキスト——<v>の中のキャッシュされた結果——が数式バッファに追記されてしまう。さらに悪いことに、この状態はセルの境界を越えて生き延びるため、本物の<f>を持つ次のセルの数式テキストが前のセルに吸収されてしまう。修正は、IsEmptyElementがTrueであるときにはElementイベントそのものの時点で数式状態を終わらせ、待つのではなくその場でフォロワー解決全体を実行することである。つまり、属性からt、si、ref、aca、caを読み取り、共有展開を適用し、再計算属性をセルに書き込み、共有状態をクリアするという一連の作業すべてを、空要素を処理する分岐の中で行う。フォーマットは<f t="shared" si="4"/>と<f t="shared" si="4"></f>という両方の綴りを許しており、後者は実際にEndElementを発生させることに注意してほしい。正しいリーダーはこの2つを同一に扱わなければならず、HotXLSが同じ回帰テストファイルの中で両方の綴りをカバーしている理由がこれである

疎で順序不定なsi値と保留キュー

si属性はファイルが与える符号なし整数であって、あなたが制御する配列位置ではない。スキーマの中には共有インデックスが密であること、ゼロから始まること、昇順に現れることを要求するものは何もなく、悪意ある、あるいは単に奇妙なファイルが最初のセルにsi="4294967290"を使うことを止めるものも何もない。したがって、観測された最大のsiからルックアップ配列のサイズを決めることは、最適化ではなくメモリ枯渇の実現手段である。HotXLSはワークブックを開く経路では代わりにソート済みの疎なテーブルを保持する:共有グループはソート済みのTStringListにその整数キーのもとで登録され、ルックアップは実際に存在するグループの数に対する二分探索になり、インデックスの数値としての大きさとは何の関係もない。順序はこの問題のもう半分だ。マスターは通常フォロワーより文書の順序で先に現れるが、それは規則ではなく慣習にすぎないため、パースされた瞬間に自分のsiを解決できないフォロワーは保留キューに入れられる。シートが終わると、このキューは今や完成しているテーブルに対して再生され、遅れて現れたマスターがそれぞれの孤児を解決する。一度もマスターを見つけられなかったセルは空の数式のままになる。これは、一度も定義されていないグループを参照するファイルにとっての正直な結果である

ワークブックを読み込まずに共有数式を展開する

ストリーミングリーダーもはるかに厳しいメモリ予算のもとで同じ要件に直面しており、ワークシートローカルなテーブルでこれを解決する。TXLSDirectReaderとTXLSRowCursorはどちらも、境界を持つメモリ使用量と射影(projection)の挙動を保ちながらフォロワーを完全なセルごとの数式へと展開するため、300MBのシートに対する前方専用の走査でも本物の数式テキストが手に入る

var
  Reader: TXLSDirectReader;
  Cursor: TXLSRowCursor;
begin
  // Projection: only rows 2..3, only column A. The master lives in row 1,
  // outside the projection, and is still parsed so the followers resolve
  Reader:= TXLSDirectReader.Create;
  try
    Reader.FirstRow:= 2;
    Reader.LastRow:= 3;
    Reader.IncludeColumn(1);
    Reader.OnCell:= HandleCell;   // Cell.Formula is fully expanded here
    Reader.ReadFile(FileName);
  finally
    Reader.Free;
  end;

  // Forward-only row traversal, same expansion
  Cursor:= TXLSRowCursor.Create;
  try
    Cursor.Open(FileName);
    if Cursor.FindFirst then
      repeat
        if Cursor.CellCount > 0 then
          WriteLn(Cursor.RowIndex, ': ', Cursor.Cells[0].Formula);
      until not Cursor.FindNext;
  finally
    Cursor.Free;
  end;
end;

この設計からは2つの制約が生じる。第一に、射影は決してマスターをスキップできない。FirstRowとLastRowで設定された行フィルタ、あるいはIncludeColumnで組んだ列フィルタは、マスターセルをあなたのコールバックへ渡すことをスキップするかもしれないが、パーサーはそれでもそのsi、アンカー座標、適用範囲、数式テキストを記録しなければならない。さもなければ射影内のすべてのフォロワーが何も解決できなくなってしまう。スキップしても安全なのはフォロワー側の作業、すなわちシフトと値のデコードだけである。第二に、このテーブルはワークシートごとのものであり、その寿命は明示的に管理されなければならない:TXLSRowCursorはシートの走査の間だけ1つのインスタンスを保持し、再起動、シートの切り替え、ファイル終端、例外、クローズのときにそれをクリアするため、シート1で定義されたグループがシート2に漏れることは決してない。ストリーミング経路はホットループであるため、ソート済み文字列テーブルではなくオープンアドレッシングの整数ハッシュを使い、セルごとの整数から文字列への変換を避けている

保存時に何が起こり、境界はどこにあるか

一度展開されたフォロワーは普通の数式であり、HotXLSはt="shared"もsiも持たない独立した<f>要素としてそれを書き戻す。この往復は安定しておりキャッシュされた<v>の結果も生き残るが、共有度の高いシートに対しては出力が入力よりも大きくなり、Excelが作ったグループ化は保存時に再構築されない。共有グループのバイトレベルの忠実性が、すべてのセルに本物の数式テキストを持つことよりもあなたにとって重要であるなら、これがあなたが受け入れているトレードオフである。ちなみにXLS側は事情が異なる:BIFF8のSHRFMLAレコードは独自のエンコーディングと独自のライターを持ち、ワークブックに共有グループの切り替えを持つ

同じ<f>要素を共有してはいるが、明示的に共有数式ではない関連する2つのものがある。レガシーなCSE配列数式はアンカー範囲をカバーするrefとともにt="array"を使い、動的配列も同じt="array"という綴りを使うが、cellMetadataを通じてXLDAPRレコードへと連鎖するcm属性によって識別される。動的配列のスピルセルを共有数式やCSEのフォロワーとして扱ってしまうことは、れっきとした正しさのバグであり、この区別は動的配列とスピル数式に関する記事で扱っている。この3つのケースを、たまたま同じタグ名を共有している3つの別々のパーサーとして読めば、コードは正直なままでいられる

ここで説明した共有数式の展開、ストリーミングリーダー、参照トランスレータは、DelphiとC++Builder向けのHotXLS Excelコンポーネントの一部として提供される。製品ページには、上記で使った射影プロパティを含む、数式と直接読み取りAPIの完全なリファレンスが掲載されている