Using cfspreadsheet in CF9


The CFSPREADSHEET function introduced in ColdFusion 9 is a great time saver.  As anyone can attest to spreadsheets reign supreme in business.  Before CF 9, our team used Ben Nadel’s POI Utility Component for most of our spreadsheets.  It worked great for all of our CF 8 applications, but the lack of functionality – that CF 9 has – started to show.

CFSPREADSHEET is not without its own problems…  We have discovered some issues with formatting on multiple sheets,  for example the currency format only works on the first sheet – ColdFusion Beyond has a nice post about that.  Another issue is with the SpreadsheetAddColumns function.

One of our clients wanted a multiple worksheet download.  This download consists of registrant’s contact information, registration details, questions, and payments collected. Nothing too complicated in the request.  Three of the worksheets were just basic queries that were run through the SpreadsheetAddRows function.  For the worksheet with the questions, I used the SpreadsheetAddColumns function which gave me some grief.

The application is designed to only record registrant’s answered questions.  So if the registrant did not answer the question, the system needs to append a ‘null’ to that cell.  The layout of the sheet is as follows: registration id, first name, last name, questions used for the event.  To pipe of the information to the spreadsheet I tried the following:

	<cfquery 
		name="qWorksheet" 
		dbtype="query">
		
		SELECT registrationid, firstname, lastname
		FROM qMyRoster
		ORDER BY lastname, firstname, registrationid
	</cfquery>
	
	<cfloop query="qMyQuestions">
		
		<cfset aQuestionAnswers = arrayNew(1) />
		<cfset arrayAppend(aRegistrationColumns,questiontext) />
		
		<!--- Filling in answers for each registrant --->
		<cfloop query="qWorksheet">
			<cfquery name="qQuestionDetails" dbtype="query">
				SELECT answer
				FROM qMyRegistrationDetails
				WHERE registrations_registrationid = #registrationid#
					AND questionid = #qMyQuestions.questionid#
			</cfquery>
			
			<cfset arrayAppend(aQuestionAnswers,qQuestionDetails.answer) />
			</cfloop>
				<cfset arrayAppend(aRegistrationAnswers,aQuestionAnswers) />
		</cfloop>
 
		<cfset SpreadsheetAddColumn(oRegistrationSpreadsheet, ValueList(qWorksheet.registrationid),2,1,false) />
		<cfset SpreadsheetAddColumn(oRegistrationSpreadsheet, QuotedValueList(qWorksheet.firstname),2,2,false) />
		<cfset SpreadsheetAddColumn(oRegistrationSpreadsheet, ValueList(qWorksheet.lastname),2,3,false) />
		
		<cfloop from="1" to="#ArrayLen(aRegistrationAnswers)#" index="counter">
			<cfset nextColumn = counter + 3 />
			<cfset newColumnData = aRegistrationAnswers[counter] />
			
			<cfset SpreadsheetAddColumn(oRegistrationSpreadsheet, ArrayToList(newColumnData), 2, nextColumn, false) />
		</cfloop>

Figure 1

This is where I started getting into issues with SpreadsheetAddColumns.  This function requires the data to be a comma delimited list, it will not take anything else.  As I looped through the array I used ValueList add data points to the column. That worked until the data stored also had commas.  Of course I could strip out all those commas, but that would be altering the data collected.  So instead I reworked the code:

	<cfset currentStructureID = 0 />
	<cfset aRegistrationColumns = ArrayNew(1) />
	<cfset aRegistrationAnswers = ArrayNew(1) />
	
	<cfquery name="qWorksheet" dbtype="query">
		SELECT registrationid, firstname, lastname
		FROM qMyRoster
		ORDER BY lastname, firstname, registrationid
	</cfquery>
 
	<cfloop query="qMyQuestions">
		<cfset aQuestionAnswers = arrayNew(1) />
		<cfset arrayAppend(aRegistrationColumns,questiontext) />
		
		<cfloop query="qWorksheet">
			<cfquery name="qQuestionDetails" dbtype="query">
				SELECT answer
				FROM qMyRegistrationDetails
				WHERE registrations_registrationid = #registrationid#
					AND questionid = #qMyQuestions.questionid#
			</cfquery>
		
			<cfset excelCellContent = ValueList(qQuestionDetails.answer,";") />
			<cfset excelCellContent = Replace(excelCellContent, ";", "; ", "ALL") />
			
			<cfset arrayAppend(aQuestionAnswers,excelCellContent) />
		</cfloop>
		
		<cfset arrayAppend(aRegistrationAnswers,aQuestionAnswers) />
	</cfloop>

Figure 2 Line 24 and 25 are used to help visual seperate answers for any multiple choice – select many questions.

After the loop completes I will have this array.

Figure 3

Now it’s time to build my spreadsheet

	<cfset RegistrationColumns = "Registration ID, First Name, Last Name," & ArrayToList(aRegistrationColumns,",")/>
	<cfset oSpreadsheet = SpreadsheetNew("Details", true) />
	<cfset SpreadsheetAddRow(oSpreadsheet, RegistrationColumns) />
	
	<cfset SpreadsheetAddColumn(oSpreadsheet, ValueList(qWorksheet.registrationid),2,1,false) />
	<cfset SpreadsheetAddColumn(oSpreadsheet, QuotedValueList(qWorksheet.firstname),2,2,false) />
	<cfset SpreadsheetAddColumn(oSpreadsheet, ValueList(qWorksheet.lastname),2,3,false) />
	
	<cfloop from="1" to="#ArrayLen(aRegistrationAnswers)#" index="counter">
	<cfset nextColumn = counter + 3 />
	<cfset newColumnData = aRegistrationAnswers[counter] />
	
	<cfset currentRow = 2 />
	<cfloop array="#newColumnData#" index="thisRow">
		<cfset SpreadsheetSetCellValue(oSpreadsheet,thisRow,currentRow,nextColumn) />
		<cfset currentRow += 1 />
	</cfloop>
</cfloop>

By using the SpreadsheetSetCellValue function I am able to pipe in any type of data collected.

Leave a Reply