Numbers not rounded when reading excel file programmatically

Question

I have a column in excel where numbers are rounded to 2 decimal places using ROUNDUP.

In excel itself the numbers look as they should after rounding, i. e. "2,22".

But when I read the numbers in c# code using openxml, instead of 2,22 I get "2,2199999999999998".

So it looks like ROUNDUP does not really change numbers, just the way they are displayed.

How can I round a number in such a way that it can be read by openxml exactly as it is shown in excel?

The excel sheet looks like this:

And the code like this:

using (var excelPackage = new ExcelPackage(File.OpenRead(pathToFile))) {
    var worksheet = excelPackage.Workbook.Worksheets.FirstOrDefault();
    var value = worksheet.Cells[2, 2].GetValue<string>();// "2,2199999999999998"
}

Answer 1

RoundUp doesn't change the cell type, it remains a double, not a real decimal number, as in many programming languages.

In reality, there is no exact binary representation of many double number. It exists, just for integers and some decimals, like 0.5

There are 52 bits for the mantissa(F), 1 bit for sign (S), and 11 bits for the exponent (E) of a scientific notation representation in binary.

if 0<E<2047, then x=(−1)^s × (1.F) × 2^(E−1023), where 1.F represents the binary number created by prefixing F with an implicit leading 1 and a binary point.

In this way, it is only possible to accurately represent decimal numbers that can be written in the form a + 2^(-n) + b * 2^(-m) + ..., where a, b, n, m are integers.

What the roundup does is to do the best within the resolution of the number in Excel (8 bytes), and the number display in a worksheet fix the way that the numbers looks.

To really round it up you need to convert it to a string before using C #:

=Text(number,"#.##")