How to read an excel (.xls/.xlsx) file in C#

How to read an excel (.xls/.xlsx) file in C#?

Click New Project, then select Visual C# on the left, then Windows and then select Windows Forms Application. Name your project "ReadExcelFile" and then click OK

You need to add the ExcelDataReader NuGet package to your project. You can do this via the NuGet Package Manager Console.

Install-Package ExcelDataReader
Install-Package ExcelDataReader.DataSet

You can also add the ExcelDataReader through the NuGet Package Manager GUI by right-clicking on your project select Manage NuGet Packages -> Search ExcelDataReader -> Install

Drag and drop the Label, Button, Combobox and DataGridView controls from your Visual Studio toolbox onto your form designer, then you can layout your form as shown below.

Create an excel file, then copy data from the Northwind database to the excel file.

Double-click the button to create an event handler for the Click event.

DataSet result;

private void btnOpen_Click(object sender, EventArgs e)
{
    using (OpenFileDialog ofd = new OpenFileDialog() { Filter = "Excel Workbook 97-2003|*.xls|Excel Workbook|*.xlsx", ValidateNames = true })
    {
        if (ofd.ShowDialog() == DialogResult.OK)
        {
            FileStream fs = File.Open(ofd.FileName, FileMode.Open, FileAccess.Read);
            IExcelDataReader reader;
            if (ofd.FilterIndex == 1)
                reader = ExcelReaderFactory.CreateBinaryReader(fs);//Read excel 97-2003
            else
                reader = ExcelReaderFactory.CreateOpenXmlReader(fs);//Read excel 2007
            reader.IsFirstRowAsColumnNames = true;
            result = reader.AsDataSet();
            cboSheet.Items.Clear();
            //Add sheet to comboxbox
            foreach (DataTable dt in result.Tables)
                cboSheet.Items.Add(dt.TableName);
            reader.Close();
        }
    }
}

private void cboSheet_SelectedIndexChanged(object sender, EventArgs e)
{
    //Fill data from excel into DataGridView based on sheet selection
    dataGridView.DataSource = result.Tables[cboSheet.SelectedIndex];
}

If your Excel file has multiple sheets and you want to read a specific one, you can iterate through result.Tables to access different sheets.

Run the application, then click the button to open an Excel file and select an .xls or .xlsx file.

You need to select your excel sheet from the combobox to see the content displayed in the DataGridView.

The ExcelDataReader library is suitable for reading Excel files, but it doesn't support writing Excel files. If you need to create or modify Excel files, consider using libraries like EPPlus or ClosedXML.

This setup provides a straightforward way to read and display Excel data in a Windows Forms application.

VIDEO TUTORIAL