• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 427
  • Last Modified:

Exporting my Cold Fusion Result Set to Excel

I am a newbie to Cold Fusion and I was hoping someone could help with the following.  I have an output query that delimits and outputs data pulled from a database.  I need to export this result set out to excel.  Can someone please help me with this.  Here is the output query that I am using, and like I said I need to export the results of this query to Excel

<cfoutput query="Results">
     <cfset answerArray = ListToArray(youranswer, '^') />
     
     <cfif ArrayLen(answerArray) EQ 3>
        <cfset response = ListGetAt(answerArray[1], 2, '~') />
        <cfset correct = ListGetAt(answerArray[2], 2, '~') />
        <cfset review = ListGetAt(answerArray[3], 2, '~') />
     <cfelse>
         <cfset response = '' />
         <cfset correct = '' />
         <cfset review = '' />
     </cfif>
studentid: #studentid, itemnumber: #itemnumber#, response : #response#, correct: #correct#, review: #review#
<br />
</cfoutput>

Thanks for the help
Nick
0
nmarano
Asked:
nmarano
  • 13
  • 12
  • +2
1 Solution
 
Ken-dohCommented:
Hi

I would suggest using a custom tag like query2excel  

http://www.cflib.org/udf.cfm?ID=560


0
 
73SpyderCommented:
Make a second copy of the cfm page

at the top of the page

put this:

<cfset variables.extension="#url.extension#">
<cfif variables.extension EQ "doc">
      <cfset variables.image="word.jpg">
<cfelse>
      <cfset variables.image="excel-icon.gif">
</cfif>
<cfset variables.local_path="#application.file_path#">
<cfset variables.http_path="#application.internal_path#">
 
<cfsetting showdebugoutput="no">
<cfparam name="extension" default=".xls">
<cfset variables.time_stamp = DateFormat(Now(), "mmddyy")&"_"&TimeFormat(Now(), "hhmmss")>
<cfset variables.xls_name = "report_name_"&variables.time_stamp&".#extension#">
<cfset variables.xls_name = Replace(variables.xls_name, " ", "", "ALL")>
<cfset xlsfilename ="variables.xls_name">
<cfsavecontent variable="xlsfile">

Then all your page stuff

then at the bottom put this:

</cfsavecontent>
<cffile action="write" file="#application.file_path#/data/#variables.xls_name#" output="#xlsfile#">

Then you can link to the file for people to download and/or view

I also use this to clean the old files out.

<cfset variables.location = application.file_path&"\data\">
<cfdirectory action="list" name="file_list" directory="#variables.location#" filter="*.xls">

<cfset variables.date_cutoff = Now()>
<cfif file_list.recordcount GT 0>
      <cfloop query="file_list">
            <cfset variables.date_diff = DateDiff("n", variables.date_cutoff, file_list.datelastmodified)* -1>
            <cfif variables.date_diff GTE 60>
                  <cffile action="delete" file="#variables.location##file_list.name#">
            </cfif>
      </cfloop>
</cfif>



You will need to pass the variables you need.  I normally do this via a url query string.
if you look at the code, I use this same code for exporting to word or excel I just pass which entension I want to use.  any styles you want need to be in a style sheet in the page not linked and inside the cfsavecontent tags.

-73Spyder
0
 
nmaranoAuthor Commented:
Thanks for the link, although this is beyond what I understand.  Could you explain how I would incorporate it with the Cfoutput that I have above?

Thanks
Nick
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
73SpyderCommented:
My post is not using the link from the other poster.  below is along the lines of what to do.  you would need to put your query in the space below, then at the bottom, provide a link to the generated file.

What this script does is create an excel file on the fly for you and saves it on your server.  Then you provide a link to it.  the last part of my code will delete any .xls files that are 60 days old.  this should keep the number of files in the temp folder low.  you can change the value to delete any files that are 1 day old if you want to

<cfset variables.extension="#url.extension#">
<cfif variables.extension EQ "doc">
     <cfset variables.image="word.jpg">
<cfelse>
     <cfset variables.image="excel-icon.gif">
</cfif>
<cfset variables.local_path="#application.file_path#">
<cfset variables.http_path="#application.internal_path#">
 
