How do I export numerical data into Excel and keep the leading zeroes from disappearing.

When I export a file into Excel, the leading zeroes disappear. How do you force Excel to treat the codes as text, and leave the zeroes? I need something that would act the way the Excel text import wizard does. I've tried enclosing the variable with single or double quotes, which sorta work, except that the quotes show up in Excel along with the zeroes.

I have used a couple of custom tags, but they are not preserving the leading zeroes so the text is importing into Excel with no leading zeroes.

For example (From CF: 0000 Import into Excel: 0)

Thanks.
dwhatleyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SidFishesCommented:
try this

<cfheader name="Content-Disposition" value="filename=test.xls">
<cfcontent type="application/vnd.ms-excel">
<table>
<tr><td style="mso-number-format:\@">
010101
</td></tr>
</table>


more html to excel formats here http://agoric.com/sources/software/htmltoExcel

0
dwhatleyAuthor Commented:
Good Day,

This is the following code. When I click the export button, the applications opens an Excel window with the data. The <tr> tag with the style appears in the Excel spreadsheet along with the newly formatted data. How do I get just the data without the <td> tag and mso style to appear in the Excel worksheet.

Here is an example of my results in an Excel spreadsheet.  
<tr>                                    
<td style="mso-number-format:\@">FBRM</td>                        
<td style="mso-number-format:\@">1</td>                        
<td style="mso-number-format:\@">10</td>                        
<td style="mso-number-format:\@">0000</td>
</tr>

Here is an example of my code snippet. How do I make this work?

Thanks.

Deetra
<cfelseif (exportType IS 'currentpage')>		
<cfoutput query="Q_ref_table" startrow="#startrow#"  maxrows="#maxrows#">			
<!--- BEGIN THIS DISPLAYS ACTUAL REFERENCE DATA RESULTS --->
<tr>
<cfloop index="i" list="#colList#" delimiters=",">
<cfif ListFindNoCase(variables.dateColList,i)>
	#DateFormat(Q_ref_table[i][currentrow],"MM/DD/YYYY")#	
<cfelse >							
         <td style="mso-number-format:\@" >#trim(Q_ref_table[i][currentrow])#</td>
</cfif> 
</cfloop>	
</tr>
<!--- END THIS DISPLAYS ACTUAL REFERENCE DATA RESULTS --->
</cfoutput>
</cfif>		

Open in new window

0
SidFishesCommented:
It works for me. What version of Excel are you using?

I believe you'll need '03 at minimum. If you have 2000-02, I believe you need to install optional Web Componenets and for 97 you need to Office 97 web connectivty kit (from MS)

btw...I don't see a <table> tag in your code? I can duplicate what you say ("<tr> tag with the style appears in the Excel spreadsheet along with the newly formatted data.") by leaving out the <table> tag????
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

dwhatleyAuthor Commented:
Okay, so you are saying that from the following code:

When I click the Export button from another CF page, it runs the following code below and the code below should have the html td tags.
 
But, when I actually import (click a button to export the following data into Excel)  the HTML tag and style is imported into the Excel spreadsheet too.
<CFCONTENT type="application/vnd.ms-excel" >   
<cfheader name="Content-Disposition" value="Attachment; filename=pas_data_ref_report.xls" > <cfoutput query="Q_ref_table" startrow="#startrow#" maxrows="#maxrows#">    
<table>
<tr> <cfloop index="i" list="#colList#" delimiters=",">
<td style="mso-number-format:\@" OR class="text">  #trim(Q_ref_table[i][currentrow])#</td>
 </cfloop>
 </tr>
</table
 </cfoutput>  

AND so the output in Excel is
<td style="mso-number-format:\@">0000</td>
</tr>
AND NOT
0000

I hope this is not confusing how I am explaining it. Thanks.
0
SidFishesCommented:
I don't believe this is a problem with the export, more perhaps the way you are creating it..

as a test try this...

<CFCONTENT type="application/vnd.ms-excel" > 
<cfheader name="Content-Disposition" value="Attachment; filename=pas_data_ref_report.xls" >
<cfoutput>
<table>
<tr> <cfloop index="i" list="0001,0002,0003,0004,0005,0006" delimiters=",">
<td style="mso-number-format:\@">  #i#</td>
 </cfloop>
 </tr>
</table>
  </cfoutput>


0
dwhatleyAuthor Commented:
THANK YOU!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
Its working. I was able to export without tables so I didn't use them. I was using the ascii characters to set the tabs and new line such as Chr(9), chr(13), chr(10)
<cfset TabChar = Chr(9)>       
<cfset newline = chr(13) & chr(10)>

I used your html table code and it all works. Definitely learned something NEW today.

THANK YOU AGAIN!!!!!!!!!!!!!!!!!!!!!!!!
0
SidFishesCommented:
Glad you got it working. Please remember to close the question.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dwhatleyAuthor Commented:
THANK YOU!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
Its working. I was able to export without tables so I didn't use them. I was using the ascii characters to set the tabs and new line such as Chr(9), chr(13), chr(10)
       


I used your html table code and it all works. Definitely learned something NEW today.

THANK YOU AGAIN!!!!!!!!!!!!!!!!!!!!!!!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Servers

From novice to tech pro — start learning today.