vb.net - 在 VB.Net 中使用 OpenXML 修改单元格

Question

我不知道为什么我很难为这个简单的任务找到示例，但是根据我的 Google-Fu 技能，没有任何显示。我正在寻找使用 OpenXML 和 VB.Net 修改现有电子表格单元格的示例。

我想将现有值更改为新值，不涉及任何公式。

这是我所拥有的:

Public Sub Main()
    Dim strSource, strSheetName As String
    Dim dtPeriod As DateTime

    strSource = Dts.Variables("sFilePath").Value

    'set period to previous month. We only care about the year in this period as the data will be reloaded
    'from the file each month
    dtPeriod = DateAdd(DateInterval.Month, -1, Today)
    Dts.Variables("dtPeriod").Value = dtPeriod

    Using mySpreadSheet As SpreadsheetDocument = SpreadsheetDocument.Open(strSource, True)
        Dim WorkbookSheets As Sheets

        WorkbookSheets = mySpreadSheet.WorkbookPart.Workbook.GetFirstChild(Of Sheets)()

        For Each childSheet As Sheet In WorkbookSheets
            strSheetName = childSheet.Name
            strSheetName = strSheetName.ToLower.Trim

            If strSheetName = "sheet1" Then     'process sheet

                SetCellValue("A1", "Last Name")
                SetCellValue("B1", "First Name")
                SetCellValue("C1", "RegionID")
                SetCellValue("D1", "RegionName")

                'rename sheet for loading
                childSheet.Name = "RegionData"
            End If
        Next

        mySpreadSheet.WorkbookPart.Workbook.Save()
        mySpreadSheet.Close()
    End Using
End Sub

Private Sub SetCellValue(ByVal loc As String, ByVal Val As String)
    Dim cell As Cell = New Cell


    cell.CellReference = loc
    cell.DataType = CellValues.String
    cell.CellValue = New CellValue(Val)

End Sub

我确信这很简单，例如确保单元格引用正确的工作表，但我发现的所有示例都是用于创建新工作表。

谢谢

Answer 1

这是我如何让它工作的:

Private Sub ProcessFile(ByVal fileName As String)
    Dim value As String = Nothing
    Dim worksheetID As String 

    Using document As SpreadsheetDocument = SpreadsheetDocument.Open(fileName, True)
        worksheetID = GetWorksheetID(document)

        If worksheetID = String.Empty Then
            MsgBox("Failure!")
        End If

        SetStrCellValue(document, worksheetID, 1, 1, "Last Name")

        document.WorkbookPart.Workbook.Save()
        document.Close()

    End Using
End Sub

Private Overloads Function GetWorksheetID(ByRef document As SpreadsheetDocument) As String 
    Dim sSheetName As String
    Dim sheetID As String = String.Empty
    Dim WorkbookSheets As Sheets = document.WorkbookPart.Workbook.GetFirstChild(Of Sheets)()

    For Each childSheet As Sheet In WorkbookSheets
        sSheetName = childSheet.Name
        sSheetName = sSheetName.ToLower.Trim

        'rename worksheet
        If sSheetName = "sheet1" Then
            childSheet.Name = "MySheet"

            sheetID = childSheet.Id
        End If
    Next

    Return sheetID
End Function

Private Overloads Sub SetStrCellValue(ByRef document As SpreadsheetDocument, ByVal sheetID As String, ByVal iRow As Integer, ByVal col As Integer, ByVal newCellValue As String)
    Dim Location As String = GetColumnLetter(col) & iRow.ToString

    SetStrCellValue(document, sheetID, Location, newCellValue)
End Sub


Private Overloads Sub SetStrCellValue(ByRef document As SpreadsheetDocument, ByVal sheetID As String, ByVal location As String, ByVal newCellValue As String)
    Dim wsPart As WorksheetPart = CType(document.WorkbookPart.GetPartById(sheetID), WorksheetPart)
    Dim Cell As Cell = wsPart.Worksheet.Descendants(Of Cell).Where(Function(c) c.CellReference = location).FirstOrDefault

    Cell.CellValue = New CellValue(newCellValue.ToString)
    Cell.DataType = CellValues.String
End Sub

'Returns column letter based on the column number
Private Function GetColumnLetter(ByVal colNumber As Integer) As String
    Dim columnLetter As String = String.Empty
    Dim abc As String = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"

    If colNumber > 26 Then
        columnLetter = abc.Substring(CInt(colNumber / 26) - 1, 1)
    End If

    columnLetter += abc.Substring(((colNumber - 1) Mod 26), 1)

    Return columnLetter
End Function