?
Solved

How to output data in MS Excel format?

Posted on 2003-02-28
7
Medium Priority
?
431 Views
Last Modified: 2013-12-24
Hi,
I like to output data in MS Excel format, then which can be saved as a .xls file or copy/paste into other
XLS-compatible files.

Is it possible in ColdFusion? Anybody have a clue?

Any assistance would be very appreciated!

Jesse
0
Comment
Question by:jingwei541b
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 25

Accepted Solution

by:
Cyclops3590 earned 300 total points
ID: 8042584
as you will see I didn't write this.  I got it off of Macromedia's Developer Exchange.  If you want anything that is an outrageous request you can generally find it there.  Here is the code I found (I tried to clean it up, but there is too much)  Anyway here it is:

<!-----------------
Excel.cfm
-----------------
Author      Michael Walsh            
Copyright   Open Source, no copyright protection  
Version     1.2                                          
Date Originally Coded  October 01, 2001
Last Modified Date     October 04, 2001                  
Comment       Excel.cfm is used to export data from a
            query to an Excel document.  Excel.cfm      
            has been tested on IE 5.0 and NN 4.76 on
            a Windows NT platform using Cold Fusion
            Server 4.5.                        
Parameters
----------
INPUT (M)andatory (C)onditional
Query (M) - name of query
Columns (M) - pipe delimited list of the columns in the  
              query to display
HeaderTitles (C) - pipe delimited list of headers to
                   display on the Excel document
FileName (C) - name of the file you want to default in the
               save as dialog box (default: download.xls)
NoDataMessage (C) - message to display when no data was
                    returned in the query (default: There
                    was no data returned.)
Title (C) - title to display in the excel document
AlternateRowColor (C) - color to display on alternating
                        rows
FontFace (C) - comma delimited list of the font(s) to
               display on excel document (default: Arial,
               Verdana, Courier New)
Border (C) - allows to pass in a value for the table's
             border
OUTPUT
------
An excel document

HTML Output
-----------
None

Syntax (Examples)
----------------
<cf_excel Query="getMsg" Columns="SystemID|MsgCode|Message|ErrLevel|ShortMsg|UpdtUserID|UpdtDateTime" HeaderTitles="System ID|Message Code|Message|Error Level|Short Message|User ID|Date Time" FileName="AppMsgCode.xls" AlternateRowColor="gray" Title="Application Message Code" NoDataMessage="There was no data returned.">

Known Issues
------------
None

--->

<cfif not isdefined("Attributes.Query")
      or not isdefined("Attributes.Columns")
      or (isdefined("Attributes.HeaderTitles") and ListLen(Attributes.HeaderTitles, "|") is not ListLen(Attributes.Columns, "|"))>
            <!--- abort the tag if the mandatory parameters are not passed --->
            <cfabort>
<cfelse>
      <!--- set variables and defaults --->
      <cfset Variables.Query = "Caller." & Attributes.Query>
      <cfparam name="Attributes.FileName" default="download.xls">
      <cfparam name="Attributes.NoDataMessage" default="There was no data returned.">
      <cfparam name="Attributes.FontFace" default="Arial, Verdana, Courier New">
</cfif>

<!--- define the mime type to be returned to the current page and output the variable that contains the data --->
<cfheader name="Content-Disposition" value="inline; filename=#Attributes.FileName#">
<cfheader name="Expires" value="#Now()#">
<cfcontent type="application/vnd.ms-excel">

<table<cfif isdefined("Attributes.Border")> border="#Attributes.Border#"</cfif>>
      <cfif isdefined("Attributes.Title")>
            <cfoutput>
                  <tr>
                        <td colspan="#ListLen(Attributes.Columns, '|')#" align="center">
                              <strong>
                                    <font size="+1" face="#Attributes.FontFace#">
                                          #Attributes.Title#
                                    </font>
                              </strong>
                        </td>
                  </tr>
            </cfoutput>
      </cfif>
      <!--- build the header titles if passed in --->
      <cfif isdefined("Attributes.HeaderTitles")>
            <tr>
                  <cfloop index="HeaderList" list="#Attributes.HeaderTitles#" delimiters="|">
                        <td>
                              <strong>
                                    <cfoutput>
                                          <font face="#Attributes.FontFace#">
                                                #Variables.HeaderList#
                                          </font>
                                    </cfoutput>
                              </strong>
                        </td>
                  </cfloop>
            </tr>
      </cfif>
      <cfif Evaluate(Variables.Query & ".RecordCount") is 0>
            <cfoutput>
                  <tr<cfif isdefined("Attributes.AlternateRowColor")> bgcolor="#Attributes.AlternateRowColor#"</cfif>>
                        <td colspan="#ListLen(Attributes.Columns, '|')#" align="center">
                              <em>
                                    <font size="-1" face="#Attributes.FontFace#">
                                          #Attributes.NoDataMessage#
                                    </font>
                              </em>
                        </td>
                  </tr>
            </cfoutput>
      <cfelse>
            <!--- build the rows in the excel sheet --->
            <cfoutput query="#Variables.Query#">
                  <tr<cfif Evaluate(Variables.Query & ".CurrentRow") mod 2 is 1 and isdefined("Attributes.AlternateRowColor")> bgcolor="#Attributes.AlternateRowColor#"</cfif>>
                        <cfloop index="HeaderList" list="#Attributes.Columns#" delimiters="|">
                              <td>
                                    <cfif #Evaluate(Variables.Query & '.' & Variables.HeaderList)# is "">
                                          &nbsp;
                                    <cfelse>
                                          <font size="-1" face="#Attributes.FontFace#">
                                                #Evaluate(Variables.Query & '.' & Variables.HeaderList)#
                                          </font>
                                    </cfif>
                              </td>
                        </cfloop>
                  </tr>
            </cfoutput>
      </cfif>
