Skip to content
Advertisement

Conditional formatting highlight only invalid cells

I am creating below conditional formatting rule in Apache POI.

Issue

Expected:

  • The formula should highlight only invalid values in column ‘J’.

Actual:

  • But all values of the entire column ‘J’ get highlighted, even for valid values.

Formula

JavaScript

Full Code

JavaScript

Required manual post-production in Excel

Now I did the below steps

  1. Add three values in column ‘J’ named “State”. All cells are getting highlighted, see image below:

all cells get highlighted, no matter if valid

  1. Click Conditional Formatting > Manage Rules > Select Rule (Format Red) > Edit Rules > OK > Apply > OK

After this action the column values are getting highlighted correctly.

enter image description here

Question

Is there anything I missed in my Java code? How do I avoid this manual activity in Excel?

Advertisement

Answer

Short answer

The problem is with the FILTERXML function used in your conditional formatting. This should be prefixed _xlfn in XML sheet storage. To make it work your formula should be changed to following:

JavaScript

Reason

FILTERXML function was introduced in Excel 2013. So it was introduced after Microsoft had published the Office Open XML file format for Microsoft Office files in 2006 for Office 2007. To mark such later introduced functions, Microsoft decided to prefix them with _xlfn in XML storage. After reading the XML, the Excel GUI knows whether the function is known in that Excel version or not. If yes, the prefix gets removed and the function name gets localized (e.g. in German Excel: to XMLFILTER). If not, the prefix remains and the user also knows that this function is unknown in that Excel version. See Issue: An _xlfn. prefix is displayed in front of a formula.

Why we need the storage formula version?

In Apache POI Cell.setCellFormula as well as SheetConditionalFormatting.createConditionalFormattingRule sets the formula string into the XML directly. So that string must be exactly as it shall be stored in XML.

Learn more about this by inspecting the file-formats

Office Open XML files, and so also *.xlsx files, are ZIP archives containing XML files and other files into a specific directory structure. So one simply can unzip a *.xlsx file to have a look into.

So after creating your color.xlsx using your code, unzip it and have a look into xl/worksheets/sheet1.xml.

You will find something like:

JavaScript

And this does not work in Excel.

Now open the color.xlsx in Excel and make it work. Then save the the color.xlsx, unzip it again and have a look into xl/worksheets/sheet1.xml.

You will find something like:

JavaScript

This shows the _xlfn prefix before FILTERXML in formula string.

It also shows that a totally different version of conditional formatting is used. But that’s a secondary problem. The prefixed version of the formula string also works using as formula string within the old version of conditionalFormatting. But, of course that is not guaranteed in all cases. So to be on save side for all cases, one of two possibilities must be given:

  • either one avoids using all Excel functions introduced after publishing Office Open XML in 2006 (Office 2007)
  • or Apache POI has to fully support all changes Microsoft made after publishing Office Open XML in 2006 (Office 2007).

To fulfill the latter Apache POI is far away. It not even supports all features of Office 2007 up to now.

User contributions licensed under: CC BY-SA
4 People found this is helpful
Advertisement