Windows Forms: Read Excel file in C# using OleDb

If you want to read both XLS and XLSX format, then this article will really help you read an excel file using OLEDB in C# .NET Windows Forms Application.

How to read excel file in c# windows application

Creating a new Windows Forms Application project, then open your form designer.

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

You can design a simple user interface as shown below that allows you to open an excel file, then read all data in excel sheet based on the sheet name you selected.

C# Get all sheets in excel

Creating an AppHelper class allows you to get all sheet name in excel file.

public class AppHelper
{
    private OleDbConnection cn = null;
    public AppHelper(string connectionString) { cn = new OleDbConnection(connectionString); }

    private void OpenConnection()
    {
        if (cn.State == ConnectionState.Closed)
            cn.Open();
    }

    private void CloseConnection()
    {
        if (cn.State == ConnectionState.Open)
            cn.Close();
    }

    public DataTable GetOleDbSchemaTable()
    {
        DataTable dt = new DataTable();
        try
        {
            OpenConnection();
            dt = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
        }
        catch { return null; }
        finally
        {
            CloseConnection();
        }
        return dt;
    }
}

C# Get all sheet names in excel

Adding a click event handler that allows you to open an excel file, then get all sheet names and add the sheet names to the Combobox control.

private void btnBrowse_Click(object sender, EventArgs e)
{
    using (OpenFileDialog ofd = new OpenFileDialog { Filter = "Excel 97-2003|*.xls|Excel Workbook|*.xlsx", Multiselect = false, ValidateNames = true })
    {
        if (ofd.ShowDialog() == DialogResult.OK)
        {
            cboSheet.Items.Clear();
            txtPath.Text = ofd.FileName;
            AppHelper h = new AppHelper(String.Format("Provider=Microsoft.Jet.OLEDB.4.0; Data Source={0};Extended Properties={1}Excel 8.0;Imex=2;HDR=yes{1}", ofd.FileName, Convert.ToChar(34)));
            DataTable sdt = h.GetOleDbSchemaTable();
            if (sdt.Rows.Count < 1)
                return;
            try
            {
                foreach (DataRow dr in sdt.Rows)
                {
                    if (!dr["TABLE_NAME"].ToString().EndsWith("_"))
                        cboSheet.Items.Add(dr["TABLE_NAME"].ToString());
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Message", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }
    }
}

As you can see, You can easily get excel sheet names in c# using oledb.

Oledb connection string for excel 2013 in c#

$"Provider=Microsoft.Jet.OLEDB.4.0; Data Source={0};Extended Properties={ofd.FileName}Excel 8.0;Imex=2;HDR=yes{Convert.ToChar(34)}"

To play the demo, Make sure that the Microsoft Access Database Engine needs to be installed.

C# Read excel sheet data

Adding a SelectionChangeCommited event handler to the Combobox allows you to get data from excel sheet when selecting sheet name.

private void cboSheet_SelectionChangeCommitted(object sender, EventArgs e)
{
    using (OleDbConnection cn = new OleDbConnection(string.Format(@"Provider=Microsoft.Jet.OLEDB.4.0; Data Source={0};Extended Properties={1}Excel 8.0;Imex=2;HDR=yes{1}", txtPath.Text, Convert.ToChar(34))))
    {
        using (OleDbDataAdapter adapter = new OleDbDataAdapter("select * from [" + cboSheet.SelectedItem.ToString() + "]", cn))
        {
            try
            {
                using (DataTable dt = new DataTable())
                {
                    adapter.Fill(dt);
                    dataGridView.DataSource = dt;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Message", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }
    }
}

Finally, Add a datasource to your DataGridView to display data read from excel file using OLEDB Data Provider in C#.