Profile a Delphi service while it saves a 400,000-row workbook and the surprise is rarely the cell-filling loop - it is the SaveAs call. With the default writer, each worksheet is serialized into one in-memory XML string before being compressed into the OOXML zip, and for a wide sheet that transient string can dwarf the cell model it was built from. The job that comfortably built its data plateau at 800 MB then spikes past the container's 2 GB limit during the save, and the OOM killer files the bug report at 03:00. HotXLS, losLab's native spreadsheet library for Delphi and C++Builder, addresses exactly this with its StreamingWrite mode - and pairs it with row-level write callbacks and a style-pool model that rewards a little discipline in tight loops.
What the default save path buffers, and what StreamingWrite changes
The default XLSX writer favors simplicity: render the worksheet XML completely, then hand the finished string to the zip compressor. That is the right trade for the overwhelming majority of workbooks, where the whole sheet's XML fits in a few megabytes. It stops being right when one sheet's serialized form runs to hundreds of megabytes - spreadsheet XML is verbose, every numeric cell costs tens of characters of markup, and the string holding it must be contiguous. The diagnostic signature is unmistakable in a memory graph: a long flat plateau while rows fill, then a sharp triangular spike during SaveAs, then collapse.
Setting Book.StreamingWrite := True switches SaveAs to a streaming worksheet writer that emits sheet XML directly into the zip stream as it is generated, so the intermediate string never exists and the spike flattens into the noise.
Be precise about what this buys, because overselling it leads to wrong capacity plans. The flag changes only the save path. Building the workbook still allocates the full in-memory cell model, so the memory plateau during the fill phase is untouched - what disappears is the serialization spike stacked on top of that plateau at save time. For a job that fills 400k rows, that spike is routinely the difference between fitting a memory budget and not. The property defaults to False to preserve historical behavior, so opting in is an explicit line in your code.
A bulk export with the flag on
Book := TXLSXWorkbook.Create;
try
BoldIdx := Book.Fonts.Add('Calibri', 11, True, False); // pool index, 0-based
Sheet := Book.Sheets.Add('Bulk');
for R := 1 to 100000 do
begin
Sheet.Cells[R, 1].Value := R;
Sheet.Cells[R, 2].Value := 'Row ' + IntToStr(R);
Sheet.Cells[R, 3].Value := R * 1.5;
if (R mod 1000) = 0 then
Sheet.Cells[R, 2].FontIndex := BoldIdx + 1; // 1-based at the cell
end;
Book.StreamingWrite := True; // stream sheet XML straight into the zip
Book.SaveAs('bulk.xlsx');
finally
Book.Free;
end;Cells[R, C] creates cells on demand, which keeps the loop body clean, and the grid caps are worth knowing by heart: 1,048,576 rows by 16,384 columns (XlsxMaxRow and XlsxMaxCol). A data feed that exceeds the row cap needs to split across sheets in your code - nothing downstream will do it for you.
Filling rows without per-cell Variant overhead
Every Cells[R, C].Value assignment pays for a cell lookup and a Variant conversion. At ten thousand rows nobody notices; at a million rows of twenty columns the per-call overhead becomes the dominant cost of the fill phase. The batch interfaces hand the writer a whole row at a time instead - WriteRows drives a callback that supplies one row per invocation:
procedure TBulkExporter.FillRow(Sender: TObject; SheetIndex, Row, FirstCol,
LastCol: Integer; var Values: Variant; var Skip: Boolean;
var Cancel: Boolean);
begin
if not FReader.Next then
begin
Cancel := True; // data source drained: stop cleanly
Exit;
end;
Values := VarArrayCreate([FirstCol, LastCol], varVariant);
Values[FirstCol] := FReader.RecordId;
Values[FirstCol + 1] := FReader.CustomerName;
Values[FirstCol + 2] := FReader.Amount;
end;
// fill rows 2..100001, columns A..C, pulling from the reader
Sheet.WriteRows(2, 1, 100001, 3, FillRow);The callback's Cancel flag turns a fixed row range into "up to N rows," which is the natural shape when the row count comes from a query you have not finished executing; Skip leaves an individual row empty without stopping the run. The callback is also the natural seam for operational concerns that otherwise get bolted on badly: incrementing a progress counter every thousand rows, checking a cancellation token from the job scheduler, or rate-limiting reads from a source database all live in one place instead of being threaded through a cell-writing loop. The mirror-image ForEachRow and ForEachCell exist on the read side for the same reasons, and matter when a batch job both consumes and produces large files.
Style pools reward hoisting
The XLSX styling model is a set of shared pools: Fonts.Add, Fills.AddSolid, and Borders.Add return 0-based pool indexes, and a cell references a font by storing that index plus one in FontIndex, with zero meaning the workbook default. The +1 is visible in the bulk example above, and forgetting it silently applies the wrong style - off-by-one in a style pool does not raise.
The loop discipline: create every style object before the row loop and reuse the index inside it. Fonts.Add deduplicates identical definitions, so calling it per row is merely wasted CPU, but Alignments.Add returns a fresh entry on every call - inside a 100k-row loop that inflates styles.xml with a hundred thousand duplicate records, bloating the file and slowing every subsequent open in Excel. Hoist once, index many times.
Streams, temp directories, and the batch loop around it all
Everything above also works without a file system. Both facades expose TStream overloads across their IO surface - Open, SaveAs, SaveAsCSV, SaveAsHTML, SaveAsODS - so a batch worker can render straight into a TMemoryStream destined for blob storage or an HTTP response. One sharp edge: SaveAs(Stream) writes from the stream's current position and does not rewind, so reset Position := 0 before handing the stream to whatever delivers it. Jobs on the XLS facade have their own knobs: SetTempDir points the BIFF writer's temporary files at a volume with the space and the IO budget to absorb them - which matters on servers where the default temp path lives on a small system disk - and UseSharedFormulas compresses repeated formula bodies into shared groups, a meaningful size reduction for the classic report shape where the same formula fills an entire column.
The batch loop itself stays boring on purpose:
for FileName in SourceFiles do
begin
Book := TXLSXWorkbook.Create; // fresh instance: no state bleed
try
Book.StreamingWrite := True;
if Book.Open(FileName) <> 1 then
Continue; // one bad input must not kill the batch
Book.SaveAsCSV(ChangeFileExt(FileName, '.csv'), 0, ',');
finally
Book.Free;
end;
end;A fresh workbook instance per file costs microseconds and eliminates an entire category of cross-file contamination bugs - styles, defined names, and document properties from file 17 can never leak into file 18. The skip-and-continue on a failed Open matters just as much: one truncated upload in a 600-file batch should produce one log line, not a dead batch. Note also what the CSV leg does not do: SaveAsCSV writes formulas as literal text without evaluating them, so a conversion batch whose consumers expect computed values must run Calculate on the relevant cells first or operate on workbooks that already carry cached results.
Concurrency: instances are cheap, sharing is forbidden
Neither facade's objects are thread-safe, and nothing about the design asks them to be: there is no shared global state between instances, so the scaling model is one workbook per worker thread, full stop. A pool of N workers each owning its own TXLSXWorkbook scales linearly until memory becomes the ceiling - and the ceiling calculation is concrete: the largest concurrent cell model times the worker count, plus save-time overhead that StreamingWrite has hopefully flattened. Apply back-pressure at the job queue rather than inside the writer; a workbook half-written by a starved thread is worth less than a job that waited its turn.
For the broader tuning picture - shared formulas, graphics skipping on the read side, and XLS-specific levers - see the large workbook performance guide; batch jobs whose rows come straight out of a database are covered in the database export patterns for Delphi reports.
HotXLS compiles into your Delphi or C++Builder service as native Object Pascal with no external dependencies; editions and licensing are on the HotXLS Component product page.