Let’s talk about XLS Exporting in Adobe Coldfusion 9 (Excel Spreadsheets). ColdFusion has this neat little tag called cfspreadsheet. It allows the writing of, updating of, and reading of CSV/XLS files in ColdFusion. This is a handy tool for exporting dynamically generated queries. It can make XLS Exporting in Adobe Coldfusion 9 very simple and easy to implement. For example, generating reports and listings of information. Adobe documented the cfspreadsheet tag as such:
Description 1. Manages Excel spreadsheet files 2. Reads a sheet from a spreadsheet file and stores it in a ColdFusion spreadsheet object, query, CSV string, or HTML string. 3. Writes single sheet to a new XLS file from a query, ColdFusion spreadsheet object, or CSV string variable. 4. Add a sheet to an existing XLS file.
The description as outlined above shows that this tag is very useful, and can write XLS files from cfquery objects, csv string variables, or html strings. You can also write a custom ColdFusion function to upload an XLS file, read the content, and input it into a database. However, XLS Exporting in Adobe Coldfusion 9 has its downsides.
The problems with using the cfspreadsheet tag arise when your code logic that builds a ColdFusion spreadsheet object becomes lengthy. When manipulating a ColdFusion spreadsheet object, it slows down your code exponentially. This means the more you have to process, the longer it takes to do so. I got to the point where I could pull a 10,000 record cfquery from the database in 3-4 seconds, but it would take me about 4-5 minutes to put together the ColdFusion spreadsheet object and formatting it the way I wanted it.
Below is an example of a lengthy ColdFusion spreadsheet object being put together and exported to an .xls file.
<cfscript>
theDir=GetDirectoryFromPath(GetCurrentTemplatePath());
theFile=theDir & "ExampleReport.xls";
theSheet = SpreadsheetNew("testSpreadsheet");
</cfscript>
<cfset spacer = ",,"><cfset newTitle = "Event / Ticket Type,Quantity">
<cfset SpreadsheetAddRow(theSheet,newTitle)><cfset tempCITY = ''>
<cfset tempTIME = ''>
<cfset totalTicketsArray = ArrayNew(1) />
<cfloop query="collection.TICKETTYPES"><cfif #CITYNAME# NEQ tempCITY OR #PERFORMANCETIME# NEQ tempTIME>
<cfset tempCITY = #CITYNAME#>
<cfset tempTIME = #PERFORMANCETIME#><cfif currentrow NEQ '1'>
<cfset arraySum = "Total Tickets: ,#REReplace(numberFormat(arraySum(totalTicketsArray)), ",", "", "All")#" />
<cfset SpreadsheetAddRow(theSheet,arraySum)>
<cfset tmp = ArrayClear(totalTicketsArray)>
</cfif><cfset SpreadsheetAddRow(theSheet,spacer)>
<cfset newRowHeader = "#CITYNAME# ( #timeService.getFormattedDateDSTSimple(PERFORMANCETIME,TIMEZONEOFFSET)# @ #timeService.getFormattedTime(PERFORMANCETIME,TIMEZONEOFFSET)# )">
<cfset SpreadsheetAddRow(theSheet,newRowHeader)>
</cfif><cfset newTicket = "#REReplace(TICKETNAME, ",", "", "All")#,#TICKETQTY#">
<cfset SpreadsheetAddRow(theSheet,newTicket)>
<cfset arrayAppend(totalTicketsArray, #TICKETQTY#) /><cfif currentrow EQ recordcount>
<cfset arraySum = "Total Tickets: ,#REReplace(numberFormat(arraySum(totalTicketsArray)), ",", "", "All")#" />
<cfset SpreadsheetAddRow(theSheet,arraySum)>
<cfset tmp = ArrayClear(totalTicketsArray)>
</cfif></cfloop><cfset SpreadsheetAddRow(theSheet,spacer)>
<cfset newTitle = "Payment Method,Amount">
<cfset SpreadsheetAddRow(theSheet,newTitle)><cfset tempCITY = ''>
<cfset tempTIME = ''>
<cfset tmp = ArrayClear(totalTicketsArray)>
<cfloop query="collection.CARDTYPES"><cfif #CITYNAME# NEQ tempCITY OR #PERFORMANCETIME# NEQ tempTIME>
<cfset tempCITY = #CITYNAME#>
<cfset tempTIME = #PERFORMANCETIME#><cfif currentrow NEQ '1'>
<cfset arraySum = "Total Tickets: ,#REReplace(dollarFormat(arraySum(totalTicketsArray)), ",", "", "All")#" />
<cfset SpreadsheetAddRow(theSheet,arraySum)>
<cfset tmp = ArrayClear(totalTicketsArray)>
</cfif><cfset SpreadsheetAddRow(theSheet,spacer)>
<cfset newRowHeader = "#CITYNAME# ( #timeService.getFormattedDateDSTSimple(PERFORMANCETIME,TIMEZONEOFFSET)# @ #timeService.getFormattedTime(PERFORMANCETIME,TIMEZONEOFFSET)# )">
<cfset SpreadsheetAddRow(theSheet,newRowHeader)>
</cfif><cfset newTicket = "#REReplace(CARDTYPE, ",", "")#,#REReplace(dollarFormat(TICKETSALES), ",", "", "All")#">
<cfset SpreadsheetAddRow(theSheet,newTicket)>
<cfset arrayAppend(totalTicketsArray, #TICKETSALES#) /><cfif currentrow EQ recordcount>
<cfset arraySum = "Total Tickets: ,#REReplace(dollarFormat(arraySum(totalTicketsArray)), ",", "", "All")#" />
<cfset SpreadsheetAddRow(theSheet,arraySum)>
<cfset tmp = ArrayClear(totalTicketsArray)>
</cfif></cfloop><cfspreadsheet
action="write"
format="csv"
filename="#theFile#"
sheetname="ExampleReport"
name="theSheet"
overwrite="true" />The first part of the code is a cfscript tag, which puts together the directory and spreadsheet variables that will be used to create the final spreadsheet file. Then follows some complex code logic that iterates through a ColdFusion variable called “collection”. This variable I instantiated previously as a ColdFusion structure that holds two cfquery variables, TICKETTYPES and CARDTYPES. I wanted both these queries to be added to the .xls spreadsheet.
In this complex code, I iterate through portions of each query, ordering them by a specific date value inside each query row. So, for example, my .xls spreadsheet would have headers on the top and for each instance of a specific date, include the TICKETTYPES (and then for the second part of the spreadsheet, the CARDTYPES).
For two queries of 10,000 rows each (one for TICKETTYPES and one for CARDTYPES) this complex code was taking forever to format a ColdFusion spreadsheet object. So I came up with a simple solution. When you format a query inside the SQL to display the way you want it on the spreadsheet, it offloads work from the ColdFusion server and onto the SQL database. In the end, all I had to do was include the below code to turn the generated cfquery into an .xls spreadsheet.
<cfscript>
theDir=GetDirectoryFromPath(GetCurrentTemplatePath());
file1=theDir & "ExampleReports.xls";
</cfscript>
<cfspreadsheet
action="write"
filename="#file1#"
sheetname="TicketTypes"
query="collection.TICKETTYPES"
overwrite="true" />
<cfspreadsheet
action="update"
filename="#file1#"
sheetname="CardTypes"
query="collection.CARDTYPES" />The code for the first cfspreadsheet tag takes the first query and writes an .xls file to display records exactly as shown in the query variable. The second cfspreadsheet tag updates the current .xls file and adds a second spreadsheet onto it (Excel Spreadsheets can have multiple pages, or different spreadsheets in the same .xls file).
This brings the entire processing time down from 4-5 minutes, to 4-5 seconds. That’s a HUGE difference! Sure, I don’t have all the formatting capabilities that ColdFusion allows for me to have. But the point of understanding came when I realized the people using the spreadsheet need everything laid out in simple rows. They didn’t need headers, spacers or data that was spread out in any fashion. They just needed a row-by-row list of raw data. So, this solution worked out perfectly. This makes XLS Exporting in Adobe Coldfusion 9 very easy to use.
Always remember, when programming using a combined group of different languages (i.e. – Adobe ColdFusion 9 with an MS SQL Database) you can always offload work from one to the other, depending on the application. Here, we took a 4-5 minute process in ColdFusion and gave that job to the database, which helped it run in less than 4-5 seconds. Using this methodology, you can program less and achieve more, and produce efficient applications and web sites!
