The concrete steps for 100,000 or a million rows. The characteristics this rests on are in Performance and Memory.

First, the thing to internalize: making the sheet big is not itself a cost. Creating a million-row sheet uses almost no memory until you write values. What makes things slow is almost always how you write.

When everything fits in memory

Up to a few hundred thousand rows, writing it all is both the simplest and the fastest approach.

using unvell.ReoGrid.Core;
using unvell.ReoGrid.Core.Data;
using unvell.ReoGrid.Core.Style;

// 1. decide the formatting first, per row and column
ws.SetColumnStyle(1, new StyleRecord { TextAlign = HAlign.Right });

// 2. write the values. SetText / SetNumber are the lightest when you know the type
for (int i = 0; i < rows.Count; i++)
{
	ws.SetText(i, 0, rows[i].Name);
	ws.SetNumber(i, 1, rows[i].Amount);
}

// 3. formulas last: write them all, then recalculate once
ws.SetFormula(rows.Count, 1, $"SUM(B1:B{rows.Count})");
ws.Recalculate();

The order matters.

1. Formatting first, per row and column. Setting it in a cell loop materializes one entry per cell and memory jumps
2. Values with the typed Set*. SetObjectValue pays for type dispatch
3. Formulas last, and Recalculate() once. SetFormula recalculates that cell and its dependents on the spot

Follow those three and writing 100,000 rows usually finishes in well under a second.

Loading only the rows that get displayed

When there are millions of records in a database and you do not want them all in memory, implement IRowDataSource. Only the rows on screen get loaded.

/// <summary>Pulls only the rows that got displayed out of the database and into the sheet.</summary>
public sealed class QueryRowSource : IRowDataSource
{
	private readonly Worksheet _sheet;
	private readonly HashSet<int> _loaded = new();

	public QueryRowSource(Worksheet sheet) => _sheet = sheet;

	public void EnsureRows(int firstRow, int lastRow)
	{
		for (int r = firstRow; r <= lastRow; r++)
		{
			if (!_loaded.Add(r)) continue;      // skip rows that are already in place
			foreach (var (col, value) in FetchRow(r))
				_sheet.SetObjectValue(r, col, value);
		}
	}

	private static IEnumerable<(int Col, object? Value)> FetchRow(int row)
		=> [(0, $"row {row}")];                 // a real one would query the database here
}

Attach it to the sheet.

ws.RowDataSource = new QueryRowSource(ws);

// The control calls this for the visible window before every paint.
// Call it yourself only when running headless or pre-loading
ws.EnsureRowsLoaded(0, 99);

The control calls EnsureRowsLoaded for the visible window before every paint. Make sure your implementation skips rows it already prepared (that is what _loaded is for above), or every scroll will hit the database.

Decide the row count (AddWorksheet’s rows) up front to match the real number of records — it is what sets the scrollbar’s length.

Where it fits and where it does not

When you need aggregation, sorting or filtering, either load everything or do the work in the database and put only the result on the sheet.

Opening a huge xlsx

The file side can page in too. Sheets become usable immediately and cells are read out of the file as they are drawn.

using var book = XlsxReader.OpenVirtual("large.xlsx", SheetLoadMode.OnDemand);
control.LoadWorkbook(book.Workbook);      // hand it straight to the control

VirtualWorkbook is IDisposable. It keeps the file and a temp file per sheet open, so dispose it when you replace the workbook or shut down. Cells read as empty after disposal.

Call MaterializeAll() before saving or exporting. Without it, only the region that happened to be loaded is written out.

book.MaterializeAll();                    // make every sheet and row resident
XlsxWriter.Write(book.Workbook, "out.xlsx");

MaterializeAll() brings everything into memory, so it costs the same as an ordinary load (XLSX).

Writing streams

XLSX export writes incrementally rather than assembling the whole document in memory first. Memory use does not grow with the row count. Writing a million rows uses only what the sheet itself occupies.

CSV is the same.

Drawing does not depend on the row count

However many rows the sheet has, only the cells on screen are drawn. Scrolling a million-row sheet is exactly as smooth.

For the same reason, Customizing the Appearance and Freeze Panes cost nothing extra at scale.

When something is slow

A ws.DistinctStyleCount larger than you expect means too many style variants.

What to read next

• Performance and Memory
• Showing a DataTable on a Sheet
• XLSX