Tuesday, January 26, 2016

Conditional Formatting Excel File Using ColdFusion

Good question off Stack Overflow. CF provides functions to create Excel spreadsheets, change the formatting of a cell, set formulas, etc. But what about Conditional Formatting, where you want Excel to dynamically highlight a cell iff the value is outside a given range? Here, you need to dip into the underlying Apache POI library that CF hooks into.

The following will only work with CF11; earlier versions do not include a new enough version of POI. This example will color cells A1:A6 in red if the value in them is greater than 100. Assume downloadDoc is the object returned by SpreadsheetNew(), after you've filled in the data.

<cfset var poiSheet = downloadDoc.getWorkBook().getSheet("Sheet1")>
<cfset poiSheet.setFitToPage(true)>

<cfset comparison = CreateObject("java", "org.apache.poi.ss.usermodel.ComparisonOperator")>

<cfset rule = poiSheet.getSheetConditionalFormatting().createConditionalFormattingRule( comparison.GE, "100.0", javacast("null", ""))>
<cfset patternFmt = rule.createPatternFormatting()>
<cfset color = CreateObject("java", "org.apache.poi.ss.usermodel.IndexedColors")>

<cfset patternFmt.setFillBackgroundColor(javacast("short", color.RED.index))>

<cfset cellRangeAddress = CreateObject("java", "org.apache.poi.ss.util.CellRangeAddress")>
<cfset regions = [ cellRangeAddress.valueOf("A1:A6") ]>
<cfset poiSheet.getSheetConditionalFormatting().addConditionalFormatting(regions, rule)>

For your own purposes,You will probably want to modify the lines I have placed in italics. the assignment to rule defines the condition under which a cell gets highlighted. The call to patternFmt.setFillBackgroundColor sets the color that is displayed (available colors are given here). Finally, the assignment to regions tells the sheet which cells should be conditionally formatted.

No comments:

Post a Comment