HotXLSの行ブロックセルストレージとストリーミングXLSX保存

SpreadsheetMLでは、通貨フォーマットを運ぶ列は、正確に1つの要素、すなわち<col min="2" max="2" style="3"/>だけで表せます。ECMA-376 Part 1がセルスタイルを付ける場所は3つしかありません。列（§18.3.1.13）、行（§18.3.1.73）、セル（§18.3.1.4）です。フォーマットをB2:D500000へ狭めると、SpreadsheetMLにはこの形状のための単一要素が存在しません。列スタイルは500000行を超えて走り、行スタイルは全列を覆ってしまうため、書き手は結局、スタイルのみのセルとして出力します。1,499,997個です

インメモリのスプレッドシートモデルは、この壁に1ステップ早くぶつかります。フォーマットが各セルオブジェクトへのプロパティ設定を意味するなら、列ヘッダーへの1クリックが、小さな整数1個を運ぶために100万個のオブジェクトを確保します。数字フォーマットが欲しかっただけのDelphiプロセスが、ディスク上では数キロバイトのワークブックでアドレス空間を使い果たすのです。本当の設計問いは、編集中のフォーマットをどこに置き、いつセル単位のデータへ変えるかです

HotXLSがセルを作らずに範囲へフォーマットする仕組み

HotXLSは、範囲フォーマットを区間として保持します。TXLSXRangeの各スタイルセッター、つまりSetNumberFormat、SetNumberFormatIndex、SetFontIndex、SetFillIndex、SetBorderIndex、SetAlignmentIndexは、ワークシートのStyleOverlaysリストへ1つのレコードを追加します。4つの座標と、その呼び出しが所有するスタイル部分、そしてその部分のプールインデックスです。矩形の内側に既に存在するセルは、新しい部分を即座に受け取ります。空の座標は空のままで、何の割り当ても行われません

var
  Workbook: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  Edge: TXLSXBorder;
  BorderIdx, Row: Integer;
  Stats: TXLSXCellStorageStatistics;
begin
  Workbook := TXLSXWorkbook.Create;
  try
    Sheet := Workbook.Sheets.Add('Ledger');
    Sheet.WriteStringRowValues(1, 2, ['Net', 'Tax', 'Gross']);
    for Row := 2 to 1001 do
      Sheet.WriteFloatRowValues(Row, 2, [Row * 1.5, Row * 0.3, Row * 1.8]);

    // 全1,048,576行にわたるB..D列：呼び出しごとに区間1つ
    Sheet.RCRange[1, 2, XlsxMaxRow, 4].SetNumberFormat('#,##0.00 "EUR"');

    // 罫線を1度だけ組み立て、インデックスで適用する
    Edge := TXLSXBorder.Create;
    try
      Edge.SetAll(xlsxBorderThin);
      BorderIdx := Workbook.Borders.LookupOrAdd(Edge); // 1始まり
    finally
      Edge.Free;
    end;
    Sheet.RCRange[1, 2, XlsxMaxRow, 4].SetBorderIndex(BorderIdx);

    Stats := Sheet.Cells.GetStorageStatistics;
    // 3,003を期待する：ヘッダー3セルと値3,000個
    WriteLn('Cells: ', Stats.CellCount);
    if Workbook.SaveAs('ledger.xlsx') <> 1 then
      raise Exception.Create('Save failed');
  finally
    Workbook.Free;
  end;
end;

2つのフォーマット呼び出しは、3,145,728座標を覆い、そのカウントには何も追加しませんでした。区間が行1から始まるのは意図的です。全列の区間は1つの<col>要素として保存され、行1のテキストヘッダーは、そもそも数字フォーマットの影響を受けません。RCRange、Range['A1:C5']、EntireColumn、EntireRowが返す範囲はワークシートが所有するため、この例は決して解放しません

TXLSXFormatPartsが、複数の区間の衝突を防いでいます。そのメンバーはxfpFont、xfpFill、xfpBorder、xfpNumberFormat、xfpAlignment、xfpProtection、xfpCellControlであり、各TXLSXRangeセッターはそのうちの正確に1つを主張します。セルが解決されるとき、それを覆う区間は追加された順に適用され、各々は自分の部分だけを書き込み、セル自身への明示的なフォーマットがそのすべてに勝ります。数字フォーマットを設定する列区間と、塗りを設定する行区間は、どちらも無傷でセルB5へ届きます

1つのレコードに複数の部分を運ばせたいときは、Sheet.StyleOverlays.Add(Row1, Col1, Row2, Col2, Parts, State)が、プールインデックスを埋めたTXLSXCellStyleStateを受け取ります。Addは座標を1,048,576 × 16,384のグリッドへクランプし、逆転した角を入れ替え、Partsが空なら何も記録せずnilを返します。構造編集はレコードを追従させます。フォーマット済みブロックの内側のInsertRowsは区間を挿入数だけ広げ、その上への挿入はレコード全体を下へずらします

