cell values wrong by using floats

[ChrisCA]

trafficstars

NetCore 2.2 EPPlus 4.5.3.1

Float values are not handled properly if used for cell values while double values are handled properly. This can be reproduced except using cell values like e.g. 0.5. Values like 0.6 are saved as 0,600000023841857.

Code for verification:

FileInfo f = new FileInfo("test.xlsx");
ExcelPackage pck = new ExcelPackage(f);

var ws = pck.Workbook.Worksheets.Add("test");
ws.Cells[1, 1].Value = float.Parse("29.5", CultureInfo.InvariantCulture);
ws.Cells[2, 1].Value = float.Parse("0.4", CultureInfo.InvariantCulture);
ws.Cells[3, 1].Value = float.Parse("0.5", CultureInfo.InvariantCulture);
ws.Cells[4, 1].Value = float.Parse("0.6", CultureInfo.InvariantCulture);

ws.Cells[1, 2].Value = double.Parse("29.5", CultureInfo.InvariantCulture);
ws.Cells[2, 2].Value = double.Parse("0.4", CultureInfo.InvariantCulture);
ws.Cells[3, 2].Value = double.Parse("0.5", CultureInfo.InvariantCulture);
ws.Cells[4, 2].Value = double.Parse("0.6", CultureInfo.InvariantCulture);

ws.Cells[4, 3].Value = 0.6f;
ws.Cells[4, 4].Value = 0.6d;

pck.Save();

Produced file: test.xlsx

[swmal]

Sorry for a very late reply to this issue. We have briefly looked into this before. As you might know, some decimal numbers can not be exactly represented by a float. Here is a good article on the topic. Excel stores 15 significant digits of precision, but - as I understand it - has the ability to round these results in the user interface. EPPlus is just a calculation engine that relies on .NET's implementation of floats.

We might look deeper into this in our next version EPPlus 5, which has moved to another repo and is distributed under a new license.