none
OpenXML add values to range in Excel from dataset RRS feed

  • Question

  • All,

     I have an excel template with a range. I want to add values from a datatable to this range of cells. I can get the range start and end as strings but I want to know how to loop through each column and row to add the data from the dataset. Below is what I have so far.  My range is A1 to AG1 though I want the rows to be added to whatever the lenght of the rows in the datatable. I'm not sure how to get the next column/row as what I've seen is them being put in as "B1" text but not sure how to loop through them. Any help would be appreciated.

     Using myexcel As SpreadsheetDocument = SpreadsheetDocument.Open(strXcelTempFileName, True)
                    Dim workbookPart As WorkbookPart = myexcel.WorkbookPart
                    Dim sheet As Generic.IEnumerable(Of Sheet) = workbookPart.Workbook.GetFirstChild(Of Sheets)().Elements(Of Sheet)().Where(Function(s) s.Name = "Exported")
                    Dim definedNames As Object = workbookPart.Workbook.DefinedNames

                    For Each dn As DefinedName In definedNames
                        If dn.Name = "ExportedValues" Then
                            Dim reference As String = dn.InnerText
                            Dim sheetName As String = reference.Split(Microsoft.VisualBasic.ChrW(33))(0)
                            Dim range As String = reference.Split(Microsoft.VisualBasic.ChrW(33))(1)
                            Dim rangeArray() As String = range.Split(Microsoft.VisualBasic.ChrW(36))
                            Dim startCol As String = rangeArray(1)
                            Dim startRow As String = rangeArray(2).TrimEnd(Microsoft.VisualBasic.ChrW(58))
                            Dim endCol As String = Nothing
                            Dim endRow As String = Nothing
                            If (rangeArray.Length > 3) Then
                                endCol = rangeArray(3)
                                endRow = rangeArray(4)
                            End If
                            'loop thru cell
                            Dim row As Row = New Row() With {.RowIndex = startRow + 1}
                            Dim cell As Cell = New Cell() With {.CellReference = startCol & startRow + 1}

                            //how to loop through columns and rows????

                             For r As Integer = 0 To DS.Tables("ExportedValues").Rows.Count - 1
                                For jt As Integer = 0 To endCol - 1

                                Next
                            Next


                        End If
                    Next
                End Using

    Thursday, March 22, 2018 2:57 PM

Answers

  • Hello jtngres,

    Not like cell object in Excel object model. If a row/ cell does not contain any value/format, the row/cell object does not exist in the sheetdata, so we could not loop through the all the range.

    Please check if below code work could for you.

            Using myexcel As SpreadsheetDocument = SpreadsheetDocument.Open(strXcelTempFileName, True)
                Dim objworkbookPart As WorkbookPart = myexcel.WorkbookPart
                Dim objsheet As IEnumerable(Of Sheet) = objworkbookPart.Workbook.Descendants(Of Sheet)().Where(Function(s) s.Name = "Exported")
                Dim objworksheetPart As WorksheetPart = CType(objworkbookPart.GetPartById(objsheet.First.Id), WorksheetPart)
                Dim objsheetData As SheetData = objworksheetPart.Worksheet.Elements(Of SheetData).First
                Dim definedNames As Object = objworkbookPart.Workbook.DefinedNames
                For Each dn As DefinedName In definedNames
                    If dn.Name = "ExportedValues" Then
                        Dim reference As String = dn.InnerText
                        Dim sheetName As String = reference.Split(Microsoft.VisualBasic.ChrW(33))(0)
                        Dim range As String = reference.Split(Microsoft.VisualBasic.ChrW(33))(1)
                        Dim rangeArray() As String = range.Split(Microsoft.VisualBasic.ChrW(36))
                        Dim startCol As String = rangeArray(1)
                        Dim startRow As String = rangeArray(2).TrimEnd(Microsoft.VisualBasic.ChrW(58))
                        Dim endCol As String = Nothing
                        Dim endRow As String = Nothing
                        If (rangeArray.Length > 3) Then
                            endCol = rangeArray(3)
                            endRow = rangeArray(4)
                        End If
    
                        Dim dt As New DataTable
                        dt.Columns.Add(1, GetType(Integer))
                        dt.Columns.Add(2, GetType(Integer))
                        dt.Columns.Add(3, GetType(Integer))
    
                        dt.Rows.Add(4, 5, 6)
                        dt.Rows.Add(7, 8, 9)
                        dt.Rows.Add(10, 11, 12)
                        dt.Rows.Add(13, 14, 15)
                        'loop thru cell
                        Dim row As Row
                        Dim cel As Cell
                        Dim startColIndex As Integer = ColumnLetterToColumnIndex(startCol)
                        Dim targerColIndex As Integer
                        Dim colLetter As String
                        For r As Integer = 0 To dt.Rows.Count - 1
                            row = New Row() With {.RowIndex = (startRow + 1 + r)}
                            For jt As Integer = 0 To dt.Columns.Count - 1
                                targerColIndex = startColIndex + jt
                                colLetter = ColumnIndexToColumnLetter(targerColIndex)
                                cel = New Cell() With {.CellReference = colLetter & row.ToString}
                                cel.DataType = CellValues.Number
                                cel.CellValue = New CellValue
                                cel.CellValue.Text = dt.Rows(r)(jt)
                                row.Append(cel)
                            Next
                            objsheetData.Append(row)
                        Next
                    End If
                Next
            End Using

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by jtngres Thursday, April 5, 2018 2:50 PM
    Friday, March 23, 2018 10:18 AM
  • Hello jtngres,

    Sorry for my careless, here they are.

     Private Function ColumnIndexToColumnLetter(colIndex As Integer) As String
            Dim div As Integer = colIndex
            Dim colLetter As String = String.Empty
            Dim modnum As Integer = 0
    
            While div > 0
                modnum = (div - 1) Mod 26
                colLetter = Chr(65 + modnum) & colLetter
                div = CInt((div - modnum) \ 26)
            End While
    
            Return colLetter
        End Function
        Private Function ColumnLetterToColumnIndex(colLetter As String) As Integer
            Dim UColLetter As String = UCase(colLetter)
            Dim modnum As Integer = 0
            For i = Len(UColLetter) To 1 Step -1
                modnum = modnum + ((Asc(Mid(UColLetter, i, 1)) - 64) * (26 ^ (Len(UColLetter) - i)))
            Next
            Return modnum
        End Function

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by jtngres Thursday, April 5, 2018 2:50 PM
    Monday, March 26, 2018 1:49 AM

