We used to blog more before twitter came along.
CFSpreadsheet and Conditional Formatting
CFSpreadsheet pretty much rocks the house when it comes to simplifying the task of creating spreadsheets (Excel and Open Office compatible) from ColdFusion. On Thursday January 14 I'll be giving a talk at the Philadelphia CFUG meeting on this topic, but something I was working on last night has me particularly excited and requires a blog post.
Several of the spreadsheets we currently generate for clients have some degree of pretty formatting associated with them. The way we've typically done it is to export a decorated "source" Excel file in Excel's XML spreadsheet format, chop it up and jack the appropriate data into the document, setting style attributes on the cell tags appropriately based on server-side logic. But what if you wanted to create the spreadsheet such that the conditional logic was built in to the spreadsheet itself? The people that use these spreadsheets like to play with the data and it would be awesome if the conditional formatting (highlight in some color, change font size, whatever you like) would apply to their edited data.
It didn't seem immediately possible using CFSpreadsheet or any of the 37(!) functions exposed in ColdFusion...but then I remembered that this functionality is built on top of Apache POI, so there had to be a way to get down and dirty with the Java internals. It's not hard to get there, but whoa the code to implement conditional formatting is definitely verbose.
What I want to do is set up a conditional formatting rule on the 6th column (F) such that if the value is greater than 10 the background is set to yellow.
<!--- get a handle on the underlying POI object --->
<cfset objWorkbook = objSS.getWorkbook() />
<cfset objSheet = objWorkbook.getSheetAt(objWorkbook.getActiveSheetIndex()) />
<!--- get a handle on the conditional formatting property of the sheet --->
<cfset objCF = objSheet.getSheetConditionalFormatting() />
<!--- now add the format rule that we want on the doc --->
<!--- formatting rule:
<cfset HSSFConditionalFormattingRule = objCF.createConditionalFormattingRule(
5, <!--- org.apache.poi.hssf.record.CFRuleRecord.ComparisonOperator.GT --->
"10", <!--- the value i want the cell to be greater than for the format to kick in --->
javacast("null", "") <!--- 2nd formula is not used for GT/GE --->
) />
<!--- Create yellow background format definition --->
<cfset HSSFPatternFormatting = HSSFConditionalFormattingRule.createPatternFormatting() />
<cfset HSSFPatternFormatting.setFillBackgroundColor(createObject('java', 'org.apache.poi.hssf.util.HSSFColor$LIGHT_YELLOW').getIndex()) />
<!--- Define a region that only impacts the "tenure" column (which we know to be the 5th (zero based)) --->
<cfset regions = arrayNew(1) />
<cfset regions[1] = createObject('java', 'org.apache.poi.ss.util.CellRangeAddress').init(1,1000,5,5) />
<!--- CellRangeAddress(firstrow, lastrow, firstcol, lastcol) --->
<!--- Apply Conditional Formatting rule defined above to the regions --->
<cfset objCF.addConditionalFormatting(regions, HSSFConditionalFormattingRule) />
Then it's just a matter of inserting the spreadsheet contents as usual.
<cfquery name="qFaculty" datasource="#application.datasource#">
select
facultyID, pidm, loginID, firstName, lastName, startYear, department, title
from
coe_faculty
order by
lastName
</cfquery>
<!--- headers are considered friendly --->
<cfset spreadsheetAddRow(objSS, "ID,PIDM,Login,First Name,Last Name, tenure, startYear, title, department") />
<!--- i hate columns that show up as #### or truncated --->
<cfset spreadsheetSetColumnWidth(objSS, 2, 13) />
<cfset spreadsheetSetColumnWidth(objSS, 3, 8) />
<cfset spreadsheetSetColumnWidth(objSS, 8, 20) />
<cfset spreadsheetSetColumnWidth(objSS, 9, 25) />
<!--- loop over the data - calc the tenure and style accordingly --->
<cfloop query="qFaculty" startrow="2">
<cfset excelFormula = "(year(today())-G" & qFaculty.currentRow & ")" />
<cfset spreadsheetSetCellValue(objSS, qFaculty.facultyID , qFaculty.currentRow, 1) />
<cfset spreadsheetSetCellValue(objSS, qFaculty.pidm, qFaculty.currentRow, 2) />
<cfset spreadsheetSetCellValue(objSS, qFaculty.loginID, qFaculty.currentRow, 3) />
<cfset spreadsheetSetCellValue(objSS, qFaculty.firstName, qFaculty.currentRow, 4) />
<cfset spreadsheetSetCellValue(objSS, qFaculty.lastName, qFaculty.currentRow, 5) />
<cfset spreadsheetSetCellFormula(objSS, excelFormula, qFaculty.currentRow, 6) />
<cfset spreadsheetSetCellValue(objSS, qFaculty.startYear, qFaculty.currentRow, 7) />
<cfset spreadsheetSetCellValue(objSS, qFaculty.title, qFaculty.currentRow, 8) />
<cfset spreadsheetSetCellValue(objSS, qFaculty.department, qFaculty.currentRow, 9) />
</cfloop>
<!--- append the worksheet to a new spreadsheet file --->
<cfspreadsheet action="write" filename="#request.rootDirectory#\sheets\faculty_style_cf.xls" name="objSS" sheet="1" overwrite="true" />
The end result:
Checking the conditional formatting panel in Excel we see that it's legit and not hard coded.
I'll post more as I dig in and build more complex examples. This is a simple expression, but it should be enough to get you started if you need to do this kind of thing with ColdFusion and a generated spreadsheet!
About the blog
Steve Rittler is the President of CounterMarch Systems and former manager of both the Philadelphia ColdFusion User Group and the Philadelphia Developer Network.
You can connect with me on Twitter and LinkedIn.
Strange issue I'm having here with CFSPREADSHEET. I'm reading an XLS file into memory. however one column which just happens to be the important integer ID column is not reading as it looks in Excel.
When I open the file in Excel a sample integer column says 101131192 for the ID column
However when it is read in by cfspreadsheet, the value looks like this: 1.01131192E8
I tried parsing out the period as well as only pull the left 9 digits, but then if the number ends in 0, I have the letter E at the end. I can also write a function to fix that, but my gut is telling me something's not right here.
Some more examples:
101362753 reads into memory as 1.01362753E8
And if there happens to be a 0 at the end of the integer then it leaves it off completely.
101588770 reads into memory as 1.0158877E8 (with the 0 completely chopped off)
I'm using CF 9.0.1
That's legit - just CF reading it as a number and then displaying it in scientific notation. Like you said, if you really need to display it otherwise, simply use numberFormat(...) to output it however you like.
Steve
Awesome..thanks! I've used numberformat a thosand times before but when I started seeing letters I thought there were issues. Thx again. For others this is what I did to show the 9 digits without commas using numberformat:
numberformat(id9,'_________')