Search code examples
c#asp.netexcelepplusasp.net-4.5

DateTime conversion is not working while reading dates from excel where Day > 12

This is my first 5 rows in my XLSX file:

enter image description here

Note that the format for the ResLastCallDate column is CustomFormat->dd/mm/yyyy hh:mm:ss. Yet, Row 4 looks different than the others for some reason. This is not important a lot tho, the important part is that the XLSX file is being uploaded to my asp.net 4.5 web page where I read the data via EPPlus. However, when I am reading ResLastCallDate column, I am receiving an error. See the code below:

System.Globalization.CultureInfo provider = System.Globalization.CultureInfo.InvariantCulture;
if (!DateTime.TryParse(dr["ResLastCallDate"].ToString(),provider, System.Globalization.DateTimeStyles.AssumeLocal, out resLastCallDate))
{
     Msg = Msg + string.Format("Row {0}: ResLastCallDate is not date ({1}).\n", r, dr["ResLastCallDate"].ToString());
}
...
txtDebug.Text = Msg;

I have tried not to use a provider, other DateTimeStyles, none of them worked, except Row 4 in the excel always been read correctly. See the output:

Row 0: ResLastCallDate is not date (20/03/2018 13:58)
Row 1: ResLastCallDate is not date (20/03/2018 13:58)
Row 3: ResLastCallDate is not date (15/03/2018 20:25)

So Row 2 (which equals ClientID=3621) works but others not for a reason. I've tried General and Date formats in the excel for those cells, still not working. What else I can do to handle this issue? Any help would be appreciated.

EDIT: When I convert it to dr["ResLastCallDate"].ToString() only (no provider I use), it works on my local machine, but when I publish to the server it does not work (same error message received)

Solution

  • It appears that CultureInfo.InvariantCulture does not like to receive strings in that particular format. I believe it's related to the fact that in the US date is written in MM/dd/yyyy order whereas in Europe it is written in dd/MM/yyyy (which includes the UK, and Australia).

    If that's the case, 20/03/2010 13:58 would have been parsed as 3rd day of 20th month, 2010, which does not make any sense.

    I believe using `new CultureInfo("en-gb") instead of CultureInfo.InvariantCulture would be better choice here.