Adding text / extra queries to cfspreadsheet output

Question

I'm running Coldfusion 10 on Microsoft Windows Server 2008 R2 Data Center – SP 1 with a SQL Server 2012 datasource.

I have a cfspreadsheet tag on a page that goes like this:

<cfinvoke component="assetdata" method="getAllNames" searchString ="#url.searchoption#" returnVariable="result">

<cfspreadsheet 
    action = "write" 
    query="result" 
    filename="#filename#" 
    overwrite="true">
<div>Your spreadsheet is ready. You may download it <a href="search_results.xls">here</a>.</div</div>

What I'd like to do is in addition to outputting the query onto a spreadsheet is add query results like these to the bottom of it:

Total number of computers in use: 110

Total number of computers to be replaced: 62

Total number of computers that have been replaced: 8

which each have their own select statement on the page drawing the info. Can this be done with cfspreadsheet? Or am I locked into only using one query per sheet?

score 4 · Answer 1 · answered Apr 18 '13 at 23:57

Can this be done with cfspreadsheet?

No. The write action only accepts a single query. However, you could always read the sheet into a variable. Then use SpreadSheetAddRow to append additional values to that sheet.

As an aside, you can also stream the spreadsheet directly from a variable. Note, that may be a little more resource intensive.

<cfheader name="Content-Disposition" value="attachment; filename=someFile.xls" />
<cfcontent type="application/vnd.msexcel" 
        variable="#spreadSheetReadBinary(yourSpreadSheetObject)#" />

Depending on the data, it is possible you could merge everything into a single query, and get around it that way. But that is a bit hackish ... — Leigh, Apr 18 '13 at 23:59

score 1 · Answer 2 · answered Apr 19 '13 at 02:30

You are not locked into anything. There are a number of ways to put data into a spreadsheet. Leigh gave you a link to one of the available functions that ColdFusion provides.

Most, if not all of the methods available to output data to a web page can be used to output data to spreadsheets. Here is some sample code that populates column 1 of a worksheet with data. Later on other queries populate a header row, and the usage data

<cfloop query="DrugsByCategory">
<cfscript>
SpreadsheetAddRow(Workbook, "", RowNumber, 1);
SpreadSheetSetCellValue(Workbook, 
DrugsByCategory.Item[DrugsByCategory.currentrow], RowNumber, 1);

if (DrugsByCategory.type[DrugsByCategory.currentrow] == "Category"){
SpreadsheetFormatCell(Workbook, CategoryFormat, RowNumber, 1); 
StartOfDrugRange = RowNumber + 1;  
}
else if (DrugsByCategory.type[DrugsByCategory.currentrow] == "Summary"){
SpreadsheetFormatCell(Workbook, SummaryFormat, RowNumber, 1); 
EndOfDrugRange = RowNumber - 1;
}
</cfscript>
<!--- get data for each drug, and then the summary --->
<cfif DrugsByCategory.type[DrugsByCategory.currentrow] is "Category">
code for this condition
<cfelseif DrugsByCategory.type[DrugsByCategory.currentrow] is "Drug">
Code for this condition
<cfelseif DrugsByCategory.type[DrugsByCategory.currentrow] is "Summary">
code for this condition
</cfif>  

<cfset RowNumber ++>
</cfloop>
<cfset SpreadSheetAddFreezePane(Workbook, 1, 3)>

Don't get hung up on the inelegance of this example. The point is that you can do lots of things with spreadsheet functions if you don't restrict yourself.

Adding text / extra queries to cfspreadsheet output

2 Answers2