How to Read Excel file in C# using OleDb

How to read excel file in c# windows application

Open your Visual Studio, then create a new Windows Forms Application project, then open your form designer.

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

Use the OleDbConnection class to connect to the Excel file. You'll need to specify the connection string, which includes information about the Excel file.

Once connected, you can use SQL queries to retrieve data from Excel sheets. You'll use the OleDbCommand class to execute SQL commands.

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.

Here's a sample code demonstrating how to read an Excel file using OleDb in C#

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