Windows Forms: Read Excel file in C# using OleDb

This post shows you How to Read Excel file in C# using oledb (without Interop) and How to get excel sheet names in c# using oledb provider for excel.

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.

read excel file in c# using oledb

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#.