Showing a DataTable on a Sheet

There is no mechanism for binding a DataTable directly. You write it in a loop — which in exchange lets you decide exactly which column goes where and how it looks.

Tens of thousands of rows are not a problem: writing costs only what you actually wrote (Performance and Memory).

Filling the sheet

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

// column names on row 1, data from row 2
for (int c = 0; c < table.Columns.Count; c++)
	ws.SetText(0, c, table.Columns[c].ColumnName);

for (int r = 0; r < table.Rows.Count; r++)
	for (int c = 0; c < table.Columns.Count; c++)
	{
		object? v = table.Rows[r][c];
		ws.SetObjectValue(r + 1, c, v == DBNull.Value ? null : v);
	}

SetObjectValue sorts string, double, int, bool, DateTime, decimal and the rest into the right kind. Turn DBNull into null before passing it (CellValue).

A DateTime goes in as an Excel serial number. To display it as a date, set a number format (below).

Write typed when you know the type

// when you know the column's type, the typed Set* calls are faster
for (int r = 0; r < table.Rows.Count; r++)
{
	DataRow row = table.Rows[r];
	ws.SetText(r + 1, 0, (string)row["Name"]);
	ws.SetNumber(r + 1, 1, Convert.ToDouble(row["Amount"]));
	ws.SetObjectValue(r + 1, 2, row["OrderDate"]);      // a DateTime becomes a serial number
}

SetText / SetNumber / SetBoolean skip the type dispatch and are lighter than SetObjectValue. The difference becomes noticeable past 100,000 rows.

Headers and formatting

// set the header row in one go with a row style (never cell by cell)
ws.SetRowStyle(0, new StyleRecord
{
	Bold = true,
	BackgroundColor = 0xFFF3F3F3,
	TextAlign = HAlign.Center,
});

// the amount column gets a column style plus a number format, clamped to the used range
ws.SetColumnStyle(1, new StyleRecord { TextAlign = HAlign.Right });
ws.SetNumberFormat(new RangePosition(1, 1, table.Rows.Count, 1), "#,##0");

Do not set formatting in a cell loop. Use SetRowStyle for the header row and SetColumnStyle to right-align a column. The result looks the same, but doing it per cell materializes one entry for every cell in that row or column (Style Inheritance).

Number formats for a date column work the same way.

ws.SetNumberFormat(new RangePosition(1, 2, table.Rows.Count, 1), "yyyy/mm/dd");

Freezing the header row is a control-side operation.

control.SetFreeze(rows: 1, cols: 0);

Fitting column widths to the content

control.AutoFitSelectedColumns();

It acts on the selected columns, so select everything first to cover them all. It is the same operation as double-clicking a header boundary.

Filling from a List<T>

Nothing changes without a DataTable — just map properties to columns.

for (int i = 0; i < items.Count; i++)
{
    ws.SetText(i + 1, 0, items[i].Name);
    ws.SetNumber(i + 1, 1, items[i].Amount);
    ws.SetObjectValue(i + 1, 2, items[i].OrderDate);
}

You could generate the columns by reflection, but at 100,000 rows the property access dominates. With a lot of rows, write it out as above.

Reading the edits back

// sheet -> DataTable, reading row 1 as the column names
var table = new DataTable();
if (!ws.TryGetUsedRange(out RangePosition used)) return table;

for (int c = used.Col; c <= used.EndCol; c++)
	table.Columns.Add(ws.GetDisplayText(used.Row, c));

for (int r = used.Row + 1; r <= used.EndRow; r++)
{
	DataRow row = table.NewRow();
	for (int c = used.Col; c <= used.EndCol; c++)
		row[c - used.Col] = ws.GetObjectValue(r, c) ?? DBNull.Value;
	table.Rows.Add(row);
}

GetObjectValue returns a string, double, bool or null depending on the cell. For a formula cell you get the computed result; use GetFormula when you want the formula itself.

To pull a value out as a date, convert the serial.

double serial = ws.GetValue(r, c).AsNumber;
DateTime date = DateTime.FromOADate(serial);