0

I have to compare two different excel files and I keep getting 0 or null values from XSSFConditionalFormattingRule methods while HSSFConditionalFormattingRule methods are working fine.

Here is the result from .xls and .xlsx file (both have the same conditional formattings).

2. CONDITIONAL FORMATTING DIFFERENCES

FORMULA1: 
Sheet1(0) Rule 0 IS:  SHOULD BE: IF(INT(COUNT($C$1:$C$7)*13%)>0,LARGE($C$1:$C$7,INT(COUNT($C$1:$C$7)*13%)),MAX($C$1:$C$7))<=A1

BCOLOR: 
Sheet1(0) Rule 0 IS: 0 SHOULD BE: 20

UNDERLINE: 
Sheet1(0) Rule 0 IS: 0 SHOULD BE: 255
Sheet1(0) Rule 0 IS: 0 SHOULD BE: 255
Sheet1(0) Rule 1 IS: 0 SHOULD BE: 255

LCOLOR: 
Sheet1(0) Rule 0 IS: 0 SHOULD BE: 20

FOREGROUND: 
Sheet1(0) Rule 0 IS: 0 SHOULD BE: 64
Sheet1(0) Rule 0 IS: 0 SHOULD BE: 64
Sheet1(0) Rule 1 IS: 0 SHOULD BE: 64

TYPE: 
Sheet1(0) Rule 0 IS: 0 SHOULD BE: 2

ESCAPMENT TYPE: 
Sheet1(0) Rule 0 IS: 0 SHOULD BE: -1
Sheet1(0) Rule 0 IS: 0 SHOULD BE: -1
Sheet1(0) Rule 1 IS: 0 SHOULD BE: -1

TCOLOR: 
Sheet1(0) Rule 0 IS: 0 SHOULD BE: 20

BACKCOLOR: 
Sheet1(0) Rule 0 IS: 0 SHOULD BE: 45
Sheet1(0) Rule 0 IS: 0 SHOULD BE: 43
Sheet1(0) Rule 1 IS: 0 SHOULD BE: 43

FONT COLOR: 
Sheet1(0) Rule 0 IS: 0 SHOULD BE: 20
Sheet1(0) Rule 0 IS: 0 SHOULD BE: 60
Sheet1(0) Rule 1 IS: 0 SHOULD BE: 60

RCOLOR: 
Sheet1(0) Rule 0 IS: 0 SHOULD BE: 20
SzikakaPupu
  • 3
  • 4
  • What version of Apache POI are you trying this with? – Gagravarr Jul 29 '15 at 09:52
  • And how are you fetching the colours? – Gagravarr Jul 29 '15 at 09:52
  • I've been trying POI 3.10 - 3.12 `rule.getPatternFormatting().getFillBackgroundColor();` I didn't find any other method. I know it is just an index and there are different color palettes in excel 2003 and 2007 but it shouldn't return 0 all the time. – SzikakaPupu Jul 29 '15 at 10:44

1 Answers1

0

You have two problems - one, you're using an old copy of Apache POI, and two, you're calling the wrong method to get the colours

Currently, your call to rule.getPatternFormatting().getFillBackgroundColor() will return the index of the colour, or 0 if the colour isn't indexed. Almost all conditional formatting colours in HSSF are indexed, very few in XSSF are. As such, for most XSSF files, if you ask for the indexed colour you'll just get 0. Instead, you need to call rule.getPatternFormatting().getFillBackgroundColorColor() which returns a colour object. From that, you can get the hex string that describes the colour, indexed or not

So, you should change code like

int colourIndex = rule.getPatternFormatting().getFillBackgroundColor();
System.out.println("Colour index is " + colourIndex);

To instead be something like:

Color colour = rule.getPatternFormatting().getFillBackgroundColorColor();
if (colour instanceof ExtendedColor) {
   System.out.println("Colour is " + ((ExtendedColor)colour).getARGBHex());
} else {
   System.out.println("Colour is " + ((HSSFColor)colour).getHexString());
}

The updated code correctly fetches the colour object, indexed or not, then allows you to fetch the hex representation of it.

Also, as mentioned already, your version of Apache POI is too old for some of this. You need to upgrade to at least 3.13 beta 1, or ideally 3.13 beta 2 / nightly build since 2015-07-20.

Gagravarr
  • 47,320
  • 10
  • 111
  • 156
  • Where can I get 3.13 beta 2? I've tried 3.13 beta 1 and I can't use this line: `Color colour = rule.getPatternFormatting().getFillBackgroundColorColor();` and there is no `ExtendedColor` class to import. I managed to solve this because I have different functions for HSSF and XSSF format. I will be writing here about my progress. – SzikakaPupu Jul 29 '15 at 14:48
  • 3.13 beta 2 isn't out yet, so you'll need to use a nightly build until it is. The answer is designed to last for a long time! – Gagravarr Jul 29 '15 at 15:07
  • XSSF returns: "FFFFFFFF" and HSSF for the same colour returns 0:0:0. I got to a point where HSSF returns: "FF000000" but it's still completely different value because of custom colour palette (Excel 2003). Is there any solution? Other examples: "FFC00000" in excel 2007 equals "FF993300" in 2003. – SzikakaPupu Jul 31 '15 at 11:03
  • If you open the .xls file in Excel 97, I have a feeling you'll see the colour as different there. Can you try? – Gagravarr Jul 31 '15 at 11:43
  • Colours are exactly the same. I created .xlsx file with theme and standard colours and saved it as .xls and they have different hex values there. Same with creating .xls and saving it to .xlsx and also creating .xls and .xlsx separately. – SzikakaPupu Jul 31 '15 at 12:38
  • White is "FFFFFFFF" in .xlsx and "FF000000" or "0:0:0" in .xls. Maybe because it takes null instead of exact colour? But what about these differences in other colours? – SzikakaPupu Jul 31 '15 at 12:43
  • I've added some unit tests to Apache POI, and for my test themed spreadsheets it returns exactly the colours I'd expect. Any chance you could write a junit testcase that shows your problem, and upload it to the [Apache POI bug tracker](https://bz.apache.org/bugzilla/enter_bug.cgi?product=POI)? – Gagravarr Aug 09 '15 at 13:03