Exporting to xls or csv from coldfusion

hey guys,
does anyone know of a good custom tag or component (or just a script for that matter) that will enable me to be able to export a query as a excel doc? .xls or even a csv. ive tried several things, several custom tags, several tutorials, looked through several forums. but they all either dont work, or give me an error when i try to open a workbook.

any help guys? thanks in advance

Integrating ColdFusion with Excel is easier said than done. Your best bet is to write out a flat text file as a CSV and import it:


<cfset crlf = Chr(10) & Chr(13)>
<cfquery name="GetStuff" datasource="#dsn#">
   SELECT one, two, three
   FROM tblMine
</cfquery>
<cffile action="write" file="#myFile#" output="""one"",""two"",""three""#crlf#">
<cfloop query="GetStuff">
   <cffile action="append" file="#myFile#" output="""#one#"",""#two#"",""#three#""#crlf#">
</cfloop>

That’s totally untested, but it should work. I’ve had to do that before and used something similar…

good to see ya david, i figured you were still out of town.

i had tried something similar, but this seemed like it might work.
its close, but something still not exactly right.

i thought the commas in some of the fields would be throwing it off, so i tried to replace all commas with spaces, but its still not working. here is what ive got:


<cfquery name="getinactive" datasource="creopuscustomers">
	SELECT *
	FROM messages
	WHERE active = 0
</cfquery>

<cfset crlf = Chr(10) & Chr(13)>
<cfset myfile = "c:/download/test.txt">

<cffile action="write" file="#myFile#" output="""active"",""to"",""fromuser"",""subject"",""read"",""readreceipt"",""messagetext"",""timestamp"",""#crlf#">
<cfloop query="getinactive">
   <cffile action="append" file="#myFile#" output="""getinactive.active"",""#getinactive.to#"",""#fromuser#"",""#replacenocase(getinactive.subject,"","","" "","","""","","""","","")#"",""#getinactive.read#"",""#getinactive.readreceipt#"",""#replacenocase(getinactive.messagetext,"","","" "","","""","","""","","")#"",""#replacenocase(getinactive.timestamp,"","","" "","","""","","""","","")#"",""#crlf#""">
</cfloop> 

the file when i import it says “the file is not completely loaded” and then it isnt formated right. the fields arent lined up in the right columns. the first row goes on for a while.

any ideas? what exactly is the chr10 and chr13 charachters? and am i missing them somewhere?

thanks guys!

I’ll look at it a little more.

Chr(10) and Chr(13) are carriage return/line feed characters (I always forget which is which…)

okay, ive done a little more here, this is what i have now:

<cfquery name="getinactive" datasource="creopuscustomers">
	SELECT *
	FROM messages
	WHERE active = 0
</cfquery>

<cfset crlf = Chr(10) & Chr(13)>
<cfset myfile = "c:/download/test.txt">

<cffile nameconflict="makeunique" action="write" file="#myFile#" output="""active""|""to""|""fromuser""|""subject""|""read""|""readreceipt""|""messagetext""|""timestamp""|#crlf#|">
<cfoutput query="getinactive">
	<cfset variables.subject = replacenocase(getinactive.subject,","," ")>
	<cfset variables.messagetext = replacenocase(getinactive.messagetext,","," ")>
	<cfset variables.messagetext = replacenocase(variables.messagetext,""""," ")>
	<cfset variables.messagetext = replacenocase(variables.messagetext,"Chr(10)"," ")>
	<cfset variables.messagetext = replacenocase(variables.messagetext,"Chr(13)"," ")>
	<cfset variables.timestamp = replacenocase(getinactive.timestamp,","," ")>
	
	<cfinvoke
	 component="creopus.cfc.bbtext"
	 method="fromBB"
	 returnvariable="fromBBRet">
		<cfinvokeargument name="inputstring" value="#variables.messagetext#"/>
	</cfinvoke>
	<cfdump var="""#getinactive.active#""|""#getinactive.to#""|""#fromuser#""|""#variables.subject#""|""#getinactive.read#""|""#getinactive.readreceipt#""|""#fromBBRet#""|""#variables.timestamp#""|#crlf#|"><br>
	
   <cffile action="append" file="#myFile#" output="""#getinactive.active#""|""#getinactive.to#""|""#fromuser#""|""#variables.subject#""|""#getinactive.read#""|""#getinactive.readreceipt#""|""#fromBBRet#""|""#variables.timestamp#""|#crlf#|">
</cfoutput> 

as you can see ive changed out the commas for pipes, and im changing out the html to bbtext

its still giving me really weird results. if you want me to attach a file so you can see i can

Okay, this looks like it’s going to mess you up:


""#replacenocase(getinactive.subject,"","","" "","","""","","""","","")#""

You shouldn’t really need to replace the commas unless there are double quotes in there as well. The reason you wrap each variable in double quotes is because you want something like this by the time you’re done:


"one","two","three"
"1","2","3"
"4","5","6"

The double quotes are around each list item as text qualifiers. That way you can have a comma inside the double quotes and it shouldn’t break it. Depending on the type of data you’re putting into it, it may be very tricky to manage. You could simply return is as an .xls document sort of like this:


   <cfsetting enablecfoutputonly="yes">
   <cfquery name="GetStuff" datasource="#dsn#">
      SELECT one,two,three
      FROM tblNumbers
   </cfquery>
   <cfcontent type="application/msexcel">
   <cfheader name="Content-Disposition" value="filename=Stuff.xls">
   <cfoutput>
      <table border="1">
         <tr>
            <th>one</th>
            <th>two</th>
            <th>three</th>
         </tr>
         <cfloop query="GetStuff">
            <tr>
               <td>#one#</td>
               <td>#two#</td>
               <td>#three#</td>
            </tr>
         </cfloop>
      </table>
   </cfoutput>

