How to read an excel using ExcelDataReader and Store result to a DataTable?

Below code will allow to read a/multiple worksheet from excel and store to a datatable. To start with we will use ExcelDataReader and ExcelDataReader.DataSet. you can download the same via Nuget package manager to reference in your project

using System.IO;

using ExcelDataReader;

using System.Data;

string fileName= "D:\\Students.xlsx";

//Open file and returns as Stream

FileStream stream = File.Open(fileName, FileMode.Open, FileAccess.Read);

//Createopenxmlreader via ExcelReaderFactory

IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);

//Return as DataSet and Set the First Row as Column Name

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

//Get all the Tables

DataTableCollection table = result.Tables;

//Store it in DataTable

DataTable resultTable = table["Sheet1"];

//Loop through the rows in datatable

foreach(DataRow dr in resultTable.Rows)
{
       var name = dr["Name"];
       var age = dr["Age"];
}

Another way to read /Write excel :How to read/write an excel using Microsoft.Office.Interop.Excel?