如何从excel 2007获取条件格式信息？

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.

我必须比较两个不同的excel文件,并且我从XSSFConditionalFormattingRule方法获得0或null值,而HSSFConditionalFormattingRule方法工作正常。

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

这是.xls和.xlsx文件的结果(两者都具有相同的条件格式)。

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

1 个解决方案

#1

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

你有两个问题 - 一个,你使用的是Apache POI的旧版本,另外两个是你用错误的方法来获取颜色

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

目前,您对rule.getPatternFormatting()。getFillBackgroundColor()的调用将返回颜色的索引,如果颜色未编入索引,则返回0。 HSSF中的几乎所有条件格式颜色都被编入索引,在XSSF中很少。因此,对于大多数XSSF文件,如果你要求索引颜色,你只需要得到0.相反,你需要调用rule.getPatternFormatting()。getFillBackgroundColorColor()返回一个颜色对象。从那里,您可以获得描述颜色的十六进制字符串,索引与否

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.

此外,如前所述,您的Apache POI版本对于其中一些来说太旧了。自2015-07-20以来,您需要升级到至少3.13 beta 1,或理想情况下升级3.13 beta 2 / nightly。

#1