Based on this: http://livedocs.macromedia.com/coldfusion/6/Developing_ColdFusion_MX_Applications_with_CFML/manageFiles5.htm

That will prompt them to open or save the file as “Stuff.xls”.

okay, heres what ive got now:

<cfsetting enablecfoutputonly="yes">
  	<cfquery name="getinactive" datasource="creopuscustomers">
	SELECT *
	FROM messages
	WHERE active = 0
	</cfquery>

   <cfcontent type="application/msexcel">
   <cfheader name="Content-Disposition" value="filename=Stuff.xls">
   <cfoutput>
      <table border="1">
         <tr>
            <th>active</th>
            <th>id</th>
            <th>to</th>
			<th>fromuser</th>
			<th>subject</th>
			<th>read</th>
			<th>readreceipt</th>
			<th>messagetext</th>
			<th>timestamp</th>
         </tr>
         <cfloop query="getinactive">
            <tr>
               <td>#getinactive.active#</td>
			   <td>#getinactive.id#</td>
			   <td>#getinactive.to#</td>
			   <td>#getinactive.fromuser#</td>
			   <td>#getinactive.subject#</td>
			   <td>#getinactive.read#</td>
			   <td>#getinactive.readreceipt#</td>
			   <td>#getinactive.messagetext#</td>
			   <td>#getinactive.timestamp#</td>
            </tr>
         </cfloop>
      </table>
   </cfoutput>

all i get is a message saying “the parameter is incorrect”

i tried something like this before and got the same thing. ive looked through everything, i dont know what im missing. i dont really even know where to look.

I use
<cfcontent type=“application/vnd.ms-excel”>
and a <cfoutput> of the query inside a <table>…
Each <td> becomes a cell in excel…

ill try that here in a few minutes and get back to you. anything else special you have to do?

Nope. I’ve done the same thing with ASP and PHP and it works just fine. :slight_smile:

Sorry, I put the wrong content type in there… application.vnd.ms-excel is in there. I must have been going at it off the top of my head…

alright, this is what i have now:


<cfsetting enablecfoutputonly="yes">
  	<cfquery name="getinactive" datasource="creopuscustomers">
	SELECT *
	FROM messages
	WHERE active = 0
	</cfquery>

   <cfcontent type="application/vnd.ms-excel">
   <cfheader name="Content-Disposition" value="filename=Stuff.xls">
   <cfoutput>
      <table border="1">
         <tr>
            <th>active</th>
            <th>id</th>
            <th>to</th>
			<th>fromuser</th>
			<th>subject</th>
			<th>read</th>
			<th>readreceipt</th>
			<th>messagetext</th>
			<th>timestamp</th>
         </tr>
         <cfloop query="getinactive">
            <tr>
               <td>#getinactive.active#</td>
			   <td>#getinactive.id#</td>
			   <td>#getinactive.to#</td>
			   <td>#getinactive.fromuser#</td>
			   <td>#getinactive.subject#</td>
			   <td>#getinactive.read#</td>
			   <td>#getinactive.readreceipt#</td>
			   <td>#getinactive.messagetext#</td>
			   <td>#getinactive.timestamp#</td>
            </tr>
         </cfloop>
      </table>
   </cfoutput>

it pops up asking you to download the file, downloads fine, but when you try to open the file it says that the “paramater is incorrect”. :frowning: see anything wrong with the code? any other ideas? i really appreciate you guys still looking at this

This is a shot in the dark and may not matter, but I usually set the <cfheader> first…

changed it to


...
<cfheader name="Content-Disposition" value="filename=Stuff.xls">
<cfcontent type="application/vnd.ms-excel">
<cfoutput>
....

same thing, paramater is incorrect. like its not actually forming the file right or something. is there anything i need at the end of the file to tell it that its done? any other ideas?

update:

okay, if i try to browse the page and save the file through internet explorer, it works. firefox just doenst like it. now i didnt really figure it had anything to do with the browser itself, but apparently it does. any ideas how to make it work through firefox anyone?

I have a custom tag I use that works <CF_Query2Excel>. I can send it along if anyone wants it.

absolutely!

This discussion helped me do my first excel export file today- thanks!

Now for the bad news- some of my data are codes that have leading zeroes (like ZIP codes). When I do the export, these codes end up in the spreadsheet as numbers, with no leading zeroes ( e.g. 02102 becomes 2102, etc.). Surrounding the variable with single or double quotes sorta works, but the quotes show up in the spreadsheet along with the zeroes. Anybody know how to get around this??

Try pushing a leading ’ in front of the fields you know will be numeric. This is a default behaviour in Excel to treat anything after a ’ as literal. Try it. If this doesn’t work try putting one either side.

I’m 95% certain this is how I’ve gotten round this in the past…

I actually tried that, but the single quote shows up in the spreadsheet. I did find one usable workaround- inserting the character code for nbsp before the variable. It does the trick, although of course you end up with a space prior to the data. It’s not a problem with this export, but I’d still like to find a formatting solution.

Speaking of which, the export also has dates- these show up in the spreadsheet as “00:00:00”, presumably because the template only asks for a date, and the time defaults to midnight. You can change the date time format in Excel to display and use the dates normally, but it would be nice not to have to explain that to people. I tried using datevalue(), but that outputs the date as text in the spreadsheet, so sorting on date doesn’t work.

Any suggestions?