Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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
like image 968
WhiskerBiscuit Avatar asked May 23 '13 21:05

WhiskerBiscuit


People also ask

What is a WorkbookPart?

Excel files components The primary task of a WorkbookPart is to keep track of the worksheets, global settings and the shared components of the Workbook. The document needs to contain at least one Worksheet that is defined inside a WorksheetPart.

How do I create an OpenXml file in Excel?

Use the GetIdOfPart(OpenXmlPart) method to get the Id of the Sheet. Then add the new sheet to the Sheet collection by calling the Append([]) method of the Sheets class. The following code example creates a new worksheet, associates the worksheet, and appends the worksheet to the workbook.


1 Answers

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.

like image 86
Adrian Avatar answered Nov 07 '22 15:11

Adrian