ColdFusion Export to Excel on Multiple Sheets

Question

I'm using the code below to export data from a database to an Excel sheet. What I want to do is have a certain set of fields be exported to a different sheet in the same workbook. The end result being that each person's data is on a separate sheet rather than all combined on the same sheet like this code is doing. Any thoughts as to how I can write a formula or a piece of ColdFusion than will let me do this?

Also I'm using ColdFusion 8 so I can't use the cfspreadsheet function.

<!--- use cfsetting to block output of HTML 
outside of cfoutput tags --->
<cfsetting enablecfoutputonly="Yes">

<!--- set content type to invoke Excel --->
<cfcontent type="application/msexcel">

<!--- suggest default name for XLS file --->
<!--- use "Content-Disposition" in cfheader for 
Internet Explorer  --->
<cfheader name="Content-Disposition" value="filename=export.xls">
<!--- output data using cfloop & cfoutput --->

<cfquery name="qquestionnaire_export" datasource="mydatabase">
Select * from registration
</cfquery>

<cfoutput>
<table>
<tr><td align="center">Transfer Registration Questionnaire</td>
<td></td>
</tr>
<th>Credit Category</th>
<th>Completed Degree</th>
<th>Highest Degree</th>





</cfoutput>

<cfloop query="qquestionnaire_export">
<cfoutput>  
<tr>
<td align="center">#credit_category#</td>
<td align="center">#completed_degree#</td>
<td align="center">#highest_degree#</td>



</tr>
</cfoutput>
</cfloop>

<cfoutput><tr><td height="10"></td></tr></cfoutput>

<cfoutput></table></cfoutput>

Answer 1

I know this is an old topic, but this solution would have been helpful if I had found it earlier. However, here is a basic function call that should allow any number of queries put into an excel sheet with multiple tabs.

<cffunction name="QueriesToXLS" access="public">
    <cfargument name="queryArr" required="true"><!--- An Array of Query Objects --->
    <cfargument name="sheetNameArr" required="false"><!--- Optional sheet names to use instead of "Sheet1","Sheet2",... --->
    <cfset tempPath = GetTempDirectory() & CreateUuid() & ".xls"><!--- Creaete a Temp XLS File --->
    <cfset counter = 1>
    <cfloop array="#ARGUMENTS.queryArr#" index="i">
        <cfset sheetName = "Sheet#counter#">
         <cfif isDefined("ARGUMENTS.sheetNameArr")>
            <cfset sheetName = ARGUMENTS.sheetNameArr[counter]>
        </cfif>
        <cfspreadsheet action="update" filename="#tempPath#" query="i" sheetName="#sheetName#"/>
        <cfset counter += 1>
    </cfloop>
    <cfreturn SpreadsheetRead(tempPath)>
</cffunction>


<cfset xlsData = QueriesToXLS(
    [query1,query2],
    ["Details","Summary"]
)>

<cfheader name="content-disposition" value="attachment; filename=export.xls">
<cfcontent type="application/msexcel" variable="#spreadsheetReadBinary(xlsData)#" reset="true">

Answer 2

Apache POI is def the way to go. http://poi.apache.org/

Take the time to read the documentation and do some tests bc it gives you total control rather than trying to fudge HTML tables with CSS.

Answer 3

If you can avoid using this method, it would be best. Excel is smart enough to output your HTML in an 'spreadsheet' looking output but it is still an HTML document with an xls extension. I've had several problems using spreadsheets created like this that expect an actual Excel file. In ColdFusion, if you try reading this file with cfspreadsheet you will most likely get a an error that says something like expected binary input file (or something close to that) and you'll have to use excel to save as to make it an actual Excel spreadsheet. Not to mention this is a static output. You can't include any formulas or any fun stuff like iKnowKungFoo indicated.

Since that doesn't answer your question, in addition to the POI tools you could also look into other BI tools, like JasperSoft, Crystal Reports, or if you're using Oracle or MS SQL Server you could look into Oracle's BI tools or SSRS for SQL Server. There are several BI tools that are free without a support option, I believe JasperSoft is one of them.

Back to ColdFusion, I've never tried using CFReport with report builder to create an Excel formated report but in CF 8 the excel format is available. I'm not sure if you can create multiple sheets with it, I would assume not considering the below snipit but it may be worth looking into. I couldn't find anything but it's early, I'm sick, and my google-foo is a bit slow this morning.

Note: The Excel report output format type provides limited support for the formatting options available in ColdFusion Reporting. Images and charts are not supported and numeric data containing formatting (commas, percents, currency, and so on) appear as plain text in Excel. The Excel output format supports simple reports only and Adobe recommends that you give careful design and layout consideration to reports designed for Excel output.

Answer 4

If you can't use <cfspreadsheet>, then I suggest using the Apache POI project instead of the simple "HTML as Excel via CFCONTENT" approach. POI enables you to create actual Excel spreadsheets with all of the fun that's associated with them.

Ben Nadel has a CFC wrapper that exposes the multi-sheet parts of the API.

http://www.bennadel.com/projects/poi-utility.htm