Row-Block Cell Storage and Streamed XLSX Saves in HotXLS

In SpreadsheetML a column that carries a currency format needs exactly one element: <col min="2" max="2" style="3"/>. ECMA-376 Part 1 attaches a cell style in three places only: a column (§18.3.1.13), a row (§18.3.1.73) and a cell (§18.3.1.4). Narrow the format to B2:D500000 and SpreadsheetML has no single element for the shape: a column style would run past row 500000 and a row style would cover every column, so a writer ends up emitting it as style-only cells, 1,499,997 of them

An in-memory spreadsheet model hits the same wall one step earlier. If formatting means setting a property on each cell object, one click on a column header allocates a million objects to carry one small integer, and a Delphi process that only wanted a number format runs out of address space on a workbook that is a few kilobytes on disk. The real design question is where formatting lives while you edit, and when it turns into per-cell data

How HotXLS formats a range without creating cells

HotXLS keeps range formatting as intervals. Every style setter on TXLSXRange — SetNumberFormat, SetNumberFormatIndex, SetFontIndex, SetFillIndex, SetBorderIndex, SetAlignmentIndex — adds one record to the worksheet's StyleOverlays list: four coordinates, the style parts the call owns, and the pool indexes for those parts. Cells that already exist inside the rectangle pick up the new parts at once. Empty coordinates stay empty, and nothing is allocated for them

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]);

    // Columns B..D over all 1,048,576 rows: one interval per call
    Sheet.RCRange[1, 2, XlsxMaxRow, 4].SetNumberFormat('#,##0.00 "EUR"');

    // Build the border once, then apply it by index
    Edge := TXLSXBorder.Create;
    try
      Edge.SetAll(xlsxBorderThin);
      BorderIdx := Workbook.Borders.LookupOrAdd(Edge); // 1-based
    finally
      Edge.Free;
    end;
    Sheet.RCRange[1, 2, XlsxMaxRow, 4].SetBorderIndex(BorderIdx);

    Stats := Sheet.Cells.GetStorageStatistics;
    // Expect 3,003: three header cells plus 3,000 values
    WriteLn('Cells: ', Stats.CellCount);
    if Workbook.SaveAs('ledger.xlsx') <> 1 then
      raise Exception.Create('Save failed');
  finally
    Workbook.Free;
  end;
end;

The two formatting calls covered 3,145,728 coordinates and added nothing to that count. The intervals start at row 1 on purpose: a full-column interval saves as one <col> element, and the text headers in row 1 are not affected by a number format anyway. Ranges returned by RCRange, Range['A1:C5'], EntireColumn and EntireRow are owned by the worksheet, so the example never frees them

TXLSXFormatParts is what keeps several intervals from fighting. Its members are xfpFont, xfpFill, xfpBorder, xfpNumberFormat, xfpAlignment, xfpProtection and xfpCellControl, and each TXLSXRange setter claims exactly one of them. When a cell is resolved, the intervals covering it are applied in the order they were added, each writing only its own parts, and explicit formatting on the cell itself wins over all of them. A column interval that sets the number format and a row interval that sets a fill both reach cell B5 intact

When one record should carry several parts, Sheet.StyleOverlays.Add(Row1, Col1, Row2, Col2, Parts, State) takes a TXLSXCellStyleState with the pool indexes filled in. Add clamps the coordinates to the 1,048,576 × 16,384 grid, swaps reversed corners, and returns nil without recording anything when Parts is empty. Structural edits keep the records in step: InsertRows inside a formatted block widens the interval by the inserted count, and inserting above it shifts the whole record down

Why does SetBorders create cells when SetBorderIndex does not?

TXLSXRange.SetBorders(Kind, Style, Color) must look at each cell on its own, because the answer depends on position. With xlsxEdgeOutline the top row gets a top edge, the left column a left edge, and the interior nothing; with xlsxEdgeInside it is roughly the reverse. The call also merges the new edges into whatever border the cell already has, so Outline followed by Inside builds a full grid. That per-cell composition goes through Cells[R, C], which creates a cell when none exists. On a 10 × 40 report block this is exactly right. On EntireColumn(3) with xlsxEdgeAll it is 1,048,576 new cell objects

So the split is simple. For anything taller than a screen of data, build the border once and apply it by index, the way the first example does: TXLSXBorder.SetAll, then Workbook.Borders.LookupOrAdd for a 1-based index, then SetBorderIndex. Keep SetBorders for blocks whose outline and inner lines differ, such as the 10 × 40 report block above

Reading formats back without undoing the work

The same trap catches code that only reads. Cells[R, C] is a create-on-miss accessor: ask it for the number format of B500000 and you get the right answer plus a new, styled, empty cell at B500000 that will now be stored and saved. Loop that over a column to audit formatting and you have rebuilt the million objects the interval was meant to avoid, one innocent-looking read at a time

TXLSXWorksheet.GetCellVisibleFormat resolves the effective style of a coordinate without adding a cell. It merges the worksheet default, row and column intervals, rectangle intervals, table styles and any explicit formatting on the cell. The overload with an out ABuiltinNumberFormat parameter reports a built-in Excel format such as 14 (short date) separately from NumberFormatIndex, which points into the workbook's custom NumberFormats pool. Cells.HasCell(R, C) answers whether a physical cell exists at all

