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, Apr 08 '11 at 18:16

score 10 · Answer 1 · edited May 23 '17 at 11:45

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 '17 at 11:45

Community

1
1

answered Mar 14 '12 at 18:06

Arun Singh

1,538
4
19
43

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. – Arun Singh Mar 18 '12 at 14:02
1

+1 This solution worked for me whilst the accepted answer did not. This is much cleaner too imo. – CalumMcCall May 16 '14 at 16:06
For some reason this worked while doing it through an external css did not o.O – TheOneWhoPrograms Jul 18 '14 at 13:28

score 7 · Accepted Answer · answered Apr 08 '11 at 17:23

7

Maybe try ="00212704"

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

answered Apr 08 '11 at 17:23

Jon Erickson

112,242
44
136
174

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' – RSolberg Apr 08 '11 at 17:30

score 4 · Answer 3 · edited May 23 '17 at 11:52

4

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 '17 at 11:52

Community

1
1

answered Apr 11 '11 at 00:58

Cheran Shunmugavel

8,319
1
33
40

score 1 · Answer 4 · answered Nov 19 '13 at 03:16

1

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

all your table cell will convert to text.

answered Nov 19 '13 at 03:16

Illusionicea

21
2

score 0 · Answer 5 · answered Apr 08 '11 at 17:16

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 08 '11 at 17:16

Subhash Lama

433
2
12

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. – RSolberg Apr 08 '11 at 17:18

score 0 · Answer 6 · answered Apr 08 '11 at 17:23

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 08 '11 at 17:23

Perplexed

123
7

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. – The Matt Apr 08 '11 at 17:26
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. – Perplexed Apr 08 '11 at 17:30

Forcing numeric values as text on HTML table exporting to excel

The Cell In Question:

Examples

6 Answers6