<cfsetting showdebugoutput="no">
<cfparam name="extension" default=".xls">
<cfset variables.time_stamp = DateFormat(Now(), "mmddyy")&"_"&TimeFormat(Now(), "hhmmss")>
<cfset variables.xls_name = "report_name_"&variables.time_stamp&".#extension#">
<cfset variables.xls_name = Replace(variables.xls_name, " ", "", "ALL")>
<cfset xlsfilename ="variables.xls_name">
<cfsavecontent variable="xlsfile">

<cfquery name="results" datasource="yourdsn">
  SElect * from sometable
</cfquery>


<cfoutput query="Results">
     <cfset answerArray = ListToArray(youranswer, '^') />
     
     <cfif ArrayLen(answerArray) EQ 3>
        <cfset response = ListGetAt(answerArray[1], 2, '~') />
        <cfset correct = ListGetAt(answerArray[2], 2, '~') />
        <cfset review = ListGetAt(answerArray[3], 2, '~') />
     <cfelse>
         <cfset response = '' />
         <cfset correct = '' />
         <cfset review = '' />
     </cfif>
studentid: #studentid, itemnumber: #itemnumber#, response : #response#, correct: #correct#, review: #review#
<br />
</cfoutput>

</cfsavecontent>

<!---  the application file path just hold a path for your app to store the files --->
<cffile action="write" file="#application.file_path#/data/#variables.xls_name#" output="#xlsfile#">



<cfset variables.location = application.file_path&"\data\">
<cfdirectory action="list" name="file_list" directory="#variables.location#" filter="*.xls">

<cfset variables.date_cutoff = Now()>
<cfif file_list.recordcount GT 0>
     <cfloop query="file_list">
          <cfset variables.date_diff = DateDiff("n", variables.date_cutoff, file_list.datelastmodified)* -1>
          <cfif variables.date_diff GTE 60>
               <cffile action="delete" file="#variables.location##file_list.name#">
          </cfif>
     </cfloop>
</cfif>
0
 
nmaranoAuthor Commented:
Sorry,

I didn't see your post.  I will try that now

Thanks
0
 
incapitalCommented:
You can just use a table to format the query output, and then use a cfcontent and cfheader to display it in a spreadsheet:

<cfheader name="Content-Disposition" value="attachment; filename=myFile.xls">
<cfcontent type="application/msexcel">
0
 
nmaranoAuthor Commented:
I ran it and got this error

Element EXTENSION is undefined in URL.  
 
 
The error occurred in C:\Inetpub\wwwroot\nmarano\delim1.cfm: line 1
 
1 : <cfset variables.extension="#url.extension#">
2 : <cfif variables.extension EQ "doc">
3 :      <cfset variables.image="word.jpg">
 
0
 
73SpyderCommented:
yes,  line 1 is looking for the extension type to be pass in via url either xls or doc.  If you do not want that, change it to

<cfset variables.extension="xls">
0
 
73SpyderCommented:
look through my code there are a few vairbales that you will need to put your own values in
0
 
nmaranoAuthor Commented:
This will take me some time to go through as I do not completely understand, but I will let you know the results.  Thanks for the help so far.
0
 
73SpyderCommented:
np.  If you have issues let me know.  just to provide a little road map.

This code was designed to have a page with the output displayed in the browser with a link to the excel part.  If the user wanted the results in excel they would click a link and the code I gave you would be called.  That is why it was looking for the extension.

Once the code provided is run it will create an excel file and save it on your server. then you provide a link to the new file.

again, if you have any issues, ask questions, and if possible post the code too.

0
 
nmaranoAuthor Commented:
Can you explain what I need to do here?  I get this error message

The requested scope application has not been enabled.  
Before application variables can be used, the application state management system must be enabled using the CFAPPLICATION tag.  
 
The error occurred in C:\Inetpub\wwwroot\nmarano\delim1.cfm: line 7
 
5 :      <cfset variables.image="excel-icon.gif">
6 : </cfif>
7 : <cfset variables.local_path="#application.file_path#">
8 : <cfset variables.http_path="#application.internal_path#">
9 :  

 



Here is all the code from the page that I am working on

<cfset variables.extension="xls">
<cfif variables.extension EQ "doc">
     <cfset variables.image="word.jpg">
<cfelse>
     <cfset variables.image="excel-icon.gif">
</cfif>
<cfset variables.local_path="#application.file_path#">
<cfset variables.http_path="#application.internal_path#">
 
