vb.net 如何从 OpenXML 中的名称或工作表 ID 获取工作表部分？

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

提问by WhiskerBiscuit

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.

下面创建一个 XLSX，添加两个包含一些数据的工作表。然后我希望能够稍后根据名称（或最好是 id）获取电子表格，以便我可以在稍后的时间点添加/修改工作表。我被困在如何在下面的代码不完整的情况下再次获取工作表。

    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

回答by Adrian

You're almost there. You're looping over doc.WorkbookPart.Workbook.Sheetsalready. 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.Nameor s.Id

您快到了。你已经在循环了doc.WorkbookPart.Workbook.Sheets。之后您需要做的就是插入一个 if 语句，通过查看属性s.Name或s.Id

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

另外，指示在这里，你可以使用LINQ选择直接名称或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 that ID you can do

一旦你有了那个 ID，你就可以做

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.

如果其中存在错误，我深表歉意，我正在快速移动的火车上使用 iPhone 上的大脑编译器执行此操作。希望这至少能让你朝着正确的方向前进。