如何在Excel中找出隐藏的工作表名称

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

如何在C#中使用ADO(OLEDB)找出隐藏的Excel工作表名称?

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.

在我的Excel工作簿中有很多工作表。只有一个Excel工作表处于隐藏模式。我需要找出隐藏床单的名称。我的代码找到隐藏和可见的工作表。

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#?

这是我查找所有工作表的Excel工作表名称的代码。是否有可能/任何人都可以告诉我如何在不使用C#中的Interop服务的情况下找出隐藏的Excel工作表名称?

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 个解决方案

#1

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

如果工作表名称以下划线(_)结尾,则其隐藏。常规工作表名称将以美元符号($)结尾。

#2

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

#1