Export to excel date format not coming as correct format.

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();

}

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Rahul Prasad10y ago
You'd better explain the differences in format between datatable and excel, otherwise it would be difficult for others to provide you solutions. Anyway, you can use a 3rd party library to import data from datatable to excel file with original formatting maintained.

Here is the demo tutorials:
http://www.e-iceblue.com/Tutorials/Spire.XLS/Demos/DataTable/EXCEL-Data-Import-for-C-VB.NET.html