Export to Excel formating problem

Report · Jan 10, 2007

I am having a problem with the formatting of a an export file to Excel, which is created by a Coldfusion page. My export file contains numbers that need to be formatted as text. I found the style code below on this forum, but I am not having any luck with it. I tried adding it to my style file or directly as style formatting on the export page, but neither one of the two are getting recognized.

Is there anything else I need to code before this works? Your help is greatly appreciated. Thanks,

Rose

<html>
<STYLE TYPE="text/css">
TD {
mso-number-format:\@;
}
</style><body>


<cfcontent type="application/vnd.msexcel">
<cfheader name="Content-Disposition" value="inline; filename=export.xls">
<cfquery name = "getclub" datasource="#DSN#">
Select mail_name name,…
</cfquery>
<table width="200" border="2">
<cfoutput query='getclub'> <tr>
<td>#customer#</td>
…
<td>#dateformat(begin_date,'mm/dd/yyyy')# - #dateformat(end_date,'mm/dd/yyyy')#</td>
…
</tr></cfoutput>

</table>

Report · Jan 11, 2007

Could it be a case sensitivity issue? I also noticed that you are missing a <head> tag pair, and that your date range field is not actually numeric. I doubt that excel is that delicate when it comes to those issues, but you might try:

<html><head>
<STYLE TYPE="text/css">
TD.number {
mso-number-format:\@;
}
</style></head><body>

<cfcontent type="application/vnd.msexcel">
<cfheader name="Content-Disposition" value="inline; filename=export.xls">
<cfquery name = "getclub" datasource="#DSN#">
Select mail_name name,…
</cfquery>
<table width="200" border="2">
<cfoutput query='getclub'> <tr>
<td class="number">#customer#</td>
…
<td>#dateformat(begin_date,'mm/dd/yyyy')# - #dateformat(end_date,'mm/dd/yyyy')#</td>
…
</tr></cfoutput>

</table>
</body>
</html>

Report · Jan 11, 2007

Try prepending a single quote to your numbers.

Report · Jan 11, 2007

Thanks a bunch, Michael.

I am still having the same problem. If I understand your rec. correctly, you have flagged the records to be formatted as numbers.

The problem I have is that the data values such as 0000456786987 get displayed as a numeric value (456786987) when the export file is opened in Excel. Or, this number 27572587298572 is displayed as 2.75726E+13
This is particularly a problem with long international phone numbers. So, I need to format the values in those columns as ***text***, so that the data is displayed as is, when the file is opened in Excel.

Let me know if you have any thoughts on how to pass a flag that would TEXT format the cells instead.

Thanks,
Ros

Report · Jan 11, 2007

Hi Dan,

I tried your suggested as my initial attempt. It fixes the formatting problem, but it displays the single quote in front of the number. When the single quote is directly entered in the cell, Excel recognizes it as its text formatting flag, but not when the file is created as a table export in CF.

any other thoughts... this tiny little glitch has really stumbled me for the past few weeks..

Report · Jan 12, 2007

Come to think about it, I had the same problem a couple of years ago. Nothing I tried work so eventually I put formatting instructions into the worksheet.

Report · Jan 12, 2007

Rouset,
I got something to work (sort of) along the lines of what you are trying to do. I created a simple XLS file in the format that I wanted, saved it as an HTM file and took a look at the content. When I import it back into excel, it maintains the correct formatting for the numbers. Below is a trimmed down version of the code I used. Now why it seems to work, I have no idea. But I would recommend creating a sample XLS->HTM document like this for your project, then recreating the HTM code exactly using CFM. Crazy MS Office attributes and all.

Hope that helps.

Report · Jan 12, 2007

Case and quotes are critical in certain areas.
See this post:
http://www.adobe.com/cfusion/webforums/forum/messageview.cfm?catid=3&threadid=1014953

You can also get fine grained control using Apache POI -- but that may be overkill for you.

Report · Jan 12, 2007

Thanks, Michael.

Great minds think a like! 🙂 I had tried your strategy early on, but did not have any luck. I did notice though that I had missed defining x:124 as a style class.. will give it a try and let you know..

MikerRoo, I liked the suggestion in your link, too. will give that a try, too.

Thanks, thanks, thanks
Ros

Report · Jan 14, 2007

It worked! Thanks for your help, Ros

Report · Jan 14, 2007

It worked! Thanks for your help, Ros

Report · Feb 02, 2007

I have a differen problem now. I am having a STRANGE problem with numbers getting converted to characters.
The data file itself contains the data as follows: <td style='mso-number-format:"\@"'>+973-39573722</td> ,
However, when I open the file in Excel, it displays it as: 39573722

and <td style='mso-number-format:"\@"'>973-17713979</td> as : 973-17713979

For some reason it does not like the + in front of +973, eventhough I have formatted the field as a text field. Any idea???

Thanks,
Ros

Adobe Community

Export to Excel formating problem