1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

Coldfusion - Export query data to excel sheet

Discussion in 'Programming' started by manjusha.pate, Oct 15, 2008.

0
  1. #1
    Hi,

    I am new to ColdFusion. Can anyone suggest me the correct way to implement export functionality in ColdFusion application.

    Below is the code -

    <cfsetting enablecfoutputonly="Yes">
    <cfquery name="queryData" datasource="#request.dsn#">
    SELECT * from EMPLOYEE
    </cfquery>
    <cfcontent type="application/msexcel">
    <cfheader name="Content-Disposition" value="filename=test.xls">

    <cfoutput>
    <table border="2">
    <tr>
    <td> ID </td><td> Name </td>
    </tr>
    <cfloop query="queryData">
    <tr>
    <td>#id#</td><td>#name#</td> </tr>
    </cfloop>
    </table>
    </cfoutput>

    Problem is -
    1) it doesn't appear as normal excel file. It looks like plain file.(doesn't appear lines of rows and column)
    2) HTML contents of my index.cfm file gets uploaded in this excel file. How to avoid this HTML contents?

    I want to export query result in column-row format in excel sheet.

    Thanks in advance.

    Regards,

    Manjusha
     
    manjusha.pate, Oct 15, 2008 IP
  2. chadzilla

    chadzilla Peon

    Messages:
    2
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #2
    I think the problem is your content type...Instead of "application/msexcel" for excel...try this..."application/vnd.ms-excel"

    That's what I use and it works perfectly
     
    chadzilla, Oct 16, 2008 IP
  3. manjusha.pate

    manjusha.pate Peon

    Messages:
    2
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #3
    Still it's not working. I am getting same output.

    Requirement - Export query result to excel sheet. But now I am just trying to open excel file on click of button.

    1)If I try to run below cfm file from wwwroot/Test folder (outside the project folder). I get popup to open the excel file and I am able to open it.

    <cfsetting enablecfoutputonly="Yes">
    <cfsetting showdebugoutput="false" />
    <cfcontent type="application/msexcel">
    <cfheader name="Content-Disposition" value="filename=test.xls">

    2)But if I try to run same code in my application, I am not able to open excel file in proper format .HTML contents of my index.cfm file (which is in wwwroot/project folder) gets uploaded in excel file.

    Please revert back if you get any solution.

    Can you tell me the all steps the way you have implemented this feature?

    Thanks in advance.
     
    manjusha.pate, Oct 16, 2008 IP
  4. chadzilla

    chadzilla Peon

    Messages:
    2
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #4
    I've got a couple of applications that use this. One is a report builder, and the other emails an excel itinerary as an attachment.

    Both work a little different. The report builder pops up a download box just like you were downloading a file from the internet and asks if you want to save or open the excel file.

    The email program actually copies a blank excel document, puts the data inside, renames it, attaches it to an email and sends it out.

    Either way, the code is pretty similar.

    Try this and see what you get. This is from my report builder, but I added your query and table structure.


    <cfquery name="queryData" datasource="#request.dsn#">
SELECT * 
FROM EMPLOYEE
</cfquery>

<body>

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



<table border="2">
<tr>
<td> ID </td><td> Name </td>
</tr>
 <cfoutput query="queryData">
<tr>
<td>#id#</td><td>#name#</td> 
</tr>
</cfoutput>
</table>

</cfcontent>
</body>
    Code (markup):
     
    chadzilla, Oct 16, 2008 IP
  5. robhustle

    robhustle Peon

    Messages:
    98
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    0
    #5
    robhustle, Oct 16, 2008 IP
  6. jbird4k

    jbird4k Peon

    Messages:
    15
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #6
    Try placing your Cfheader / cf content code at the bottom of the page so that it is the last bit of code to execute. Just before the </body> tag. By having it at the top of the page it has nothing to convert.
     
    jbird4k, Apr 9, 2009 IP
  7. machsimus

    machsimus Guest

    Messages:
    3
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #7
    Another CF noob with a question. I used the code from Chadzilla and it works, but even though I name the document export.xls, it still gives it a .cfm. Excel can open and display, but I'm sure our users won't like the extra errors popping up.

    Any ideas? Thanks in advance.
     
    machsimus, Apr 13, 2009 IP
  8. jbird4k

    jbird4k Peon

    Messages:
    15
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #8
    manjusha.pate

    The code works, when you view it on-line it views as html, if you save it to disk, and open it with excel you will get the look and feel your looking for.

    A quick question, what is the name of the template your code is in? is it index.cfm
    or are using a frame work such as fusebox / coldbox/ mach1 and its loading that index file?
     
    jbird4k, Apr 16, 2009 IP