Class ExcelReader

Namespace

SheetStream.Reading

Assembly

SheetStream.dll

Asynchronous reading: the counterpart of Worksheet.LoadFromAsyncEnumerable. An upload can be buffered with OpenAsync(Stream, bool, CancellationToken) and its rows pulled with ReadRowsAsync(string, CancellationToken) without ever blocking a request thread on I/O. Only available on .NET 8 and later; netstandard2.0 has no built-in IAsyncEnumerable<T>.

public sealed class ExcelReader : IDisposable

Inheritance

object

ExcelReader

Implements

IDisposable

Inherited Members

object.Equals(object)

object.Equals(object, object)

object.GetHashCode()

object.GetType()

object.ReferenceEquals(object, object)

object.ToString()

Examples

using var reader = ExcelReader.Open("import.xlsx");
foreach (var row in reader.ReadRows("Products"))
    Console.WriteLine($"{row.GetString(0)} = {row.GetDouble(2)}");
var products = reader.Read<Product>("Products").ToList();

Properties

SheetNames

Sheet names in tab order (hidden sheets included).

public IReadOnlyList<string> SheetNames { get; }

Property Value

IReadOnlyList<string>

Methods

Dispose()

Performs application-defined tasks associated with freeing, releasing, or resetting unmanaged resources.

public void Dispose()

GetVisibility(string)

Visibility of a sheet, by name.

public SheetVisibility GetVisibility(string sheetName)

Parameters

sheetName string

Returns

SheetVisibility

Open(Stream, bool)

Opens a workbook from a stream. A non-seekable stream (an HTTP upload, say) is first copied into memory, because the zip directory sits at the end of the file.

public static ExcelReader Open(Stream stream, bool leaveOpen = false)

Parameters

stream Stream

leaveOpen bool

Returns

ExcelReader

Open(string)

Opens a workbook file for reading. The file is shared for reading so Excel can keep it open.

public static ExcelReader Open(string path)

Parameters

path string

Returns

ExcelReader

OpenAsync(Stream, bool, CancellationToken)

Opens a workbook from a stream, buffering asynchronously when the stream cannot seek. A seekable stream (a FileStream or MemoryStream) is wrapped directly, exactly like Open(Stream, bool). A non-seekable one (an ASP.NET Core request body, an HttpClient response) must be copied first because the zip directory sits at the end of the file; the copy uses CopyToAsync-style reads so no thread is blocked on the network, and it stays in memory up to SheetStream.Reading.ExcelReader.InMemoryBufferLimit (64 MB) before spilling into a temporary file that is deleted with the reader.

public static Task<ExcelReader> OpenAsync(Stream stream, bool leaveOpen = false, CancellationToken cancellationToken = default)

Parameters

stream Stream

The .xlsx content.

leaveOpen bool

True to leave stream open when the reader is disposed (or, for a non-seekable stream, after it has been copied).

cancellationToken CancellationToken

Cancels the copy of a non-seekable stream.

Returns

Task<ExcelReader>

Remarks

Once the bytes are in place the package directory, workbook, shared strings and styles are parsed synchronously: ZipArchive has no asynchronous API and those parts are small compared with the sheets. The row data itself is read asynchronously by ReadRowsAsync(string, CancellationToken).

OpenAsync(string, CancellationToken)

Opens a workbook file for asynchronous reading. The file is opened for overlapped I/O (Asynchronous) so that ReadRowsAsync(string, CancellationToken) pulls sheet bytes with true asynchronous reads instead of thread-pool emulation. Opening itself is not fully asynchronous: the zip directory, workbook, shared strings and styles are read synchronously because ZipArchive has no asynchronous API; the returned task only surfaces I/O errors (a missing file, say) the way an async caller expects. Like Open(string), the file is shared for reading so Excel can keep it open.

public static Task<ExcelReader> OpenAsync(string path, CancellationToken cancellationToken = default)

Parameters

path string

cancellationToken CancellationToken

Returns

Task<ExcelReader>

ReadAsync<T>(string, ISheetRowMapper<T>, CancellationToken)

Maps rows to objects asynchronously through an explicit ISheetRowMapper<T>, like Read<T>(string, ISheetRowMapper<T>). No reflection is involved, so this is the overload for trimmed and Native AOT applications.

public IAsyncEnumerable<T> ReadAsync<T>(string sheetName, ISheetRowMapper<T> mapper, CancellationToken cancellationToken = default)

Parameters

sheetName string

mapper ISheetRowMapper<T>

cancellationToken CancellationToken

Returns

IAsyncEnumerable<T>

Type Parameters

T

ReadAsync<T>(string, CancellationToken)

Maps rows to objects asynchronously, with the rules of Read<T>(string): the first row is the header, headers match members case-insensitively, a value that cannot be converted throws a FormatException naming the row and column, and blank rows are skipped. A mapper registered in SheetMappers takes precedence over reflection.

public IAsyncEnumerable<T> ReadAsync<T>(string sheetName, CancellationToken cancellationToken = default) where T : new()

Parameters

sheetName string

cancellationToken CancellationToken

Returns

IAsyncEnumerable<T>

Type Parameters

T

Examples

