Forcing numeric values as text on HTML table exporting to excel

12,733

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.

Share:
12,733

Related videos on Youtube

RSolberg
Author by

RSolberg

@russsolberg http://rsolberg.com

Updated on June 04, 2022

Comments

  • RSolberg
    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
    RSolberg over 12 years
    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.
  • The Matt
    The Matt over 12 years
    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.
  • RSolberg
    RSolberg over 12 years
    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'
  • Perplexed
    Perplexed over 12 years
    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.
  • Arun Singh
    Arun Singh over 11 years
    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
    CalumMcCall over 9 years
    +1 This solution worked for me whilst the accepted answer did not. This is much cleaner too imo.
  • TheOneWhoPrograms
    TheOneWhoPrograms over 9 years
    For some reason this worked while doing it through an external css did not o.O

Related