locked
Excel to XML Parsing with number type RRS feed

  • Question

  • User-543160537 posted

    Hi,

    I am trying to parse the number type from excel and generate xml out of it. Below is the code which i tried but the output is not as expected. 

    In excel Id column has : 12345678910111213 which is more than 16 digits, excel is treating all the digits as 0 after 16 digits. i.e 12345678910111200.

    But i want to show as  12345678910111213.. can someone please advice..

     Int64 evtId = Int64.Parse(EventDetails.Rows[0]["Event Id"].ToString(), NumberStyles.AllowExponent | NumberStyles.AllowDecimalPoint);
                                writer.WriteElementString("ID", evtId.ToString());

    Thanks

    Wednesday, October 14, 2015 11:33 AM

Answers

  • User-219423983 posted

    Hi sreekanth.jonna,

    First of all, you could refer to the following KB that is about “Last digits are changed to zeroes when you type long numbers in cells of Excel”. This is because Excel follows the IEEE 754 specification on how to store and calculate floating-point numbers. Excel therefore stores only 15 significant digits in a number, and changes digits after the fifteenth place to zeroes. So, you could make the column is formatted as text to show the expected format, when you read the value, you could also regard it as text.

    https://support.microsoft.com/en-us/kb/269370

    I hope it’s useful to you.

    Best Regard,

    Weibo Zhang

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, October 15, 2015 4:33 AM

All replies

  • User-219423983 posted

    Hi sreekanth.jonna,

    First of all, you could refer to the following KB that is about “Last digits are changed to zeroes when you type long numbers in cells of Excel”. This is because Excel follows the IEEE 754 specification on how to store and calculate floating-point numbers. Excel therefore stores only 15 significant digits in a number, and changes digits after the fifteenth place to zeroes. So, you could make the column is formatted as text to show the expected format, when you read the value, you could also regard it as text.

    https://support.microsoft.com/en-us/kb/269370

    I hope it’s useful to you.

    Best Regard,

    Weibo Zhang

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, October 15, 2015 4:33 AM
  • User-543160537 posted

    Hi,

    Thanks for your response, i have gone through above explanation but trying to find if there is any work around. I already have an old solution which uses exel interop references to do the required stuff now i need to skip interop references and get it work.

    Thanks 

    Thursday, October 15, 2015 10:00 AM
  • User37182867 posted

    Have you tried just using xslt to translate to xml then you could serialize it to objects and what ever else you might need to do...

    Thursday, October 15, 2015 10:06 AM
  • User-2105033194 posted

    Thanks a lot ... I was suffering from same Issue. Now It Fix

    Saturday, October 17, 2015 10:05 AM
  • User-219423983 posted

    Hi sreekanth.jonna,

     i have gone through above explanation but trying to find if there is any work around. I already have an old solution which uses exel interop references to do the required stuff now i need to skip interop references and get it work.

    In your code above, you first get the string value and then convert it to Int64. So, you’d better directly get it as a string and format the value as a string in excel to let it show have expected format. If not, the value in excel has been changed as “12345678910111200”, so you could just get the value “12345678910111200” from excel. As you said, you have had a solution to solve it by using Interop references, could you provide something about it? What’s the type in excel? Besides, the following similar thread that you could have a look.

    http://stackoverflow.com/a/904642

    I hope it’s useful to you.

    Best Regard,

    Weibo Zhang

    Sunday, October 18, 2015 10:35 PM