Read data from an Excel sheet in C# with ExcelDataReader

On more occasions than we would like, we will have to read data from an Excel sheet in our application. Let’s see a quick way to connect to an Excel sheet with C# and the ExcelDataReader library.

Apart from the fact that we would have to consider why we have to read an Excel and the data is not in a DB, the truth is that more often than we would like, we will have to read data from an Excel sheet.

There are many ways and alternatives to access data from an Excel sheet. This time we will see a very simple way to connect an application with C# and the help of ExcelDataReader.

ExcelDataReader is a library designed to read Excel sheets and CSV files, with special emphasis on speed and convenience. It is very practical if we only have to read data and not modify the Excel.

We can add ExcelDataReader to our project from the Nutget package manager.

We have two ways to perform the reading. The most comfortable is to project the Excel sheet to a DataSet. We only have to indicate the address of the Excel sheet, and we will have all the data loaded in the DataSet.

The loaded DataSet contains a table for each Sheet of the Workbook. Each table has the rows of the Sheet, and these in turn have the cells of each row. The loaded data starts in the range ‘A1’ and goes to the last non-empty cell in the Excel.

//Ruta del fichero Excel
string filePath = ""; 

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

    // Examples of data access
    DataTable table = result.Tables[0];
    DataRow row = table.Rows[0];
    string cell = row[0].ToString();
  }
}

Loading the data into a DataSet is a very convenient and sufficient option in a large majority of cases. However, it has the disadvantage of not being able to use special functions that ExcelDataReader contains.

For this reason, we have an alternative way of reading the data in which a loop is used to perform the reading, very similar to using an SQLDataReader.

//Ruta del fichero Excel
string filePath = "";

using (var stream = File.Open(filePath, FileMode.Open, FileAccess.Read))
{
  using (var reader = ExcelReaderFactory.CreateReader(stream))
  {
    do
    {
      while (reader.Read())
      {
         reader.GetDouble(0);
      }
    } while (reader.NextResult());

  }
}

In short, ExcelDataReader is a very simple and fast solution to integrate into our project if all we need is to load data from an Excel sheet.

ExcelDataReader is Open Source and the code is available at https://github.com/ExcelDataReader/ExcelDataReader.