Excel template for invoice

See more:

VB

My idea was let end user to open excel and create template for invoice. After that i would let him now that for column "Article Name" he need to put in the Name manager column name "NameArticle" and refers to that column and so on for other columns.

After that with loop i would read from my sql data and fill the data in Excel by those template.

I have one solution where this is currently working but it is terribly slow so i decided to quit it and look for another solution.

End.
I tried to translate the code from my native language to be more understanding to you

What I have tried:

My solution was going smth like this.

Fill Class with Property Name and Property Value

Prepare rows

For j = 1 To bsItems.Count - 1
    For i = 1 To numberOfRowsPerItem
        xlSheet.Rows(endRow).Insert
    Next i
Next j

After that i would copy the formated cells

For j = 1 To bsItems.Count - 1
    For i = 1 To numberOfRowsPerItem
        xlSheet.Rows(ItemStartRow + i).Copy
        xlSheet.Rows(ItemStartRow + (j * numberOfRowsPerItem) + i).PasteSpecial
    Next i
Next j

And fill values

For j = 1 To bsItems.Count
    For i = 1 To UsedParameters.Count
        cParametar = UsedParameters(i)

        MyPos.ExcelPos = xlBook.Names(cParametar).RefersTo
        DataRow= MyPos.Row+ (j * numberOfRowsPerItem)
        DataColumn= MyPos.Column

        If ParameterExists(cParametar) = True Then
            cValue= bsItems.Item(j).Item(cParametar).ValueOfItem
            cTekst = xlSheet.Cells(DataRow, DataColumn)
            cTekst = PrepareAndFormatCell(cTekst, cVrijednost)

            cNumFormat = xlSheet.Cells(DataRow, DataColumn).NumberFormat
            If InStr(1, cNumFormat, "0.00") <> 0 Then
                xlSheet.Cells(DataRow, DataColumn) = CDbl(cTekst)
            Else
                xlSheet.Cells(DataRow, DataColumn) = cTekst
            End If        
        End If
    Next i
Next j