How to get excel sheet in C#

How to get excel sheet in C# using OleDbConnection?

You can create a GetSchema method to get the excel sheet in c#.

public DataTable GetSchema(string filePath)
{
    DataTable dt = new DataTable();
    dt.Columns.Add("FullSheetName", typeof(string));
    dt.Columns.Add("SheetName", typeof(string));
    using (OleDbConnection oleConn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + filePath + ";Extended Properties=" + Convert.ToChar(34).ToString() + "Excel 8.0;Imex=2;HDR=yes" + Convert.ToChar(34).ToString()))
    {
        oleConn.Open();
        // Get the sheet names
        DataTable dtSchema = oleConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
        oleConn.Close();
        if (dtSchema.Rows.Count != 0)
        {
            foreach (DataRow dr in dtSchema.Rows)
            {
                DataRow drNew = dt.NewRow();
                drNew["FullSheetName"] = dr["TABLE_NAME"].ToString();
                drNew["SheetName"] = drNew["FullSheetName"].ToString().Replace("$", string.Empty);
                dt.Rows.Add(drNew);
            }
        }
    }
    return dt;
}

For .xls, use Excel 8.0; and for .xlsx, use Excel 12.0 Xml;.

The OleDbConnection object is used to open a connection to the Excel file.

The GetOleDbSchemaTable method retrieves the names of the sheets in the Excel file.

How to read excel file in C# using OleDbConnection?

To get content of excel sheet you can create a GetSheetContent method as shown below.

public DataTable GetSheetContent(string filePath, string fullSheetName)
{
    DataTable dt = new DataTable();
    using (OleDbConnection oleConn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + filePath + ";Extended Properties=" + Convert.ToChar(34).ToString() + "Excel 8.0;Imex=2;HDR=yes" + Convert.ToChar(34).ToString()))
    {
        OleDbDataAdapter oleDb = new OleDbDataAdapter("select * from [" + fullSheetName + "]", oleConn);
        oleDb.Fill(dt);
    }
    return dt;
}

The statement suggests that you can retrieve all data from a specific sheet by using a SQL query.

The results are filled into a DataTable using OleDbDataAdapter.

Make sure your file path is correct and that the necessary drivers are installed.