Adobe Coldfusion Help: Query To Spreadsheet

COLDFUSION HELP TIP: QUERY to SPREADSHEET

I've been using Adobe ColdFusion for a number of years. Everyday, I come across something new that I have not seen or done. I was hoping to help others with some sample code to everyday situations that would occur. I'm asked to generate an Excel spreadsheet from SQL data often. Below is my sample code.

SAMPLE CODE:

<!--- ---- query table for data ----------------- --->
<CFQUERY NAME="get_records" DATASOURCE="sampledata">
SELECT *
FROM employees
</CFQUERY>

<cfif get_records.recordcount GTE 66000>
<!--- Excel can only handle 66000 rows --->
Too many records

<cfelse>


<cfif get_records.recordcount GT 0>


<!--- create the file to be written --->
<cffile action="WRITE" file="d:\inetpub\domains\employee\www\data\filename.xls" output="
<cfcontent type='application/vnd.ms-excel'>

<table border='1'>
<tr>
<td>FIRST NAME</td>
<td>LAST NAME</td>
<td>EMAIL</td>
<td>TITLE</td>
<td>DEPARTMENT</td>
</tr> " addnewline="Yes">
<!--- append the dynamic data to the file --->
<cfoutput>

<cfloop query="get_records">

<cffile action="APPEND"
file="d:\inetpub\domains\employee\www\data\filename.xls" output="
<tr>

<td>#TRIM(FNAME)#</td>
<td>#TRIM(LNAME)#</td>
<td>#TRIM(EMAIL)#</td>
<td>#TRIM(TITLE)#</td>
<td>#TRIM(DEPARTMENT)#</td>

</tr> "
addnewline="yes">

</cfloop>


</cfoutput>

<!--- end your table in the file --->


<cffile action="Append"
file="d:\inetpub\domains\employee\www\data\filename.xls" output="
</table> " addnewline="Yes">

</cfif>

</cfif>

CONCLUSION:

Once the routine executes, and there are no errors. The xls file will be written to the location specified in the code. In the sample, the xls file would be written to, d:\inetpub\domains\employee\www\data\filename.xls.

From here you could email the file, or just generate a link, so the user can download the file directly from the page.

More by this Author

  • Tips For How to Stain Concrete Floors
    1

    You don't always have to tile, carpet, or hardwood your floors. The new thing is to just stain the concrete that's already there. It's a lot of work, but if done properly, your home will look gorgeous. If you're...

  • How to Get Rid of Mice In and Around The House?
    5

    Until this year, the only time that I've had mice or rats in the house were when my daughter had pet mice and rats, and one of those times we did have one loose in the house. (Science fair project got loose.) Anyway,...

  • How to Build Wooden Roof Trusses
    23

    You may refer to a truss as the rafter, but it's basically the skeleton of the roof, carrying the weight of the frame and supporting the walls of the building. Trusses are very important to preventing the walls from...


Click to Rate This Article
working