Windows Forms: 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 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 right-click on your project, then select Manage Nuget Packages.

Next, Search and Install "ExcelDataReader" and "ExcelDataReader.DataSet" to your project.

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;
            using (var stream = File.Open(ofd.FileName, FileMode.Open, FileAccess.Read))
            {
                using (IExcelDataReader reader = ExcelReaderFactory.CreateReader(stream))
                {
                    DataSet result = reader.AsDataSet(new ExcelDataSetConfiguration()
                    {
                        ConfigureDataTable = (_) => new ExcelDataTableConfiguration() { UseHeaderRow = true }
                    });
                    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;
    }
}

VIDEO TUTORIAL