Get name of worksheets that contain an excel file with C #

Question

Get name of worksheets that contain an excel file with C #

Navigation

#1 by (1 votes)

0

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

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

c# excel

asked by anonymous 18.01.2015 / 13:30

1 answer

Problem getting very large JSON in WebService RestFul Problem trying to insert into mysql using PDO

score 1 · Accepted Answer

In this article has one example that demonstrates this by using the OLEDB . The function returns an array containing the names of the sheets, 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 Excel 2003 change the connection string to:

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