CODEWITHSUNDEEP
c#excelxlsxoledbconnection
Neil Humby
Neil Humby

Reputation: 273

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

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>();
            }

Upvotes: 2

Views: 1414

Answers (1)

Muhammad Gouda
Muhammad Gouda

Reputation: 869

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

  1. Add Microsoft Excel COM reference to your project

  2. Add the below line to your application

    using Excel = Microsoft.Office.Interop.Excel;

  3. 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

Upvotes: -1

Related Questions