使用ColdFusion条件格式化Excel文件

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

我正在使用cfscript spreadsheetNew方法动态构建电子表格。

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.

我正在构建此用户请求我添加条件格式以根据值更改公式单元格的颜色（即，如果更改大于20％或小于-20％，则单元格应为红色）。由于影响公式的其中一个值是由用户键入的，因此颜色更改需要在Excel中进行，而不是在我的函数中进行。

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

它在Excel中很容易，只是不确定如何将它构建到cfml生成的Excel文件中。

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

我的问题是，有没有人知道这是否可以使用cfml（通过cfscript或cfspreadsheet标签）以及如何做到这一点？

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

我无法找到任何谷歌搜索，搜索cfdocs.org并没有改变任何东西。

1 个解决方案

#1

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.

好消息！它可以完成（虽然不是在CF10中;随附的POI版本太低）。因为您使用的是CF11，所以它将为您提供最大的便利。这个特殊的演示会变成大于100的红色。

<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

取自组合

• ConditionalFormats.java and
• ConditionalFormats.java和
• HSSFConditionalFormatting.html
• HSSFConditionalFormatting.html

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

（但请注意，后者给出的例子并不真正有用）