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!

Register Log in
  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

dynamically create an Excel file with coldfusion? 2

Status
Not open for further replies.
ryanbrand

ryanbrand

Programmer
Oct 28, 2003
22
US
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
 
Sort by date Sort by votes
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
 
Upvote 0 Downvote
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?
 
Upvote 0 Downvote
&quot;The first field on the document contained all the data&quot;??

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
 
Upvote 0 Downvote
Below is the code I am using...

<cfquery name=&quot;qryData&quot; datasource=&quot;&quot; username=&quot;&quot; password=&quot;&quot;>
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=&quot;application/vnd.ms-excel&quot;>
<CFHEADER name=&quot;Content-Disposition&quot; value=&quot;filename=Employees.xls&quot;>

<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>


<CFDUMP var=&quot;#qryData#&quot;>
 
Upvote 0 Downvote
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 &quot;oh... I'm interpreting an HTML table here&quot;... so it ignores the TABs it the first output.


-Carl
 
Upvote 0 Downvote
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 = &quot;qryData&quot; datasource = &quot;mydatasource&quot;>
SELECT *
FROM data
WHERE dataID = #session.dataIKD#
</cfquery>
<CFCONTENT type=&quot;application/vnd.ms-excel&quot;>
<CFHEADER name=&quot;Content-Disposition&quot; value=&quot;filename=contactlist.xls&quot;>
<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>

thereptilian120x120.gif
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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=&quot;application/vnd.ms-excel&quot;>
<CFHEADER name=&quot;Content-Disposition&quot; value=&quot;attachment;filename=contestentries.csv&quot;>

thereptilian120x120.gif
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

FALCONSEYE
  • Locked
  • Question
ColdFusion microservices with REST
Replies
0
Views
569
FALCONSEYE
FALCONSEYE
cfhelp
  • Locked
  • Question
Coldfusion 2016 - Cant find CFX_tags
Replies
0
Views
534
cfhelp
cfhelp
miagriff
  • Locked
  • Question
Coldfusion if statement syntax
Replies
1
Views
676
LyndonOHRC
LyndonOHRC
CTR
  • Locked
  • Question
ColdFusion Administrator - Secure DNS
Replies
2
Views
304
CTR
CTR
fdgsogc
  • Locked
  • Question
Displaying results of an SQL Pivot Query
Replies
1
Views
655
jdhilljr
jdhilljr

Part and Inventory Search

Sponsor

Back
Top