OleDbConnection.GetOleDbSchemaTable for .xlsx doesn't identify hidden worksheets

Question

I'm using the OleDbConnection to retrieve data from a .xlsx workbook. When I retrieve a list of worksheets it doesn't identify if any of them have been hidden. This used to be the case by ending it's name with an underscore eg "Sheet1$_". Do you know how to tell if this is hidden now?

                using (var connection =
                new OleDbConnection(string.Concat("Provider=Microsoft.ACE.OLEDB.12.0;Mode=Read;Data Source=",
                    fileName,
                    ";Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\"")))
            using (var command = new OleDbCommand("", connection))
            {
                connection.Open();
                var listedSheets = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,
                    new object[] {null, null, null, "Table"});

                if (listedSheets != null)
                {
                    var sheetNames = new List<string>(listedSheets.Rows.Count);

                    foreach (DataRow row in listedSheets.Rows)
                    {
                        sheetNames.Add(row[2].ToString());
                    }

                    return sheetNames;
                }

                return new List<string>();
            }

I am confused, do you get the hidden sheets but you want your code to flag them as hidden, or you don't get them at all ? — Muhammad Gouda, Aug 22 '16 at 13:44
All the sheets are returned with no indication which ones are hidden. — Neil Humby, Aug 24 '16 at 07:43

score -2 · Answer 1 · edited May 23 '17 at 12:15

I think GetOleDbSchemaTable is not the best option here

Because it returns DataTable object regardless the datasource that might be Excel, Access, or something else. So, it is not aware of any of Excel sheet properties

Rather, you can use Excel com components to have full control on your Excel file

Steps

Add Microsoft Excel COM reference to your project
Add the below line to your application

using Excel = Microsoft.Office.Interop.Excel;
Read the Visible property of the loaded sheet

Sample Code

private static Excel.Workbook MyBook = null;
private static Excel.Application MyApp = null;
private static Excel.Worksheet MySheet = null;
static void ReadExcel()
{
    MyApp = new Excel.Application();
    MyApp.Visible = false;
    MyBook = MyApp.Workbooks.Open("C:\\test.xlsx");
    MySheet = (Excel.Worksheet)MyBook.Sheets[1];

    if (MySheet.Visible == Excel.XlSheetVisibility.xlSheetHidden)
    {
        //handle hidden sheet here
    }
}

Notes:

Selecting the correct COM reference may depend on your Visual Studio version Check this SO question

Also, This article is a good reference

ًWhen you down vote, you have to justify in a comment to improve the answer — Muhammad Gouda, Nov 12 '17 at 11:57

OleDbConnection.GetOleDbSchemaTable for .xlsx doesn't identify hidden worksheets

1 Answers1