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.

Download

protected void Button1_Click(object sender, EventArgs e) {
            var datatable = CreateDataTableWithData();

            System.IO.StringWriter stringWriter = GetHtmlTagsfromDatagrid(datatable);

            WriteResponse(stringWriter);
        }

        private void WriteResponse(System.IO.StringWriter stringWriter) {
            Response.Clear();
            Response.Charset = "UTF-8";
            Response.ContentEncoding = System.Text.Encoding.GetEncoding("UTF-8");
            Response.ContentType = "application/vnd.ms-excel";
            Response.ContentEncoding = System.Text.Encoding.Unicode;
            Response.BinaryWrite(System.Text.Encoding.Unicode.GetPreamble());
            Response.AppendHeader("Content-Disposition", "attachment;filename=myexcel.xls");
            Response.Write(stringWriter.ToString());
            Response.End();
        }

        private static System.IO.StringWriter GetHtmlTagsfromDatagrid(DataTable datatable) {
            System.IO.StringWriter stringWriter = new System.IO.StringWriter();
            HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWriter);
            DataGrid dg = new DataGrid();
            dg.DataSource = datatable;
            dg.DataBind();
            dg.RenderControl(htmlWrite);
            return stringWriter;
        }

        private static DataTable CreateDataTableWithData() {
            var datatable = CreateDataTable();

            datatable.Rows.Add(CreateDataRow(datatable, 1, "Michael Sync",
                "8/31/1982 3:19:40 PM"));
            datatable.Rows.Add(CreateDataRow(datatable, 1, "Shwesin Sync",
                "9/29/1982 3:00:32 PM"));
            datatable.Rows.Add(CreateDataRow(datatable, 1, "Elena Sync",
                "1/15/2011 00:00:01 PM"));
            datatable.Rows.Add(CreateDataRow(datatable, 1, "Tiffany Sync",
                "7/6/2012 00:00:01 PM"));

            datatable.AcceptChanges();
            return datatable;
        }

        private static DataTable CreateDataTable() {
            var datatable = new DataTable();
            datatable.Columns.Add("Id");
            datatable.Columns.Add("Name");
            datatable.Columns.Add("Date of Birth");
            return datatable;
        }

        private static DataRow CreateDataRow(DataTable datatable, int id, string name, string dob) {
            DataRow dr = datatable.NewRow();
            dr[0] = id;
            dr[1] = name;
            //dr[2] = string.Format("{0:yyyy-MM-dd HH:mm:ss}", dob); //Right code
            dr[2] = dob; // Wrong code

            return dr;
        }

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. :)

Happy Silverlighting! Oh no! Happy sharing!!

Related ~