JETT supports the creation of Excel formulas. Specify a JETT Formula of the format "$[Formula]" in the template spreadsheet, e.g. "$[SUM(B2)]".
JETT keeps track of all original cell references inside the JETT Formula, including all transformations that occur, including shifts, copies, and removals. Once all transformation on all Sheets has completed, JETT goes back and replaces all JETT formulas with Excel formulas. Consecutive cells are recognized and merged into Excel ranges, e.g. "B2, B3, B4" gets merged into "B2:B4". JETT formula cell references may include sheet references, e.g. "Sheet2!C4", "'With Spaces'!B5". JETT does not verify the existence of the Excel formula name.
Here's an example, which uses implicit collections processing. Note that the cell containing "${employees.salary}" is cell B2.
| Employee | Salary |
| ${employees.lastName}, ${employees.firstName} | ${employees.salary} |
| Total Salary: | $[SUM(B2)] |
... gets transformed into...
| Employee | Salary |
| Stack, Robert | $1,000.00 |
| Queue, Suzie | $900.00 |
| Fudd, Elmer | $800.00 |
| Bunny, Bugs | $1,500.00 |
| Total Salary: | $4,200.00 |
The cell with the total salary of $4,200.00 is actually an Excel formula, with formula text "=SUM(B2:B5)".
Each cell reference in a JETT Formula may include an optional "||default" clause after the cell reference (two "pipe" characters followed by the default value). If all cell references corresponding to the original cell reference are deleted (this may occur when looping tags process empty Collections), then the default value is used. The default value itself defaults to zero.
In this example, the "noEmployees" collection exists in the beans map, but it is empty. The Excel formula "COUNTA" is used to count the employees, but if there are no cell references, zero is used in the formula, resulting in the incorrect display value of "1". So a default value is specified -- an unpopulated and unused cell is referenced.
| Employee | Salary |
| ${noEmployees.lastName}, ${noEmployees.firstName} | ${noEmployees.salary} |
| $[COUNTA(A2||$Z$1)] | $[SUM(B2)] |
... gets transformed into...
| Employee | Salary |
| 0 | $0.00 |
The cell under "Employee" contains the Excel formula text "=COUNTA($Z$1)" and the cell under "Salary" contains the Excel formula text "=SUM(0)".