OpenXML value different from Excel value

Question

I'm working with the OpenXML SDK at the moment and I've got a problem with the value shown in Excel compared to the value in OpenXML.

Basically I have an excel Spreadsheet where the value in B4 is "202". But when I read the value from the spreadsheet using OpenXml the value I receive is "201.99999999999997".

I've written a test app to check the values, and code I'm using to read the Excel file using OpenXml is as follows:

using (SpreadsheetDocument document = SpreadsheetDocument.Open(_excelFile1, false))
{
    WorkbookPart wbPart = document.WorkbookPart;

    var sheet = wbPart.Workbook.Descendants<Sheet>().First();

    var wsPart = (WorksheetPart)(wbPart.GetPartById(sheet.Id));

    Cell cell1 = wsPart.Worksheet.Descendants<Cell>().Where(c => c.CellReference == "B4").FirstOrDefault();
    Cell cell2 = wsPart.Worksheet.Descendants<Cell>().Where(c => c.CellReference == "C4").FirstOrDefault();
    Cell cell3 = wsPart.Worksheet.Descendants<Cell>().Where(c => c.CellReference == "D4").FirstOrDefault();

    Console.WriteLine(String.Format("Cell B4: {0}", GetCellValue(cell1)));
    Console.WriteLine(String.Format("Cell C4: {0}", GetCellValue(cell2)));
    Console.WriteLine(String.Format("Cell D4: {0}", GetCellValue(cell3)));
}

private static string GetCellValue(Cell cell)
{
    if (cell != null) return cell.CellValue.InnerText;
    else return string.Empty;
}

Then the value I get is as follows:

Cell B4: 201.99999999999997

[UPDATE - RAW XML FROM XLSX FILE]

<row r="4" spans="1:4" x14ac:dyDescent="0.2">
    <c r="A4" s="2">
        <v>39797</v>
    </c>
  <c r="B4" s="3">
    <v>201.99999999999997</v>
  </c>
  <c r="C4" s="3">
    <v>373</v>
  </c>
  <c r="D4" s="3">
    <v>398</v>
  </c>
</row>

So as you can see from the XLSX file XML, OpenXML is reading the correct value. However, Excel must be applying some kind of formatting to display the value "201.99999999999997" as "202". This is the value the users see so this is the value they're expecting when it's read from the XLSX file using OpenXML.

So now I'm wondering if anyone knows of any formatting that's applied by Excel to get the value as the user sees it in the spreadsheet, but using OpenXML.

As a test I've tried recreating the spreadsheet, but sometimes the values appear the same as a decimal when a whole number has been entered into the spreadsheet. It's very intermittent and I can't explain or fix the issue.

I assume that GetCellValue is "your own" method. Please provide the implementation for this method as well. — Anders Gustafsson
– Anders Gustafsson, Commented Feb 25, 2013 at 14:40
It would also be great if you could extract and present the complete XML data for the cell B4. — Anders Gustafsson
– Anders Gustafsson, Commented Feb 25, 2013 at 14:51
i've similar problem with percentage values and decimals.. Did u find any workaround to prevent such conversion? — Harshini
– Harshini, Commented Dec 22, 2017 at 21:45
@Harshini - I am also facing similar issue. Did you find any solution to fix this issue? — Tech Learner
– Tech Learner, Commented Aug 6, 2021 at 15:57

dizzy128 · Accepted Answer · 2013-04-19 08:11:29Z

3

The 201.99999999999997 is a real representation of floating point number. It may not be always accurate, for more information look for example here. So you are right, excel applying some kind of formatting - significant digits. .NET can do the same, look at this:

    static void Main(string[] args)
    {
        string s = "201.99999999999997";
        // print s as string
        Console.WriteLine(s);
        // print s as double
        Console.WriteLine(double.Parse(s, System.Globalization.CultureInfo.InvariantCulture));
        // print s as double, converted back to string
        Console.WriteLine(double.Parse(s, System.Globalization.CultureInfo.InvariantCulture).ToString());
        Console.ReadKey();

        // output is:
        // 201.99999999999997
        // 202
        // 202
    }

So, if you need a number, convert "201.99999999999997" to number. if you need a string representation of number, convert it to number and then back to string.

answered Apr 19, 2013 at 8:11

dizzy128

2893 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

John McElreavey Over a year ago

Worth mentioning here when using CultureInfo.InvariantCulture and then converting it back into a string you should apply the InvariantCulture to the ToString too to avoid incorrect formatting. i.e..ToString(CultureInfo.InvariantCulture)

Collectives™ on Stack Overflow

OpenXML value different from Excel value

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related