Search code examples
c#epplus

C# EPPlus date format generating warning

I have date like 08/09/21 which is MM/dd/yy format. when i am showing this date in excel by EPPlus then warning is showing.

this is sample code i used. many ways i tried but no luck. please have a look.

1)

ws.Cells[1 + 1].Value = Convert.ToDateTime(dtGroup.Rows[r][dtGroup.Columns[c].ColumnName].ToString()).Date;
ws.Cells[1 + 1].Style.Numberformat.Format = "m/d/yy";

Warning is generated for the above code.

2)

ws.Cells[1 + 1].Value = Convert.ToDateTime(dtGroup.Rows[r][dtGroup.Columns[c].ColumnName].ToString()).Date;
ws.Cells[1 + 1].Style.Numberformat.Format = "@";

Warning is generated for the above code.

I tried four way but nothing worked. so i am confused and not able to understand where i made the mistake. please suggest something because i need to show date in excel like 08/09/21 which is MM/dd/yy format

Thanks

EDIT

ws.Cells[r + 6, groupstartcol].Value = DateTime.Parse(dtGroup.Rows[r][dtGroup.Columns[c].ColumnName].ToString()).ToString("MM/dd/yy");
ws.Cells[r + 6, groupstartcol].Style.Numberformat.Format = "mm/dd/yy";

dtGroup is datatable and date is coming from db table where date has been stored as string.

i tried the above code and it is still not working. here i am pasting a screen shot that may help you to understand the issue. Thanks

enter image description here

Solution

  • You want a date cell, so don't pass a string value to it, instead, pass a DateTime and apply a format (which will affect the presentation only, not the data). If dtGroup.Rows[r][dtGroup.Columns[c].ColumnName] is a DateTime, you can do

    ws.Cells[r + 6, groupstartcol].Value =((DateTime)dtGroup.Rows[r][dtGroup.Columns[c].ColumnName]).Date;
ws.Cells[r + 6, groupstartcol].Style.Numberformat.Format = "mm/dd/yy";

    If you have a string:

    ws.Cells[r + 6, groupstartcol].Value = DateTime.Parse(myString).Date;
ws.Cells[r + 6, groupstartcol].Style.Numberformat.Format = "mm/dd/yy";