asked on

Export query to excel

I want to export a query to excel or CSV. I need to preserve the data as it is in the database. For example, if a number is 02578 I need the first zero to remain once it is exported.

I cannot use this method:

 <CFHEADER NAME="Content-Disposition" VALUE="attachment;filename=Cars.xls">
<cfcontent type="application/vnd.ms-excel" reset="true">

Open in new window

Because the file will contain HTML which is unacceptable for my application.

I tried to use this method, but numbers are being converted and leading zeros are being left off.

<cfcontent type="application/msexcel">
<cfheader name="Content-Disposition" value="filename=Cars.csv">

<cfoutput>Car#chr(delim)#Make#chr(delim)#Model#chr(delim)#VIN#chr(delim)#</cfoutput>

<cfoutput>#chr(13)#</cfoutput> <!--- line break after column header --->

<!--- Spill out data from a query --->
<cfloop query="carlist"><cfoutput>#Car##chr(delim)##Make##chr(delim)##Model##chr(delim)##VIN##chr(delim)#</cfoutput></cfloop>

Open in new window

Coast Line

Now here you need to use the style formatting which is used for Excel if you need the keep the 0 and all other things n tact, check this out

http://niallodoherty.com/post.cfm/basic-html-to-excel-formatting

earwig75

ASKER

@myselfandhawa, using that method creates HTML - I am trying to have a pure excel file and format the cells as text as you would normally in excel. Creating them with that method uses HTML for the formatting.

ASKER CERTIFIED SOLUTION

_agx_

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

earwig75

ASKER

@ _agx_

I read the documentation but I am confused. Can I create a spreadsheet on-the-fly based on what is contained in my database table?

Thanks.

_agx_

Sure. Here's an example tested w/CF10.


<cfset someQuery = queryNew("")>
<cfset queryAddColumn(someQuery, "name", "varchar", listToArray("John,Tom"))>
<cfset queryAddColumn(someQuery, "zipCode", "varchar", listToArray("02222,11111"))>

<cfset theSheet = SpreadSheetNew()>

<cfset SpreadsheetFormatColumn(theSheet,{dataformat="@"},1)>
<cfset SpreadsheetFormatColumn(theSheet,{dataformat="@"},2)>
<cfset SpreadsheetAddRows(theSheet, someQuery)>

<CFHEADER NAME="Content-Disposition" VALUE="attachment;filename=Cars.xls">
<cfcontent type="application/vnd.ms-excel" reset="true" variable="#SpreadSheetReadBinary(theSheet)#">

_agx_

If you're on CF9 be sure you've installed the updates. There were spreadsheet fixes in updates 1 and 2.

earwig75

ASKER

@_agx_

The spreadsheet generated like the below when I run it. I am using CF9 is that the issue?

John 2222
Tom 11111

earwig75

ASKER

Right now I am using it like this. I have 2 issues. There is no header-row and the columns are not formatting as text. Can this be fixed with this method?
Right now it is simply spitting out my query into a spreadsheet with excel handling all the formatting.

<cfset theSheet = SpreadSheetNew()>

<!--- format columns as text to preserve leading zeroes --->
<cfset SpreadsheetFormatColumn(theSheet,{dataformat="@"},1)>
<cfset SpreadsheetFormatColumn(theSheet,{dataformat="@"},2)> 
<cfset SpreadsheetFormatColumn(theSheet,{dataformat="@"},3)>
<cfset SpreadsheetFormatColumn(theSheet,{dataformat="@"},4)>
<cfset SpreadsheetAddRows(theSheet, mycarquery)> 

<CFHEADER NAME="Content-Disposition" VALUE="attachment;filename=Cars.xls">
<cfcontent type="application/vnd.ms-excel" reset="true" variable="#SpreadSheetReadBinary(theSheet)#">

Open in new window

_agx_

1) Did you see my comment about cf updates? What version of CF9 are you running?

<cfdump var="#server.coldfusion#">

> There is no header-row

2) SpreadsheetAddRows doesn't include a "header row". If you want a header row, you can add it yourself with SpreadSheetAddRow. Note these are 2 different functions.

<cfset SpreadSheetAddRow( theSheet, "list,of,column,names,here")>

http://help.adobe.com/en_US/ColdFusion/9.0/CFMLRef/WSc3ff6d0ea77859461172e0811cbec22c24-67ad.html

earwig75

ASKER

I updated this on my end a bit and the strange thing is, excel says "text" for the fields but it is still cutting off the leading zeros. Does that make sense?

In my database, a number is 08454 but even though the field in excel says "text" it outputs as 8454.

My ColdFusion version is: 9,0,0,251028

_agx_

> My ColdFusion version is: 9,0,0,251028

You don't have any of the updates/hot fixes installed. Like I mentioned earlier you need to install first because they contain some fixes to spreadsheet functions. See hotfixes:

http://www.adobe.com/support/coldfusion/downloads_updates.html#cf9

earwig75

ASKER

I do not have control over the server software at all. Does this mean I can't use it at this point?

_agx_

Oh boy... is it shared hosting? Because they should be running 9.0.2 for the security fixes alone. Can you use createObject("java")? If not .. you may be out of luck.

earwig75

ASKER

@_agx_ Yes it is shared hosting and I cannot use createObject. Does POI use these same methods as well? This is very frustrating.
Thank you.

_agx_

Yeah, POI uses createObject too. Might ask them if they plan on updating to 9.0.2 for the security patches. Then this would be a snap. For tasks like this you really need the proper tools and unfortunately shared hosting rarely provides that kind of control/access. That's the tradeoff.

Anyway, I haven't used it but the only other option remaining is xml. Note, you must use an *.xml file extension to avoid the extension hardening warning you got with html. If xml doesn't fit your requirements, then sadly you may be out of luck ...

http://www.bennadel.com/blog/461-Creating-Microsoft-Excel-Documents-With-ColdFusion-And-XML.htm

_agx_

How did it work out?

earwig75

ASKER

I've requested that this question be deleted for the following reason:

Cannot use any methods because of environment limitations.

_agx_

Sometimes the correct answer is "it's not possible". Even though your specific environment and requirements prevented you from making use of any of the 4 options discussed (unfortunately :/), it's good to keep such a complete listing around for the next guy. So I don't think the thread shouldn't be deleted.

_agx_

@SouthMod - Looks like another mod closed out the request for attention, but left this question open (usually they're resolved when that happens). Not sure what's the next step. Do I need to do anything else?