Hi everyone,I’m using the Advanced Excel Forge component to export data efficiently (avoid per-cell writes). I’m targeting the Cell_WriteRange action, which expects the DataSet parameter as an Object. My dataset is built as a 2D matrix using List<List<Text>> (each inner list is one row, multiple columns).However, when I call Cell_WriteRange, only the first column gets written in the worksheet (data appears only in column A).
I’ve debugged the data and confirmed:
- Each row is a List with multiple values (e.g., 10 columns).- All rows have consistent lengths (same number of columns).- I am not merging cells in the target area (tested on a clean sheet named "Data").
Unfortunately, I cannot share any screenshots or OML examples because this is a private project.Also, I’ve seen in the forums that Cell_WriteRange often uses a predefined structure for the dataset, but in my case, I cannot use a fixed structure because the number of columns is dynamic (it depends on the templates created by the users).
Hi @Jose Cadena
This behavior is expected with Advanced Excel O11.
Even though Cell_WriteRange accepts an Object, it doesn’t support List> or other nested lists. Internally, the action expects a List of Records (Structure), where each record represents a row and each attribute maps to a column.
When a List> is passed, the component only reads the first value of each inner list, which is why data ends up only in column A.
Since your columns are dynamic, the usual workaround is to:
Convert the data into a List of Records (using JSON or another dynamic mapping approach), or
Fall back to per-cell writes (or a custom extension) if a dynamic structure isn’t feasible.
In short, Cell_WriteRange is optimized for performance with structured datasets, not generic 2D matrices.
Hope this clarifies the issue.
regards,
Manish Jawla
Just one note.
A bit better than a per-cell writing is a per-column writing. If the data can be transposed in the matrix, you can write one column at a time.
To add more detail, @Nuno Reis is right that the Cell_WriteRange will write per column, which mean if you have a text record list with 4 items, it will write 4 rows with 1 column only. Therefore you can consider to loop the matrix and change to write column by column instead of row by row. The basic flow is the same, just change your mind from "loop to write row" to "loop to write column"
And @Manish Jawla mentioned above, the Cell_WriteRange is used for predefined structure which you need to declare at the beginning. Actually, if it has predefined structure, the built-in function of Outsystems below is better than using Advanced Excel.
I tried using a per-column approach, changing the List<List<text>> for a List of Records, but the time it took to process the Excel was even higher than doing per-cell writes.
Currently, I'm trying a 'CSV' approach. I'm using stringbuilders (text extension) to create a local CSV, and then converting it to .xlsx. So far I've seen an improvement in time using this approach, but I will continue to perform some more tests. The only downside of this method is the limit on memory. For regular Excel files, the method seems to work fine, but when the amount of data is huge (e.g. >100k records), the app is likely going to throw an OutOfMemory Exception. Still trying to fiure out a way to buffer the processed data, and do the writes in batches.