Click here to Skip to main content
15,887,214 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
im using this method to get Excel's sheets name , but some how it gives me wrong result .
some times strange names which there are not sheet with the same name .
some times gives me a sheet name twice .
whats wrong with it ?
do u have any other idea ?

thanks for your help .



public static List<string> GetSheetsNames(string path, ExcelVersion excelversion)
       {
           string connectionString;
           List<string> sheets = new List<string>();
           if (excelversion == ExcelVersion.Excel2003)
           {
               connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\";";
           }
           else
           {
               connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES\";";

           }
           DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.OleDb");
           DbConnection connection = factory.CreateConnection();
           connection.ConnectionString = connectionString;
           connection.Open();
           DataTable tbl = connection.GetSchema("Tables");
           connection.Close();
           foreach (DataRow row in tbl.Rows)
           {
               string sheetName = (string)row["TABLE_NAME"];
               if (sheetName.EndsWith("$"))
               {
                   sheetName = sheetName.Substring(0, sheetName.Length - 1);
               }
               sheets.Add(sheetName);
           }
           return sheets;
       }
Posted
Comments
dan!sh 10-Aug-11 3:22am    
That works fine for me. Can you provide any scenario where this fails?
Christ_88 10-Aug-11 3:24am    
how can i send u an Excel file to check it ? can i attach a file in this site ?
Christ_88 10-Aug-11 3:36am    

1 solution

I think these are some hidden sheets (Dont know why) which have "_" at the end of their name.
You can filter out sheets with "_".
Hope this will help...
 
Share this answer
 
Comments
Christ_88 10-Aug-11 4:31am    
thanks its a good idea , but lets assume that my client set a sheet name which ended with "_" what would happen to it , it will ignore , i think this can be a final solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900