CODEWITHSUNDEEP
excelcoldfusioncfspreadsheet
Barrett Chamberlain
Barrett Chamberlain

Reputation: 129

Adding text / extra queries to cfspreadsheet output

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?

Upvotes: 0

Views: 1114

Answers (2)

Dan Bracuk
Dan Bracuk

Reputation: 20804

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.

Upvotes: 1

Leigh
Leigh

Reputation: 28873

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)#" />

Upvotes: 4

Related Questions