Exporting To Excel With ASP - Number Problem

[williamsd]

Hello,

I'm trying to export a report from an .asp page using th
Response.ContentType method. The only problem with the export is tha
when numbers are exported, if there is a 0 or more than one 0 in th
front of the number, it gets removed by excel when the export i
complete. Also, if the number is too long it gets auto-formated t
Scientific. I need to know if there is a formula that I can use on m
export to force the Number Cells to be Text only Cells so that I don'
loose the 0's in front of the number and also to prevent it fro
auto-formating to Scientific if the number is too long. Anyone kno
how I could do this

 

[Josh Sale]

You might try prefixing each data cell with an appostropy. I think this
should cause Excel to treat the values as strings instead of numbers thus
preserving your leading zeros.

 

[williamsd]

Yea I know about that, but it's for a report, and I do not want th
apostrophes to be displayed, are there any other possible solutions

 

[Josh Sale]

They won't be displayed.

Give it a try. Just launch Excel and type '000001 into a cell. After you
move the focus from the cell all you'll see is 000001. Excel suppresses the
display of the initial '.

 

[williamsd]

Sorry, but it does display on my export. I opened Excel on it's own an
tried it like you suggested, worked perfectly. But as soon as I trie
it on my export, the apostrophe still shows. I even did cantation o
the value and the apostrophe before I displayed it and it still doe
not work

 

[williamsd]

Once it's open, I can remove the apostrophe and then type it back in b
hand and then it dissappears! But it won't dissappear unless I d
that. This is making me want to rip the hair from my head

 

[Tim Williams]

Aprevious post of mine.....

"What version of Excel, and how are you doing the export?

If as HTML, there are some attributes you can add to the html to indicate to
Excel how it should format the output.

Saving an Excel sheet with text-formatted cells gives me this "CSS" style
(amongst others):

mso-number-format:"\@";

You can investigate the various possible values by saving an XL sheet with
cells of various formats and looking at the generated HTML.

Also a small example here:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnwebteam/h
tml/webteam10012002.asp

I think this is OK (or at least a subset of it) for XL 97+"

Tim.

 

[Greg Griffiths]

check out http://www.greggriffiths.org/webdev/both/excel/ which offers some
other methods of doing this.

 

[Greg Griffiths]

check out http://www.greggriffiths.org/webdev/both/excel/ which offers some
other methods of doing this.