Upload, Read and Display Excel File in .Net Core Using C#.net

In today's article will show a simple tutorial with example how you can upload, read or extract or get data of Excel (.xlsx or xls) into dataset and display it into DataGridView using c#.net in .NET core without using Microsoft.Jet.OLEDB or any office com library. Here I have used OpenXML. Here I am using .net8 core. This tutorial you can user in windows application, console application and in asp.net core mvc application also. So, for this article first we need to create a .net 8 core windows application using c#.net.

So, for this example we will first browse the file and click on submit button to will display the excel data in DataGridView. Now let's create a windows application and add come controls to prepare the form.

Now let's create the click even for Browse button to add code for selection of excel file in your .net 8 sample application.

private void btnBrowse_Click(object sender, EventArgs e)
{
    OpenFileDialog ofd = new OpenFileDialog();
    ofd.Filter = "Excel Files (.xls,.xlsx)|*.xls;*.xlsx";
    if (ofd.ShowDialog() == DialogResult.OK)
    {
        txtPath.Text = ofd.FileName;
    }
}

In above code I have used OpenFileDialog to open file dialog for file selection and used filter to make user only select only excel file. Now let's move forward to write code to read the excel file in dataset and display it in DataGridView. So, for this we need to install two of the NuGet package "ExcelDataReader".

After installing this both NuGet packages we need to create the click event for the Submit button and add the below code. Here is the sample excel which we are going user.

Now let's move towards the code to read the excel file.

private void btnSubmit_Click(object sender, EventArgs e)
{
    bool isFirstRowHeader = true; //This you can change as per to your input sheet
    DataSet dataSet = new DataSet();
    string ext = Path.GetExtension(txtPath.Text);
    if (ext == ".xls" || ext == ".xlsx")
    {
        //code to read excel file with all sheets
        System.Text.Encoding.RegisterProvider(System.Text.CodePagesEncodingProvider.Instance);
        using var stream = new FileStream(txtPath.Text, 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)
        {
            dataGridView1.DataSource = dataSet.Tables[0];
        }
    }
    else
    {
        MessageBox.Show("Error !!!wrong file selected.");
    }
}

In above code I have defined a bool variable to consider the header or not. After that I have defined object for dataset. Here one thing we must need to register the provider in .net 8 core "System.Text.Encoding.RegisterProvider(System.Text.CodePagesEncodingProvider.Instance);". Without this the code will not work it will throw exception. At the end I have bind the first table to DataGridView. Now we have done let's run the code to check the output.