poi-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From David Fisher <dfis...@jmlafferty.com>
Subject Re: Conditional Formatting using POI
Date Wed, 30 Dec 2009 19:11:35 GMT
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: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>


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


Mime
View raw message