Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2006-04-27
11
Medium Priority
?
279 Views
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

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


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

Expert Comment

by:mshogren
ID: 16553963
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
 

Author Comment

by:CaseyRobertsTTU
ID: 16554021
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
 

Author Comment

by:CaseyRobertsTTU
ID: 16554365
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 12

Expert Comment

by:jkmyoung
ID: 16554424
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
 
LVL 60

Expert Comment

by:Geert Bormans
ID: 16556590
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
 
LVL 60

Expert Comment

by:Geert Bormans
ID: 16556641
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
 

Author Comment

by:CaseyRobertsTTU
ID: 16563063
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
 
LVL 60

Accepted Solution

by:
Geert Bormans earned 2000 total points
ID: 16568826
<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
 
LVL 60

Expert Comment

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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…
Suggested Courses
Course of the Month13 days, 15 hours left to enroll

581 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