ExcelDataReader

# ExcelDataReader Lightweight and fast library written in C# for reading Microsoft Excel files (2.0-2021, 365). Please feel free to fork and submit pull requests to the develop branch. If you are reporting an issue it is really useful if you can supply an example Excel file as this makes debugging much easier and without it we may not be able to resolve any problems. ## Continuous integration ## Supported file formats and versions | File Type | Container Format | File Format | Excel Version(s) | | --------- | ---------------- | ----------- | ---------------- | | .xlsx | ZIP, CFB+ZIP | OpenXml | 2007 and newer | | .xlsb | ZIP, CFB | OpenXml | 2007 and newer | | .xls | CFB | BIFF8 | 97, 2000, XP, 2003
98, 2001, v.X, 2004 (Mac) | | .xls | CFB | BIFF5 | 5.0, 95 | | .xls | - | BIFF4 | 4.0 | | .xls | - | BIFF3 | 3.0 | | .xls | - | BIFF2 | 2.0, 2.2 | | .csv | - | CSV | (All) | ## Finding the binaries It is recommended to use NuGet through the VS Package Manager Console `Install-Package ` or using the VS "Manage NuGet Packages..." extension. As of ExcelDataReader version 3.0, the project was split into multiple packages: Install the `ExcelDataReader` base package to use the "low level" reader interface. Compatible with net462, netstandard2.0 and netstandard2.1. Install the `ExcelDataReader.DataSet` extension package to use the `AsDataSet()` method to populate a `System.Data.DataSet`. This will also pull in the base package. Compatible with net462, netstandard2.0 and netstandard2.1. ## How to use ``` … ``` ### Reading .CSV files Use `ExcelReaderFactory.CreateCsvReader` instead of `CreateReader` to parse a stream of plain text with comma separated values. See also the configuration options `FallbackEncoding` and `AutodetectSeparators`. The input CSV is always parsed once completely to set FieldCount, RowCount, Encoding, Separator (or twice if the CSV lacks BOM and is not UTF8), and then parsed once again while iterating the row records. Throws `System.Text.DecoderFallbackException` if the input cannot be parsed with the specified encoding. For large CSV files where `RowCount` is not needed upfront and `FieldCount` from the first row (e.g. a header row) is sufficient, set `AnalyzeInitialCsvRows = 1` to limit the pre-scan to a single row. This avoids reading the entire file twice. The reader returns all CSV field values as strings and makes no attempts to convert the data to numbers or dates. This caller is responsible for interpreting the CSV data. ### Using the reader methods The `AsDataSet()` extension method is a convenient helper for quickly getting the data, but is not always available or desirable to use. IExcelDataReader extends the `System.Data.IDataReader` and `IDataRecord` interfaces to navigate and retrieve data at a lower level. The most important reader methods and properties: | Method | Property | |-----------------------------------------------------------------------------------------|-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| | `Read()` | reads a row from the current sheet. | | `NextResult()` | advances the cursor to the next sheet. | | `ResultsCount` | returns the number of sheets in the current workbook. | | `Name` | returns the name of the current sheet. | | `CodeName` | returns the VBA code name identifier of the current sheet. | | `FieldCount` | returns the number of columns in the current sheet. | | `RowCount` | returns the number of rows in the current sheet. This includes terminal empty rows which are otherwise excluded by AsDataSet(). Throws `InvalidOperationException` on CSV files when used with `AnalyzeInitialCsvRows`. | | `Depth` | always returns `0` because ExcelDataReader does not expose nested result sets. | | `HeaderFooter` | returns an object with information about the headers and footers, or `null` if there are none. | | `MergeCells` | returns an array of merged cell ranges in the current sheet. | | `RowHeight` | returns the visual height of the current row in points. May be 0 if the row is hidden. | | `GetColumnWidth()` | returns the width of a column in character units. May be 0 if the column is hidden. | | `GetFieldType()` | returns the type of a value in the current row. Always one of the types supported by Excel: `double`, `int`, `bool`, `DateTime`, `TimeSpan`, `string`, or `null` if there is no value. | | `IsDBNull()` | checks if a value in the current row is null. | | `GetValue()` | returns a value from the current row as an `object`, or `null` if there is no value. | | `GetDouble()`
`GetInt32()`
`GetBoolean()`
`GetDateTime()`
`GetString()` | return a value from the current row cast to their respective type. | | `GetNumberFormatString()` | returns a string containing the formatting codes for a value in the current row, or `null` if there is no value. See also the Formatting section below. | | `GetNumberFormatIndex()` | returns the number format index for a value in the current row. Index values below 164 refer to built-in number formats, otherwise indicate a custom number format. | | `GetCellStyle()` | returns an object containing style information for a cell in the current row: indent, horizontal alignment, hidden, locked. | | The typed `Get*()` methods | throw `InvalidCastException` unless the types match exactly. | ### CreateReader() configuration options The `ExcelReaderFactory.CreateReader()`, `CreateBinaryReader()`, `CreateOpenXmlReader()`, `CreateCsvReader()` methods accept an optional configuration object to modify the behavior of the reader: ``` … ``` `CreateReader()`, `CreateBinaryReader()`, `CreateOpenXmlReader()`, and `CreateCsvReader()` require seek support during probing and parsing. If the input stream is non-seekable, ExcelDataReader copies it to a `MemoryStream` first. This is a 4.0 breaking behavior change: when a non-seekable stream is copied, the original source stream may be consumed even when `LeaveOpen = true`. ### AsDataSet() configuration options The `AsDataSet()` method accepts an optional configuration object to modify the behavior of the DataSet conversion: ``` … ``` Setting up `AsDataSet()` configuration, use the FilterRow callback to implement a "progress indicator" while loading, e.g.: ```c# int currentRow = 0; var result = reader.AsDataSet(new ExcelDataSetConfiguration() { ConfigureDataTable = (tableReader) => new ExcelDataTableConfiguration() { FilterRow = (rowReader) => { int progress = (int)Math.Ceiling((decimal)++currentRow / (decimal)rowReader.RowCount * (decimal)100); // progress is in the range 0..100 return true; } } }); ``` ## Formatting ExcelDataReader does not support formatting directly. Users may retrieve the number format string for a cell through `IExcelDataReader.GetNumberFormatString(i)` and use the third party ExcelNumberFormat library for formatting purposes. `GetNumberFormatString(i)` returns Excel's original locale-independent built-in format strings. To get locale-specific date and time format strings for built-in number format indices 14–17 and 22, use the overload `GetNumberFormatString(i, provider)` with a `CultureInfo` or `DateTimeFormatInfo`. Passing `null` is equivalent to calling the no-argument overload. Example helper method using ExcelDataReader and ExcelNumberFormat to format a value: ```c# string GetFormattedValue(IExcelDataReader reader, int columnIndex, CultureInfo cu