Excel Formulas

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).

Java Code

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.

Formulas Template

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

Excel formulas template

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

Formulas output

Formula Processor

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.

Default Formula value

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 Formulas

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

Cell reference tracking

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);