This section demonstrates how to apply different formulas to your exported data. First any static Excel formulas (which are supported by
POI) will be copied to result Excel file untouched.
But if you want to apply formula to dynamic data like exported collections you have to use predefined notation.
Any cell with value starting with
$[ and ending with
] will be rendered as a formula cell. For example
$[formula]
where
formula is the real formula which will be processed by jXLS engine.
When you reference a worksheet cell in a jXLS formula and that cell is transformed into some other cell or a set of cells the cell reference in the formula will be updated accordingly. For example if you have a formula like
$[E5 + B4 * 5]
And after jXLS transformation cell E5 turns to E20 and cell B4 turns to cell B15 the result formula will look like
E20 + B15 * 5
Let's consider more complex sample where a source cell was duplicated and turned into several cells forming a cell range. For example if we have a formula like
$[SUM(C2)]
and cell C2 turns into a set of cells C2, C3, C4, C5, C6. This can happen for example if cell C2 is contained in jx:forEach tag body. In this case jXLS detects that result cells form a range C2:C6 and replaces the original cell reference with this range
SUM(C2:C6)
jXLS also detects column ranges like A5:E5 when result cells are A5, B5, C5, D5, E5.
If result cells do not form any cell range original cell will be replaced with list of all cells separated with commas. If in previous example cell C2 is transformed into cells C2, C3, C4, C10, C11, C20 result formula will look like
SUM(C2,C3,C4,C10,C11,C20)
Also you can reference cells from different worksheets in a formula cell. For example you can have following formula referencing cells from other worksheets
$[SUM(Sheet2!B10) - 'Sheet 1'!D15]
jXLS updates formula cell references throughout the workbook during excel template transformation. It means
for example that any transformation that changes cell original location will reflect new cell location
in all affected formulas. If we have jx:forEach tag like this
<jx:forEach items="${employees}" var="employee">
${employee.name} | ${employee.payment}
<jx:forEach>
And assuming that before transformation ${employee.payment} was located in a cell B5 and after transformation
we have employee payments in all cells from B5 to B10 then if any other cell references
original cell B5 like for example $[SUM(B5)] then all such formulas will be updated.
In this case we will get a formula like SUM(B5:B10).
Sometime you may have a situation when all cells referenced in your formula are removed from the sheet.
For example it can happen when formula references a cell inside jx:forEach tag and the collection is empty.
To deal with this situation you can use @ symbol to define default formula value.
Default formula value will replace the formula only in that case when all underlying cell references were removed during processing.
Example
$[SUM(B6) + SUM(B13)@0 + (SUM(B20)@0) +SUM(B27)@0 + SUM(B34)]
Assuming cells B13 and B27 were removed during transformation you will get Excel output similar to this
=SUM(B5:B9)+0+(SUM(B18:B21))+0+SUM(B30:B32)