ExcelDataReader

Lightweight and fast library written in C# for reading Microsoft Excel files

4,146

988

4,146

View on GitHub

Top Related Projects

ImportExcel

2,567

PowerShell module to import/export Excel spreadsheets, without Excel

ClosedXML is a .NET library for reading, manipulating and writing Excel 2007+ (.xlsx, .xlsm) files. It aims to provide an intuitive and user-friendly interface to dealing with the underlying OpenXML API.

EPPlus

1,909

EPPlus-Excel spreadsheets for .NET

Open-XML-SDK

4,261

Open XML SDK by Microsoft

Quick Overview

ExcelDataReader is a lightweight and fast library for reading Microsoft Excel files (XLS, XLSX, CSV) in .NET applications. It provides a simple API to extract data from Excel spreadsheets without requiring Microsoft Excel to be installed on the system.

Pros

Fast and efficient reading of Excel files
Supports multiple Excel formats (XLS, XLSX, CSV)
No dependency on Microsoft Excel or COM interop
Compatible with .NET Framework and .NET Core

Cons

Limited write capabilities (primarily focused on reading)
May not support all advanced Excel features
Requires manual mapping of data to custom objects
Limited documentation and examples compared to some other libraries

Code Examples

Reading all sheets from an Excel file:

using (var stream = File.Open("path/to/file.xlsx", FileMode.Open, FileAccess.Read))
{
    using (var reader = ExcelReaderFactory.CreateReader(stream))
    {
        var result = reader.AsDataSet();
        // Process data in result.Tables
    }
}

Reading a specific sheet by name:

using (var stream = File.Open("path/to/file.xlsx", FileMode.Open, FileAccess.Read))
{
    using (var reader = ExcelReaderFactory.CreateReader(stream))
    {
        var result = reader.AsDataSet();
        var sheet = result.Tables["SheetName"];
        // Process data in sheet
    }
}

Iterating through rows in a sheet:

using (var stream = File.Open("path/to/file.xlsx", FileMode.Open, FileAccess.Read))
{
    using (var reader = ExcelReaderFactory.CreateReader(stream))
    {
        do
        {
            while (reader.Read())
            {
                // Process row data
                var column1 = reader.GetString(0);
                var column2 = reader.GetDouble(1);
            }
        } while (reader.NextResult());
    }
}

Getting Started

Install the NuGet package:

Install-Package ExcelDataReader

Add the following using statements:

using ExcelDataReader;
using System.IO;

Basic usage:

using (var stream = File.Open("path/to/file.xlsx", FileMode.Open, FileAccess.Read))
{
    using (var reader = ExcelReaderFactory.CreateReader(stream))
    {
        var result = reader.AsDataSet();
        foreach (DataTable table in result.Tables)
        {
            foreach (DataRow row in table.Rows)
            {
                // Process row data
            }
        }
    }
}

Competitor Comparisons

ImportExcel

2,567

PowerShell module to import/export Excel spreadsheets, without Excel

Pros of ImportExcel

PowerShell-native solution, integrating seamlessly with PowerShell workflows
Supports both reading and writing Excel files
Offers advanced features like pivot tables and charts creation

Cons of ImportExcel

Limited to PowerShell environment
May have slower performance for large datasets compared to ExcelDataReader

Code Comparison

ImportExcel:

$excel = Import-Excel -Path "data.xlsx"
$excel | Export-Excel -Path "output.xlsx" -AutoSize

ExcelDataReader:

using (var stream = File.Open("data.xlsx", FileMode.Open, FileAccess.Read))
using (var reader = ExcelReaderFactory.CreateReader(stream))
{
    var result = reader.AsDataSet();
}

Key Differences

ImportExcel is PowerShell-focused, while ExcelDataReader is a .NET library
ExcelDataReader is primarily for reading Excel files, while ImportExcel supports both reading and writing
ImportExcel provides more Excel-specific features, while ExcelDataReader offers a simpler API for data extraction

Use Cases

Choose ImportExcel for PowerShell-based workflows and when advanced Excel features are needed
Opt for ExcelDataReader in .NET applications or when performance is crucial for large datasets

ClosedXML

5,061

Pros of ClosedXML

Provides both read and write capabilities for Excel files
Offers more advanced features like cell styling and formula manipulation
Supports creating Excel files from scratch without templates

Cons of ClosedXML

Larger library size and potentially slower performance for simple read operations
Steeper learning curve due to more complex API
Requires more memory for large file operations

Code Comparison

ClosedXML example:

using (var workbook = new XLWorkbook())
{
    var worksheet = workbook.Worksheets.Add("Sample Sheet");
    worksheet.Cell("A1").Value = "Hello World!";
    workbook.SaveAs("HelloWorld.xlsx");
}

ExcelDataReader example:

