Search code examples
excelcoldfusionapache-poicoldfusion-11cfspreadsheet

Conditional Formatting Excel File Using ColdFusion

I'm building a spreadsheet dynamically using the cfscript spreadsheetNew method.

i.e.

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

One of the columns I'm building contains a formula to show the percent difference between values that a user keys into a blank column and the current value (which is in a different column).

The user I'm building this for requested that I add conditional formatting to change the color of the formula cell based on the value (i.e. if the change is greater than 20% or less than -20% the cell should be red). Since one of the values that affects the formula is keyed in by the user, the color change will need to occur in Excel, not in my function.

It's easy in Excel, just not sure how to build this into an Excel file that is generated by cfml. enter image description here

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

I wasn't able to find anything Googling this, and a search of cfdocs.org didn't turn anything up.

Solution

  • Good news! It can be done (though not in CF10; the version of POI shipped with that is too low). Since you're on CF11, this will get you most of the way there. This particular demo turns anything greater than 100 red.

    <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 a combination of

    (but note that the examples given in the latter don't really work)