How to Read an Excel File in C#

How to Read an Excel File in C# using ExcelDataReader

Start by opening your Visual Studio, From the main menu, click on File, then select New > Project. In the left pane, select Visual C#, then choose Windows, and click on Windows Forms Application. Name your project "ReadExcelFile" and click OK to create the project.

Install the ExcelDataReader Package

To read Excel files in your C# project, you’ll need to install the ExcelDataReader library. This can be done via NuGet Package Manager.

1. Right-click on your project in the Solution Explorer and choose Manage NuGet Packages.
2. Search for ExcelDataReader and install it. This lightweight library is perfect for reading Microsoft Excel files (from Excel 2.0 to 2007).

Design the Form

Drag and drop the Combobox, Label, Button and DataGridView from the Visual Toolbox onto your form designer, then design your form as shown below.

Double-click on the Open button to create its click event handler, and add the following code to allow users to open an Excel file:

DataSet result;

// c# read excel file
private void btnOpen_Click(object sender, EventArgs e)
{
    using (OpenFileDialog ofd = new OpenFileDialog() { Filter = "Excel Workbook|*.xls", ValidateNames = true })
    {
        if (ofd.ShowDialog() == DialogResult.OK)
        {
            // Open and read the Excel file
            FileStream fs = File.Open(ofd.FileName, FileMode.Open, FileAccess.Read);
            IExcelDataReader reader = ExcelReaderFactory.CreateBinaryReader(fs);
            reader.IsFirstRowAsColumnNames = true; // Treat first row as column names
            result = reader.AsDataSet(); // Load Excel data into DataSet
            // Populate the ComboBox with sheet names
            cboSheet.Items.Clear();
            // Add sheet to combobox
            foreach (DataTable dt in result.Tables)
                cboSheet.Items.Add(dt.TableName);
            reader.Close(); // Close the reader
        }
    }
}

This code allows the user to choose an Excel file through an OpenFileDialog, and it loads the data into a DataSet using the ExcelReaderFactory.

Now, handle the event when the user selects a sheet from the ComboBox.

Add the following code to the SelectedIndexChanged event of the ComboBox:

private void cboSheet_SelectedIndexChanged(object sender, EventArgs e)
{
    // Display the selected sheet in the DataGridView
    dataGridView.DataSource = result.Tables[cboSheet.SelectedIndex];
}

This will display the data from the selected Excel sheet in the DataGridView.

When you click the Open button, an OpenFileDialog will appear, allowing the user to select an Excel file. The ExcelDataReader library will read the file, and you can then choose which sheet to display from the ComboBox. The data from the selected sheet will be shown in the DataGridView.

VIDEO TUTORIAL