How to iterate through Excel worksheets in C#?

▲
-1
▼

I have a workbook with multiple sheets and I was trying to iterate through them but it's causing me problems. The code below throws the error:

Microsoft.Office.Tools.Excel.Worksheet'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{297DC8D9-EABD-45A1-BDEF-68AB67E5C3C3}' failed due to the following error: No such interface supported (Exception from HRESULT: 0x80004002 (E_NOINTERFACE)).

but I don't really understand what that even means.

The error occurs when trying to assign the sheet variable the first sheet in my sheets variable which is just a workbook

public List<Worksheet> GetAllDatasheets()
    {
        var datasheets = new List<Worksheet>();
        var sheets = _book;
        foreach (Worksheet sheet in sheets.Worksheets)
        {
            if (sheet.Name.StartsWith("$"))
                datasheets.Add(sheet);
        }
        return datasheets;
    }

EDIT: Here's my full code:

using Workbook = Microsoft.Office.Tools.Excel.Workbook;
using Worksheet = Microsoft.Office.Tools.Excel.Worksheet;

private Workbook _book;

    public ExcelObjectDAL()
    {
        _book = Globals.Factory.GetVstoObject(Globals.ThisAddIn.Application.ActiveWorkbook);
    }

public List<Worksheet> GetAllDatasheets()
    {
        var datasheets = new List<Worksheet>();
        var sheets = _book;
        foreach (var sheet in sheets.Worksheets)
        {
            var actualSheet = (Worksheet) sheet;
            if (actualSheet.Name.StartsWith("$"))
                datasheets.Add(actualSheet);
        }
        return datasheets;
    }
c#
excel
vsto
asked on Stack Overflow Jan 8, 2020 by NotTheSmartestInTheRoom • edited Jan 8, 2020 by NotTheSmartestInTheRoom

1 Answer

▲
-1
▼

I think this is what you are looking for:

 public List<string> GetAllDatasheets()
        {
            Excel.Application excelApp = new Excel.Application();
            Excel.Workbook workBook = excelApp.Workbooks.Open(filePath);

            var datasheets = new List<string>();
            foreach (Excel.Worksheet sheet in workBook.Worksheets)
            {
                if (sheet.Name.StartsWith("$"))
                    datasheets.Add(sheet.Name);
            }

            excelApp.Quit();
            return datasheets;
        }

If you have the worksheet name you know enough you don't have to make the list with type Worksheet

answered on Stack Overflow Jan 8, 2020 by Joeri E

User contributions licensed under CC BY-SA 3.0