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

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

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


How can I tell Excel not to convert this data?
CaseyRobertsTTUAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mshogrenCommented:
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.
0
CaseyRobertsTTUAuthor Commented:
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>
0
CaseyRobertsTTUAuthor Commented:
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.
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

jkmyoungCommented:
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)
0
Geert BormansInformation ArchitectCommented:
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" />
</TD>

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"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns="http://www.w3.org/TR/REC-html40">


Cheers!
0
Geert BormansInformation ArchitectCommented:
CaseyRobertsTTU,

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

cheers

Geert
0
CaseyRobertsTTUAuthor Commented:
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="http://www.w3.org/1999/XSL/Transform">
<xsl:template match="/">
<HTML xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel">
<HEAD>

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?
0
Geert BormansInformation ArchitectCommented:
<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

cheers

Geert
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Geert BormansInformation ArchitectCommented:
> that this is non valid XML in the result tree
not wellformed would be more correct :-)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
XML

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.