Excel is changing the an xlst string value to a number upon opening

Posted on 2006-04-27
Last Modified: 2008-02-01
I have a datagrid that has a column which is a string identity field that is 19 characters long - all the characters values are numeric.  I'm exporting and opening the datagrid in Excel using a xslt file.  Excel changes the values from string to numeric and the result is a number who's last character is incorrect and and displays in scientific notation.  Also, I can't prepend the value with an apostrophe.  I need to be able to change my xslt line below so that Excel knows to leave it as a string.

Datagrid Column Value: 4003000000009010001
What I see in the resulting worksheet in Excel: 4.003E+18
What i see in the data field at the top: 4003000000009010000

The line in the xslt file

<xsl:value-of select="IDNUMBER" />

How can I tell Excel not to convert this data?
Question by:CaseyRobertsTTU
    LVL 15

    Expert Comment

    Sounds like something that Excel is doing to the data.  You may have to change the data type and formatting of the Excel cells.  You may need to turn autoformatting off.

    Author Comment

    It is definitely Excel converting a string to a number.  I have no control of the users Excel program, just the file I'm creating.  I'd like to know how to explicitly declare a field as a string in XML or in the XSLT.

    XML line is: <IDNUMBER>4003000000009010001</IDNUMBER>

    Author Comment

    BY THE WAY - Sorry, for the confusion.  I'm not writing an Excel file (.xls). The VB.Net program is taking the data from my datagrid columns and writing an XML file to be opened by Excel.  In the XML file, the data is correct.  I need to write the XML file in such a way that Excel doesn't reformat the data when it opens it.

    Any comments are welcome.
    LVL 12

    Expert Comment

    I suggest you create an Excel sheet with 2 cells, each with that value.
    Format Cells (Ctrl+1) on one of them, and change the number formatting to "Number" with 0 decimal places

    Then export it to xml.
    See what has changed between the 2 cells in the xml file. (Sorry, I don't have Excel 2003)
    LVL 60

    Expert Comment

    by:Geert Bormans
    Hi CaseyRobertsTTU,
    > <TD>
    > <xsl:value-of select="IDNUMBER" />
    > </TD>

    all you need is a x:str in the tag
    the others should be x:num (but they are casted automatically)

    <TD x:str>
      <xsl:value-of select="IDNUMBER" />

    you need to put the x namespace in though, if it is not in your html table already

    it would be something like this
    <html xmlns:o="urn:schemas-microsoft-com:office:office"

    LVL 60

    Expert Comment

    by:Geert Bormans

    the above comment applies when you are uploading html into excel
    (I was confused by your <td> example
    I am not sure if you can give your datagrid attributes

    check the XML created by .net
    the cell that has the text casted to number should contain
    <Cell ><Data ss:Type="String">4003000000009010001</Data></Cell>
    it will likely contain
    <Cell ><Data ss:Type="Number">4003000000009010001</Data></Cell>

    note: xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"

    another possibility is that your XML is uploaded as data
    then it is just a matter op correcting your XML schema



    Author Comment

    The .Net program is opening an HTML file in Excel using the xslt file. (Don't ask me, that's just how it is.)

    Here is the top line of the XSLT (is this right?):
    <xsl:stylesheet version="1.0" xmlns:xsl="">
    <xsl:template match="/">
    <HTML xmlns:o="urn:schemas-microsoft-com:office:office"

    There are no other specs on the other <TD> tags.  

    When I add the "x:str" to the my <TD> tag i get the following error:
    "This is an unexpected token.  The expected token is 'EQUALS'. Line 61, Position 10."
    Line 61 starts here:<TD x:str> (the editor indicates that position 10 is after the "r" in the "str" before the ">".

    Any thoughts?
    LVL 60

    Accepted Solution

    <TD x:str>

    this is of course not straightforward to create in XSLT
    the problem your are facing is that this is non valid XML in the result tree

    there are some options, all are ugly

    - set the output method of the XSLT to "text", but you need to replace all unbalanced tags with &lt;TD x:str&gt; then to keep the XSLT wellformed
    - set the "attribute" value to something recognisable and do a regular expression post process
    eg. <TD xstr="####">
    and remove every ="####" in a postprocess


    LVL 60

    Expert Comment

    by:Geert Bormans
    > that this is non valid XML in the result tree
    not wellformed would be more correct :-)

    Featured Post

    Live: Real-Time Solutions, Start Here

    Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

    Join & Write a Comment

    Browsing the questions asked to the Experts of this forum, you will be amazed to see how many times people are headaching about monster regular expressions (regex) to select that specific part of some HTML or XML file they want to extract. The examp…
    The Confluence of Individual Knowledge and the Collective Intelligence At this writing (summer 2013) the term API ( has made its way into the popular lexicon of the English language.  A few years ago, …
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
    This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now