8 Replies Latest reply on Dec 22, 2006 8:06 AM by dwright65

    export to excel

    Level 7
      Hi All,
      I'm exporting a query to excel file but my code is on the top of the page
      after the query. I'm using get method to validate the form with url
      variables.
      The download works, but at the end of the file also includes all page code.
      I try cfabort but doesn't work.
      FYI I'm using cf7 with application.cfc
      How can I fix this problem?
      Thank you in advance.

      Johnny
      <cfif IsDefined("url.downloadReport")>
      <CFHEADER NAME="Content-Disposition" VALUE="inline;
      filename=contactsReport.csv">
      <CFCONTENT
      TYPE="application/unknown">"Company","City","State","Zipcode","SalesRep","Type","First
      Name","Last Name","Title","Phone","Ext","Mobile","Fax","Email"
      <cfoutput
      query="getStruc">"#customer#","#city#","#state#","#Zipcode#","#appearance#","#typeDesc#", "#cfirstName#","#clastName#","#ctitle#","#cPhone#","#cExtension#","#cMobile#","#cFax#","#c Email#"#chr(13)#</cfoutput>
      <cfabort>
      </cfif>


        • 1. Re: export to excel
          insuractive Level 3
          Have you tried using <cfsetting enablecfoutputonly="Yes">? You'd have to wrap all the text you want to actually output in <cfoutput> tags, which means changing <cfoutput query="getStruc"> to <cfloop query="getStruc">, but that may fix your problem.
          • 2. Re: export to excel
            Level 7
            Tks for reply, I just try and it doesn't work.
            Maybe I have to setup in different way???
            Rgds

            Johnny

            <cfif IsDefined("url.downloadReport")>
            <CFHEADER NAME="Content-Disposition" VALUE="inline;
            filename=contactsReport.csv">
            <cfsetting enablecfoutputonly="Yes">
            <CFCONTENT
            TYPE="application/unknown">"Company","City","State","Zipcode","SalesRep","Type","First
            Name","Last Name","Title","Phone","Ext","Mobile","Fax","Email"
            <cfloop
            query="getStruc"><cfoutput>"#customer#","#city#","#state#","#Zipcode#","#appearance#","#t ypeDesc#","#cfirstName#","#clastName#","#ctitle#","#cPhone#","#cExtension#","#cMobile#","# cFax#","#cEmail#"#chr(13)#</cfoutput></cfloop>
            <cfabort>
            </cfif>

            "insuractive" <webforumsuser@macromedia.com> wrote in message
            news:embr4s$9ak$1@forums.macromedia.com...
            > Have you tried using <cfsetting enablecfoutputonly="Yes">? You'd have to
            > wrap
            > all the text you want to actually output in <cfoutput> tags, which means
            > changing <cfoutput query="getStruc"> to <cfloop query="getStruc">, but
            > that may
            > fix your problem.
            >


            • 3. export to excel
              insuractive Level 3
              Make sure you move the <cfsetting> command to the top of the page. I had success using the following code:

              Hope that helps!

              • 4. Re: export to excel
                Level 7
                Tks for you reply and help.
                Looks like I have a different issue with <cfabort> and application.cfc
                Before I found a site that told me to use the code above to pass <cfabort>
                error
                <cfif arguments.exception.rootCause eq
                "coldfusion.runtime.AbortException">
                <cfreturn/>
                </cfif>

                If I take this code out of the cfc.. works... but give me cfabort error.
                coldfusion.runtime.AbortException at
                coldfusion.tagext.lang.AbortTag.doStartTag(AbortTag.java:62) at
                coldfusion.runtime.CfJspPage._emptyTag(CfJspPage.java:1908) at
                cfsearchCustomer2ecfm2064181429._factor13

                Do you know anything about this?
                I will keep digging...
                Rgds

                Johnny



                "insuractive" <webforumsuser@macromedia.com> wrote in message
                news:emca28$qv3$1@forums.macromedia.com...
                > Make sure you move the <cfsetting> command to the top of the page. I had
                > success using the following code:
                >
                > <!--- Insert at top --->
                > <cfsetting enablecfoutputonly="Yes">
                >
                > <!--- Building a dummy query to use as an example (you can ignore
                > this) --->
                > <cfset getStruc =
                > QueryNew("customer,city,state,Zipcode,appearance,typeDesc,cfirstName,clastName,c
                > title,cPhone,cExtension,cMobile,cFax,cEmail")>
                > <cfset temp = QueryAddRow(getStruc, 1)>
                > <cfset QuerySetCell(getStruc, "customer", "1", 1)>
                > <cfset QuerySetCell(getStruc, "city", "testville", 1)>
                > <cfset QuerySetCell(getStruc, "state", "tx", 1)>
                > <cfset QuerySetCell(getStruc, "Zipcode", "55555", 1)>
                > <cfset QuerySetCell(getStruc, "appearance", "1", 1)>
                > <cfset QuerySetCell(getStruc, "typeDesc", "1", 1)>
                > <cfset QuerySetCell(getStruc, "cfirstName", "Test", 1)>
                > <cfset QuerySetCell(getStruc, "clastName", "User", 1)>
                > <cfset QuerySetCell(getStruc, "ctitle", "Mr", 1)>
                > <cfset QuerySetCell(getStruc, "cPhone", "555-5555", 1)>
                > <cfset QuerySetCell(getStruc, "cExtension", "123", 1)>
                > <cfset QuerySetCell(getStruc, "cMobile", "555-5556", 1)>
                > <cfset QuerySetCell(getStruc, "cFax", "555-5557", 1)>
                > <cfset QuerySetCell(getStruc, "cEmail", "testuser@somewhere.com", 1)>
                > <!--- End Dummy data section --->
                >
                > <!--- Make the <CFHEADER> tag is all on 1 line --->
                > <CFHEADER NAME="Content-Disposition" VALUE="inline;
                > filename=contactsReport.csv">
                > <!--- Notice the use of <cfoutput>'s below --->
                > <CFCONTENT
                > TYPE="application/unknown"><cfoutput>"Company","City","State","Zipcode","SalesRe
                > p","Type","First
                > Name","Last
                > Name","Title","Phone","Ext","Mobile","Fax","Email"#chr(13)#</cfoutput>
                > <cfloop
                > query="getStruc"><cfoutput>"#customer#","#city#","#state#","#Zipcode#","#appeara
                > nce#","#typeDesc#","#cfirstName#","#clastName#","#ctitle#","#cPhone#","#cExtensi
                > on#","#cMobile#","#cFax#","#cEmail#"#chr(13)#</cfoutput></cfloop>
                >
                >
                > Hope that helps!
                >


                • 5. Re: export to excel
                  efecto747 Level 1
                  Hi,

                  Exporting to Excel works best if you just generate standard html tables and then let excel convert it for you.

                  The following code should give you what you're after:

                  • 6. GOT IT
                    Level 7
                    Thanks for you reply...
                    These works finally, I just have to put the code at the end of the page,
                    otherwise it will include the page result inside the excel sheet.
                    Really appreciate you help.
                    Tks

                    Johnny

                    "efecto747" <webforumsuser@macromedia.com> wrote in message
                    news:emcqdc$fcd$1@forums.macromedia.com...
                    > Hi,
                    >
                    > Exporting to Excel works best if you just generate standard html tables
                    > and
                    > then let excel convert it for you.
                    >
                    > The following code should give you what you're after:
                    >
                    >
                    >
                    > <cfif IsDefined("url.downloadReport")>
                    > <cfheader name="Content-Disposition" value="inline;
                    > filename=acmesalesQ1.xls">
                    > <cfcontent type="application/vnd.msexcel">
                    > <table>
                    > <tr>
                    > <td>Company</td>
                    > <td>City</td>
                    > <td>State</td>
                    > <td>Zipcode</td>
                    > <td>SalesRep</td>
                    > <td>Type</td>
                    > <td>First Name</td>
                    > <td>Last Name</td>
                    > <td>Title</td>
                    > <td>Phone</td>
                    > <td>Ext</td>
                    > <td>Mobile</td>
                    > <td>Fax</td>
                    > <td>Email</td>
                    > </tr>
                    > <cfoutput query="getStruc">
                    > <tr>
                    > <td>#customer#</td>
                    > <td>#city#</td>
                    > <td>#state#</td>
                    > <td>#Zipcode#</td>
                    > <td>#appearance#</td>
                    > <td>#typeDesc#</td>
                    > <td>#cfirstName#</td>
                    > <td>#clastName#</td>
                    > <td>#ctitle#</td>
                    > <td>#cPhone#</td>
                    > <td>#cExtension#</td>
                    > <td>#cMobile#</td>
                    > <td>#cFax#</td>
                    > <td>#cEmail#</td>
                    > </tr>
                    > </cfoutput>
                    > </table>
                    > </cfif>
                    >


                    • 7. Re: export to excel
                      dwright65 Level 1
                      I'm trying to download a report into Excel for the first time and this thread has been helpful (I think?). The code seems to run just fine, but I'm not getting the window that pops up that would allow me to open/save the excel file. I used the last code set supplied by efecto747 to create the document. Any help would be appreciated.

                      David
                      • 8. Re: export to excel
                        dwright65 Level 1
                        Never mind - had an epiphany and figured it out as soon as I sent the message.