- HubPages»
- Technology»
- Computers & Software»
- Computer How-Tos & Tutorials
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>
Other ColdFusion Articles
- Adobe Coldfusion Tip For Finding Duplicate Records In SQL Database
- Coldfusion: allowing max file size when uploading
- ColdFusion: Monitor Hard Drive Space And Email Alert
- Coldfusion SQL Tips: Creating Tables, Backup, Fields
- Coldfusion Programming: Accessing a shared network drive
- Coldfusion: Encrypting and Decrypting Data
- Adobe Coldfusion Help: Query To Spreadsheet
- ColdFusion: Upload a pipe delimited file and insert into SQL table
- Coldfusion programming: How to submit muliple records at the same time that have the same field name
- How To Upload Multiple Files With ColdFusion
- ColdFusion & Javascript: How to pause a web page process
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.