using (var stream = File.Open("test.xlsx", FileMode.Open, FileAccess.Read))
using (var reader = ExcelReaderFactory.CreateReader(stream))
{
    var result = reader.AsDataSet();
    var data = result.Tables[0];
}

ClosedXML is more suitable for complex Excel operations and file creation, while ExcelDataReader is lightweight and focused on reading Excel data efficiently. Choose based on your specific requirements and performance needs.

EPPlus

1,909

EPPlus-Excel spreadsheets for .NET

Pros of EPPlus

Supports both reading and writing Excel files
Offers advanced features like formula calculation and chart creation
Actively maintained with regular updates

Cons of EPPlus

Requires a commercial license for some use cases
Larger library size, potentially impacting application size
Steeper learning curve due to more complex API

Code Comparison

EPPlus:

using (var package = new ExcelPackage(new FileInfo("workbook.xlsx")))
{
    var worksheet = package.Workbook.Worksheets.Add("Sheet1");
    worksheet.Cells["A1"].Value = "Hello, EPPlus!";
    package.Save();
}

ExcelDataReader:

using (var stream = File.Open("workbook.xlsx", FileMode.Open, FileAccess.Read))
using (var reader = ExcelReaderFactory.CreateReader(stream))
{
    var result = reader.AsDataSet();
    var value = result.Tables[0].Rows[0][0];
}

ExcelDataReader is primarily focused on reading Excel files, offering a simpler API for this specific task. It's lightweight and easy to use for basic read operations. EPPlus, on the other hand, provides a comprehensive solution for both reading and writing Excel files, with advanced features that come at the cost of increased complexity and potential licensing requirements.

Open-XML-SDK

4,261

Open XML SDK by Microsoft

Pros of Open-XML-SDK

More comprehensive support for Office Open XML formats (Word, Excel, PowerPoint)
Allows both reading and writing of Office documents
Provides lower-level access to document structure and properties

Cons of Open-XML-SDK

Steeper learning curve due to more complex API
Slower performance for simple Excel reading tasks
Larger library size and more dependencies

Code Comparison

ExcelDataReader:

using (var stream = File.Open(filePath, FileMode.Open, FileAccess.Read)) {
    using (var reader = ExcelReaderFactory.CreateReader(stream)) {
        var result = reader.AsDataSet();
        // Process data from result
    }
}

Open-XML-SDK:

using (SpreadsheetDocument doc = SpreadsheetDocument.Open(filePath, false)) {
    WorkbookPart workbookPart = doc.WorkbookPart;
    Sheet sheet = workbookPart.Workbook.Sheets.GetFirstChild<Sheet>();
    WorksheetPart worksheetPart = (WorksheetPart)workbookPart.GetPartById(sheet.Id);
    // Process data from worksheetPart
}

ExcelDataReader is more straightforward for simple Excel reading tasks, while Open-XML-SDK offers more control and flexibility at the cost of increased complexity. The choice between the two depends on the specific requirements of your project, such as the need for writing capabilities, performance considerations, and the level of document manipulation required.

Convert designs to code with AI

Introducing Visual Copilot: A new AI model to turn Figma designs to high quality code using your components.

Try Visual Copilot

README

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

Branch	Build status
develop
master

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 <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

using (var stream = File.Open(filePath, FileMode.Open, FileAccess.Read))
{
    // Auto-detect format, supports:
    //  - Binary Excel files (2.0-2003 format; *.xls)
    //  - OpenXml Excel files (2007 format; *.xlsx, *.xlsb)
    using (var reader = ExcelReaderFactory.CreateReader(stream))
    {
        // Choose one of either 1 or 2:

        // 1. Use the reader methods
        do
        {
            while (reader.Read())
            {
                // reader.GetDouble(0);
            }
        } while (reader.NextResult());

        // 2. Use the AsDataSet extension method
        var result = reader.AsDataSet();

        // The result of each spreadsheet is in result.Tables
    }
}

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.

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`.
`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:

var reader = ExcelReaderFactory.CreateReader(stream, new ExcelReaderConfiguration()
{
    // Gets or sets the encoding to use when the input XLS lacks a CodePage
    // record, or when the input CSV lacks a BOM and does not parse as UTF8. 
    // Default: cp1252 (XLS BIFF2-5 and CSV only)
    FallbackEncoding = Encoding.GetEncoding(1252),

    // Gets or sets the password used to open password protected workbooks.
    Password = "password",

    // Gets or sets an array of CSV separator candidates. The reader 
    // autodetects which best fits the input data. Default: , ; TAB | # 
    // (CSV only)
    AutodetectSeparators = new char[] { ',', ';', '\t', '|', '#' },

    // Gets or sets a value indicating whether to trim white space values for CSV (Default 'true').
    // (CSV only)
    TrimWhiteSpace = true,

    // Gets or sets a value indicating whether to leave the stream open after
    // the IExcelDataReader object is disposed. Default: false
    LeaveOpen = false,

    // Gets or sets a value indicating the number of rows to analyze for
    // encoding, separator and field count in a CSV. When set, this option
    // causes the IExcelDataReader.RowCount property to throw an exception.
    // Default: 0 - analyzes the entire file (CSV only, has no effect on other
    // formats)
    AnalyzeInitialCsvRows = 0,
});

