How to Read Excel file (*.xls, *.xlsx) in C#

After you finish creating a new Windows Forms Application project, you need to open your form designer.

Next, Drag and drop the Label, TextBox, Button and Combobox controls from the Visual Studio Toolbox to your form designer.

You can design a simple user interface allows you to select the excel file (*.xls or *,xlsx), then open and read all sheet names in the excel file.

Each time you select a sheet name from the Combobox. We will get a list of columns corresponding to the sheet you have selected.

Finally, You need to bind data to the Combobox when you select column name from the Combobox as shown below.

After you complete the interface design, you need to

You need to install the ExcelDataReader and ExcelDataReader.DataSet packages using NuGet Package Manager.

Right-click on your project, then select Manage Nuget Packages. Search for "ExcelDataReader" and "ExcelDataReader.DataSet" and Install to your project.

You can also open the Package Manager Console from Visual Studio (Tools -> NuGet Package Manager -> Package Manager Console) and run the following commands:

Install-Package ExcelDataReader
Install-Package ExcelDataReader.DataSet

You should declare DataTableCollection and DataTable variables to get all sheet names, then retrieve data from the sheet name you selected and load it into the DataTable.

DataTableCollection dataTableCollection;
DataTable dt;

Adding a click event handler to the Browse button allows you to open and read excel (*.xls or *.xlsx) file.

private void btnBrowse_Click(object sender, EventArgs e)
{
    using (OpenFileDialog ofd = new OpenFileDialog() { Filter = "Excel 97-2003 Workbook|*.xls|Excel Workbook|*.xlsx" })
    {
        if (ofd.ShowDialog() == DialogResult.OK)
        {
            txtFilename.Text = ofd.FileName;
            // Use FileStream to read the Excel file
            using (var stream = File.Open(ofd.FileName, FileMode.Open, FileAccess.Read))
            {
                // Create a reader using ExcelReaderFactory
                using (IExcelDataReader reader = ExcelReaderFactory.CreateReader(stream))
                {
                    // Choose one of the ExcelDataReader.DataSet methods
                    DataSet result = reader.AsDataSet(new ExcelDataSetConfiguration()
                    {
                        // Use first row as column names
                        ConfigureDataTable = (_) => new ExcelDataTableConfiguration() { UseHeaderRow = true }
                    });
                    // Get the first DataTable from the result set
                    dataTableCollection = result.Tables;
                    cboData.DataSource = null;
                    cboSheet.Items.Clear();
                    cboSheet.Items.AddRange(dataTableCollection.Cast<DataTable>().Select(t => t.TableName).ToArray<string>());
                }
            }
        }
    }
}

Adding a SelectionChangeCommitted event handler to the Sheet combobox allows you to get all column names in datatable c#.

private void cboSheet_SelectionChangeCommitted(object sender, EventArgs e)
{
    //select column by sheet name
    dt = dataTableCollection[cboSheet.SelectedItem.ToString()];
    var columnNames = (from c in dt.Columns.Cast<DataColumn>()
                       select c.ColumnName).ToArray();
    cboColumn.Items.Clear();
    cboColumn.Items.AddRange(columnNames);
}

Adding a SelectionChangeCommitted event handler to the Column combobox allows you to get data from column in datatable c#.

private void cboColumn_SelectionChangeCommitted(object sender, EventArgs e)
{
    //select data by column name
    if (dt != null)
    {
        string columnName = cboColumn.SelectedItem.ToString();
        var data = dt.DefaultView.ToTable(false, columnName);
        cboData.DataSource = data;
        cboData.DisplayMember = columnName;
        cboData.ValueMember = columnName;
    }
}

ExcelDataReader provides a way to read Excel files without Excel installed on the machine.

ExcelDataReader.DataSet allows you to read Excel files into a DataSet, making it easier to work with data in a tabular format.

AsDataSet method reads the entire Excel file into a DataSet object, where each sheet in the Excel file becomes a DataTable within the DataSet.

ExcelDataSetConfiguration allows configuration of how the data should be read, such as whether to use the first row as headers (UseHeaderRow = true).

cboData.DataSource sets the Combobox's data source to the DataTable read from the Excel file, displaying the data in the UI.

Through this example, you can easily read and display data from Excel files (*.xls, *.xlsx) in your C# Windows Forms Application using ExcelDataReader.

VIDEO TUTORIAL