Excel Interop Worksheets list has extra sheets, UsedRange has no data

Question

I'm using the Excel Office Interop from C#. (In VS 2013). I'm working with an Excel workbook that was created in the version of Excel that came with Office 2010 Professional Plus. The workbook has an .xlsx extension.

When I open the workbook in Excel, it has 4 sheets visible, but when I open it using the interop, the Worksheets collection contains sheets with a bunch of other names, like these:

Icons 
International URLs 
International Settings 
DropdownSizer
International Translastions 
Data Validation 
International Data

Then after that list, the sheets that are visible in the tabs of the workbook finally appear. But when I try to get at the UsedRange for any of the sheets, it has no data in it.

My code works fine for other workbooks created from the same Office/Excel installation, which only have single sheets.

Here are some mashed together snippets from my code with comments, null checks, debugging code, etc. stripped out:

private Excel.Application xlApp;
private Excel.Workbook xlWorkbook;
private Excel.Worksheet xlWorksheet;
public Excel.Range usedRange;   

public void Open(string pathname, int sheetNum = 1) {
    xlApp = new Excel.Application();
    xlWorkbook = xlApp.Workbooks.Open(pathname, 0, true, 5, "", "", true, Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);        
    xlWorksheet = (Excel.Worksheet)xlWorkbook.Sheets.get_Item(sheetNum);
    usedRange = xlWorksheet.UsedRange;
}

public string[] GetSheetNames() {
    var sheetNames = new List<string>();
    foreach (Excel.Worksheet worksheet in xlApp.Worksheets) {
        sheetNames.Add(worksheet.Name);
    }
    return sheetNames.ToArray();
}

public void SetCurrentSheet(int sheetNum) {
    usedRange = xlWorkbook.Worksheets[sheetNum].UsedRange;
}

I've tried using the xlWorkbook.Worksheets instead of xlApp.Worksheets. No difference.

How can I get just the sheets that show up in the tabs when I open the workbook in Excel?

After doing that, how can I get at the data in each sheet?

Answer 1

If you need to filter your sheetNames list to only include visible sheets, you can try the following:

foreach (Excel.Worksheet worksheet in xlApp.Worksheets)
{
    if (worksheet.Visible == XlSheetVisibility.xlSheetVisible) 
    {
        sheetNames.Add(worksheet.Name);
    }
}

I've tried using the xlWorkbook.Worksheets instead of xlApp.Worksheets. No difference.

The documentation says that the Application.Worksheets property returns a collection of all the worksheets in the active workbook. Since the currently active workbook is the same object as the one referenced by your xlWorkbook variable, there can't be any difference.