Large Spreadsheet Streaming

Overview

Loading very large spreadsheets (e.g. millions of populated cells) into memory can lead to out-of-memory errors in 32-bit processes; HotXLS provides streaming readers and writers with near-constant memory usage, bypassing DOM generation during workbook parsing

Direct Reader API

The TXLSDirectReader class parses workbook streams without creating in-memory cell models and fires OnSheet and OnCell events as selected cells are encountered

Reader := TXLSDirectReader.Create;
try
  Reader.ParallelSheets := True;
  Reader.ParallelMaxThreads := 4;
  Reader.ParallelBufferMemoryLimit := 16 * 1024 * 1024;
  Reader.OnDimension := HandleDimension;
  Reader.OnColumn := HandleColumn;
  Reader.OnRow := HandleRow;
  Reader.OnMerge := HandleMerge;
  Reader.OnCell := HandleCell;
  Reader.ReadFile('large.xlsx');
finally
  Reader.Free;
end;

ParallelSheets enables worker-local worksheet inflaters and XML readers while the calling thread emits callbacks in worksheet and cell order

ParallelBufferMemoryLimit bounds the aggregate queued raw-cell payload across active workers and defaults to 16 MiB, while ParallelPeakBufferedBytes reports the last parallel read's peak for diagnostics and capacity tests

Each active worksheet receives an independent share of the queue budget, preventing a later worksheet from starving the worksheet currently being emitted; a single cell larger than its share is admitted as irreducible payload

OnDimension, OnSheetFormat, OnColumn, OnRow, OnPane, and OnMerge expose the used range, default widths and heights, column spans, row height or visibility, outline and style indexes, pane state, and merged ranges without constructing worksheet objects

Layout callbacks are opt-in and run in a bounded-memory metadata pass before the selected sheet's OnCell callbacks; merge ranges therefore arrive before cells inside the merge even though OOXML stores mergeCells after sheetData

When parallel sheet parsing is enabled, layout callbacks remain on the calling thread and complete for the selected sheets before worker queues are drained

Setting Abort in OnCell wakes blocked producers and consumers, stops in-flight ZIP reads and decompression at bounded internal steps, joins every worker, and leaves the reader reusable

Direct Writer API

The TXLSDirectWriter class writes rows directly into the ZIP file package, ensuring minimal memory overhead when generating large data exports

Writer := TXLSDirectWriter.Create;
try
  Writer.BeginFile('large.xlsx');
  Writer.AddSheet('Data');
  Writer.AddRow(1);
  Writer.WriteString(1, 'Quarter');
  Writer.WriteString(2, 'Revenue');
  Writer.AddComment(2, 1, 'Validated source', 'Reviewer');
  Writer.AddImageFromFile(4, 1, 8, 12, 'logo.png',
    xlsDirectImagePng, 'Company logo');
  ChartIndex := Writer.AddChart(9, 1, 18, 16,
    xlsDirectChartColumn, 'Revenue');
  Writer.AddChartSeries(ChartIndex, 'Revenue',
    'Data!$A$2:$A$5', 'Data!$B$2:$B$5');
  Writer.Close;
finally
  Writer.Free;
end;

AddComment writes classic cell notes with author metadata and the matching VML note shapes without loading a worksheet object model

AddImage, AddImageFromFile, and AddImageFromStream accept two-cell anchors and PNG, JPEG, GIF, BMP, EMF, or WMF payloads; file sources flow directly into the ZIP entry when the sheet finishes, while stream sources retain only the stream reference and recorded byte range

When AddImageFromStream receives OwnsStream=True, the writer releases that stream after the worksheet side parts are finalized; otherwise the caller must keep the seekable stream alive until the next AddSheet or Close call

AddChart and AddChartSeries generate column, bar, line, area, pie, doughnut, or scatter ChartML with formula-backed series, while AddChartXml attaches a complete caller-supplied chart part for advanced chart families and extensions

Comments, drawing anchors, and chart descriptors are retained only for the current worksheet; after sheetData closes, the writer emits worksheet relationships, comments, VML, DrawingML, media, and ChartML sequentially, then releases the side-channel state before the next worksheet begins