Purpose
Demo ZCL_XTT_DEMO_021 shows how formulas survive row and column expansion. Define formulas in Excel, where they remain visible and testable; generating formula strings in ABAP is fragile and differs between XLSX absolute references and Excel XML relative references.
Formula-copying model
XTT creates new rows and cells by copying the template range. Formulas travel with values, borders, styles, and other cell properties.
To prevent the formulas from “shifting” during copying cells, you can use several methods:
Relative references in XLSX
For references relative to the current formula cell, use OFFSET() with INDIRECT(), or use R1C1 notation through INDIRECT().
To sum the preceding three cells, move three columns left and return a three-column range.
The offset can also be specified directly in INDIRECT(“RC[-3]”, 0), in such formulas it can also be useful:
- =ROW() &
- =COLUMN() functions
which, without passing arguments, return the current row and column
Runtime row substitution
As a simpler alternative, XTT supports a template-specific $ rule:
When the current template row number is prefixed with $, XTT replaces it with the generated row number at runtime.
The final report will be like this
For ;direction=column, the equivalent substitution applies to column references such as $E.
Shared formulas
Current XTT versions preserve repeated shared formulas while converting between absolute and relative references. This avoids the disappearing-formula problem seen in older releases.
Named ranges
Named ranges are updated during generation:
- A normal named range is expanded once to cover the generated data.
- If the name ends in
_, XTT repeats the range and substitutes the resulting range list into the formula.
Totals, arrays, and conditional formatting
You do not need named ranges or an XTT tree merely to total a flat Excel table. Standard Excel table formulas are often sufficient.
Use Excel’s native table features when the calculation is specific to XLSX output.
Array formulas are also supported
For calculations that must also work in Word or PDF, use XTT aggregation functions: ;func=SUM, AVG, COUNT, or FIRST.
For conditional-formatting formulas, define the applicable range as whole columns when possible. This gives XTT room to expand the data without leaving generated cells outside the rule.
