Reading a duration over 24h from Excel using EPPlus

user_4934061 0
Viewed 526

I am trying to read the text value of a specific cell from excel. The value in question is a formula which sums up multiple cells.

Duration

What Ive tried so far:

worksheet.Cells["E42"].Style.Numberformat.Format  = "hh:mm"; //and [hh]:mm
result = worksheet.Cells["E42"].Text;

Converting the worksheet.Cells["E42"].Value to DateTime using FromAoDate doesnt work for me either. The best result was 08:20 which is conveniently 24h less than the actual value.

EDIT: Results from the suggestions

worksheet.Cells["E42"].Style.Numberformat.Format  = "[hh]:mm";
result = worksheet.Cells["E42"].Text; //returns "08"

worksheet.Cells["E42"].Style.Numberformat.Format  = "hh:mm";
result = worksheet.Cells["E42"].Text; //returns "08:20"

worksheet.Cells["E42"].Style.Numberformat.Format  = "hh:mm";
result = worksheet.Cells["E42"].Text;
result = TimeSpan.TryParse((string)result, out TimeSpan tres); //returns 08:20:00

worksheet.Cells["E42"].Style.Numberformat.Format  = "[hh]:mm";
result = worksheet.Cells["E42"].Text;
result = TimeSpan.TryParse((string)result, out TimeSpan tres); //returns 8.00:00:00

//converting to DateTime has similar results

result = DateTime.FromOADate(Convert.ToDouble(worksheet.Cells["E42"].Value)); 
//returns 31.12.1899 08:20:00
epplus excel .net c#
2 Answers

This will give you a TimeSpan object that should work for your needs. 

string result = worksheet.Cells["E42"].Text;
TimeSpan timeResult;

string[] split = result.Split(':');
timeResult = new TimeSpan(int.Parse(split[0]), int.Parse(split[1]), 0);
Admin1

I havent found a proper solution yet, but there is a way.

var worksheet = package.Workbook.Worksheets[2];
var value = worksheet.Cells["E42"].GetValue<DateTime>(); //the cell contains 32:20 in excel
//returns 31.12.1899 08:20:00
var value2 = worksheet.Cells["E39"].GetValue<DateTime>(); //the cell contains 00:00 in excel
// which returns 30.12.1899 00:00:00
var t = value.Subtract(value2); //returns 1.08:20:00, which translates to 32:20:00

The 00:00 Date seems to convert to DateTime.Parse("30.12.1899 00:00:00"); and 33:20 to 31.12.1899 08:20:00, so by subtracting we can recover the actual timespan.

Here's an example implementation:

//Only works for single values
private bool TryGetTimeSpan(ExcelWorksheet sheet, string address, out TimeSpan? result)
{
    var excelMinDate = DateTime.Parse("30.12.1899 00:00:00");

    try
    {
        var time = sheet.Cells[address].GetValue<DateTime>();
        result = time.Subtract(excelMinDate);
    }
    catch (FormatException)
    {
        result = null;
        
        return false;
    }

    return true;
}
user_49340610
Related