Hi everyone,
I am working on an OutSystems application where I need to export an Excel file in a very specific, pre-defined layout.
Instead of generating the Excel structure dynamically, I want to use a fixed Excel template and populate the data into it at runtime.
My requirement is:
I already have an Excel template with:
Header section (logo and headings)
Multiple static sections with fixed rows and columns
Two grid sections where only the rows are dynamic
I need to:
Load this Excel template (stored as a Binary)
Fill fixed cells for the static sections
Dynamically insert multiple rows for few grids
Keep the formatting, merged cells and layout exactly as defined in the template
I am planning to use the Advanced Excel component for this.
My questions are:
What is the recommended way to load an existing Excel template (Binary) and use it as the base workbook with Advanced Excel?
How can I insert dynamic rows into a predefined table area in the template while preserving formatting and formulas?
Is there a best practice to avoid breaking merged cells and styles when adding rows in Advanced Excel?
The final file should be downloaded by the user in the same structure as the template, with only the data filled at runtime.
Any guidance or sample approach using Advanced Excel would be really helpful.
Thanks in advance!
Halo Roshni,
I’ve implemented this many times in real projects. Below is the approach that works well when you have a strict Excel template with merged cells, formulas, and fixed layout.
*Treat the Excel File as a Real Template
Do not generate the layout in code.
Instead:
Store the Excel file as Binary (Resource / DB)
Load it as the base workbook
Modify it directly
Export it
This way:
Logo, layout, merged cells, and formulas stay intact
You only fill data
from my side, I see there is a limitation when using a initial Advance excel ext on forge
Merge ranges are complex
Layout is strict
You need special formatting later (subscript, superscript, etc.)
so, In real projects, I usually: Clone the Advanced Excel extension and enhance it.
build some method:
Generic MergeRange(startRow, endRow, startCol, endCol)
CellConfig for advanced styling
Flow I suggest:
Create one helper Server Action in the Foundation layer for Excel export.
Example responsibilities:
Inputs:
Template Binary
Table data (rows + cells)
Layout configs
Merge configs
Action does:
Open template
Fill static fields
Insert dynamic rows (based on input start_row for table)
Apply merges
Return final Binary
Hi @Roshni,
the best way to do this is to upload your Excel template as a binary resource in your app. Instead of building the layout in code, just use the component to open that template file directly. You can then fill in your data, and all your logos and merged cells will stay exactly as they are. When you're finished, just download the final binary. Hope that solves your problem.