Multi-Line Statements in XLSX
All multi-line directives such as #if, #else, #elseif, #foreach, and #macro must be used under the following conditions.
The beginning and ending statements must be declared within a single cell. Figure 1 and Figure 2 below show samples of invalid usage of the #if and #foreach statements between cells respectively.
Figure 1: How to execute multiline statements.
Figure 2: Invalid usage of foreach statements.
In Figure 1, since the body of the #if statement ($e.name) resides in the A2 cell, not in the A1 cell, the body will not be generated when generating a report, regardless of the evaluation of the #if statement. The code shown in Figure 2 will break the structure of a spreadsheet document.
Figure 3 and Figure 4 demonstrate samples of valid usage of the #if and #foreach statements respectively.
Figure 3: Valid usage of an if statement.
Figure 4: Valid usage of a foreach statement.
- A VTL Macro must be declared within a single cell. Do not insert the multi-cell recorded macros in a single cell, see figure below.
Figure 5: Invalid usage of a VTL Macro.
The macro will copy all contents between #macro and #end. Cells and rows will be included in the macro as well. Once this record has been inserted, the macro content will break the document structure.
Figure 6 demonstrates a sample of valid usage of the #macro statement.
Figure 6: Valid usage of a VTL Macro
Creating data for multiple rows
The #foreach directive can only be used in a single cell record. To create data for multiple rows, use the #forrow directive instead (Figure 7).
Figure 7: The forrow directive.
Fiogure 8 demonstrates the output of the above code.
Figure 8: Results of running the code from figure 7.
Creating data for multiple columns
#forcol is used for creating data for multiple columns (Figure 9). This statement can be used in conjunction with the #forrow statement.
Figure 9: The forcol directive.
Figure 10 demonstrates the output of the above code.
Figure 10: Results of running the code from figure 9.
Displaying content in a cell
Texts in any generated report are always wrapped. Also, the cells’ width in the generated report depends on the cells’ width in the report template used. For example, an XLSX report template in Figure 11 will generate an output report as shown in Figure 12.
Figure 11: The forrow directive
Figure 12: Results of running the code from figure 11.
Limitation when used in Microsoft Office Excel worksheet
You cannot use #sectionBegin and #includeSection in XLSX. If you try to use #sectionBegin or #includeSection in an XLSX report template (Figure 13) an error message will open (Figure 14).
Figure 13: Invalid usage of a sectionBegin directive.
Figure 14: The error message from running the code in figure 13.