Base solution for your next web application
Open Closed

Excel Export - Microsoft Excel OLE Formatted Time #10639


0
Hostmaster created

We're using AspNetZero 10.3, and we have a case where we want to output a number, eg 450, as 7:30. Client needs to be able to sum these up using Excel thus this formatting is particularly useful.

With AspNetZero 6, we were able to output the time as using this function:

double? ConvertMinutesToHHMM(int minutes) { var baseExcelDate = new DateTime(1900, 1, 1, 0, 0, 0); return baseExcelDate.AddMinutes(minutes).ToOADate() - 2; }

and then specify a format as:

        "[h]:mm"
        

Unfortunately it seems that within ASP.NET Zero 10.3, it appears that this no longer works, and despite the formatting being applied to the cell, the cell gets output using NPOI as a double - ie 0.3125.

Any solutions for this?


6 Answer(s)
  • 0
    ismcagdas created
    Support Team

    Hi @hostmaster

    We were using EPPlus but it changed its license type to a commercial one and we switched to NPOI. I'm not sure how to do this at the moment. Could you create an issue on GitHub so we can try to find a solution ?

  • 0
    Hostmaster created

    Hi,

    I don't seem to have access to that section of GitHub. Could you advise for me?

    Regards, Simon Dean

  • 0
    Hostmaster created

    I figured out how to gain access to the GitHub area. Issue raised.

  • 0
    rvanwoezik created

    SetCellDataFormat(sheet.GetRow(i).Cells[3], "[h]:mm");

  • 0
    Hostmaster created

    Hi @rvanwoezik - yes we've tried that, but that doesn't work. Although the style IS set with Excel when you look at the Cell Properties, it displays as a number as 0.3125 as noted above.

  • 0
    ismcagdas created
    Support Team

    Thanks @hostmaster