Modifying a cell with OpenXML in VB.Net

Question

I don't know why I'm having such a hard time finding examples for this simple task, but nothing is showing based on my Google-Fu skills. I'm looking for an example of modifying a cell of an existing spreadsheet using OpenXML and VB.Net.

I want to change the existing value to a new one, no formulas involved.

Here is what I have:

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

I'm sure it's something simple like making sure the cell is referencing the correct sheet, but all the examples I've found are for creating a new sheet.

Thanks

Answer 1

Here's how I got it working:

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