Excel Question

[trojnfn]

I have a CF application that outputs carrier tracking numbers. I need to export this data to excel. If the output is 1Z234TY988784AQ, the export works fine because of the alphas. If the output is 1234567890, without any alpha, excel thinks it is a number and exports it in the scientific notation. If I put a space at the beginning or end, then the ouput works fine.

However, the user then takes the excel file and uses it to compare against another file with carrier tracking numbers. They claim they are not finding any matches becasue of the space.

How do I get around the scientific export  ? Will csv file work ? I have not found any documentation yet on csv so I thought I would post my problem here.

Thanks

[ilssac]

What version of CF?

How are you exporting to Excel?

Have you confirmed that the number is actually exported as scientific notation, or is Excel just displaying it as scientific notation.

If the latter, then it is probably up to the user to change how the cell display the data.

[trojnfn]

It is cf 7

Here is the export command :

<cfsetting enablecfoutputonly="Yes" >
<cfheader name="Content-Disposition" value="attachment;filename=advancedSearchResults.xls">
<cfcontent type="application/vnd.ms-excel" >

I guess excel is displaying 375070700100000014....... as 3.75051E+31, but dont know if it is exporting it that way or not.

CF displays it at 37507070010000014........that is th e actual value.

[JR__Bob__Dobbs]

Try putting a single quote prior to your values.  A more complete code sample might also be helpful.

[ilssac]

trojnfn wrote:

I guess excel is displaying 375070700100000014....... as 3.75051E+31, but dont know if it is exporting it that way or not.

In MS Excell click on the cell or row or column with the data.  Select Format -> Cells and Select "Text" as the Category.  It is highly likely that the data will now be displayed as "375070700100000014".

[trojnfn]

I hightlight the column, right click, format cells, text, and nothing happens, values remain in scientific format.

[ilssac]

When I tried it, I had to click in the data cell and out again to get it to reformat the data.  Excel seems to be somewhat confused by this data.

I suspect the earlier sugestion to use single quotes to tell Excel this is text data not numeric data up front is a better option.

[trojnfn]

Addiing a single quote to either the front or the end still does not work, it does export to excel as a text, but includes the quote, for example :

1234567890' or '1234567890. Even adding a single quote to both the front and the back yields '1234567890'. So it still does not seem to work.

[ilssac]

Can you show a little more how you are "exporting" to Excell.  You showed the content header you are using to let the browser know that it should expect an Excel file attachement.  But how are you actually defining the cells and the data in them?

Since you are only on CF7, you can not use the <cfspreadsheet...> which is new in CF9.  It would probably eliminate this problem.

You may need to use the POI tools -- if you are not doing so -- Ben Nadel has many posts about using them in ColdFusion, to get more explict control over the content in the excel file.

[trojnfn]

Here is the code I use for export to Excel :