<cfsetting showdebugoutput="no">
<cfparam name="extension" default=".xls">
<cfset variables.time_stamp = DateFormat(Now(), "mmddyy")&"_"&TimeFormat(Now(), "hhmmss")>
<cfset variables.xls_name = "report_name_"&variables.time_stamp&".#extension#">
<cfset variables.xls_name = Replace(variables.xls_name, " ", "", "ALL")>
<cfset xlsfilename ="variables.xls_name">
<cfsavecontent variable="xlsfile">

<cfquery name="Results" datasource="testremoting">
      Select Studentid, itemnumber, youranswer From itemresponse_algmis where testletid='3' order by itemnumber,studentid
      </cfquery>


<cfoutput query="Results">
     <cfset answerArray = ListToArray(youranswer, '^') />
     
     <cfif ArrayLen(answerArray) EQ 3>
        <cfset response = ListGetAt(answerArray[1], 2, '~') />
        <cfset correct = ListGetAt(answerArray[2], 2, '~') />
        <cfset review = ListGetAt(answerArray[3], 2, '~') />
     <cfelse>
         <cfset response = '' />
         <cfset correct = '' />
         <cfset review = '' />
     </cfif>
studentid: #studentid#, itemnumber: #itemnumber#, response : #response#, correct: #correct#, review: #review#
<br />
</cfoutput>

</cfsavecontent>

<!---  the application file path just hold a path for your app to store the files --->
<cffile action="write" file="#application.file_path#/data/#variables.xls_name#" output="#xlsfile#">



<cfset variables.location = application.file_path&"\data\">
<cfdirectory action="list" name="file_list" directory="#variables.location#" filter="*.xls">

<cfset variables.date_cutoff = Now()>
<cfif file_list.recordcount GT 0>
     <cfloop query="file_list">
          <cfset variables.date_diff = DateDiff("n", variables.date_cutoff, file_list.datelastmodified)* -1>
          <cfif variables.date_diff GTE 60>
               <cffile action="delete" file="#variables.location##file_list.name#">
          </cfif>
     </cfloop>
</cfif>

Thanks
Nick
0
 
73SpyderCommented:
Yes I can.

These lines need to be set up

<cfset variables.local_path="#application.file_path#">
<cfset variables.http_path="#application.internal_path#">  //-- delete this one.  not used.

these lines are refering to 2 variables that need to be in your application file.

In the application file you would do this:

<cfset application.file_path = "">  //-- Inside the "" put http://www.yoursite.com/folderforfilestobedreated


Back inside your main page you will need a link to the file you jsut created  like this:

<cfoutput>
<a href="#application.file_path#/data/#variables.xls_name#">excel file</a>
</cfoutput>
0
 
nmaranoAuthor Commented:
Are you refering to the Application.cfm file as my application file?  And if so, would I simply create the application.cfm file and put

<cfset application.file_path = "C:\Inetpub\wwwroot\nicks_delim">

Because right now all I have is the one file of code from above.  

Again Thanks for all the help, and the patience.

Nick
0
 
73SpyderCommented:
Yes, that is what I am referring to.  If you do not need this file, you can just use a local variable on the page.

<cfset variables.local_path="http://www.yoursite.com">

Use this line, with your site info, instead of this one. <cfset variables.local_path="#application.file_path#">


This should work for you without having to create an application file.
0
 
nmaranoAuthor Commented:
I did what I think you asked me to do and got this error

An error occurred when performing a file operation write on file C:\Inetpub\wwwroot\nmarano/data/report_name_042406_094035.xls.  
The cause of this exception was: java.io.FileNotFoundException: C:\Inetpub\wwwroot\nmarano\data\report_name_042406_094035.xls (The system cannot find the path specified).  
 
The error occurred in C:\Inetpub\wwwroot\nmarano\delim1.cfm: line 43
 
41 :
42 : <!---  the application file path just hold a path for your app to store the files --->
43 : <cffile action="write" file="#application.file_path#/data/#variables.xls_name#" output="#xlsfile#">
44 :
45 :

 
In my appication.cfm file I have
<cfapplication name="delim">

<cfset application.file_path = "C:\Inetpub\wwwroot\nmarano">

And in my main page for the Cfoutput I have