</table>
0
 

Expert Comment

by:crthorn
ID: 8043921
I always use the following site for references related to ms office and cold fusion.  http://www.cfcomet.com/excel/

Hope this helps
0
 
LVL 1

Expert Comment

by:Concigliere
ID: 8043947
wow....how bout just loop ur content into a text file and delimit by tab?

first thing you would do would be to add ur column headers

ou can just hit the tab key as ur delimiter or you can find the chr(#) for it whicih i forget right off the top of my head

<cfset headers = "Column1     Column2     Column3">
 enter that into ur text file first, it will be used as the column headers when immporting into excel

then you can do the same thing for the content of each row


<cfloop query = "query">
<cffile action="write" addnewlien="yes" file="text.txt" ouput="#value1#     #value2#     #value3#">
</cfloop>

those are tabs between them believe it or not

so youd have something like

Column1     Column2     Column3
value1     value2     value3
value1     value2     value3
value1     value2     value3
value1     value2     value3


excel likes tab delimited files....

you can then import it into excel : )
try renaming the extension to .xls too to see if it opens up in excel automatically : )

0
Why Off-Site Backups Are The Only Way To Go

You are probably backing up your data—but how and where? Ransomware is on the rise and there are variants that specifically target backups. Read on to discover why off-site is the way to go.

 

Expert Comment

by:kkoehler
ID: 8044793
Jesse:

Yes, it is possible. I have done it before.  Here is how I did it:

 <CFSET OUTFILE= "filepath\filename.xls">          

  <CFIF #FileExists(OUTFILE)# is "Yes">
  <CFFILE ACTION=DELETE File="filepath\filename.xls">
  </CFIF>    
   
 <CFSET TXTOUTPUT='<table>
<tr>
     <td>Column1 name</td>
     <td>Column2 name</td>
</tr>
</table>'>
<CFFILE ACTION=APPEND FILE=#OUTFILE# OUTPUT=#TXTOUTPUT#>

<cfoutput query="Query Name">
 <CFSET TXTOUTPUT='<table>
<tr>
     <td>#Column1 data#</td>
     <td>#column2 data#</td>
</tr>
</table>'>
<CFFILE ACTION=APPEND FILE=#OUTFILE# OUTPUT=#TXTOUTPUT#>
</cfoutput>


Karla
0
 
LVL 17

Expert Comment

by:anandkp
ID: 8046681
if all the above dosent help - take a look at

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

K'Rgds
Anand
0
 

Author Comment

by:jingwei541b
ID: 8057662
Hi Guys,

First, I like to thank you guys. So many supports in a couple days. It supprises me. I really appreciate your helps.

I solve my problems based on the information you shared. I am new here and only have 75 points right now. I like you guys to shared these points. I will pay you more next time.

Thanks again, have fun!

Jesse
0
 

Author Comment

by:jingwei541b
ID: 8057743
Wow, I could not make you share the points. anandkp, kkoehler, crthorn and Concigliere, I like to thank you again for your help, esp. to  kkoehler and Concigliere for sharing your codes here.

Later,
Jesse
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Lease-to-own eliminates the expenditure of hardware replacement and allows you to pay off the server over time. Usually, this is much cheaper than leasing servers. Think of lease-to-own as credit without interest.
When it comes to security, close monitoring is a must. According to WhiteHat Security annual report, a substantial number of all web applications are vulnerable always. Monitis offers a new product - fully-featured Website security monitoring and pr…
The purpose of this video is to demonstrate how to insert an Iframe into WordPress. This will be demonstrated using a Windows 8 PC. Go to your WordPress login page. This will look like the following: mywebsite.com/wp-login.php : Open Page or Post…
The purpose of this video is to demonstrate how to add AdSense Ads to a WordPress Website, and how to set up WordPress to automatically place Ads in Sidebars. This will be demonstrated using a Windows 8 PC. Log into your AdSense account. : Cli…
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question