How can I get Worksheetpart from a name or sheet id in OpenXML?

Question

The following creates XLSX, adds two sheets of some data. Then I want to get the spreadsheet later based on the name (or preferably id) so that I can add / change sheets at a later point in time. I was fixated on how to get the sheet again, where is the code 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 

Answer 1

You are almost there. You are already sorting through doc.WorkbookPart.Workbook.Sheets . All you have to do after this is insert the if statement to see if the sheet you are looking for is your current loop point by looking at the s.Name or s.Id

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

 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 this ID, you can do

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

I apologize if there are errors in this, I do it on a fast moving train using my brain compiler on the iPhone. Hopefully this should make you move in the right direction, at least.