?
Solved

How to output data in MS Excel format?

Posted on 2003-02-28
7
Medium Priority
?
437 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
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
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.

 

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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

When setting up new project requests for our site, one of the most powerful tools our team has available to use is Axure (http://www.axure.com/). It’s a tool for creating software and web prototypes that can function and interact as if it were the a…
Dramatic changes are revolutionizing how we build and use technology. Every company is automating, digitizing, and modernizing operations. We need a better, more connected way to work together as teams so we can harness the insights from our system…
The purpose of this video is to demonstrate how to create a Printer Friendly PDF on a WordPress Page. This will be demonstrated using a Windows 8 PC. Tools Used are Photoshop, Awesome Screenshot” Google Chrome Extension, and SmallPDF.com Log…
The purpose of this video is to demonstrate how to Import and export files in 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 : Click on Too…
Suggested Courses

621 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