AsDataSet() configuration options

The AsDataSet() method accepts an optional configuration object to modify the behavior of the DataSet conversion:

var result = reader.AsDataSet(new ExcelDataSetConfiguration()
{
    // Gets or sets a value indicating whether to set the DataColumn.DataType 
    // property in a second pass.
    UseColumnDataType = true,

    // Gets or sets a callback to determine whether to include the current sheet
    // in the DataSet. Called once per sheet before ConfigureDataTable.
    FilterSheet = (tableReader, sheetIndex) => true,

    // Gets or sets a callback to obtain configuration options for a DataTable. 
    ConfigureDataTable = (tableReader) => new ExcelDataTableConfiguration()
    {
        // Gets or sets a value indicating the prefix of generated column names.
        EmptyColumnNamePrefix = "Column",

        // Gets or sets a value indicating whether to use a row from the 
        // data as column names.
        UseHeaderRow = false,

        // Gets or sets a callback to determine which row is the header row. 
        // Only called when UseHeaderRow = true.
        ReadHeaderRow = (rowReader) => {
            // F.ex skip the first row and use the 2nd row as column headers:
            rowReader.Read();
        },

        // Gets or sets a callback to determine whether to include the 
        // current row in the DataTable.
        FilterRow = (rowReader) => {
            return true;
        },

        // Gets or sets a callback to determine whether to include the specific
        // column in the DataTable. Called once per column after reading the 
        // headers.
        FilterColumn = (rowReader, columnIndex) => {
            return true;
        }
    }
});

Setting up AsDataSet() configuration, use the FilterRow callback to implement a "progress indicator" while loading, e.g.:

var result = reader.AsDataSet(new ExcelDataSetConfiguration()
{
    ConfigureDataTable = (tableReader) => new ExcelDataTableConfiguration()
    {
        FilterRow = (rowReader) => {
            int progress = (int)Math.Ceiling((decimal)rowReader.Depth / (decimal)rowReader.RowCount * (decimal)100);
            // progress is in the range 0..100
            return true;
        }
    }
});

Formatting

ExcelDataReader does not support formatting directly. Users may retreive the number format string for a cell through IExcelDataReader.GetNumberFormatString(i) and use the third party ExcelNumberFormat library for formatting purposes.

Example helper method using ExcelDataReader and ExcelNumberFormat to format a value:

string GetFormattedValue(IExcelDataReader reader, int columnIndex, CultureInfo culture)
{
    var value = reader.GetValue(columnIndex);
    var formatString = reader.GetNumberFormatString(columnIndex);
    if (formatString != null)
    {
        var format = new NumberFormat(formatString);
        return format.Format(value, culture);
    }
    return Convert.ToString(value, culture);
}

Important note when upgrading from ExcelDataReader 2.x

ExcelDataReader 3 had some breaking changes, and older code may produce error messages similar to:

'IExcelDataReader' does not contain a definition for 'AsDataSet'...
'IExcelDataReader' does not contain a definition for 'IsFirstRowAsColumnNames'...

To fix:

Make sure to rename any Excel namespace references in the code to the new namespace ExcelDataReader
Make sure the project has a reference to the ExcelDataReader.DataSet package to use AsDataSet()
Remove the line of code with IsFirstRowAsColumnNames and change the call to AsDataSet() to something like this:

var result = reader.AsDataSet(new ExcelDataSetConfiguration()
{
    ConfigureDataTable = (_) => new ExcelDataTableConfiguration()
    {
        UseHeaderRow = true
    }
});

Important note on .NET Core

By default, ExcelDataReader throws a NotSupportedException "No data is available for encoding 1252." on .NET Core and .NET 5.0 or later.

To fix, add a dependency to the package System.Text.Encoding.CodePages and then add code to register the code page provider during application initialization (f.ex in Startup.cs):

System.Text.Encoding.RegisterProvider(System.Text.CodePagesEncodingProvider.Instance);

This is required to parse strings in binary BIFF2-5 Excel documents encoded with DOS-era code pages. These encodings are registered by default in the full .NET Framework, but not on .NET Core and .NET 5.0 or later.

Top Related Projects

Convert designs to code with AI

Introducing Visual Copilot: A new AI model to turn Figma designs to high quality code using your components.

Try Visual Copilot