dynamically create an Excel file with coldfusion? 2

ryanbrand · Oct 28, 2003

I'm wondering if it is possible to dynamically create an excel file with ColdFusion. All my data is stored in Access, and my processing team wants to be able to create an excel file by clicking on a link on the web page. Right now they copy and paste the data from my display page to Excel, but that is a very long process.

Please let me know how I can dynamically create Excel files with Coldfusion and my Access Database.

Thank you for help!!

Ryan

csteinhilber · Oct 28, 2003

Yes, it's entirely possible usually using just two tags... CFCONTENT to trick the browser into thinking it's actually downloading an Excel file, and CFHEADER to tell it what to do with that file.

If you create a file under your webroot, and paste in the following code:

Code:

<CFCONTENT type=&quot;application/vnd.ms-excel&quot;>
<CFHEADER name=&quot;Content-Disposition&quot; value=&quot;filename=ActiveStudents.xls&quot;>

<CFSCRIPT>
	qryData = QueryNew(&quot;student_id,last_name,first_name&quot;);
	QueryAddRow(qryData);
	QuerySetCell(qryData,&quot;student_id&quot;,&quot;11111&quot;);
	QuerySetCell(qryData,&quot;last_name&quot;,&quot;Smith&quot;);
	QuerySetCell(qryData,&quot;first_name&quot;,&quot;John&quot;);

	QueryAddRow(qryData);
	QuerySetCell(qryData,&quot;student_id&quot;,&quot;22222&quot;);
	QuerySetCell(qryData,&quot;last_name&quot;,&quot;Jones&quot;);
	QuerySetCell(qryData,&quot;first_name&quot;,&quot;Jane&quot;);

	QueryAddRow(qryData);
	QuerySetCell(qryData,&quot;student_id&quot;,&quot;33333&quot;);
	QuerySetCell(qryData,&quot;last_name&quot;,&quot;Robinson&quot;);
	QuerySetCell(qryData,&quot;first_name&quot;,&quot;Michael&quot;);
</CFSCRIPT>

<CFDUMP var=&quot;#qryData#&quot;>

and point your IE browser to the page, it should open a spreadsheet with your data in it using the Excel ActiveX control. Netscape users would need to either save the .xls file to their local system, or choose to open it directly in Excel as an external file.

One of the tricks is that the .cfm page must not have any

Code:

!DOCTYPE, <HTML> or <BODY>

tags... otherwise you'll defeat the CFCONTENTs attempt to fool the browser. And it's best if CFCONTENT and CFHEADER are the first two lines of the page's code.

Of course, you can create the query any way you like (CFQUERY of your Access database, etc), and output it any way you like.

In outputting, you can build a standard HTML containing your data in separate cells and Excel will interpret each cell as a cell in it's spreadsheet (which is why CFDUMP is so convenient... since it builds a table from a query automatically). OR you can output your data separated by tab characters to delimit cells, and carriage returns to delimit rows:

Code:

<CFOUTPUT query=&quot;qryData&quot;>
  <CFLOOP list=&quot;#qryData.columnlist#&quot; index=&quot;whichColumn&quot;>
     <CFSET sFieldValue = qryData[whichColumn]>
     #sFieldValue##Chr(9)#
  </CFLOOP>
  #Chr(13)#
</CFOUTPUT>

... your choice.

-Carl

bgirsh · Dec 3, 2003

I used csteinhilber's query version and everything worked great except that the first field on the document contained all the data. Below was the correct layout. Any ideas on how to get rid of this first field?

csteinhilber · Dec 3, 2003

"The first field on the document contained all the data"??

I'm not entirely sure what that means. Do you mean that cell A1 contained your entire result set in a single cell... and then cells B1 through whatever had the result set again in properly separated individual cells?

That would/should only happen if you output the data twice within your code. You're using the CFDUMP version or the CFLOOP version of my sample above, right? Not both in the same document?

Post your current code... and also specify which version of ColdFusion you're running and which version of IE and Office you have installed.

-Carl

bgirsh · Dec 3, 2003

Below is the code I am using...

<cfquery name="qryData" datasource="" username="" password="">
SELECT prem_lname, prem_fname, prem_minit FROM dbo.prempmst WHERE prem_proj =
0 AND prem_act_stat = 'A' AND prem_lname LIKE 'gi%' ORDER BY prem_lname
</cfquery>
<CFCONTENT type="application/vnd.ms-excel">
<CFHEADER name="Content-Disposition" value="filename=Employees.xls">

<CFOUTPUT query="qryData">
<CFLOOP list="#qryData.columnlist#" index="whichColumn">
<CFSET sFieldValue = qryData[whichColumn]>
#sFieldValue##Chr(9)#
</CFLOOP>
#Chr(13)#
</CFOUTPUT>

<CFDUMP var="#qryData#">

csteinhilber · Dec 3, 2003

That's what I thought.

You don't need both the CFOUTPUT block and the CFDUMP block. One or the other will do.

If you include both, not only will your dataset get written out twice, but Excel will get confused as to what it's trying to interpret.
It looks at

Code:

value1TABvalue2TABvalue3TABvalue4

, which would ordinarily work fine, but then it looks at the HTML that CFDUMP produces and says "oh... I'm interpreting an HTML table here"... so it ignores the TABs it the first output.

-Carl

TruthInSatire · Dec 22, 2003

please help i'm using the following code. I get it to open in excel however cell 1,1 contains the code as written in the document.

<cfquery name = "qryData" datasource = "mydatasource">
SELECT *
FROM data
WHERE dataID = #session.dataIKD#
</cfquery>
<CFCONTENT type="application/vnd.ms-excel">
<CFHEADER name="Content-Disposition" value="filename=contactlist.xls">
<CFOUTPUT query="qryData">
<CFLOOP list="#qryData.columnlist#" index="whichColumn">
<CFSET sFieldValue = qryData[whichColumn]>
#sFieldValue##Chr(9)#
</CFLOOP>
#Chr(13)#
</CFOUTPUT>

http://thereptilian.com/

csteinhilber · Dec 22, 2003

If you mean that Excel is displaying the

Code:

<CFQUERY>

and your

Code:

<CFLOOP>

s, etc... that means that your CFML isn't getting processed.

What's the name of the template you're running? It must still have a .CFM extension.

-Carl

TruthInSatire · Dec 22, 2003

Carl, thank you for responding. Yes the template has a cfm extention. does a setting need to be set in the administrator anywhere to handle this?

http://thereptilian.com/

TruthInSatire · Dec 22, 2003

removing the cfcontent and cfheader tags causes the cfm to process just fine....

http://thereptilian.com/

TruthInSatire · Dec 22, 2003

ok aparently its something with my browser. other people see it just fine. how do i make it open in excel, not excel within the browser?

http://thereptilian.com/

TruthInSatire · Dec 22, 2003

got it to work. for some reason my browser was asking for a password, if i canceled i got the data. used the following to open in excel (save or open)

<CFCONTENT type="application/vnd.ms-excel">
<CFHEADER name="Content-Disposition" value="attachment;filename=contestentries.csv">

http://thereptilian.com/

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

dynamically create an Excel file with coldfusion? 2

ryanbrand

Programmer

csteinhilber

Programmer

bgirsh

Programmer

csteinhilber

Programmer

bgirsh

Programmer

csteinhilber

Programmer

TruthInSatire

Programmer

csteinhilber

Programmer

TruthInSatire

Programmer

TruthInSatire

Programmer

TruthInSatire

Programmer

TruthInSatire

Programmer

Similar threads

Part and Inventory Search

Sponsor