How to write to multiple columns in ActiveData with a .NET script
In this article, we will demonstrate how to write to multiple columns within an ActiveData asset.
The below example uses a sample SAP Application where we will be retrieving the data from multiple columns within a SapTable control and writing to separate columns in the ActiveData asset where the ActiveData asset is a .xlsx file.
To begin, we retrieve the data from the table control with each columns data added to separate lists; 'accounts' and 'descriptions'.
The script will then loop through every item in each list using a For Each statement. This For Each statement will call our custom function 'AddToBook'.
The 'AddToBook' function accepts three parameters; 'item', 'columnId' and 'cRow'. The 'item' is a string representation of each individual data item in the respective list. The 'columnId' is the name (header) of the column in the Excel sheet which you wish to write the data too and 'cRow' is the current row index which the data will be written to.
The function declares an ActiveDataRow which is assigned to a specified row index using the 'item(cRow)' method.
Dim row As ActiveDataRow = data.item(cRow)
Next you will call the 'SetString' method for each ActiveDataRow and pass in the 'columnId' and 'item' values.
To finish, the function then saves the currently loaded ActiveData.
During the For Each statement, you need to increment the existing row index which you wish to write to. We have initially declared an Integer called 'currentRow' with a value of 1. This is the row to start writing to. We cannot use '0' as this would be the header of the column.
Once AddToBook has been called, you simply increment the currentRow value by calling 'currentRow +=1'. Therefore on the next loop, the row index will be 2 and then 3 and so forth.
After the first For Each statement, it is important to reset the currentRow value to 1 so that the next column will not start at the end of the last columns length.