Jxls-2 supports standard Excel formulas and does not require any special syntax when using them in a report template (as opposed to Jxls 1.x).

To render formulas in a XLS Area you have to invoke `processFormulas()` method of XlsArea instance after the main transformation is done. On invoking `processFormulas()` method Jxls engine will process and render all the template formulas updating them as necessary to count for possible cells shifting and collections expanding.

The main Java code may look like this

Area xlsArea; Context context; // construct XLS Area and set it into xlsArea var // ... // fill in context var with data // ... // apply XLS Area at A1 cell of 'Result' sheet xlsArea.applyAt(new CellRef("Result!A1"), context); // process area formulas xlsArea.processFormulas(); // save excel output // ...

The line `xlsArea.processFormulas()` does all the formulas processing job.

To see how formulas look in a template file let’s take a template from Excel formulas example.

You can download it from here.

The screenshot of the template is below

As you can see there are three formula cells in the template `E4`, `C5`, `E5` .

Cell `E4` is itself a part of `jx:each` command area and contains a formula `=C4*(1+D4)`.

Both `C4` and `D4` cells are part of the same `jx:each` command area.

After jx:each transformation the area `A4:E4` will be expanded into multiple rows.

The original formula `=C4*(1+D4)` will be modified accordingly for each new row so that we will get formulas like `=C5*(1+D5)`, `=C6*(1+D6)` and so on.

The summation formula in cell `C5` `=SUM(C4)` references cell `C4` from inside `jx:each` command area.

It means that after transformation and formulas processing the reference to cell `C4` will be replaced with a range like `SUM(C4:C8)`.

Same goes for cell `E5` with formula `=SUM(E4)`.

The final excel output is here and also can be seen on the following screenshot

By default Jxls uses *FastFormulaProcessor* to process formulas in the template when *processFormulas()* method is invoked. This classes uses simplified formula processing algorithm and works pretty fast.

But in some cases when there is a complex template or non-trivial processing it can produce invalid results. In this case you should switch to *StandardFormulaProcessor* which uses a different formulas processing algorithm and works reliably.

To switch to *StandardFormulaProcessor* use the following code

xlsArea.setFormulaProcessor(new StandardFormulaProcessor());

The formula processor must be set before you invoke *processFormulas()* method.

Please note that *StandardFormulaProcessing* performs about 10 times slower than *FastFormulaProcessing* so in case you are processing thousands of formulas (e.g. you have a formula inside *each-command* area which processes huge collection) you may see a decrease in performance.

If the cells participating in a formula calculation are removed during processing then the formula value can become corrupted or undefined. To avoid this situation starting from v.2.2.8 Jxls sets such formula to *=0*. To use custom default value for such formulas use *jx:params* comment to set *defaultValue* property. For example

jx:params(defaultValue="1")

This sets the default formula value to 1.

Parameterized formula allows you to use context variables in the formula.

To set a parameterized formula you have to enclose it into `$[` and `]` symbols and each formula variable must be enclosed in `$\{` and `}` symbols. For example `$[SUM(E4) * ${bonus}]` . Here we use ‘bonus’ context variable in the formula. During the `processFormulas()` Jxls will substitute all the variables with values from the context.

To see this in action please take a look at Parameterized formulas example

While performing an area transformation Jxls keeps track of all the processed cells so that it knows what are the target cells for each particular source cell. If you do not have or do not need to process the formulas then it makes sense to disable this functionality to save some memory. This can be done by setting the following configuration parameter into the context config e.g.

Context context = new PoiContext(); context.getConfig().setIsFormulaProcessingRequired(false);