Forcing numeric values as text on HTML table exporting to excel

Question

I'm trying to fix a bug at work where in classic ASP a HTML table is being rendered and then sent to the client as an Excel file. I'll spare the entire source code sample, but essentially we have one column that is alpha numeric, yet when the value starts with one or more zeros, the zeros disappear. I know this is standard Excel behavior for handling numbers, but I want it to treat the value as text. How can I do this?

The Cell In Question:

Response.Write("<td class='tdsmall' align='left' NOWRAP>" & rsPODetail("ITM_ID") & "</td>")

Examples

HTML | EXCEL
00212704 | 212704
00212336 | 212336
00212251 | 212251

@jlembke thank you so much for the kind and valuable edit. Unfortunately, that typo isn't as embarrassing as having to work on classic ASP apps built 10 years ago and given no love over the years. — RSolberg
– RSolberg, Commented Apr 8, 2011 at 18:16

Community · Accepted Answer · 2017-05-23 11:45:51Z

10

Just add one line before your table

Response.Write("<style> TD { mso-number-format:\@; } </style>");

Check this out: Export Gridview to Excel with rows formatted as text

edited May 23, 2017 at 11:45

CommunityBot

11 silver badge

answered Mar 14, 2012 at 18:06

Arun Singh

1,5464 gold badges19 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Arun Singh Over a year ago

RSolberg - I don't know the issue was resolved or not for you, Have you tried the above code? Just Add ONE LINE. you don't have to change your data.

CalumMcCall Over a year ago

+1 This solution worked for me whilst the accepted answer did not. This is much cleaner too imo.

TheOneWhoPrograms Over a year ago

For some reason this worked while doing it through an external css did not o.O

Jon Erickson · Accepted Answer · 2011-04-08 17:23:05Z

7

Maybe try ="00212704"

Response.Write("<td class='tdsmall' align='left' NOWRAP>=""" & rsPODetail("ITM_ID") & """</td>")

answered Apr 8, 2011 at 17:23

Jon Erickson

115k45 gold badges140 silver badges178 bronze badges

1 Comment

RSolberg Over a year ago

Amazing how an apostrophe renders it one way and the double quotes another. The double worked! I had tried this earlier with a single quote and it showed this on output: ='00212704'

Community · Accepted Answer · 2017-05-23 11:52:53Z

5

One option that doesn't require modifying the content of the tables is to use the mso-number-format CSS style, as described on this SO answer. For text, you would use something like:

CSS:

.xlText {
    mso-number-format: "\@";
}

HTML:

<td class="xlText">00030</td>

edited May 23, 2017 at 11:52

CommunityBot

11 silver badge

answered Apr 11, 2011 at 0:58

Cheran Shunmugavel

8,4691 gold badge35 silver badges41 bronze badges

Comments

Illusionicea · Accepted Answer · 2013-11-19 03:16:39Z

1

put style at head of html. TD { mso-number-format:\@; }

all your table cell will convert to text.

answered Nov 19, 2013 at 3:16

Illusionicea

212 bronze badges

Comments

Subhash Lama · Accepted Answer · 2011-04-08 17:16:08Z

0

excel treats a number as text if you put a single quote before the number e.g. '001234

may be this will help you solve your problem

answered Apr 8, 2011 at 17:16

Subhash Lama

4332 silver badges12 bronze badges

1 Comment

RSolberg Over a year ago

I did try this. The result was '00212704 being visible rather than 00212704. For some reason Excel didn't hide the apostrophe and kept it visible.

Perplexed · Accepted Answer · 2011-04-08 17:23:53Z

0

Can you render it it as a .csv file and download it on the client? Edit: Crap, that doesn't work.

Really, you can't do much of anything that isn't going to be a hack, because IE and Excel don't really "talk" in a standard format. It's a client side issue in Excel that you're trying to override the default behavior on.

Really what you might have to do is create an excel template file, save it to the web server, and write a script that copies the the excel file, makes changes, and sends it to the user. That way you can control the number formatting.

answered Apr 8, 2011 at 17:23

Perplexed

1237 bronze badges

2 Comments

The Matt Over a year ago

Is the generation of the XLS happening client-side? That can (should?) be moved server-side where he would have full control of the output.

Perplexed Over a year ago

i'm guessing that's exactly the problem - the client is being allowed to interpret the output without being told how to format it. Because the two (HTML and Excel) don't really "talk". Excel just interprets what its opening.

Collectives™ on Stack Overflow

Forcing numeric values as text on HTML table exporting to excel

The Cell In Question:

Examples

6 Answers 6

3 Comments

1 Comment

Comments

Comments

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

The Cell In Question:

Examples

6 Answers 6

3 Comments

1 Comment

Comments

Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related