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

Question

下面创建一个 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

Answer 1

您快到了。你在循环 doc.WorkbookPart.Workbook.Sheets已经。之后您需要做的就是插入一个 if 语句，通过查看属性 s.Name 来查看您要查找的工作表是否是循环中的当前点。或 s.Id
或者，如所示 here ，您可以使用 LINQ 直接按名称或 ID 选择工作表:

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

或者

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

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

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

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