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.
There are no comments for this entry.
[Add Comment]