How to Open and Read Excel Files in C#

Creating a new windows forms application project, then open your form designer. You can drag TextBox, Label, Button, Combobox and DataGridView from the visual studio toolbox to your winform.

How to Open and Read Excel Files in C#

To open and read Excel files in C# using ExcelDataReader, you'll first need to install the ExcelDataReader package via NuGet. Once you have it installed, you can follow these steps

How to open an Excel file in C#

You can install ExcelDataReader, ExcelDataReader.DataSet to your project via NuGet Package Manager Console by running the following command.

Install-Package ExcelDataReader
Install-Package ExcelDataReader.DataSet

- ExcelDataReader: It's a lightweight and fast library written in C# for reading Microsoft Excel files (2.0-2007).

- ExcelDataReader.DataSet: This is an extension for reading Microsoft Excel files into System.Data.DataSet.

Next, Add the click event handler to the Browse button allows you to select the excel file, then you can open and read an Excel file (.xls or .xlsx) using the following code snippet:

private void BtnBrowse_Click(object sender, EventArgs e)
{
    using (OpenFileDialog openFileDialog = new OpenFileDialog() { Filter = "Excel 97-2003 Workbook|*.xls|Excel Workbook|*.xlsx" })
    {
        if (openFileDialog.ShowDialog() == DialogResult.OK)
        {
            txtFilename.Text = openFileDialog.FileName;
            // Use FileStream to open the Excel file in read-only mode
            using (var stream = File.Open(openFileDialog.FileName, FileMode.Open, FileAccess.Read))
            {
                 // Create an ExcelDataReader object
                using (IExcelDataReader reader = ExcelReaderFactory.CreateReader(stream))
                {
                    // Use DataSet to hold the result of the Excel file
                    DataSet result = reader.AsDataSet(new ExcelDataSetConfiguration()
                    {
                        ConfigureDataTable = (_) => new ExcelDataTableConfiguration() { UseHeaderRow = true }
                    });
                    tableCollection = result.Tables;
                    cboSheet.Items.Clear();
                    foreach (DataTable table in tableCollection)
                        cboSheet.Items.Add(table.TableName);//add sheet to combobox
                }
            }
        }
    }
}

Using ExcelReaderFactory.CreateReader method allows you to easily read excel 97-2003 or excel workbook in c#.

Finally, Add the SelectedIndexChanged event handler to the Combobox allows you to select the sheet name, then load data from table into the DataGridView.

DataTable dt = tableCollection[cboSheet.SelectedItem.ToString()];
dataGridView1.DataSource = dt;

Through the c# example above you've learned how to read and import excel file to DataGridView in C# using ExcelDataReader and ExcelDataReader.DataSet library.

VIDEO TUTORIAL