The Formula Tag
During transformation, dynamic Excel formulas may created using the "formula" tag.
This tag must be used in the bodiless form. When processed, the "formula" tag creates
a new Excel formula in the cell.
This is different than JETT
Formulas. JETT Formulas are static, but JETT keeps track of the original cell
references in the template and produces the correct references for the finished
spreadsheet. With dynamic formulas, the formula text comes from an Expression. JETT
does not process these formulas any further than the evaluation of the formula
Expression, other than placing the new Excel formula in the cell.
Attributes
- The "formula" tag supports all base tag attributes.
- bean: String Optional. This specifies the name of the bean to
be evaluated to obtain the formula text. E.g. bean="beanName" =>
${beanName} => "formula" =>
"${wins} / (${wins} + ${losses})". Either "bean" or "text" must be
specified, but not both.
- text: String Optional. This specifies the actual formula text
to be used in the Excel formula. E.g.
text="${wins} / (${wins} + ${losses}). Either "bean" or "text" must
be specified, but not both.
- ifError: String Optional. This specifies the text that the
Excel formula should display if the main formula text is evaluated and it results
in an Excel error. This wraps the formula text in an Excel "IF" formula and an
"ISERROR" formula, e.g.
text="${numerator} / ${denominator}" ifError="Can't divide by zero! =>
IF(ISERROR(5 / 0), "Can't divide by zero!", 5 / 0). If this is not
specified, then the formula is not wrapped.
Text Example
This template uses the "text" attribute to create an Excel formula.
| Pct. |
| <jt:formula text="${wins} / (${wins} + ${losses})"/> |
This gets transformed into...
In Excel, the formula text is visible: =10 / (10 + 10).
In this case, it may be better to include "ifError" to avoid an Excel error showing
up in the cell.
| Pct. |
| <jt:formula text="${wins2} / (${wins2} + ${losses2})" ifError="-"/> |
This gets transformed into...
In Excel, the formula text is visible:
=IF(ISERROR(0 / (0 + 0)), "-", 0 / (0 + 0)).
Bean Example
In this example, the bean "formulaBean" has the value "formulaText". The
"formulaText" bean has the value "${wins} + ${losses}".
| Games Played |
| <jt:formula bean="formulaBean" /> |
This gets transformed into...
In Excel, the formula text is visible:
=10 + 10.
