I have experienced numerous cases where I wanted to dump database information into an Excel sheet for the users. Usually, the application that the data is entered through is a ColdFusion web application, so it makes sense to dump the data directly from the web-based application. To do this I have used two basic techniques. The first technique is to generate a ColdFusion page which returns an Excel file as the result so that users can save the file. The second technique I use is to dump the data to a web page that can be accessed inside of Excel using the import data from a web query method. Below I will give some examples of both methods.
Technique #1, Method #1
<cfsetting enablecfoutputonly="yes">
<cfset tab="CHR(9)">
<cfquery name="get_Data" datasource="Deg">
SELECT username,EMail,
FROM Tbl_Data
</cfquery>
<cfheader name="Content-Disposition" value="inline; filename=export.xls">
<cfcontent type="application/msexcel">
<cfoutput query="get_Data">
#Currentrow##TAB##username##TAB##EMail##chr(13)#
</cfoutput>
Technique #1, Method #2
<cfquery datasource="MyDataSourceHere" name="qryAccessData">
SELECT Field1, Field2, FieldN
FROM AccessTable
</cfquery>
<cfheader name="content-disposition" value="inline;filename=AccessToExcelDump.xls">
<cfcontent type="application/msexcel">
<table border="1">
<tr>
<th>Field1</th>
<th>Field2</th>
<th>FieldN</th>
</tr><cfoutput query="qryAccessData">
<tr>
<td>#qryAccessData.Field1#</td>
<td>#qryAccessData.Field2#</td>
<td>#qryAccessData.FieldN#</td>
</tr></cfoutput>
</table>
Technique #2
<html>
<head>
</head>
<body>
<cfif isdefined('url.file')>
<cffile action="read" file="#application.serverpath##url.file#" variable="csvfile">
<table>
<cfloop index="index" list="#csvfile#" delimiters="#chr(10)##chr(13)#">
<tr>
<cfloop index="LoopCount" from="1" to="#listlen(index)#">
<td><cfoutput>#listgetAt('#index#',LoopCount, ',')#</cfoutput></td>
</cfloop>
</tr></cfloop>
</table>
</cfif>
</body>
</html>
