How to Open and Read Excel Files in C#

Reading Excel files in a C# application is a common requirement in many projects, such as data analysis, reporting, and importing data. One of the most popular libraries for handling Excel files in C# is ExcelDataReader.

This lightweight library makes it easy to parse Excel files (.xls and .xlsx) into a DataSet or DataTable. In this article, we will explore how to read Excel files in C# and load their content into a DataTable.

How to Open and Read Excel Files in C#?

Open Visual Studio, then create a new Windows Forms application project.

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

To open and read Excel files in C# using ExcelDataReader, you'll first need to install the ExcelDataReader package via NuGet.

Run the following commands in the Package Manager Console or add the package via the NuGet Package Manager in Visual Studio:

Install-Package ExcelDataReader
Install-Package ExcelDataReader.DataSet

The ExcelDataReader library supports both .xls (Excel 97-2003 format) and .xlsx (Excel Workbook format) files. To enable better compatibility, also install the ExcelDataReader.DataSet package to work with DataSet or DataTable structures.

Once you have it installed, you can follow these steps.

How to open an Excel file in C#

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) use the ExcelDataReader library, and populate a ComboBox with the sheet names.

// c# excel read file
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)) // excel c# read file
            {
                 // 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 }
                    });
                    // excel to datatable c#
                    tableCollection = result.Tables;
                    cboSheet.Items.Clear();
                    foreach (DataTable table in tableCollection)
                        cboSheet.Items.Add(table.TableName);//add sheet to combobox
                }
            }
        }
    }
}

The OpenFileDialog allows the user to browse and select an Excel file. The filter ensures that only Excel files (.xls and .xlsx) appear in the file browser.

A FileStream object is used to open the selected file in read-only mode. This ensures the Excel file is not locked or altered during the reading process.

The ExcelReaderFactory.CreateReader method creates an IExcelDataReader instance that can read Excel file content.

Using the reader.AsDataSet method, the Excel data is converted into a DataSet object. The configuration specifies whether the first row of the Excel sheet should be used as column headers (UseHeaderRow = true).

The result.Tables collection contains the sheets from the Excel file as DataTable objects. Each sheet is added to the ComboBox (cboSheet) for selection.

Once you have read the Excel file, you can extract specific sheet data into a DataTable.

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

// c# read excel
private void cboSheet_SelectedIndexChanged(object sender, EventArgs e)
{
    if (cboSheet.SelectedItem != null)
    {
        DataTable dt = tableCollection[cboSheet.SelectedItem.ToString()];
        dataGridView1.DataSource = dt; // Display the data in a DataGridView
    }
}

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