convert to ms excel

[peter11]

Is there a way to convert cfm outputs into an excel spread sheet on the fly?

I am aware that you can right click on an cfm page and select "Export to Microsoft Excel" (in IE) but is there a way to click on a link and generate an Excel file from data generated by cold fusion?

It would also be great if it were cross browser friendly.

hmmm!

Pete

 

[mimi2]

this is what i used based on an answer provided by this forum


<CFFILE contentType=&quot;application/x-msexcel&quot;
action=&quot;write&quot;
file=&quot;c:\inetpub\

http://wwwroot\ReportFile.xls&quot;

output =
'
<table border=1>
<tr>
<td><b>First Name</b></td>
<td><b>Last Name</b></td>
<td><b>Phone</b></td>
<td><b>Email</b></td>
</tr>
</table>
'>
Excel File created!<Br><Br>

<CFQUERY datasource=&quot;ProjectDB&quot; NAME=&quot;GetP&quot;>
SELECT * FROM tblEmployees
WHERE strDptIDFK = '1' or strDptIDFK = '2' or strDptIDFK = '3'
ORDER BY strDptIDFK, strLName, strFName
</CFQUERY>
<cfloop query = &quot;GetP&quot;>
<cfoutput>
<table border=1>
<tr>
<td>#GetP.strLName#</td>
<td>#GetP.strFName#</td>
<td>#GetP.strPhone#</td>
<td>#GetP.strEmail# </td>
</tr>
</table>
</cfoutput>

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

http://wwwroot\ReportFile.xls&quot;

output =
'
<table border=1>
<tr>
<td>#GetP.strLName#</td>
<td>#GetP.strFName#</td>
<td>#GetP.strPhone#</td>
<td>#GetP.strEmail# </td>
</tr>
</table>
'>
</cfloop>
Records added.

 

[csteinhilber]