<cfif excelFlag is "Yes">
            <cfsetting enablecfoutputonly="Yes">
            <cfheader name="Content-Disposition" value="attachment;filename=advnacedSearchResults.xls">
            <cfcontent type="application/vnd.msexcel">
            <cfoutput>
                <table cols="14">
                 <tr>
                        <th align="center" style="font-family:Arial, Helvetica, sans-serif; font-size:9px">GFM RDN</th>
                        <th align="center" style="font-family:Arial, Helvetica, sans-serif; font-size:9px">Date Arrived</th>
                        <th align="center" style="font-family:Arial, Helvetica, sans-serif; font-size:9px">RayTrak</th>
                        <th align="center" style="font-family:Arial, Helvetica, sans-serif; font-size:9px">Bill of Lading</th>
                        <th align="center" style="font-family:Arial, Helvetica, sans-serif; font-size:9px">Carrier</th>
                        <th align="center" style="font-family:Arial, Helvetica, sans-serif; font-size:9px">Carrier Tracking</th>
                        <th align="center" style="font-family:Arial, Helvetica, sans-serif; font-size:9px">Temp. Bin Loc.</th>
                        <th align="center" style="font-family:Arial, Helvetica, sans-serif; font-size:9px">Purchase Order</th>
                        <th align="center" style="font-family:Arial, Helvetica, sans-serif; font-size:9px">Document No.</th>
                        <th align="center" style="font-family:Arial, Helvetica, sans-serif; font-size:9px">Supplier</th>
                        <th align="center" style="font-family:Arial, Helvetica, sans-serif; font-size:9px">Problem</th>
                        <th align="center" style="font-family:Arial, Helvetica, sans-serif; font-size:9px">Material No.</th>
                        <th align="center" style="font-family:Arial, Helvetica, sans-serif; font-size:9px">Serial No.</th>
                        <th align="center" style="font-family:Arial, Helvetica, sans-serif; font-size:9px">U/Z Group No.</th>
                 </tr>
           <cfloop query="qrySearch">
                    <tr>
                        <td align="center" style="font-family:Arial, Helvetica, sans-serif; font-size:9px">#gfmRdn#</td>
                        <td align="center" style="font-family:Arial, Helvetica, sans-serif; font-size:9px">#dateformat(dateArrived, "mm/dd/yyyy")#</td>
                        <td align="center" style="font-family:Arial, Helvetica, sans-serif; font-size:9px">#RayTrak#</td>
                        <td align="center" style="font-family:Arial, Helvetica, sans-serif; font-size:9px">#bol#</td>
                        <td align="center" style="font-family:Arial, Helvetica, sans-serif; font-size:9px">#carrier#</td>
                        <td align="center" style="font-family:Arial, Helvetica, sans-serif; font-size:9px">#carrierTracking#</td>
                        <td align="center" style="font-family:Arial, Helvetica, sans-serif; font-size:9px">#bin#</td>
                        <td align="center" style="font-family:Arial, Helvetica, sans-serif; font-size:9px">#purchaseOrder#</td>
                        <td align="center" style="font-family:Arial, Helvetica, sans-serif; font-size:9px">#documentNumber#</td>
                        <td align="center" style="font-family:Arial, Helvetica, sans-serif; font-size:9px">#supplierName#</td>
                        <td align="center" style="font-family:Arial, Helvetica, sans-serif; font-size:9px">#problemDescription#</td>
                        <td align="center" style="font-family:Arial, Helvetica, sans-serif; font-size:9px">#materialNumber#</td>
                        <td align="center" style="font-family:Arial, Helvetica, sans-serif; font-size:9px">#serialNumber#</td>
                        <td align="center" style="font-family:Arial, Helvetica, sans-serif; font-size:9px">#zGroupNumber#</td>
                    </tr>
           </cfloop>
          </table>
         </cfoutput>
         </cfif>

[ilssac]

That is what I thought might be going on.

You are not exporting to Excel.  You are creating an HTML table, telling the browser that this is an Excel file, and Excel is using it's rules to convert an HTML table to Excel table.

I would try a CSV format.  That might work better.

The next level would be the POI tool mentioned before.

http://www.bennadel.com/projects/poi-utility.htm

http://www.bennadel.com/index.cfm?dax=search.search&cx=002737295264664880728%3A7rz15jfmlbi&cof=FORID%3A11&q=POI+site%3Abennadel.com&sa=Search&siteurl=www.bennadel.com%252Findex.cfm%253Fdax%253Dsearch.search%2526the_search_criteria%253DPOI#1018

After that, I'm afraid it's ColdFusion 9 and the <cfspreadsheet...> tag.

[trojnfn]

Thanks for the updated info. I have always used this method to export to excel.

If this is not the correct way, can you show me what is the right way to export to excel ?

Also, if I use csv, I would just change the .xls extension to .csv, like <cfheader name="Content-Disposition" value="attachment;filename=advnacedSearchResults.csv">

Thanks

[ilssac]

Well it is not that using an HTML table is a wrong way.  Just that, like most programming choices, there are limitations and caveats.

In this case, it is probably the caveat that HTML has no idea of data types that is causing you grief.  And the programmers for Excel know this.  So when they built the feature to allow Excel to understand an HTML table and make an Excel table out of it, they created code that does it's best to guess the appropriate Excel data type based on the value in the HTML table.

You would not need to change the file type to csv, but that might not be a bad choice.  Excel is perfectly capble of parsing an csv file with either extension even better then an HTML table.  At least it has been doing it for a longer time!

With CSV you can designate text values as text values by wrapping them in quotes, either single or double, just be consistant.  This is also useful when you have data values that have commas in the value.

I would give that a try before I went to the more complex POI route where you can use Java tools to directly manipulate an Excel file.

[ilssac]

Also when you change to CSV you would remove the HTML table and just output comma deliminated and line feed|carriage return terminated lines of data.

I.E.

<cfoutput query="aQuery">#aColumn#,"#bColumn#",#cColumn#,#dColumn#
</cfoutput>

Note how I was aware of the white space included in the output.  HTML ignores extra white space and one can be generous with it.  CSV and other formats do not!