guys, i have a bit of an anomole exporting to excel. on date formats, it seems no matter what i export to excel, it becomes a 24hr clock. for instance the date format thats being exported is stored as:: 03/11/2015 04:26:09 PM
- however when it hits excel, it becomes: 3/11/2015 15:56:09. so, since i'm literally doing nothing to format the incoming data, excel is conveniently making the change for me. it's shows as a 24 hr clock on the rows however, if you click on that cell in that row, in the upper window of excel, it actually shows the data that it was originally. the format of that cell in excel is: m/d/yyyy h:mm - the issue is for my company is other locations are showing some mixed results. i.e.: some of the rows are 24hr and other rows are what the original data was. so, without having to highlight every date column and force the change, is there some setting in Microsoft Office or the Pc itself that can make the default work?
i've added the code that takes a simple datatable to export to excel as an example. i'm pretty sure that there's nothing in this code i can do anything about since all the decisions are being made by excel itself, but it's just for reference.
thanks again
rik
private void exportDT(DataTable myDt)
{
DataTable dt = new DataTable();
dt = myDt;
string rptName = "Export";
Response.Clear();
Response.Buffer = true;
Response.ContentType = "application/vnd.ms-excel";
Response.ContentEncoding = System.Text.Encoding.UTF8;
Response.AddHeader("Content-Disposition", "attachment;filename=" + rptName + ".xls");
System.IO.StringWriter sw = new System.IO.StringWriter();
Response.Charset = "";
EnableViewState = false;
string tab = "";
foreach (DataColumn dc in dt.Columns)
{
sw.Write(tab + dc.ColumnName);
tab = "\t";
}
sw.Write("\r\n");
int i;
foreach (DataRow dr in dt.Rows)
{
tab = "";
for (i = 0; i < dt.Columns.Count; i++)
{
if (dr[i].ToString() == "")
{
sw.Write(tab + " ");
}
else
{
string replaceWith = "";
string myData = dr[i].ToString();
string myDReplace = myData.Replace("\r\n", replaceWith).Replace("\n", replaceWith).Replace("\r", replaceWith);
sw.Write(tab + myDReplace);
}
tab = "\t";
}
sw.Write("\r\n");
}
System.Web.UI.HtmlTextWriter htmlWrite = new System.Web.UI.HtmlTextWriter(sw);
Response.Write(sw.ToString());
sw.Close();
Response.End();
}
