Hi,
I know that can be done successfully using POI 3.5.
You are missing a step try the change below. You may want to adjust
the arguments to createConditionalFormattingRule
http://poi.apache.org/apidocs/org/apache/poi/hssf/usermodel/HSSFSheetConditionalFormatting.html#createConditionalFormattingRule%28byte,%20java.lang.String,%20java.lang.String%29
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=
scf.createConditionalFormattingRule(formula);
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);
I hope this helps.
Regards,
Dave
On Dec 30, 2009, at 10:34 AM, Mahesh Ganapathy wrote:
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: [email protected]
For additional commands, e-mail: [email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
