Search code examples
c#epplus

EPPlus cells not updated after number format

Im trying to change number format with epplus in excel to this format ([h]:mm:ss), and it seems to change the cells format correctly. But the cells value doesnt update after the format...

As the picture shows the correct way of the text should be like the first cell on the picture. It works if I click on the cells and presses enter againPicture

var sheet = package.Workbook.Worksheets.Add(filename);

var tableRange = sheet.Cells["A1"].LoadFromCollection(list);
var table = sheet.Tables.GetFromRange(tableRange);
package.Save();

NumberFormat

[EpplusTableColumn(Order = 2, NumberFormat = "[h]:mm:ss")]
public string timeWorked { get; set; }

In the end, I want to use the SUM function in excel to calculate the total timeworked, but it doesnt work when the cells doesnt change to the format I have sat for it

Solution

  • Firstly, change type from string to TimeSpan

    public TimeSpan TimeWorked { get; set; } = new TimeSpan(2, 18, 19);

    Secondly, set printHeader to true

    var tableRange = sheet.Cells["A1"].LoadFromCollection(actors, true);

    For some reason column when using EpplusTableColumn attribute headers need to be included in excel otherwise cell formatting doesn't work. I'm not sure if it's a bug or am I missing something.

    Would love if someone can give me more explanation.

    If you really don't want headers to show there is a workaround. Before saving remove first row from excel.

    sheet.DeleteRow(1);
package.Save();