All replies

  • Hello jtngres,

    Not like cell object in Excel object model. If a row/ cell does not contain any value/format, the row/cell object does not exist in the sheetdata, so we could not loop through the all the range.

    Please check if below code work could for you.

            Using myexcel As SpreadsheetDocument = SpreadsheetDocument.Open(strXcelTempFileName, True)
                Dim objworkbookPart As WorkbookPart = myexcel.WorkbookPart
                Dim objsheet As IEnumerable(Of Sheet) = objworkbookPart.Workbook.Descendants(Of Sheet)().Where(Function(s) s.Name = "Exported")
                Dim objworksheetPart As WorksheetPart = CType(objworkbookPart.GetPartById(objsheet.First.Id), WorksheetPart)
                Dim objsheetData As SheetData = objworksheetPart.Worksheet.Elements(Of SheetData).First
                Dim definedNames As Object = objworkbookPart.Workbook.DefinedNames
                For Each dn As DefinedName In definedNames
                    If dn.Name = "ExportedValues" Then
                        Dim reference As String = dn.InnerText
                        Dim sheetName As String = reference.Split(Microsoft.VisualBasic.ChrW(33))(0)
                        Dim range As String = reference.Split(Microsoft.VisualBasic.ChrW(33))(1)
                        Dim rangeArray() As String = range.Split(Microsoft.VisualBasic.ChrW(36))
                        Dim startCol As String = rangeArray(1)
                        Dim startRow As String = rangeArray(2).TrimEnd(Microsoft.VisualBasic.ChrW(58))
                        Dim endCol As String = Nothing
                        Dim endRow As String = Nothing
                        If (rangeArray.Length > 3) Then
                            endCol = rangeArray(3)
                            endRow = rangeArray(4)
                        End If
    
                        Dim dt As New DataTable
                        dt.Columns.Add(1, GetType(Integer))
                        dt.Columns.Add(2, GetType(Integer))
                        dt.Columns.Add(3, GetType(Integer))
    
                        dt.Rows.Add(4, 5, 6)
                        dt.Rows.Add(7, 8, 9)
                        dt.Rows.Add(10, 11, 12)
                        dt.Rows.Add(13, 14, 15)
                        'loop thru cell
                        Dim row As Row
                        Dim cel As Cell
                        Dim startColIndex As Integer = ColumnLetterToColumnIndex(startCol)
                        Dim targerColIndex As Integer
                        Dim colLetter As String
                        For r As Integer = 0 To dt.Rows.Count - 1
                            row = New Row() With {.RowIndex = (startRow + 1 + r)}
                            For jt As Integer = 0 To dt.Columns.Count - 1
                                targerColIndex = startColIndex + jt
                                colLetter = ColumnIndexToColumnLetter(targerColIndex)
                                cel = New Cell() With {.CellReference = colLetter & row.ToString}
                                cel.DataType = CellValues.Number
                                cel.CellValue = New CellValue
                                cel.CellValue.Text = dt.Rows(r)(jt)
                                row.Append(cel)
                            Next
                            objsheetData.Append(row)
                        Next
                    End If
                Next
            End Using

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by jtngres Thursday, April 5, 2018 2:50 PM
    Friday, March 23, 2018 10:18 AM
  • Terry,

      Thank you. You reference

    ColumnIndexToColumnLetter
    ColumnLetterToColumnIndex

    Do you have those functions since that was my biggest hangup. I can't hard code these as there are multiple ranges in the sheet that we want to use this code for.

    • Marked as answer by jtngres Thursday, April 5, 2018 2:50 PM
    • Unmarked as answer by jtngres Thursday, April 5, 2018 2:50 PM
    Friday, March 23, 2018 3:19 PM
  • Hello jtngres,

    Sorry for my careless, here they are.

     Private Function ColumnIndexToColumnLetter(colIndex As Integer) As String
            Dim div As Integer = colIndex
            Dim colLetter As String = String.Empty
            Dim modnum As Integer = 0
    
            While div > 0
                modnum = (div - 1) Mod 26
                colLetter = Chr(65 + modnum) & colLetter
                div = CInt((div - modnum) \ 26)
            End While
    
            Return colLetter
        End Function
        Private Function ColumnLetterToColumnIndex(colLetter As String) As Integer
            Dim UColLetter As String = UCase(colLetter)
            Dim modnum As Integer = 0
            For i = Len(UColLetter) To 1 Step -1
                modnum = modnum + ((Asc(Mid(UColLetter, i, 1)) - 64) * (26 ^ (Len(UColLetter) - i)))
            Next
            Return modnum
        End Function

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by jtngres Thursday, April 5, 2018 2:50 PM
    Monday, March 26, 2018 1:49 AM