5

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.

James A Mohler
  • 11,060
  • 15
  • 46
  • 72
kuhl
  • 172
  • 1
  • 11
  • 2
    It's possible. I suggest taking baby steps. First, if you don't already know how to format spreadsheet cells, learn to do so. Once you have the hang of that, the conditional logic will be simpler. – Dan Bracuk Jan 26 '16 at 16:46
  • 1
    For just one user, I'd skip ColdFusion altogether and write a macro for him. – Dan Bracuk Jan 26 '16 at 16:48
  • 3
    You will probably need to dip into Apache POI to accomplish this. CFML exposes only very limited functionality from that library. – Tim Jasko Jan 26 '16 at 16:50
  • 1
    (Edit) Yes, CF does not support conditional formatting AFAIK. You will have to use alternative methods, such as POI. *if the change is greater than 20% or less than -20%* What should happen if the value is neither, for example: 10%? – Leigh Jan 26 '16 at 17:10
  • @Leigh Either no color, or green – kuhl Jan 26 '16 at 17:20
  • @DanBracuk I understand how to format spreadsheet cells using spreadsheetFormatCell and the like, the issue is the dynamic conditional formatting that changes upon user input. – kuhl Jan 26 '16 at 17:22
  • So, you want to create a spreadsheet using ColdFusion that changes the formatting based on values the user enters when they open the spreadsheet in Excel? – Scott Stroz Jan 26 '16 at 17:44

1 Answers1

5

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)

Leigh
  • 28,765
  • 10
  • 55
  • 103
Tim Jasko
  • 1,532
  • 1
  • 8
  • 12