Or, if you want the Excel spreadsheet generated inline (in IE's browser window, for instance), you can use cfcontent and cfheader in concert to basically trick the browser into believing it's downloading an Excel file rather than a .CFM:

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


<CFSET Student_ID = &quot;11111,22222,33333&quot;>
<CFSET Last_Name = &quot;Smith,Jones,Robinson&quot;>
<CFSET First_Name = &quot;John,Jane,Michael&quot;>

<CFOUTPUT>
Student ID#Chr(9)#Last Name#Chr(9)#First Name#Chr(13)#

</CFOUTPUT>

<CFLOOP index=&quot;whichStudent&quot; from=&quot;1&quot; to=&quot;#ListLen(Student_ID)#&quot;>
<cfoutput>
#ListGetAt(Student_ID,whichStudent)##Chr(9)##ListGetAt(Last_Name,whichStudent)##Chr(9)##ListGetAt(First_Name,whichStudent)##Chr(13)#
</cfoutput>
</CFLOOP>

or, if everything is up-to-date on the users machine, Excel (versions greater than 97) usually converts HTML tables to cells fairly nicely:

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

<CFSET Student_ID = &quot;11111,22222,33333&quot;>
<CFSET Last_Name = &quot;Smith,Jones,Robinson&quot;>
<CFSET First_Name = &quot;John,Jane,Michael&quot;>

<table>
<CFOUTPUT>
<tr><td>Student ID</td><td>Last Name</td><td>First Name</td></tr>
</CFOUTPUT>

<CFLOOP index=&quot;whichStudent&quot; from=&quot;1&quot; to=&quot;#ListLen(Student_ID)#&quot;>
<tr>
<td><cfoutput>#ListGetAt(Student_ID,whichStudent)#</cfoutput></td>
<td><cfoutput>#ListGetAt(Last_Name,whichStudent)#</cfoutput></td>
<td><cfoutput>#ListGetAt(First_Name,whichStudent)#</cfoutput></td>
</tr>
</CFLOOP>
</table>

Hope it helps,

-Carl

 

[mdd]

This is exactly what I have been trying to do - except I need to take it a bit further... I need to give the user the ability to enter the parameter they want to query by, then send the results of that query to excel.

What I REALLY would like to do is: have a list menu, with the options of HTML and Excel. The user would enter the parameter they are querying by, select HTML or Excel and they either get a web page, or the file to Excel. Because I have been told you cant do that, I am separating my tasks and one option will be the web page and a separate query option will be the Excel query.

I have tested the cfcontent and selected * and send it to Excel. That works. But how do I set up a parameter? I'm on an Intranet also.
And if anyone knows how to solve the list menu question, that would be FABULOUS!
Thanks!! M

 

[csteinhilber]

I actually do this all the time (if I'm getting what you're doing).

Basically I have a page with two... could be links, could be a select box, could be checkboxes, could be submit buttons... whatever. I'll use submit buttons as an example because it's closer to what you're after:

<form action=&quot;displaypage.cfm&quot; method=&quot;post&quot; ...>
Enter your product category here:
<input type=&quot;text&quot; name=&quot;partnumber&quot;><br />
<input type=&quot;submit&quot; name=&quot;submit&quot; value=&quot;Open as Excel&quot;>
<input type=&quot;submit&quot; name=&quot;submit&quot; value=&quot;Open as HTML&quot;>
</form>

then displaypage.cfm looks something like:

<CFPARAM name=&quot;FORM.submit&quot; default=&quot;open as html&quot;>
<CFIF CompareNoCase(FORM.submit,&quot;open as excel&quot;) EQ 0>

<CFSET htmlMode=false>
<cfcontent type=&quot;application/vnd.ms-excel&quot;>
<cfheader name=&quot;Content-Disposition&quot; value=&quot;filename=ActiveStudents.xls&quot;>

<CFELSE>
<CFSET htmlMode=true>
<!DOCTYPE html PUBLIC &quot;-//W3C//DTD XHTML 1.0 Transitional//EN&quot;    
&quot;[URL unfurl="true"]http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd&quot;>[/URL]
<html xmlns=&quot;[URL unfurl="true"]http://www.w3.org/1999/xhtml&quot;>[/URL]
<head>
	<title>HTML Display</title>
</head>

<body>

</CFIF>

<CFIF IsDefined(&quot;FORM.productCategory&quot;) and len(trim(FORM.productCategory)) GT 0>
<!--- run query --->
<CFQUERY name=&quot;getProducts&quot; ...>
SELECT * 
  FROM productTable
 WHERE product_category = '#FORM.productCategory'>

</CFQUERY>

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

<CFELSE>
   Please enter a product category!

</CFIF>

<CFIF htmlMode>
</body>
</html>
</CFIF>

Note the Excel display and the HTML display are handled by the same page by using the CFIF at the top that defines whether the page uses

<CFHEADER>/<CFCONTENT>

or

<!DOCTYPE>/<HTML>

. This is possible because versions of Excel newer than Office97 dynamically convert HTML tables in cells of the spreadsheet. Your mileage may vary if you have other versions of Excel, etc.

I'm just using CFDUMP above, but you could easily build your own tables however you wish.

Is that what you mean?
-Carl

 

[parly]

Is is possible to generate the *.xls file and allow the user to save it to the destination of their choice?

 

[csteinhilber]

They should be able to do that themselves once it opens in the browser. Simply choose File -> Save As...



-Carl

 

[parly]

Sorry, Carl, I should have told you I am using mimi2's example code because my users need to open the file in Excel not in the browser. It works fine except it is not practical for the user, as the code will write and append data to an Excel file in the destination of the programmer's choice, *not* the user's. Obviously, this would mean hanging this file off the root, as this would be the only sure available place to save it. As I said, not practical.

I am hoping there is a way to do everything this code does *and* give the user an opportunity to save the file where they want.

 

[parly]

Okay, Carl, I've given up on opening the file in Excel in lieu of opening an Excel spreadsheet in an I.E browser window as you suggest. I'm attempting to use the example you give as my users use Office 97 or greater (or did you mean *greater than* '97? Hmmm. Does that mean that this code will not work in Excel 97?

I must be missing something, however, as the user is prompted to open or save (so far so good) but it's a *.cfm studio file not a *.xls file they are saving.

Please assist one more time. Thanks.

 

[RichardBro]

There is an effective and fairly easy solution, see the explaination at

http://www.cfcomet.com/Excel/index.cfm?ArticleID=B5ED33FB-5CB1-4ACC-899689A15A0E1539

there is also an article on running macros etc.

 

[parly]

I guess &quot;fairly easy&quot; is relative I could not glean enough from the article and code at the site to make it run error free for me. I kept getting 'COM' errors. Once I got errors on my server that I believe were in response to this code. I'm sure it's something I didn't do correctly and not the responsibility of the code...but that still leaves me solution-free.

Please someone help me with any solution that will allow my users to save their generated Excel file anywhere *but* on my server. At this point I don't care if it's inline or Excel. My users need to generate the data, view and save (but NOT saved to my server and that's what all the above code seems to do).

Help please.

 

[igormed]

Hi Peter,

It is really great... It works! I have just one problem and can't come out:

Your code (at the top of the page):
<table border=1>
<tr>
<td><b>First Name</b></td>
<td><b>Last Name</b></td>
<td><b>Phone</b></td>
<td><b>Email</b></td>
</tr>
</table>

... I just cannot make it dynamic... I would like to have something as: <td>#First Name#</td>

but if I do so, than I'm getting (in Excel) only the last record and only one...

What I'doing wrong?

Thanks in Advance,
Igor

 

[csteinhilber]

Sorry... just got back from vacation...

I must be missing something, however, as the user is prompted to open or save (so far so good) but it's a *.cfm studio file not a *.xls file they are saving.

I'm confused. Is the file opening in the Excel control, or are you getting the prompt dialog immediately? If you're getting the prompt dialog (to open or save)... it sounds like you don't have a full install of Excel (which should include the Excel OCX for IE).

Have you tried selecting &quot;Open&quot; rather than &quot;Save&quot;?

Once it opens in the browser (assuming the OCX is installed and working properly), if you go to File -> Save As... in IE, it should automatically choose &quot;Excel Workbook (.XLS)&quot; as the file type. And the file will, indeed, be saved as an .xls file, not a .cfm.

Our I/T still only supports Excel 97... so that's what we use. So it's definitely &quot;Excel 97 or greater&quot;, not &quot;greater than Excel 97&quot;.



-Carl

 

[parly]

Hi carl, I'm sorry too, I just returned from my vacation, hope yours was as good as mine.

You write:
I'm confused. Is the file opening in the Excel control, or are you getting the prompt dialog immediately? If you're getting the prompt dialog (to open or save)... it sounds like you don't have a full install of Excel (which should include the Excel OCX for IE).

All my users are using MS Excel 97 or better and I wanted to use the table layout option so I used the example you gave beginning with:

or, if everything is up-to-date on the users machine, Excel (versions greater than 97) usually converts HTML tables to cells fairly nicely:

I didn't receive an option at all. It just saved the file on the server so I could get it of course but no one else could. I did edit some of the code and tried some of the other options above but could not give the user the option to save it where they wanted even though, yes, they do have the full version installed.

The work-around to this problem is I set up an automatic <cfmail> to send each logged in user who requested the file the attached file to their email address. It works for this little ditty, but I have future projects that need to allow users to view in MS Excel then save if they desire.

Which of the above examples are you meaning will work like this?

 

[csteinhilber]

Whoa... now I'm really confused. (and, yes, my vacation was great... where'd you go)


Neither

<CFCONTENT>

nor

<CFHEADER>

should be able to save a file on the server itself. What's your code look like??


-Carl

 

[11JTH11]

I just have an additional question. I am using code similar to some of the above:
<CFFILE contentType=&quot;application/x-msexcel&quot;
action=&quot;write&quot;

etc... only I wish to know how does one force gridlines to show in the resulting excel file? I suppose it would be to good to ask that there be a &quot;showgridlines=yes&quot; attribtue!?

Thanks in advance.

 

[aimee95]

hey 11jth11,
just wondering if you've had any luck with your gridline problem. I'm having similar problems and wondered if you'd struck upon an answer yet?

 

[11JTH11]

g'day aimee95,

yes I have a solution - of sorts - ... just not as elegant perhaps as a &quot;showgridlines=yes&quot; attribute. All I did was have the border attribute of the tables being created (<table border=1>)set to 1. These tables are written directly into the excel spreadsheet. The cells in the spreadsheet which data have gridlines, but the unused portions of the excel spreadsheet do not. The users didn't mind, so I stopped there.
Hope that helps.

 

[aimee95]

thanks for your help 11jth11, will try it that way!

 

[peterswan]

Hello,

This thread has been most informative. Does anyone know of a way to close an instance of excel from ColdFusion if a user wants to reload the same excel page with new data? Here's the code I'm using:

<CFHEADER NAME=&quot;Content-Disposition&quot; VALUE=&quot;inline; filename=temp.xls&quot;><CFCONTENT TYPE=&quot;application/msexcel&quot; >

Then I'm running a cfoutput of query information. It works great and loads right into Excel the first time. But the next time they try to open the same page with new query parameters, the old page is displayed. Maybe someone might know of a way to refresh the excel page or close out the old one before the new one loads.

Thanks for any help

Peter Swanson