Read Excel file in C# Without Using Interop

In today's article I will show you a simple tutorial with an example how you can read the excel file of multiple Sheet in a dataset without using Microsoft.Office.Interop.Excel in .Net 8 Core using c#.net. In this article I will pass the path of the file and read the data into dataset and display the number of sheet and total record available in first sheet in console application.

Now let's start, for this first we will create a new console application and install the NuGet package names as ExcelDataReader and ExcelDataReader.DataSet.

Before proceeding the to write the code w will create an excel file with two sheets. Here i am showing only one sheet with data. which we will display.

Here we will read the sheet in data sheet without using office library.

using ExcelDataReader;
using System.Data;

string path = "C:\\Personal\\InputExcel.xlsx";
DataSet dataSet = new DataSet();
string ext = Path.GetExtension(path);
if (ext == ".xls" || ext == ".xlsx")
{
    bool isFirstRowHeader = true; //This you can change as per to your input sheet
    System.Text.Encoding.RegisterProvider(System.Text.CodePagesEncodingProvider.Instance);
    using var stream = new FileStream(path, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
    using IExcelDataReader reader = ExcelReaderFactory.CreateReader(stream);
    dataSet = reader.AsDataSet(new ExcelDataSetConfiguration()
    {
        UseColumnDataType = false,
        ConfigureDataTable = (tableReader) => new ExcelDataTableConfiguration()
        {
            UseHeaderRow = isFirstRowHeader
        }
    });
    //Here to demonstrate display of data from dataset
    if (dataSet.Tables[0].Rows.Count > 0)
    {
        Console.WriteLine("Total Sheet Count: " + dataSet.Tables.Count);
        Console.WriteLine("Total Record in Excel: " + dataSet.Tables[0].Rows.Count);
    }
}
else
{
    Console.WriteLine("Error !!!wrong file selected.");
}
Console.ReadLine();

In above code I have taken the path of file in and after that I have validated the file extension if it is excel or not. If file is excel on that I have readd the file detail in DataSet. Before reading I have passed whether we need to first row is included as header or not in dataset or not. At the end I have displayed the total no of rows in the excel file sheet. Now let's put a break point execute the code.