Get name of worksheets containing in an excel file with c#

Question

I have a problem regarding getting the name of the spreadsheets that have to in an Excel file. I need to read a file that contains several spreadsheets, and would like to get the name of all and list in one ComboBox.

Example: In this case you would need a method to read the Excel file, and return to the user in a Combobox with these 3 items (FINANCIAL, BEADS And PRODUCTS).

Answer 1

In that article has an example that demonstrates this using the OLEDB. The function returns a array containing the sheet names in the parameter excelFile you will point to the Excel file.

// Utilize o namespace  System.Data.OleDb;
private String[] GetExcelSheetNames(string excelFile)
{
  OleDbConnection objConn = null;
  System.Data.DataTable dt = null;

  try
  {
    // Configura a Connection String
    String connString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 Xml;HDR=No;IMEX=1\";", excelFile);

    // Cria o objeto de conexão usando a connection string
    objConn = new OleDbConnection(connString);

    // Abre a conexão com o banco de dados
    objConn.Open();
    dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

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

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

    // Adiciona os nomes na array
    foreach(DataRow row in dt.Rows)
    {
      excelSheets[i] = row["TABLE_NAME"].ToString();
      i++;
    }

    // Loop através de todas as folhas se você quiser também..
    for(int j=0; j < excelSheets.Length; j++)
    {
      // Consultar cada folha de excel
    }

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

Note: If you are using the Excel 2003 changes the string connecting to:

string connString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0; data source={0}; Extended Properties=\"Excel 8.0;HDR=No;IMEX=1\";", excelFile);