To loop through a query result in CFSCRIPT and build a spreadsheet, you can utilize the SpreadsheetAddColumn. This example does not take into consideration a zero-length query result, so you will have to provide your own conditional checks. The idea here is to just show how you can loop through a query and add each column in succession.
First are the basics for creating a spreadsheet:
<cfscript>
TheFile=GetDirectoryFromPath(GetCurrentTemplatePath()) & "TestDocument.xls";
TheSheet=SpreadsheetNew("TestDocument");
SpreadsheetAddRow(TheSheet, "Column A, Column B");
So far I've set the location for the XLS document to be created and stored, and set the name of my spreadsheet. For this example, there are two columns defined in the event I want to call them something other than the name of the records in the query result. I am going to use SpreadsheetAddColumn as an example where the query is being pulled for other processes on the page and may have more column names than I actually want in the XLS document.
For (i=1; i LTE GetQueryResult.RecordCount; i=i+1) {
SpreadsheetAddColumn(TheSheet, GetQueryResult.FruitColor[i], i+1, 1, false);
SpreadsheetAddColumn(TheSheet, GetQueryResult.FruitNames[i], i+1, 2, false);}
</cfscript>
We're looping through the query results (assuming there is at least one result for this example) and generating the column data. The result is grabbing the query record by position according to the looped value of "i". So we can keep the header in place we are adding one to the position start row and number our columns. The "false" is for the optional "insert".
Next we need to commit the spreadsheet to a file on the server and in this example, trigger a download of the file.
<cfspreadsheet action="write" filename="#TheFile#" name="TheSheet" overwrite="yes" sheetname="TestDocument">
<cfheader name="Content-Disposition" value="attachment; filename=TestDocument.xls">
<cfcontent type="application/vnd.ms-excel">
<cfcontent file="#TheFile#" reset="yes">
You may want to add a MIME type to the CFCONTENT page for properly generated code, but I've found that it will still deliver the XLS document without it, so its up to you and your situation. The CFHEADER file name location may also need to be tweaked if it is now pointing to the literal location of where the absolute path created our document with the TheFile variable.
If you find this post useful please leave a comment and let me know how you used the information.