<cfoutput query="Results">
     <cfset answerArray = ListToArray(youranswer, '^') />
     
     <cfif ArrayLen(answerArray) EQ 3>
        <cfset response = ListGetAt(answerArray[1], 2, '~') />
        <cfset correct = ListGetAt(answerArray[2], 2, '~') />
        <cfset review = ListGetAt(answerArray[3], 2, '~') />
     <cfelse>
         <cfset response = '' />
         <cfset correct = '' />
         <cfset review = '' />
  </cfif>
studentid: #studentid#, itemnumber: #itemnumber#, response : #response#, correct: #correct#, review: #review#
<br />
<a href="#application.file_path#/data/#variables.xls_name#">excel file</a>
</cfoutput>

Thanks

Nick
0
 
73SpyderCommented:
Ok.  Do you have a folder C:\Inetpub\wwwroot\nmarano\data  ?

If not change this line

<cffile action="write" file="#application.file_path#/data/#variables.xls_name#" output="#xlsfile#">

to

<cffile action="write" file="#application.file_path#/#variables.xls_name#" output="#xlsfile#">

0
 
jimmy282Commented:
I just posted some sample code regarding this here
http://www.experts-exchange.com/Web/WebDevSoftware/ColdFusion/Q_21823728.html

you might want to have a look.

cheers.
0
 
nmaranoAuthor Commented:
We're pretty close!  It is writing and creating an excel file, but it is skipping 10 rows, inserting a record, skipping 10 more, and so on.  Also all of the info is in one column.  Is there a way to seperate #studentid#, #itemnumber#,#response#, #correct#, #review# into their own columns?  studentid could be column A and response could be B and so on?  


Thanks
Nick
0
 
nmaranoAuthor Commented:
Jimmy,

Thanks for the link, I will take a look

Nick
0
 
73SpyderCommented:
there must be a rouge loop in the code.

And yes,  put the #studentid#, #itemnumber#,#response#, #correct#, #review#  in a table

<table>
<tr>
<td>Student ID</td>
<td>Item Number</td>
//  etc.. for the headers
</tr>


/// put a cfloop or outout block here

<tr>
<td>#studentid#</td>
<td>#itemnumber#</td>

</tr>

</table>


This will put the values into there own columns
0
 
73SpyderCommented:
The skipping could also be caused by the fact that the data is not laid out in a table.  Try putting this all in a table and see what happens
0
 
nmaranoAuthor Commented:
Spyder,

You're correct about the skipping, once I put it into a table there was no more skipping.  I attempted the CFLOOP, and placed the code below, Can you tell me what I am missing

Thanks
Nick

<cfoutput query="Results">
     <cfset answerArray = ListToArray(youranswer, '^') />
     
     <cfif ArrayLen(answerArray) EQ 3>
        <cfset response = ListGetAt(answerArray[1], 2, '~') />
        <cfset correct = ListGetAt(answerArray[2], 2, '~') />
        <cfset review = ListGetAt(answerArray[3], 2, '~') />
     <cfelse>
         <cfset response = '' />
         <cfset correct = '' />
         <cfset review = '' />
  </cfif>
  <table>
<tr>
<td>Student ID</td>
<td>Item Number</td>
<td>Response</td>
<td>Correct</td>
<td>Review</td>
</tr>


<CFLOOP query="Results">
<tr>
<td>#studentid#</td>
<td>#itemnumber#</td>
<td>#response#</td>
<td>#correct#</td>
<td>#review#</td>
</tr>

</table>

</CFLOOP>
<!---<br /><a href="#application.file_path#/#variables.xls_name#">excel file</a>--->
</cfoutput>
0
 
73SpyderCommented:
Try this


<table>
<tr>
<td>Student ID</td>
<td>Item Number</td>
<td>Response</td>
<td>Correct</td>
<td>Review</td>
</tr>

<cfoutput query="Results">
     <cfset answerArray = ListToArray(youranswer, '^') />
     
     <cfif ArrayLen(answerArray) EQ 3>
        <cfset response = ListGetAt(answerArray[1], 2, '~') />
        <cfset correct = ListGetAt(answerArray[2], 2, '~') />
        <cfset review = ListGetAt(answerArray[3], 2, '~') />
     <cfelse>
         <cfset response = '' />
         <cfset correct = '' />
         <cfset review = '' />
  </cfif>
