Forcing numeric values as text on HTML table exporting to excel
Solution 1
Maybe try ="00212704"
Response.Write("<td class='tdsmall' align='left' NOWRAP>=""" & rsPODetail("ITM_ID") & """</td>")
Solution 2
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
Solution 3
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>
Solution 4
put style at head of html. TD { mso-number-format:\@; }
all your table cell will convert to text.
Related videos on Youtube
Comments
-
RSolberg over 1 year
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 -
RSolberg over 12 yearsI 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.
-
The Matt over 12 yearsIs the generation of the XLS happening client-side? That can (should?) be moved server-side where he would have full control of the output.
-
RSolberg over 12 yearsAmazing 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'
-
Perplexed over 12 yearsi'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.
-
Arun Singh over 11 yearsRSolberg - 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 9 years+1 This solution worked for me whilst the accepted answer did not. This is much cleaner too imo.
-
TheOneWhoPrograms over 9 yearsFor some reason this worked while doing it through an external css did not o.O