Search code examples
exceldatetimeepplus

EPPlus Excel report with DateTime before 1900.01.00

I want to create an Excel report with the help of EPPlus but when it puts a date before 1900.01.00. into the Excel worksheet it convers to a negative number or a long '#####...' line appears in the place of a date in the Excel. I have set the format but it didn't help:

workSheet.Column(columnIndex).Style.Numberformat.Format = "yyyy.mm.dd.";

I used this page:

http://www.c-sharpcorner.com/article/export-to-excel-in-asp-net-mvc/

Solution

  • MS Excel does not recognize dates before 1900. The date number format is useless. You need to format the date in C# and place the value as string, not as number.

    In order to set the number format as text use:

    workSheet.Column(columnIndex).Style.Numberformat.Format = "@";

    In order to format the date use the following code, if the date is a datetime:

    System.IFormatProvider formatProvider = new System.Globalization.CultureInfo("en-US", true);//or a different culture
dateTime.ToString("yyyy.MM.dd.", formatProvider))