<tr>
<td>#studentid#</td>
<td>#itemnumber#</td>
<td>#response#</td>
<td>#correct#</td>
<td>#review#</td>
</tr>

</table>


</cfoutput>
0
 
nmaranoAuthor Commented:
When I try to open it in Excel It came back saying Cell data too large.  It appears that most of the data is being place in one cell from the little that I could see.  
0
 
73SpyderCommented:
I think I put the closing cfoutut in the wrong spot  try this

<table>
<tr>
<td>Student ID</td>
<td>Item Number</td>
<td>Response</td>
<td>Correct</td>
<td>Review</td>
</tr>

<cfoutput query="Results">
     <cfset answerArray = ListToArray(youranswer, '^') />
     
     <cfif ArrayLen(answerArray) EQ 3>
        <cfset response = ListGetAt(answerArray[1], 2, '~') />
        <cfset correct = ListGetAt(answerArray[2], 2, '~') />
        <cfset review = ListGetAt(answerArray[3], 2, '~') />
     <cfelse>
         <cfset response = '' />
         <cfset correct = '' />
         <cfset review = '' />
  </cfif>
<tr>
<td>#studentid#</td>
<td>#itemnumber#</td>
<td>#response#</td>
<td>#correct#</td>
<td>#review#</td>
</tr>
</cfoutput>

</table>


0
 
nmaranoAuthor Commented:
Spyder,

Thank you very much for all the help.  You've been great.  Thanks for being patient with me, I really appreciate it.  I am posting all of the code here if anyone else needs it.  Again thanks

<cfset variables.extension="xls">
<cfif variables.extension EQ "doc">
     <cfset variables.image="word.jpg">
<cfelse>
     <cfset variables.image="excel-icon.gif">
</cfif>
<cfset variables.local_path="#application.file_path#">
<!---<cfset variables.http_path="#application.internal_path#">--->
 
<cfsetting showdebugoutput="no">
<cfparam name="extension" default=".xls">
<cfset variables.time_stamp = DateFormat(Now(), "mmddyy")&"_"&TimeFormat(Now(), "hhmmss")>
<cfset variables.xls_name = "report_name_"&variables.time_stamp&".#extension#">
<cfset variables.xls_name = Replace(variables.xls_name, " ", "", "ALL")>
<cfset xlsfilename ="variables.xls_name">
<cfsavecontent variable="xlsfile">

<cfquery name="Results" datasource="testRemoting">
SELECT Studentid, itemnumber, youranswer
FROM itemresponse_algmis
WHERE testletid='3'
ORDER BY itemnumber,studentid
</cfquery>

<table>
<tr>
<td>Student ID</td>
<td>Item Number</td>
<td>Response</td>
<td>Correct</td>
<td>Review</td>
</tr>

<cfoutput query="Results">
     <cfset answerArray = ListToArray(youranswer, '^') />
     
     <cfif ArrayLen(answerArray) EQ 3>
        <cfset response = ListGetAt(answerArray[1], 2, '~') />
        <cfset correct = ListGetAt(answerArray[2], 2, '~') />
        <cfset review = ListGetAt(answerArray[3], 2, '~') />
     <cfelse>
         <cfset response = '' />
         <cfset correct = '' />
         <cfset review = '' />
  </cfif>
<tr>
<td>#studentid#</td>
<td>#itemnumber#</td>
<td>#response#</td>
<td>#correct#</td>
<td>#review#</td>
</tr>
</cfoutput>

</table>



</cfsavecontent>

<!---  the application file path just hold a path for your app to store the files --->
<!---<cffile action="write" file="#application.file_path#/data/#variables.xls_name#" output="#xlsfile#">--->
<cffile action="write" file="#application.file_path#/#variables.xls_name#" output="#xlsfile#">




<cfset variables.location = application.file_path&"\data\">
<cfdirectory action="list" name="file_list" directory="#variables.location#" filter="*.xls">

<cfset variables.date_cutoff = Now()>
<cfif file_list.recordcount GT 0>
     <cfloop query="file_list">
          <cfset variables.date_diff = DateDiff("n", variables.date_cutoff, file_list.datelastmodified)* -1>
          <cfif variables.date_diff GTE 60>
               <cffile action="delete" file="#variables.location##file_list.name#">
       </cfif>
  </cfloop>
</cfif>
0
 
73SpyderCommented:
Glad to have helped
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 13
  • 12
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now