Open XML SDK 2.0 to get access to excel 2010 worksheet by name

Question

I have an Excel 2010 spreadsheet that has 3 worksheets named Sheet1, Sheet2 and Sheet3.

I'm trying to get a reference to a worksheet by name.

I'm using the code:

using (SpreadsheetDocument myWorkbook = SpreadsheetDocument.Open(FileName, true))
{
    //Access the main Workbook part, which contains all references 
    WorkbookPart workbookPart = myWorkbook.WorkbookPart;

    WorksheetPart worksheetPart = workbookPart.WorksheetParts.Last(); 

    // this gives me Sheet1
    SheetData sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>();
}

I am trying to get a reference to Sheet2, but I cannot find a way to do this.

I'm getting closer, but I'm not there yet:

var x = workbookPart.Workbook.Sheets.Where(s=> s.GetAttribute("name", "").Value == "Sheet2").FirstOrDefault();

That gets me a reference to the sheet, but not to the data on the sheet

Thanks

Answer 1

What you really want is the WorksheetPart which is what contains the SheetData that you are looking for. Grabbing the Sheets under the Workbook will only give you certain metadata about the worksheets. Here is an example on how to grab that WorksheetPart (feel free to add error checking as you see fit as I assume the sheetName already exists by calling First and not FirstOrDefault)

public WorksheetPart GetWorksheetPart(WorkbookPart workbookPart, string sheetName)
{
    string relId = workbookPart.Workbook.Descendants<Sheet>().First(s => sheetName.Equals(s.Name)).Id;
    return (WorksheetPart)workbookPart.GetPartById(relId);
}

Then just use your code above to grab the correct SheetData reference and you will be able to find the data you want from there.