Get Excel Worksheets Name to C#

Question

I am making and App that reads data from excel workbooks as following:

The excel contains data about Bill of Quantity (BOQ) where I have about 4 or 5 columns that contains data but there are divisions in the BOQ table where there are Civil, Architecture, Mechanical, Electrical , ... , etc. Each of this division is in separate worksheet.

My app has a DataGridView and a Combobox where the combobox will be populated by the name of the worksheets (Divisions) once a division is selected from the combobox the DataGridView is regenerated by the data in this division.

I have already written a code that puts data in a specific worksheet in the DataGridView

private void button1_Click(object sender, EventArgs e)
    {
        String name = "FF";
        String constr = "Provider=Microsoft.ACE.OLEDB.12.0;" +
                        "Data Source=C:\\Book1.xlsx;" + 
                        "Extended Properties='Excel 12.0 XML;" +
                        "HDR=YES;';";

        OleDbConnection con = new OleDbConnection(constr);
        OleDbCommand oConn = new OleDbCommand("Select * From [" + name + "$]", con);
        con.Open();

        OleDbDataAdapter sda = new OleDbDataAdapter(oConn);
        DataTable data = new DataTable();
        sda.Fill(data);

        dataGridView1.DataSource = data;
        dataGridView1.Columns[1].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
        foreach (DataGridViewColumn dgvc in dataGridView1.Columns)
        {
            dgvc.SortMode = DataGridViewColumnSortMode.NotSortable;
        }


    }

But I can't populate the combobox items with worksheets name.

EDIT

Using this code as suggested:

DataTable schemaTable = connection.GetOleDbSchemaTable(
    OleDbSchemaGuid.Tables,
    new object[] { null, null, null, "TABLE" });

Returns ambiguous value as in the image below:

[

In excel they look like this:

[

My results is by using this code:

    private void Form1_Load(object sender, EventArgs e)
    {

        foreach (string s in GetExcelSheetNames("C:\\Book1.xlsx"))
        {
            cboSheetName.Items.Add(s);
        }
    }

    /// <summary>
    /// This method retrieves the excel sheet names from 
    /// an excel workbook.
    /// </summary>
    /// <param name="excelFile">The excel file.</param>
    /// <returns>String[]</returns>
    private String[] GetExcelSheetNames(string excelFile)
    {
        OleDbConnection objConn = null;
        System.Data.DataTable dt = null;

        try
        {
            // Connection String. Change the excel file to the file you will search.
            String connString = "Provider=Microsoft.ACE.OLEDB.12.0;" +
                                "Data Source=" + excelFile + ";" +
                                "Extended Properties='Excel 12.0 XML;" +
                                "HDR=YES;';";

            // Create connection object by using the preceding connection string.
            objConn = new OleDbConnection(connString);

            // Open connection with the database.
            objConn.Open();

            // Get the data table containg the schema guid.
            //dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

            if (dt == null)
            {
                return null;
            }

            String[] excelSheets = new String[dt.Rows.Count];
            int i = 0;

            // Add the sheet name to the string array.
            foreach (DataRow row in dt.Rows)
            {
                excelSheets[i] = row["TABLE_NAME"].ToString();
                i++;
            }

            // Loop through all of the sheets if you want too...
            for (int j = 0; j < excelSheets.Length; j++)
            {
                // Query each excel sheet.
            }

            return excelSheets;
        }
        catch (Exception ex)
        {
            return null;
        }
        finally
        {
            // Clean up.
            if (objConn != null)
            {
                objConn.Close();
                objConn.Dispose();
            }
            if (dt != null)
            {
                dt.Dispose();
            }
        }
    }

Answer 1

Why not use the Com object?

So here is what I do to get the names of the sheets in excel:

List<string> workSheets = new List<string>();

if(xlsApp==null)
   xlsApp = new Microsoft.Office.Interop.Excel.ApplicationClass();

Workbook book = xlsApp.Workbooks.Open(file.FullName, 0, true, 5, "", "", true, XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
foreach (Worksheet sheet in book.Worksheets)
{
    workSheets.Add(sheet.Name);
}
xlsApp.Workbooks.Close();
xlsApp.Quit();