title: Get Excel Worksheets Name to C#
body: <p>I am making and App that reads data from excel workbooks as following:</p>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>I have already written a code that puts data in a specific worksheet in the DataGridView</p>
  
  <pre><code>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;
          }
  
  
      }
  </code></pre>
  
  <p>But I can't populate the combobox items with worksheets name.</p>
  
  <p>EDIT</p>
  
  <p>Using this code as suggested:</p>
  
  <pre><code>DataTable schemaTable = connection.GetOleDbSchemaTable(
      OleDbSchemaGuid.Tables,
      new object[] { null, null, null, "TABLE" });
  </code></pre>
  
  <p>Returns ambiguous value as in the image below:</p>
  
  <p>[<img src="https://i.stack.imgur.com/s42xO.png" alt="Combobox Result[2]"></p>
  
  <p>In excel they look like this:</p>
  
  <p>[<img src="https://i.stack.imgur.com/lQBPo.png" alt="[1]"></p>
  
  <p>My results is by using this code:</p>
  
  <pre><code>    private void Form1_Load(object sender, EventArgs e)
      {
  
          foreach (string s in GetExcelSheetNames("C:\\Book1.xlsx"))
          {
              cboSheetName.Items.Add(s);
          }
      }
  
      /// &lt;summary&gt;
      /// This method retrieves the excel sheet names from 
      /// an excel workbook.
      /// &lt;/summary&gt;
      /// &lt;param name="excelFile"&gt;The excel file.&lt;/param&gt;
      /// &lt;returns&gt;String[]&lt;/returns&gt;
      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 &lt; 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();
              }
          }
      }
  </code></pre>