You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by Filip Defoort <fi...@cirquedigital.com> on 2011/03/31 19:51:44 UTC
Conditional formatting for XSSF
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: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Conditional formatting for XSSF
Posted by Yegor Kozlov <ye...@dinom.ru>.
>
>
> >
> > 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);
> >
>
> I was digging more and found the dxfId, but I wasn't sure how to get
> to the style. Are you sure about the workbook.getCellStyle() ? I don't
> seem to get the right style this way.
>
> The long way seems to work:
>
> CTDxf dxf =
> sheet.getWorkbook().getStylesSource().getCTStylesheet().getDxfs().getDxfArray((int)
> rule.getDxfId());
>
>
Correct.
Sorry for the confusion, I mixed up the "s" and "dxfId' attributes. The
first one indicates the index of this cell's style. The dxfId attribute is
for "differential formatting index " and it points to a CTDxf element,
exactly as in your code snippet. The dxf formatting is to be applied on top
of or in addition to any formatting already present on the cell, that is, to
display a cell you need to first apply this cell's style and then calculate
conditional formatting and apply the dxf style.
Yegor
Re: Conditional formatting for XSSF
Posted by Filip Defoort <fi...@cirquedigital.com>.
Hi,
On Thu, Mar 31, 2011 at 11:32 AM, Yegor Kozlov <ye...@dinom.ru> wrote:
> 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.
..
>
> 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:
Yes, I was afraid of that, but it's probably the easiest way to avoid
duplicating a whole bunch of code.
>
> 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);
>
I was digging more and found the dxfId, but I wasn't sure how to get
to the style. Are you sure about the workbook.getCellStyle() ? I don't
seem to get the right style this way.
The long way seems to work:
CTDxf dxf = sheet.getWorkbook().getStylesSource().getCTStylesheet().getDxfs().getDxfArray((int)
rule.getDxfId());
but it's a bit of a detour...
Thanks for you help,
- Filip
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Conditional formatting for XSSF
Posted by Yegor Kozlov <ye...@dinom.ru>.
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
<fi...@cirquedigital.com>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: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>