• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 485
  • Last Modified:

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

0
earwig75
Asked:
earwig75
  • 10
  • 8
1 Solution
 
Gurpreet Singh RandhawaWeb DeveloperCommented:
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
0
 
earwig75Author Commented:
@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.
0
 
_agx_Commented:
Honestly at this point your best bet is to create a *real* excel file (cfspreadsheet, POIUtility.cfc, etc..).  That's the only way you'll have the control you want AND avoid the extension hardening warnings that occur with html files.

The obvious problem with CSV is it's just data. Formatting/display is all left up to Excel, which unfortunately chooses to drop the zeros with values it "thinks" are numbers.  There's a few hacks involving quotes (single or double).  While they work, the quotes become part of the cell value - which may not be acceptable.    Another option is to return a *.txt file so Excel displays the import wizard. Then users can customize the column formats. But that's not a good solution for inexperienced users ..

So if the HTML and CSV options are out, what's left is generating a real spreadsheet:

http://help.adobe.com/en_US/ColdFusion/9.0/CFMLRef/WSc3ff6d0ea77859461172e0811cbec17cba-7f87.html
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
earwig75Author Commented:
@ _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.
0
 
_agx_Commented:
Sure.   Here's an example tested w/CF10.  

<!--- simulate a db query --->
<cfset someQuery = queryNew("")>
<cfset queryAddColumn(someQuery, "name", "varchar", listToArray("John,Tom"))>
<cfset queryAddColumn(someQuery, "zipCode", "varchar", listToArray("02222,11111"))>
   
<cfset theSheet = SpreadSheetNew()>
<!--- format columns as text to preserve leading zeroes --->
<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)#">
0
 
_agx_Commented:
If you're on CF9 be sure you've installed the updates. There were spreadsheet fixes in updates 1 and 2.
0
 
earwig75Author Commented:
@_agx_

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

John      2222
Tom      11111
0
 
earwig75Author Commented:
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

0
 
_agx_Commented:
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
0
 
earwig75Author Commented:
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
0
 
_agx_Commented:
>  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
0
 
earwig75Author Commented:
I do not have control over the server software at all. Does this mean I can't use it at this point?
0
 
_agx_Commented:
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.
0
 
earwig75Author Commented:
@_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.
0
 
_agx_Commented:
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
0
 
_agx_Commented:
How did it work out?
0
 
earwig75Author Commented:
I've requested that this question be deleted for the following reason:

Cannot use any methods because of environment limitations.
0
 
_agx_Commented:
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.
0
 
_agx_Commented:
@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?
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 10
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now