3

I am exporting data from vb.net to excel and it is dropping the leading zero when its displayed on excel. How can I avoid the dropping of leading zero? I read the solution of adding a single quote but it makes my excel sheet column ugly. Also users will complain if they see a single quote on zip code field. vb.net code

Response.AddHeader("content-disposition", attachment)
        Response.ContentType = "application/vnd.ms-excel"
        Response.Charset = ""

the stored procedure outputs XML and it is transformed by XSLT before it is displayed on EXCEL

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  <xsl:template match="/">
    <HTML>
      <HEAD>
        <STYLE type="text/css"> TABLE{table-layout: automatic; width:100%} .tblHeader{background-color:RGB(192,192,192);font-weight:bold} .row1{background-color:RGB(204,204,255)} .row2{background-color:RGB(153,204,255)} </STYLE>
      </HEAD>
      <BODY>
        <TABLE border="1">

          <THEAD>
            <tr class="tblHeader">
              <xsl:for-each select="*/*[1]/*">
                <td>
                  <xsl:value-of select="."/>
                </td>
              </xsl:for-each>
            </tr>
          </THEAD>

          <TBODY>
            <xsl:for-each select="ClientArray/Client">
              <TR>

                <xsl:for-each select="*">
                  <TD>
                    <xsl:value-of select="."/>
                  </TD>
                </xsl:for-each>

              </TR>
            </xsl:for-each>

          </TBODY>
        </TABLE>
      </BODY>
    </HTML>

  </xsl:template>
</xsl:stylesheet>

Please help me with a solution other than adding a single quote or manipulating excel sheet with column formatter (general/numbers ) etc. I don't want user to make any changes on excel to get the display properly when the excel is displayed from web page it should be all set with proper formatting. also we have no control over client excel software

Jon B
  • 51,025
  • 31
  • 133
  • 161
Enggr
  • 627
  • 2
  • 14
  • 27
  • 1
    It's because it's a number field and Excel strips leading zeros from them. You'll have to store it as text, but other than using the leading ' I'm not sure how you'd do that. – ChrisF Aug 16 '10 at 21:37
  • well, the zip code is a string field in my database and is exported as a string to excel. i'm still wondering – Enggr Aug 17 '10 at 17:45
  • See http://stackoverflow.com/questions/137359/excel-csv-number-cell-format – Mark Ransom Apr 12 '13 at 16:11

5 Answers5

1

Number cells in Excel are automatically stripped of leading zeros. In order to preserve the preceeding zeros, you'll have to set the cell's data to text [which is achieved by adding an apostrophe to the zip code].

I don't use Excel Interop that much so I can't tell you exactly how to set the cell's data type to text but I can assure you that that's the way to fix your problems.

Alex Essilfie
  • 12,339
  • 9
  • 70
  • 108
  • 1
    well, the zip code is a string field in my database and is exported as a string to excel. i'm still wondering. I don't use excel interop either. Its just a response.contenttype and not an excel interop API. Also we cannot control the properties of excel instance at client. please help – Enggr Aug 17 '10 at 17:49
1

Use 

<TD style="mso-number-format:\@;">

It will preserve the Leading zeros.

Arun Singh
  • 1,538
  • 4
  • 19
  • 43
1

I ran into this problem when exporting to Excel via PHP. I found that prepending an equals sign and surrounding the zip with double-quotes caused Excel to preserve the leading zero.

I don't know vb.net, but in PHP that would look like this:

echo '="' . $the_zip_code . '"';
melanjolly
  • 31
  • 5
0

how about formatting the number through xsl? look at http://www.w3schools.com/xsl/func_formatnumber.asp

itsho
  • 4,640
  • 3
  • 46
  • 68
-2

Go to the cell you are working on. Right click, Click on format cells, and test. Type in your number with the leading zeros, it will stay.

user2275159
  • 1