Conditional Excel file formatting using ColdFusion

Question

I am dynamically creating a table using the cfscript spreadsheet method.

i.e.

<cfscript> downloadDoc = spreadsheetNew("spreadSheetName"); spreadsheetAddRow(downloadDoc,"spreadsheetCols"); .... </cfscript> 

One of the columns I'm building contains a formula showing the percentage difference between the values ​​that the user enters in an empty column and the current value (which is in another column).

The user I create for this asked to add conditional formatting to change the color of the formula cell based on the value (i.e. if the change exceeds 20% or less than -20%, the cell should be red). Since one of the values ​​that affects the formula is entered by the user, the color change should occur in Excel, not in my function.

Easy in Excel, just not sure how to do this in an Excel file created by cfml.

My question is: does anyone know if this is possible using cfml (or via cfscript or the cfspreadsheet tag) and how to do it?

I could not find something on Google, and the cfdocs.org search did not change anything.

Answer 1

Good news! This can be done (although not in CF10, the POI version with this is too low). Since you are on CF11, this will help you. This particular demonstration does something more than 100 reds.

 <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)> 

Taken from combination

• ConditionalFormats.java and
• HSSFConditionalFormatting.html

(but note that the examples given in the latter do not really work)