Reading double's from Excel "the Excel way"

Question

Background

I am trying to read a 22 x 22 matrix from a Excel Worksheet. The matrix holds percent values and the values of each row must have a sum of 100% (or 1 when dealing with the numbers behind the percent value). When I open such a Excel worksheet and build the sum on each row, it is always 100% (1). Perfect.

But when I read the worksheet and sum up the (double) values read from the sheet I get a significant distance to 1 on most of the rows (significant means more than 0.00000000001 absolute distance to 1).

Investigation

I modified the matrix in excel to display me the numbers behind the percent values and the compared it to what I've read using EPPlus. For example I had

99.86% (Excel with percent)

0.998610811163197 (Excel as number)

0.9986108111631975 (read with EPPlus)

I renamed my Excel document to a ZIP archived, unpacked it and opened the according sheet in Visual Studio. The value stored was exact the value I got with EPPlus - which wasn't really surprising.

Solution?

I decided to operate as excel does, at least I thought excel does it so. I tried to round the values after 15 digits. But funny enough, the result wasn't the same as in excel, even worse, after looking at some other values I had:

0.00 % (Excel with percent)

0.00000330942432252678 (Excel as number)

3.3094243225267778E-6 (stored in the XML, read via EPPlus)

So, the question is: is there a way to round or read the values from Excel as Excel displays them?

Here is my code for reading the excel:

        using (ExcelPackage excel = new ExcelPackage())
        {
            excel.Load(File.OpenRead("data.xlsx"));
            var a1 = excel.Workbook.Worksheets.First().Cells["A1"].Value;
            var a2 = excel.Workbook.Worksheets.First().Cells["A2"].Value;
        }

Apologies, I am not able to upload the excel file at the moment from my workplace to dropbox or something else, I'll attach it later.

Edit: here is the excel document.

Why are you bothering to get the 1x10^-16 decimal place? It's the equivalent of 15 nano-metres when measuring the distance from the Earth to the Sun. — Enigmativity
@Enigmativity who said that I am bothering. I have to bother with it. In financial sector such differences sum up to cents, sum up to millions, ... And as I wrote, I get a matrix. Each row must sum up to 100% which it does not in .NET (/XML), but does in Excel. — MiGro
Then make that clear. Your question doesn't do that. Perhaps read How to Ask? — Enigmativity

as94 as94 · Accepted Answer · 2017-09-30T03:30:34

If i understand your question, you have problem with display double value, right? You can use correct format for displaying double values. For example:

double val = 99.8610811163198;
Console.WriteLine(val.ToString("P", CultureInfo.InvariantCulture));

About this read MS article: https://msdn.microsoft.com/en-us/library/kfsatb94(v=vs.110).aspx

Reading double's from Excel "the Excel way"

1 Answers