POI's formula evaluator can only evaluate cells. Neither HSSF nor XSSF can
determine if a cell meets its conditional formatting criteria. It would
involve evaluation of the format formula which is not yet supported. A
usermodel API for conditional formatting in XSSF is a planned improvement.
Patches are welcome.
Basically, to determine if a cell is highlighted you need to evaluate a
boolean formula:
formula: cellValue operator expression
Consider the following conditional formatting rule:
<conditionalFormatting sqref="E3:E9">
<cfRule type="cellIs" dxfId="1" operator="greaterThan">
<formula>0.5</formula>
</cfRule>
</conditionalFormatting>
The formula to evaluate is "cell.getNumericCellValue() > 0.5"
Perhaps, the easiest way to evaluate it is to create a dummy formula cell
somewhere not in the client area:
Cell dummyCell = worksheet.createRow(1000000).createCell(0); // assume that
the 1000000th row is never used
dummyCell.setCellFormula(cell.getNumericCellValue() + "> 0.5");
//evaluate
boolean isFormatted = evaluator.evaluate(dummyCell).getBooleanValue();
if(isFormatted ) //bingo! {
}
//cleanup
worksheet.getRow(1000000).removeCell(0);
Note the dxfId="1" attribute of the cfRule bean. It is a format index in the
style table and you should be able to get the font information as follows:
CellStyle style = workbook.getCellStyle(dxfId);
Yegor
On Thu, Mar 31, 2011 at 9:51 PM, Filip Defoort
<[email protected]>wrote:
> Hello,
>
> I'm trying to update my excel viewer so it display conditional
> formatting properly. That's pretty easy to do for HSSF (actually do
> have one remaining problem there as well - see below), but it seems to
> be a bit trickier for XSSF so I'm hoping someone can give me some
> pointers (I'm still stuck at 3.6 for now, but from a quick code scan
> it seems it hasn't changed on 3.8-beta1):
>
> 1/ How do you best evaluate the formulas ? The normal
> XSSFFormulaEvaluator seems to only work for Cells and the formulas
> associated with conditional formatting aren't cell formulas. I can
> parse the formula in its parts via FormulaParser, but then I'm
> reimplementing the whole evaluation part of the individual Ptg's which
> seems wrong. How do I evaluate the Formula Ptg's directly ?
>
> 2/ I'm getting to the conditional formatting via
> sheet.getCTWorksheet().getConditionalFormattingArray(). That seems to
> be a pretty low level approach - the HSSF variant is a nicer
> sheet.getSheetConditionalFormatting().
>
> 3/ How do I figure out what to actually do once I determine that a
> rule matches? With HSSF that's rule.getFontFormatting() and the likes,
> but I'm not finding the equivalent in CTCfRule. I'm finding Databar
> and ColorScale but both of those are null in my test formats, so it's
> gotta be something else...
>
> Thanks much for any hints!
>
> - Filip
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [email protected]
> For additional commands, e-mail: [email protected]
>
>