SetBorderIndexがセルを作らないのにSetBordersは作る理由

TXLSXRange.SetBorders(Kind, Style, Color)は、各セルを個別に見なければなりません。答えが位置に依存するからです。xlsxEdgeOutlineなら最上行が上辺を、左端列が左辺を受け取り、内側は何も受け取りません。xlsxEdgeInsideならほぼその逆です。この呼び出しは、新しい辺をセルが既に持つ罫線へマージするため、Outlineの後にInsideで完全なグリッドができます。このセル単位の合成はCells[R, C]を通りますが、これは存在しなければセルを作ります。10 × 40のレポートブロックなら、まさに正しい挙動です。xlsxEdgeAllのEntireColumn(3)では、1,048,576個の新しいセルオブジェクトになります

だから分割は単純です。画面いっぱいより高いデータには、最初の例と同じく、罫線を1度組み立ててインデックスで適用します。TXLSXBorder.SetAll、次に1始まりのインデックスを得るWorkbook.Borders.LookupOrAdd、そしてSetBorderIndexです。SetBordersは、外周と内側の線が異なるブロック、上の10 × 40のレポートブロックのようなものに取っておいてください

作業を台無しにせずフォーマットを読み戻す

同じ罠は、読むだけのコードにも掛かります。Cells[R, C]はミス時に作成するアクセッサです。B500000の数字フォーマットを尋ねると、正しい答えに加えて、B500000に新しいスタイル付きの空セルが出来上がり、それが保存されるまで残ります。監査のために列をループで回すと、区間が避けたかった100万個のオブジェクトが、無害に見える読み取りの1つ1つで再構築されます

TXLSXWorksheet.GetCellVisibleFormatは、セルを追加せずに座標の実効スタイルを解決します。ワークシートのデフォルト、行と列の区間、矩形の区間、テーブルスタイル、セルへの明示的なフォーマットをマージします。out ABuiltinNumberFormatパラメータを持つオーバーロードは、14（短い日付）のようなExcel組み込みフォーマットを、ワークブックのカスタムNumberFormatsプールを指すNumberFormatIndexとは別に報告します。Cells.HasCell(R, C)は、物理セルがそもそも存在するかを答えます

var
  Sheet: TXLSXWorksheet;
  Before, After: TXLSXCellStorageStatistics;
  State: TXLSXCellStyleState;
  Row, Builtin, Unformatted, Physical: Integer;
begin
  Sheet := Workbook.Sheets[1];  // Sheetsは1始まり
  Before := Sheet.Cells.GetStorageStatistics;
  Unformatted := 0;
  Physical := 0;
  for Row := 1 to 200000 do
  begin
    // 実効スタイル。シートへセルを追加せずに解決する
    State := Sheet.GetCellVisibleFormat(Row, 2, Builtin);
    if (State.NumberFormatIndex = 0) and (Builtin = 0) then
      Inc(Unformatted);
    if Sheet.Cells.HasCell(Row, 2) then
      Inc(Physical);
    // Sheet.Cells[Row, 2].NumberFormatIndexは同じ問いに答えるが、
    // 訪れた行ごとに新しいセルオブジェクトを1つ残す
  end;
  After := Sheet.Cells.GetStorageStatistics;
  // 両カウントはループの前後で同一のはず
  WriteLn(Format('cells %d -> %d, styled blanks %d -> %d',
    [Before.CellCount, After.CellCount,
     Before.StyledBlankCellCount, After.StyledBlankCellCount]));
end;

こうした検査を、実際のワークロードに対して1回は走らせてください。StyledBlankCellCountは、スタイルを持ち値、数式、リッチテキストを持たないセルを数えます。読み取り専用のはずのパスでそれが増えるなら、どこかのヘルパーがスタイルだけを保持するためにセルを作っています。典型的には、セルがフォーマットを要するか判断するためにCells[R, C]を呼んでいるのです

256行ブロックが自分のコードに意味するもの

物理セルは256行のブロックで暮らしています。ブロックの内側では、値を持つ各行が列順にソートされたリストでセルを保持し、セルのない行はただの空スロットであり、セルのない256行の区間にはブロックそのものがありません。したがって、行3、7、512にデータを持つ疎なシートは2つのブロックを保持します。保存は同じ構造を行優先の順序で歩きます。SpreadsheetMLが<row>要素とその<c>子に要求する順序です

保持しているTXLSXCell参照は、周囲でセルが追加されたり取り除かれたりしても、同じオブジェクトであり続けます。ブロックが再編成するのは自分の帳簿であって、あなたのハンドルではありません。WriteFloatRowValuesやWriteStringRowValuesのような一括書き手は、新しいシート上で高速な経路を取り、行優先の値をパックされたストアへ追記します。Cells[R, C]経由の最初のセル単位アクセスが、そのストアを1度だけセルオブジェクトへ変換します。GetStorageStatisticsは、RowBlockCountやPopulatedRowCountの隣に、PackedStorageActiveとPackedCellCountでどの状態にいるかを示します

