2

I am generating xls file from ASP DataTable:

var excelGrid = new DataGrid { DataSource = dataTable };
excelGrid.DataBind();
Response.Clear();
Response.ContentType = "application/vnd.xls";
Response.ContentEncoding = Encoding.GetEncoding("iso-8859-1");
Response.Charset = Encoding.UTF8.EncodingName;

var fileName = "export_" + DateTime.Now.ToShortDateString() + ".xls";
Response.AddHeader("content-disposition", "attachment;filename=" + fileName);
Response.ContentEncoding = Encoding.Unicode;
Response.BinaryWrite(Encoding.Unicode.GetPreamble());

var swriter = new StringWriter();
var hwriter = new HtmlTextWriter(swriter);

excelGrid.RenderControl(hwriter);

Response.Write(swriter.ToString());
Response.End();

I write date using this formatting

row[dataTable.Columns[i++]] = date.ToString("yyyy-MM-dd");

The problem is that Excel displays it as dd.mm.yyyy. Can I force Excel to display this date in my format? How can I do this?

akeyo20
  • 23
  • 3
  • I don't know the type of your objects but I somehow doubt that a control of type `DataGrid` when _rendered_ to `TextWriter` will produce an output of type `application/vnd.xls`. I'm tempted to think that you have an HTML table served with the wrong MIME type (but Excel just opens it after a warning about extension that does not match content). – Adriano Repetti Aug 30 '17 at 12:54
  • You aren't generating an Excel file. You're generating an HTML file, serving it up with an Excel MIME type and extension. Why not generate an actual Excel file with a library, where you'll have full control over its content? – mason Aug 30 '17 at 12:55
  • I think you can't force Excel to display date as you want in that way. But with use of excel library or excel interop that may be possible. – Bhuban Shrestha Aug 30 '17 at 12:55
  • 1
    @BhubanShrestha Please do not suggest Excel interop as a possible solution for ASP.NET. That is a [terrible idea](https://support.microsoft.com/en-us/help/257757/considerations-for-server-side-automation-of-office). – mason Aug 30 '17 at 12:55
  • 1
    Please consider using EpPlus https://www.nuget.org/packages/EPPlus/. I recommend. It's very easy and have all this problems fugured out. – Pedro S Cord Aug 30 '17 at 12:57
  • @mason yeah using excel interop for asp.net would be bad idea. I didn't considered that while commenting. Thanks for correcting me. OP should consider choosing some decent Excel Library. – Bhuban Shrestha Aug 30 '17 at 13:00
  • @AdrianoRepetti, mason: Using a DataGrid to produce "Excel" is a known hack, but it's just a hack. I join PedroSouki in recommending EPPlus. – Tsahi Asher Aug 30 '17 at 13:10

2 Answers2

0

When adding the DataTable columns, set the colum type as string, like

dataTable.Columns.Add("Date1", typeof(string));

Then when adding the rows set a DateTime.ToString("yyyy-MM-dd") to the column slot.

dataTable.Rows.Add(date.ToString("yyyy-MM-dd"));

This makes your Excel file to understand it later as a string and to not try and format it by itself as a date.

Tiramonium
  • 557
  • 5
  • 15
  • I gave this answer assuming you are generating an actual Excel file. In case you don't, try OfficeOpenXml – Tiramonium Aug 30 '17 at 13:03
  • Why are you assuming an actual Excel file is generated, when the question itself shows exactly how it's generated? – mason Aug 30 '17 at 13:06
  • Office Open XML SDK is terrible with Excel. A horrible API you wouldn't find your arms and legs in. I recommend using EPPlus, which wraps the SDK with a reasonable set of API. – Tsahi Asher Aug 30 '17 at 13:12
  • I can make a xlsx file that works both with MS Office as with LibreOffice from a DataSet with 30 lines of code with it. It is a lot better than the previous library I was using. – Tiramonium Aug 30 '17 at 13:15
0

Probably your system settings are overruling the formatting when opening in Excel. As it is nothing more than html content you're sending out there to the client. If you keep using that piece of code, try adding an apostrophe to the line where you write the date:

row[dataTable.Columns[i++]] = "'" + date.ToString("yyyy-MM-dd");

This way, excel won't see this as a Date field and thus not following the system setting's short date notation. If this is fine by you... it will load/show as a text field.

If you still want go along with your coding, but dislike the apostrophe when viewing the resulting file in excel, then read up this first and then that for extended info on mso-number-format.

Michael
  • 344
  • 5
  • 17