How to verify which is the name of the spreadsheet tab Excel C#

Question

How to verify what is the name of the "Tab" (Sheet$) of the Excel spreadsheet before performing the import pro SQL ?

My code so far:

 protected void btnProcessar_Click(object sender, EventArgs e)
    {
        string path = Server.MapPath(".") + "\\" + FileUpload.FileName;
        this.ImportarArquivoExcel(path);
    }

private void conexaoExcel(string path)
    {
        connectionExcel = string.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0 Xml;HDR=YES;""", FilePath);
        excelConn = new OleDbConnection(connectionExcel);
    }

    private void conexaoSQL()
    {
        connectionSQL = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
        sqlConn = new SqlConnection(connectionSQL);
    }

 public void ImportarArquivoExcel(string path)
    {
        conexaoExcel(path);

        query = string.Format("Select * FROM [{0}]", "DailySalesDetails$");

        OleDbCommand Ecom = new OleDbCommand(query, excelConn);
        excelConn.Open();

        DataSet ds = new DataSet();
        OleDbDataAdapter oda = new OleDbDataAdapter(query, excelConn);
        excelConn.Close();
        oda.Fill(ds);
        DataTable Exceldt = ds.Tables[0];
        conexaoSQL();
}

Answer 1

You can use the method GetOleDbSchemaTable, as mentioned in that reply.

public static List <string> ListSheetInExcel(string filePath) {
    OleDbConnectionStringBuilder sbConnection = new OleDbConnectionStringBuilder();
    sbConnection.DataSource = filePath;
    string strExtendedProperties = string.Empty;

    // Verifica a extensão do arquivo e aplica a Connection String apropriada
    if (Path.GetExtension(filePath).Equals(".xls")) // Excel 97-03
    {
        sbConnection.Provider = "Microsoft.Jet.OLEDB.4.0";
        strExtendedProperties = "Excel 8.0;HDR=Yes;IMEX=1";
    } 
    else if (Path.GetExtension(filePath).Equals(".xlsx")) // Excel 2007
    {
        sbConnection.Provider = "Microsoft.ACE.OLEDB.12.0";
        strExtendedProperties = "Excel 12.0;HDR=Yes;IMEX=1";
    }

    sbConnection.Add("Extended Properties", strExtendedProperties);
    List <string> listSheet = new List <string>();

    using(OleDbConnection conn = new OleDbConnection(sbConnection.ToString())) {
        conn.Open();
        System.Data.DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

        foreach(DataRow row in dt.Rows) {
            if (row["TABLE_NAME"].ToString().Contains("$")) {
                listSheet.Add(row["TABLE_NAME"].ToString());
            }
        }
    }    
    return listSheet;
}

To use, do so:

List<string> listSheet = ListSheetInExcel(@"C:\bar\foo.xlsx");

foreach (string sheet in listSheet)
{
     Console.WriteLine(sheet);
}

Answer 2

You can use this to get the names from the tabs of a. xlsx file.

Go through all the names to read the tabs one by one.

foreach (DataRow dr in ds.Rows)
{
   string sht = dr[2].ToString().Replace("'", "");
   OleDbDataAdapter dataAdapter = new OleDbDataAdapter("select * from [" + sht + "]", connection);
}

See more details. Here