await foreach (var product in reader.ReadAsync<Product>("Products", ct))
    await db.Products.AddAsync(product, ct);

ReadDataTable(string, bool)

Reads a sheet into a DataTable. With firstRowIsHeader the first row supplies column names (duplicates get a numeric suffix); otherwise columns are named A, B, C... Column types are object so mixed columns survive.

public DataTable ReadDataTable(string sheetName, bool firstRowIsHeader = true)

Parameters

sheetName string

firstRowIsHeader bool

Returns

DataTable

ReadDataTableAsync(string, bool, CancellationToken)

Reads a sheet into a DataTable asynchronously, with the column rules of ReadDataTable(string, bool). The table is built in memory, so the gain over the synchronous call is only that the sheet bytes are read without blocking a thread.

public Task<DataTable> ReadDataTableAsync(string sheetName, bool firstRowIsHeader = true, CancellationToken cancellationToken = default)

Parameters

sheetName string

firstRowIsHeader bool

cancellationToken CancellationToken

Returns

Task<DataTable>

ReadHyperlinks(int)

Lists the hyperlinks of a sheet by zero-based position.

public IReadOnlyList<ExcelHyperlink> ReadHyperlinks(int sheetIndex)

Parameters

sheetIndex int

Returns

IReadOnlyList<ExcelHyperlink>

ReadHyperlinks(string)

Lists the hyperlinks of a sheet by name (case-insensitive). External targets are resolved through the sheet's relationships; in-workbook links surface as Location. The sheet's rows are skipped, not parsed. A sheet without links yields an empty list.

public IReadOnlyList<ExcelHyperlink> ReadHyperlinks(string sheetName)

Parameters

sheetName string

Returns

IReadOnlyList<ExcelHyperlink>

ReadMergedRanges(int)

Lists the merged cell ranges of a sheet by zero-based position.

public IReadOnlyList<MergedRange> ReadMergedRanges(int sheetIndex)

Parameters

sheetIndex int

Returns

IReadOnlyList<MergedRange>

ReadMergedRanges(string)

Lists the merged cell ranges of a sheet by name (case-insensitive). The sheet's rows are skipped, not parsed, so this is cheap even on a sheet with a million rows. A sheet without merges yields an empty list.

public IReadOnlyList<MergedRange> ReadMergedRanges(string sheetName)

Parameters

sheetName string

Returns

IReadOnlyList<MergedRange>

ReadRows(int)

Streams the rows of a sheet by zero-based position.

public IEnumerable<ExcelRow> ReadRows(int sheetIndex)

Parameters

sheetIndex int

Returns

IEnumerable<ExcelRow>

ReadRows(string)

Streams the rows of a sheet by name (case-insensitive).

public IEnumerable<ExcelRow> ReadRows(string sheetName)

Parameters

sheetName string

Returns

IEnumerable<ExcelRow>

ReadRowsAsync(int, CancellationToken)

Streams the rows of a sheet by zero-based position; see ReadRowsAsync(string, CancellationToken).

public IAsyncEnumerable<ExcelRow> ReadRowsAsync(int sheetIndex, CancellationToken cancellationToken = default)

Parameters

sheetIndex int

cancellationToken CancellationToken

Returns

IAsyncEnumerable<ExcelRow>

ReadRowsAsync(string, CancellationToken)

Streams the rows of a sheet by name (case-insensitive) without blocking a thread: the sheet XML is parsed with the asynchronous XmlReader methods and cancellation is checked before every row. Values are identical to ReadRows(string); the two share the cell parsing code. Breaking out of the await foreach early disposes the XML reader and the zip entry stream.

public IAsyncEnumerable<ExcelRow> ReadRowsAsync(string sheetName, CancellationToken cancellationToken = default)

Parameters

sheetName string

cancellationToken CancellationToken

Returns

IAsyncEnumerable<ExcelRow>

Examples

using var reader = await ExcelReader.OpenAsync(Request.Body, cancellationToken: ct);
await foreach (var row in reader.ReadRowsAsync("Products", ct))
    Console.WriteLine($"{row.GetString(0)} = {row.GetDouble(2)}");

Read<T>(string)

Maps rows to objects. The first row is the header; each header is matched (case-insensitively) to a writable property or field by SheetColumnAttribute name, [DisplayName] or member name. Values are converted to the member type; a value that cannot be converted throws a FormatException naming the row and column. Unmatched columns are ignored.

public IEnumerable<T> Read<T>(string sheetName) where T : new()

Parameters

sheetName string

Returns

IEnumerable<T>

Type Parameters

T

Read<T>(string, ISheetRowMapper<T>)

Maps rows to objects through an explicit ISheetRowMapper<T> (for example Order.SheetMapper generated for a [SheetRow] type). Behaves like Read<T>(string): the first row is the header, headers are matched case-insensitively, values are converted to the member type and a failed conversion throws a FormatException naming the row and column. No reflection is used, so this overload is safe for trimmed and Native AOT applications.

public IEnumerable<T> Read<T>(string sheetName, ISheetRowMapper<T> mapper)

Parameters

sheetName string

mapper ISheetRowMapper<T>

Returns

IEnumerable<T>

Type Parameters

T