Read data from an Excel sheet in C# with ExcelDataReader

More often 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.

Aside from the fact that we should question why we have to read an Excel file and the data isn’t in a DB, the truth is that more often than we’d 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 are going to see a very simple one to connect a C# application with the help of ExcelDataReader.

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

We can add ExcelDataReader to our project via the NuGet package manager.

We have two ways to perform the reading. The most convenient one is to project the Excel sheet into a DataSet. We only have to indicate the path of the Excel sheet, and we will have all the data loaded into the DataSet.

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

//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 data into a DataSet is a very convenient option and sufficient in a vast 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 to read the data which uses a loop 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 quick solution to integrate into our project if all we need is to load data from an Excel sheet.