Export data from ColdFusion (or SQL) to Excel 1

[w11z]

Hello,
Situation : My web page receive data from users (ie: name textbox in a .cfm form) this data is compiled and stored in a SQL table. How do I export these data to a specific Excel file on the server?
w11z

 

[GunJack]

Do you need to write out an excel file or a file that Excel can open such as a comma delimited file? The structure of a true Excel file is most likely very complicated to store all of the data Excel needs. A comma delimited file though is very simple to create and this is what most people create when they want to export data to Excel.

If you want to go the csv route, then this should do what you need.

<cfquery name=&quot;q1&quot; ...
select all data
</cfquery>

<cfloop query=&quot;q1&quot;>
<cffile action=&quot;append&quot; output=&quot;#q1.var1#,#q1.var2#,#q1.var3#&quot; file=&quot;...&quot;>
</cfloop>

Good luck,
GJ

 

[w11z]

GunJack - Actually, I need to export a small number of data to a specific Excel file (named result.xls) located on the server. This file contains graphics and images that is needed. The csv option is interesting if you can save data in any Excel file. Unfortunately this is not my case. Help

 

[tanny]

Maybe you could get the Excel spreadsheet to use ODBC to look up the SQL database directly?

 

[GunJack]

If you need to put data in a true Excel file, I think tanny's on the right path in that you could try using the Excel ODBC driver to write directly to the file. I've always heard that this tends to be problematic but it's the only way I can think of that will allow you to write directly to an Excel file without learning the details of how Excel formats it's files.

GJ

 

[KevinFSI]

You could also look up the Data Transformation Services in SQL Server. I don't know how helpful this will be to you, but I know you can export to any number of formats, including several versions of Excel. I haven't used it really, but I know you can right-click &quot;Data Transformation Services&quot; then &quot;All Tasks&quot; then &quot;Export Data&quot; From there, I really don't know what to tell ya. I believe you can schedule this transformation also.

If this helps you, and you figure out what to do with it, please let me know as I am kinda curious myself. Kevin
slanek@ssd.fsi.com

 

[strantheman]

w11z,

The CFFILE tag will do what you need. Specify your contentType and give the file a XLS extension. XCEL can interpret HTML tables, so just output your queries into HTML tables.

Make a new file every time the template is loaded, do not attempt to append to an XLS file that already exists unless you do so within the same template where it was created. There are many things that can 'lock' access to this file on the server side, and since you're running reports, its better to have the date in the name of the file so each one is unique.

This should do what you need:

<CFFILE contentType=&quot;application/x-msexcel&quot;
action=&quot;write&quot;
file=&quot;/

http://wwwroot/03292001a.xls&quot;

output =
'

<table border=1>
<tr>
<td><b>Name</b></td>
<td><b>Phone</b></td>
</tr>
<tr>
<td>strantheman</td>
<td>youwish</td>
</tr>
</table>
'>
Test.xls created!<Br><Br>


<!--- add to the file --->
<CFFILE
contentType=&quot;application/x-msexcel&quot;
action=&quot;append&quot;
file=&quot;/

http://wwwroot/03292001a.xls&quot;

output =
'

<table border=1>
<tr>
<td>jackblack</td>
<td>5551212</td>
</tr>
</table>
'>
file added to!

----

Remember, you can use any combination of CFFILE action=&quot;append&quot; if say, you wanted to append only 1 row, not an entire table, for each record returned. Then when you're done looping through the output, close the table.

hope this helps.

 

[w11z]

Thanks everyone, you gave me great leads.
w11z