How to find out Hidden Sheet Names in Excel

1
votes

How do I find out the hidden excel sheet name using ADO(OLEDB) in C#?

In My Excel workbook there are a lot of sheets. Only one Excel sheet is in hidden mode. I need to find out the names of hidden sheets. My code finds both hidden and visible sheets.

This is my code to find excel sheet names for all sheets. Is it possible/can any one tell me how to find out hidden excel sheet names without using Interop services in C#?

connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelFile + ";Extended Properties=\"Excel 12.0;;IMEX=1;HDR=YES\"";
query = String.Format("SELECT * FROM [{0}", fileName + "]");
OleDbCommand objCmd = new OleDbCommand(query, objCon);
OleDbDataAdapter adap = new OleDbDataAdapter(objCmd);
adap.FillSchema(dtExcelSchema, SchemaType.Mapped);
adap.Fill(dsExecelData);
2
c#asp.net.netvb.netexcel
As a side note, when the connection is closed, each call to .Fill will open and close the connection. To load the data and schema in one .Fill, something like: adap.MissingSchemaAction = MissingSchemaAction.AddWithKey; adap.Fill(dsExecelData); – Slai

2 Answers

0
votes

If the sheet name ends with an underscore (_) then its hidden. Regular sheet names will end with a dollar sign ($).

1
votes

This post works for me

using Excel = Microsoft.Office.Interop.Excel;

...

Excel.Application xlAppSource = null;
Excel.Workbook xlWorkBookSource = null;


// workbook
xlWorkBookSource = xlAppSource.Workbooks.Open(xlsFilePath, 0, false, 5, null, null, false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, null, true, false, 0, true, false, false);

// here is your list
List<int> list = getHiddenSheetsIndexes(xlWorkBookSource.Worksheets);

...

private List<int> getHiddenSheetsIndexes(Excel.Sheets sheets) 
{
    // return
    List<int> indexes = new List<int>();

    int index = 0;

    foreach (Excel.Worksheet sheet in sheets)
    {
        index++;

        if (sheet.Visible == Microsoft.Office.Interop.Excel.XlSheetVisibility.xlSheetHidden)
            indexes.Add(index);

    }

    // return
    return indexes;
}