Goal:
Best way to create an Excel spreadsheet dynamically using ColdFusion.
Story:
There are many occasions when I have to prepare the Excel Reports from database. I have two options
- I run SQL query in query analyzer and open the Excel and copy the results.
- I copy the query in my ColdFusion code and create the Excel File dynamically.
The advantages of the second options are:
- I save lots of time
- I can save the code file for future use.
- I do not have to open two different programs (MS SQL and MS Excel) to do one simple job. I am able to do it right away in my HTML Editor. I use Dream Weaver but one can use Notepad.
- I can give the name of the Excel file on the file.
- If the data is changed, complete process can be done automatically running the same code again.
- If we use cfschedule to run the schedule task, we can create the dynamic Excel file with dynamic name everytime.
Result:
I can produce Excel file in ColdFusion using dynamic data and dynamic file name on the fly.
Code:
<cfquery datasource="MyDataSourceHere" name="qryAccessData">
SELECT Field1, Field2, FieldN
FROM AccessTable
</cfquery>
<!--- cfheader will create the xls file download on fly (run of code) --->
<cfheader name="content-disposition" value="inline;filename=AccessToExcelDump.xls"> <!--- Mime type which will create the content type of Excel --->
<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>
Online Reference:
cfcontent http://livedocs.macromedia.com/coldfusion/6.1/htmldocs/tags-a17.htm#wp2850760