var
  Sheet: TXLSXWorksheet;
  Before, After: TXLSXCellStorageStatistics;
  State: TXLSXCellStyleState;
  Row, Builtin, Unformatted, Physical: Integer;
begin
  Sheet := Workbook.Sheets[1];  // Sheets is 1-based
  Before := Sheet.Cells.GetStorageStatistics;
  Unformatted := 0;
  Physical := 0;
  for Row := 1 to 200000 do
  begin
    // Effective style, resolved without adding a cell to the sheet
    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 answers the same question,
    // but leaves one new cell object behind for every row it visits
  end;
  After := Sheet.Cells.GetStorageStatistics;
  // Both counts should be identical before and after the loop
  WriteLn(Format('cells %d -> %d, styled blanks %d -> %d',
    [Before.CellCount, After.CellCount,
     Before.StyledBlankCellCount, After.StyledBlankCellCount]));
end;

Run a check like this once against your real workload. StyledBlankCellCount counts cells that have a style and no value, formula or rich text. If it climbs during a pass that was supposed to be read-only, some helper is creating cells purely to hold style, typically by calling Cells[R, C] to decide whether a cell needs formatting

What the 256-row blocks mean for your code

Physical cells live in blocks of 256 rows. Inside a block, each populated row keeps its cells in a list sorted by column; a row without cells is just an empty slot, and a 256-row stretch with no cells has no block at all. A sparse sheet with data in rows 3, 7 and 512 therefore holds two blocks. Saving walks the same structure in row-major order, which is the order SpreadsheetML requires for <row> elements and their <c> children

The TXLSXCell reference you hold stays the same object while other cells are added or removed around it; the blocks reorganize their own bookkeeping, not your handles. Bulk writers such as WriteFloatRowValues and WriteStringRowValues take a faster route on a fresh sheet and append row-major values into a packed store. The first per-cell access through Cells[R, C] converts that store to cell objects once. GetStorageStatistics shows which state you are in through PackedStorageActive and PackedCellCount, next to RowBlockCount and PopulatedRowCount

Style pools are trimmed at save time. Before styles.xml is written, HotXLS rebuilds the font, fill, border, number-format, alignment and protection pools so that they hold only entries still in use, and a workbook that a user restyled for an hour does not ship every abandoned fill. Cells pinned to an absolute FormatIndex keep their slot untouched

Row-streamed saves and the numbers that show it

TXLSXWorkbook.StreamingWrite defaults to True. With it on, SaveAs generates each worksheet <row> and writes it straight into the deflate stream of its ZIP entry, so the largest piece of worksheet XML in memory at any moment is a single row. With it off, every worksheet's XML is built as one complete string first and compressed afterward, and peak memory follows the biggest sheet. The style-only cells that rectangle intervals turn into, the B2:D500000 case from the opening, are generated inside that per-row loop too, so they add to the file but never sit in memory as one block of XML

Shared strings and pivot cache records follow the same pattern. Each <si> or <r> entry is generated and written on its own, so on a wide analytical workbook the shared string table, frequently bigger than any single worksheet, never has to exist as one block of XML

None of this needs to be taken on faith. After every save the workbook exposes four read-only properties: LastSaveUsedStreaming, LastSavePeakRowXmlChars (the longest single row, in characters), LastSaveBufferedWorksheetXmlChars (the total worksheet XML held in memory, zero when streaming) and LastSavePeakAuxiliaryXmlChars (the longest shared-string or pivot entry). LastSaveUsedStreaming is True for .xlsx, .xltx and .xltm output and for the binary .xlsb writer; OpenDocument output goes through its own writer and reports 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;

// Same workbook, both modes; watch process memory in your profiler
ReportSave(Workbook, 'ledger-stream.xlsx', True);
ReportSave(Workbook, 'ledger-buffered.xlsx', False);

On your own data, the buffered line shows a buffered figure on the order of the whole sheet's XML, while the streaming line shows zero and a peakRow no larger than your widest row. Leave StreamingWrite on. The flag decides only whether worksheet XML is assembled in full before compression, and LastSaveBufferedWorksheetXmlChars reports the size of that assembly in characters

Where the remaining memory goes

Once cells and styles are compact, the next largest consumers on a big workbook are the shared string table and whatever satellite parts the file carries: pivot caches, drawings, and preserved XML from parts the object model does not model. Those have their own levers, and no single setting covers all of them

If opening is the bottleneck rather than saving, selective loading is the place to start: the walkthrough of metadata-only and selective worksheet loading covers reading a workbook without paying for sheets you will not touch. For read-path throughput on very large files, see the notes on parallel XLSX parsing and the memory allocator, and for output-only jobs that never need an object model, streaming writes for server batch jobs fits better than any amount of tuning here

HotXLS reads and writes XLS and XLSX from native Delphi and C++Builder code without Excel or OLE automation, so every number above comes from your own process and can be checked there. Supported formats and RAD Studio versions are listed on the HotXLS Delphi spreadsheet component page