How to check the name of the Excel C #

Navigation
3

How to check the name of the "Sheet" of the Excel worksheet before performing the% 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();
}
    
asked by anonymous 15.08.2016 / 18:32

2 answers

2

You can use this to get the tab names of an .xlsx file.

Scroll 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

    
15.08.2016 / 19:12
3

You can use the GetOleDbSchemaTable , as mentioned in this answer . 

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);
}
    
15.08.2016 / 19:12
Hibernate, force table name in low case Compare rows between rows between two data frames?