javaexcelapache-poijxls

JXLS 2.0 Conditional Formatting Forumla not updated per row

Trying to create an JXLS excel template where it should be possible to copy conditional formatting from a cell on a specific row to the next generated row.

In the template, I create my formatting. If the value in the cell is equal to "yes" the row should be red.

Template enter image description here

Conditional formatting

Formula: =$B2="yes"
Applies to: $A$2:$B$2

enter image description here

I know this formula works on an already populated excel sheet here is an example https://trumpexcel.com/highlight-rows-based-on-cell-value/

But when I do this with my excel template and JXLS 2.0 it fails. It copies the formula as it is to each new generated row. So instead of one condition for the whole sheet, there will now be as many as there are rows. The problem here is that it will copy it as is, which means that the formula in each condition will be based on the value in cell C2. So even if cell C3 is generated with the value "no" it will be red, since it is based on the value in C2.

Output excel enter image description here

Condition Formatting output excel enter image description here

Any tips on how to solve this directly in the template?

Using jxls 2.9.0 jxls-poi 2.9.0

Solution

  • One approach is to modify the formula in the template to acheive what we want.

    Formula: =INDIRECT("$B" & ROW())="yes"

    Description:

    1. ROW() returns the current row number.
    2. "$B" & ROW() gives the cell reference. For example, at row 5, we will get B5
    3. Finally, using INDIRECT(...) we get the value at cell reference and check if is "yes".

    Output excel: enter image description here