Date Issue in Excel Export

This is just a tip on solving date issue in exporting the excel file from ASP.NET.

Introduction

One of my guys was using this following code to export the excel file from ASP.NET application. The code is very simple. He created the datatable with the data that he wants to export from database. He bind that datatable with datagrid, write the HTML code to HtmlTextWriter and write those HTML code to the response with “application/vnd.ms-excel” content type.

Problem

We are able to view the exported file in excel but the problem is that the date formats are not consistent for all rows. Take a look at Elena’s b’day and Tifa’s b’day in this screenshot below. The date formats are different, right?

Why?

This problem occurred because Excel is using the formats from regional setting. The format of short date in my system is M/d/yyyy and the long date format is dddd/MMMM dd,yyyy. All data that follows those date format will be treated as a date in excel. Otherwise, it will be treated as a string. If you look at my b’day “8/31/1982 3:19:40 PM”, it doesn’t follow dddd/MMMM dd,yyyy format so excel treat it as a string.

Solution

There are more than one solution for this problem. You can specify “vnd.ms-excel.numberformat:dd/mm/yy” style to that cell. but the easiest fix that we came up is to set the date format as below while populating the data.

dr[2] = string.Format("{0:yyyy-MM-dd HH:mm:ss}", dob); //Right code

Conclusion

It’s just a simple issue and simple solution but we did face this issue so I’m sharing it here. Hopefully, developers who are facing the same problem might find this post useful. :)