How can I get the Worksheetpart from name or sheet ID in OpenXML?

Question

The following creates an XLSX, adds two worksheets with some data. I then want to be able to get the spreadsheet later based on name (or preferably the id) so I can add/modify the sheets at a later point in time. I'm stuck on how to get the sheet again where the code is incomplete below.

    Sub Main()

    Using doc As SpreadsheetDocument = SpreadsheetDocument.Create(System.IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "c:\temp\fubar.xlsx"), SpreadsheetDocumentType.Workbook)
        Dim currSheet As WorksheetPart

        ' create the workbook
        doc.AddWorkbookPart()
        doc.WorkbookPart.Workbook = New Workbook()
        doc.WorkbookPart.Workbook.AppendChild(New Sheets())

        currSheet = InsertWorksheet(doc.WorkbookPart, "First")
        currSheet.Worksheet.First().AppendChild(New Row())
        currSheet.Worksheet.First().First().AppendChild(New Cell() With { _
         .CellValue = New CellValue("1") _
        })

        currSheet = InsertWorksheet(doc.WorkbookPart, "Second")
        currSheet.Worksheet.First().AppendChild(New Row())
        currSheet.Worksheet.First().First().AppendChild(New Cell() With { _
         .CellValue = New CellValue("1") _
        })

        For Each s As Sheet In doc.WorkbookPart.Workbook.Sheets
            System.Diagnostics.Debug.WriteLine(s.Id)
            System.Diagnostics.Debug.WriteLine(s.SheetId)
        Next

        cursheet = ... 'Get worksheetpart with name "First"

        cursheet = ...  'Get worksheet with sheetid = 2


        doc.WorkbookPart.Workbook.Save()
    End Using

End Sub


Private Function InsertWorksheet(ByVal workbookPart As WorkbookPart, SheetName As String) As WorksheetPart
    ' Add a new worksheet part to the workbook.
    Dim newWorksheetPart As WorksheetPart = workbookPart.AddNewPart(Of WorksheetPart)()
    newWorksheetPart.Worksheet = New Worksheet(New SheetData)
    newWorksheetPart.Worksheet.Save()
    Dim sheets As Sheets = workbookPart.Workbook.GetFirstChild(Of Sheets)()
    Dim relationshipId As String = workbookPart.GetIdOfPart(newWorksheetPart)

    ' Get a unique ID for the new sheet.
    Dim sheetId As UInteger = 1
    If (sheets.Elements(Of Sheet).Count() > 0) Then
        sheetId = sheets.Elements(Of Sheet).Select(Function(s) s.SheetId.Value).Max() + 1
    End If

    ' Add the new worksheet and associate it with the workbook.
    Dim sheet As Sheet = New Sheet
    sheet.Id = relationshipId
    sheet.SheetId = sheetId
    sheet.Name = sheetName
    sheets.Append(sheet)

    workbookPart.Workbook.Save()
    Return newWorksheetPart
End Function

Luciolucita

Answer 1

You're almost there. You're looping over doc.WorkbookPart.Workbook.Sheets already. All you should need to do after that is insert an if statement to see if the sheet you're looking for is your current point in the loop by looking at the properties s.Name or s.Id

Alternatively, as indicated here, you can use LINQ to select the worksheet by name or ID directly:

sID as Integer = doc.WorkbookPart.Workbook.Descendants(Sheet)().First(s => s.Name.Equals("First")).Id

Or

sID as Integer  = doc.WorkbookPart.Workbook.Descendants(Sheet)().First(s => s.Id.Equals(2)).Id

Once you have that ID you can do

wsp As WorksheetPart = doc.WorkbookPart.GetPartById(sID)

I apologise if there's bugs in this, I'm doing this on a rapidly-moving train using my brain compiler on an iPhone. Hopefully that should get you move in the right direction at least.

Cervantez

I can get that to return a something that claims to be a sheet, but upon examining the XML, there is no cell data that I previously inserted. – Luciolucita

Sorry about that, I misread your question and thought you wanted the Worksheet, not the WorksheetPart. I've updated the example. – Cervantez

I wish this OpenXML structure weren't such a convoluted mess. This worked perfectly. Thanks! – Sarmentum