スタイルプールは保存時に刈り込まれます。styles.xmlが書かれる前に、HotXLSはフォント、塗り、罫線、数字フォーマット、配置、保護の各プールを組み立て直し、使用中のエントリだけを保持します。ユーザーが1時間スタイルをいじったワークブックも、放棄された塗りをすべて同梱しては出荷しません。絶対的なFormatIndexに固定されたセルは、自分のスロットに手を付けられません

行ストリーミング保存と、それを示す数字

TXLSXWorkbook.StreamingWriteのデフォルトはTrueです。有効なとき、SaveAsは各ワークシートの<row>を生成し、ZIPエントリのdeflateストリームへ直接書き込みます。したがって、どの瞬間にもメモリに居るワークシートXMLの最大の断片は、単一の行です。無効なときは、各ワークシートのXMLがまず1つの完全な文字列として組み立てられ、その後に圧縮されるため、ピークメモリは最大のシートに追従します。矩形区間が変換するスタイルのみのセル、冒頭のB2:D500000のケースも、その行ごとのループの内側で生成されるため、ファイルには加わるものの、1つのXMLブロックとしてメモリに座ることは決してありません

共有文字列とピボットキャッシュレコードも同じパターンに従います。各<si>や<r>エントリは個別に生成され、書き込まれます。そのため、幅の広い分析ワークブックでも、しばしばどの単一ワークシートより大きい共有文字列テーブルが、1つのXMLブロックとして存在する必要はありません

どれも、信仰の対象ではありません。保存のたびに、ワークブックは4つの読み取り専用プロパティを公開します。LastSaveUsedStreaming、LastSavePeakRowXmlChars（最長の単一行、文字数）、LastSaveBufferedWorksheetXmlChars（メモリに保持されたワークシートXMLの総量、ストリーミング時はゼロ）、LastSavePeakAuxiliaryXmlChars（最長の共有文字列やピボットエントリ）です。LastSaveUsedStreamingは、.xlsx、.xltx、.xltm出力とバイナリの.xlsbライターでTrueです。OpenDocument出力は独自のライターを通り、Falseを報告します

procedure ReportSave(Workbook: TXLSXWorkbook; const FileName: string;
  Streaming: Boolean);
begin
  Workbook.StreamingWrite := Streaming;
  if Workbook.SaveAs(FileName) <> 1 then
    raise Exception.CreateFmt('Save failed: %s', [FileName]);
  WriteLn(Format('streaming=%s peakRow=%d buffered=%d peakAux=%d',
    [BoolToStr(Workbook.LastSaveUsedStreaming, True),
     Workbook.LastSavePeakRowXmlChars,
     Workbook.LastSaveBufferedWorksheetXmlChars,
     Workbook.LastSavePeakAuxiliaryXmlChars]));
end;

// 同じワークブックを両モードで。プロファイラでプロセスメモリを見る
ReportSave(Workbook, 'ledger-stream.xlsx', True);
ReportSave(Workbook, 'ledger-buffered.xlsx', False);

自分のデータでは、バッファリング側の行はシート全体のXML相当のbuffered数値を示し、ストリーミング側の行はゼロと、最も広い行を超えないpeakRowを示します。StreamingWriteはオンのままにしてください。このフラグが決めるのは、圧縮前にワークシートXMLを丸ごと組み立てるかどうかだけであり、その組み立てのサイズはLastSaveBufferedWorksheetXmlCharsが文字数で報告します

残るメモリの行き先

セルとスタイルがコンパクトになったら、大きなワークブックでの次の消費者は、共有文字列テーブルと、ファイルが運ぶ衛星パートです。ピボットキャッシュ、図面、そしてオブジェクトモデルがモデル化しないパートの保存済みXMLです。これらにはそれぞれのレバーがあり、すべてをカバーする単一の設定はありません

保存ではなくオープンがボトルネックなら、選択的読み込みから始めてください。メタデータのみと選択的ワークシート読み込みの解説が、触れないシートの代金を払わずにワークブックを読む方法を扱います。非常に大きなファイルでの読み取りパスのスループットには並列XLSXパースとメモリアロケータのノートを、そしてオブジェクトモデルを一度も必要としない出力専用ジョブには、ここでのどんなチューニングよりもサーバーバッチジョブ向けのストリーミング書き込みが合います

HotXLSは、ネイティブなDelphiと C++Builder コードから、ExcelもOLEオートメーションもなしにXLSとXLSXを読み書きします。だから上記の数字はすべて自分のプロセスから来ており、そこで検証できます。対応フォーマットとRAD Studioバージョンは、HotXLS Delphiスプレッドシートコンポーネントページに一覧されています