Range Operations

[gaj]

Hallo,
We have to fill a Excel-Template with data.
The first column of the Tableheader is a "NamedRange" eg. "Start".
How to navigate to the next Row (yellow - cell in attachment) of the "NamedRange" to start and how to fill the table with data coming form an Array?

thx for helping us.
Johannes

[gaj]

Found the solution my self!
thx

Code: Select all

Public Function FillExcelTable(docpath As String, textmark As String, Data(,) As String, Optional TextmarkIsInLastHeaderRow As Boolean = True) As Boolean
        Dim workbook As New Spire.Xls.Workbook
        workbook.LoadFromFile(docpath)
        Dim sheet As Spire.Xls.Worksheet = workbook.Worksheets(0)
        Dim NamedRange As Core.INamedRange = workbook.NameRanges.GetByName(textmark)
        Dim currentRow As Integer = NamedRange.RefersToRange.Row
        Dim currentCol As Integer = NamedRange.RefersToRange.Column
        currentRow += 1
        For r As Integer = currentRow To currentRow + Data.GetUpperBound(0)
            For c As Integer = currentCol To currentCol + Data.GetUpperBound(1)
                'special formating
                If c = currentCol + 11 Or c = currentCol + 15 Then 'Eigenschaftsprofile mit Zeilenumbruch formatieren
                    sheet.Range(r, c).Style.WrapText = True
                End If
                If c >= currentCol + 3 AndAlso c <= currentCol + 5 Then ' X..zentriert
                    sheet.Range(r, c).Style.HorizontalAlignment = HorizontalAlignType.Center
                End If
                'Set value (only String)
                sheet.Range(r, c).Value2 = Data(r - currentRow, c - currentCol)
            Next
        Next
        workbook.Save()
        Return True
    End Function

[Nina.Tang]

Hello,

Awesome! I am glad to hear that you find a solution.
In addition, you could use InsertArray method to insert array directly, which is more effective for your case.

Code: Select all

sheet.InsertArray(Data, currentRow + 1, currentCol)

Sincerely,
Nina
E-iceblue support team