如何从OpenXML中的名称或工作表ID获取Worksheetpart?

问题描述

以下创建一个XLSX，添加两个带有一些数据的工作表.然后，我希望以后能够根据名称(或最好是id)获取电子表格，以便我可以在以后的某个时间点添加/修改工作表.我被卡在下面的代码不完整的地方，如何重新获得工作表.

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

推荐答案

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

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

或者，如所示，您可以使用LINQ直接通过名称或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

或

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

拥有该ID后，您就可以

Once you have that ID you can do

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

我对此表示歉意，我正在使用我的大脑编译器在iPhone上快速移动的火车上进行此操作.希望这至少可以使您朝正确的方向前进.

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.

这篇关于如何从OpenXML中的名称或工作表ID获取Worksheetpart?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！