EPPlus GetValue<TimeSpan>() returns incorrect value for formatted time cells

[darrelsilveira047]

EPPlus usage

Commercial use (I have a commercial license)

Environment

Windows

Epplus version

8.7.1

Spreadsheet application

Excel

Description

GetValue() returns 06:09:59.9999999 for an Excel cell containing 06:10:00 when a time format such as h:mm or h:mm:ss is applied. If the cell format is changed to General or time, the value is returned correctly as 06:10:00

using OfficeOpenXml;

ExcelPackage.License.SetNonCommercialPersonal("TESTING");

var filePath = Path.Combine("../../../", "Resources", "TestTime.xlsx");

using var stream = new FileStream(filePath, FileMode.Open, FileAccess.Read);

using var excel = new ExcelPackage(stream);

var worksheet = excel.Workbook.Worksheets.First();
var cell = worksheet.Cells[1, 1];

var rawValue = cell.Value;
var valueAsString = cell.GetValue<string>();
var valueAsTimeSpan = cell.GetValue<TimeSpan>();

Console.WriteLine($"Raw value     : {cell.Value}");
Console.WriteLine($"Raw type      : {cell.Value?.GetType()}");
Console.WriteLine($"Number format : {cell.Style.Numberformat.Format}");

var time = cell.GetValue<TimeSpan>();

Console.WriteLine($"TimeSpan      : {time:c}");
Console.WriteLine($"Hours         : {time.Hours}");
Console.WriteLine($"Minutes       : {time.Minutes}");
Console.WriteLine($"Seconds       : {time.Seconds}");
Console.WriteLine($"Ticks         : {time.Ticks}");

var expected = new TimeSpan(6, 10, 0);
var matches =
    valueAsTimeSpan.Hours == expected.Hours &&
    valueAsTimeSpan.Minutes == expected.Minutes &&
    valueAsTimeSpan.Seconds == expected.Seconds;

Console.WriteLine($"Actual   : {valueAsTimeSpan:hh\\:mm\\:ss}");
Console.WriteLine($"Expected : {expected:hh\\:mm\\:ss}");
Console.WriteLine(matches ? "PASS" : "FAIL");

TestTime.xlsx

[OssianEPPlus]

We've looked into this. It's indeed a rounding bug and we've created a fix

[OssianEPPlus]

This fix will be included in the next release of EPPlus. Until then, you can test the fix via our development NuGet feed (Epplus8). Please see this link:

Using our develop Nuget feed · EPPlusSoftware/EPPlus Wiki