How to get names of all sheets in excel

Question

I want to create a method to get names of all sheets in a workbook. My workbook has 7 sheets. If I want to read and save names of sheets to the variable excelSheets, I receive 9 names, where two names response to non-exists sheets ("lists$" and "TYPAB").

I don't understand where is the problem? How can I get names only the existing sheets?

public List<string> NamesOfSheets(string filename)
    {
        string con = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filename + ";Extended Properties='Excel 12.0;HDR=Yes;'";
        using (OleDbConnection connection = new OleDbConnection(con))
        {
            connection.Open();

            List<string> excelSheets;

            try
            {
                DataTable dt = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

                excelSheets = dt.Rows.Cast<DataRow>()
                    .Select(i => i["TABLE_NAME"].ToString()).ToList();

                return excelSheets;
            }
            catch (Exception)
            {
                throw new Exception("Failed to get SheetName");

            }
        }
    }

Answer 1

Oscar, thanks for your help, but office interlop doesn't solve my problem. I found that "lists$" is hidden sheet, so only name TYPAB doesn't respond to any existing sheet.

So I added clause where and problem is solved. :)

public List<string> NamesOfSheets(string filename)
    {
        string con = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filename + ";Extended Properties='Excel 12.0;HDR=Yes;'";

        List<string> excelSheets;

        using (OleDbConnection connection = new OleDbConnection(con))
        {
            connection.Open();

            try
            {
                DataTable dt = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

                excelSheets = dt.Rows.Cast<DataRow>()
                    .Where(i => i["TABLE_NAME"].ToString().EndsWith("$") || i["TABLE_NAME"].ToString().EndsWith("$'"))
                    .Select(i => i["TABLE_NAME"].ToString()).ToList();

                return excelSheets;
            }
            catch (Exception)
            {
                throw new Exception("Failed to get SheetName");
            }
        }
    }

Answer 2

Why not use Office Interop for this?

foreach (Excel.Worksheet displayWorksheet in Globals.ThisWorkbook.Worksheets)
{
    Debug.WriteLine(displayWorksheet.Name);
}

https://msdn.microsoft.com/en-us/library/59dhz064.aspx