Export to excel date format not coming as correct format.

Question

Hi ,

 

while am exporting datatable  in to excel but some of the date format coming correct in excel but some of them not as correct .

In datatable its correct but export to excel not correct format.

 

below is my code:

 

 

dtSelectedColumns = dt.DefaultView.ToTable(false, "LOS_Appln_Number", "Bureauonerefno", "BureauProduct", //"Product_Code",

"Contract", "Transaction_Amount", "FirstNameEnglish", "LastNameEnglish", "FullNameEnglish", "First_Name_AR", "Last_Name_AR", "Full_Name_AR", "DOB", "Passport_Id",

"PassportExpiryDate", "EmiratesID", "DrivingLicense", "Nationality", "CreatedBy", "CreatedOn", "dbstatus", "Errormsg");

columnName = new ArrayList();

columnName.Add("ProviderAppln No.");

columnName.Add("BureauOne Ref No.");

//columnName.Add("Bureau Category");

columnName.Add("Bureau");

columnName.Add("Contract Type");

columnName.Add("Transaction Amount");

columnName.Add("First Name(EN)");

columnName.Add("Last Name(EN)");

columnName.Add("Full Name(EN)");

columnName.Add("First Name(AR)");

columnName.Add("Last Name (AR)");

columnName.Add("Full Name(AR)");

columnName.Add("DOB(dd-mm-yyyy)");

columnName.Add("Passport Id");

columnName.Add("Passport ExpiryDate");

columnName.Add("EmiratesID");

columnName.Add("Driving License");

columnName.Add("Nationality");

columnName.Add("EnquiredBy");

columnName.Add("EnquiryDate");

columnName.Add("EnquiryStatus");

columnName.Add("Remarks");

 

ExportDataTable(dtSelectedColumns, columnName,"EnquirySearch"); 

public static void ExportDataTable(DataTable dtSelectedColumns, ArrayList columnName,string pagename)//working

{

string strFileName = pagename + "_" + DateTime.Now.ToString("dd/MM/yyyy HH:mm");

//Guid.NewGuid().ToString();

string attachment = "attachment; filename=" + strFileName + ".xls";

HttpContext.Current.Response.Clear();

HttpContext.Current.Response.AddHeader("content-disposition", attachment);

HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";

string sTab = "\t";

string heading = string.Empty;

foreach (string colum in columnName)

{

heading += colum + sTab;

}

//HttpContext.Current.Response.Write("username" + sTab + "Status" + sTab + "CreatedBy" + sTab + "CreatedOn" + sTab + "UpdatedBy" + sTab + "UpdatedOn");

HttpContext.Current.Response.Write(heading);

HttpContext.Current.Response.Write("\n");

foreach (DataRow dr in dtSelectedColumns.Rows)

{

sTab = "";

for (int i = 0; i < dtSelectedColumns.Columns.Count; i++)

{

HttpContext.Current.Response.Write(sTab + dr[i].ToString());

sTab = "\t";

}

HttpContext.Current.Response.Write("\n");

}

HttpContext.Current.Response.End();

}