A Method to return Sheets Names in an EXCEL file using C# ADO.NET

A Method to return Sheets Names in an EXCEL file using C# ADO.NET

        public static List<string> GetSheetsNames(string path)
        {
            List<string> sheets = new List<string>();

            string connectionString = @”Provider=Microsoft.Jet.OLEDB.4.0;” +
                “Data Source=” + path + “;” +
                @”Extended Properties=”"Excel 8.0;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;
        }
Note that the schema has each sheet name ending with $. you must have the $ in the sheet name in order to read from or write to the excel using ADO.NET