poi-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Mahesh Ganapathy <mgan...@gmail.com>
Subject Conditional Formatting using POI
Date Wed, 30 Dec 2009 18:34:27 GMT
Hello All,
I have been using POI for almost 2 years now and never had to go
beyond the basic file manipulation. However, I have been tasked with
the need to produce an excel file that has dependent drop down lists
with conditional formatting.

Using POI 3.2 Final, I have been successful in creating an excel file
with dependent drop down lists using named ranges and indirect
references. However, conditional formatting still poses a challenge.
The POI documentation (javadocs) mention
HSSFSheet#getConditionalFormattingAt(int); and
sheet.addConditionalFormatting(regions, rule);
However, these API's are not present in the HSSFSheet class. I did
look around and found HSSFSheetConditionalFormatting class that
provides the API's mentioned in the javadoc examples. However, I am
still not able to get the conditional formatting implemented.

I am trying to set the background of a cell to be red when a previous
cell has a specific value such as "Contracts". I tried setting
conditional formatting using excel and it works. I would have to use
the formula similar to $C$2="Contracts".

My code is as below:

scf = m_dataSheet.getSheetConditionalFormatting();

// This would return $C$2, $C$3 so on
CellReference invalidCellRef = new CellReference(rowNumber,
depCellColNum , true, true);

// The formula would then be $C$2="Contracts"
String formula = invalidCellRef.formatAsString() +"=" + compareString;

HSSFConditionalFormattingRule rule=HSSFPatternFormatting patternFmt =
rule.createPatternFormatting();
patternFmt.setFillBackgroundColor(HSSFColor.RED.index);

HSSFFontFormatting fontFmt = rule.createFontFormatting();
fontFmt.setStrikeout(true);
CellRangeAddress[] cra = {new CellRangeAddress(rowNumber, rowNumber,
startCol, endCol) };
scf.addConditionalFormatting(cra, rule);

With this code I do not get any errors in executing the program but
the generated excel file does not have the conditional formatting
applied on the cells. Also, when I open the file in excel, select this
cell and try to view the conditional formatting (Format -->
Conditional Formatting) nothing happens, excel does not open the
conditional formatting box.

Can any one please help me with this issue?

---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org


Mime
View raw message