views:

132

answers:

2

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"&gt;
  <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

A: 

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
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
A: 

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

itsho