When I try to export a DataTable containing a DateTime field to Excel, it is shown as a number. The number is a correct value when shown as DateTime, but the cell format should be DateTime instead of Default. The DataTable correctly contains the column as DateTime.
Here is a code snippet that reproduces the issue:
- Code: Select all
SqlCommand cmd = new SqlCommand();
cmd.Connection = new SqlConnection(/* Insert some SQL Server connection string */);
cmd.CommandText = "SELECT CAST('2010-01-01 0:00' AS DATETIME)";
cmd.CommandType = CommandType.Text;
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = cmd;
DataTable table = new DataTable();
da.Fill(table);
CellExport ce = new CellExport();
ce.DataTable = table;
ce.DataSource = Spire.DataExport.Common.ExportSource.DataTable;
ce.SaveToHttpResponse("test.xls", Response);
The resulting Excel sheet contains the following data:
- Code: Select all
Column1
40179
I've tried setting the DataFormats property, but it didn't work. Actually I cannot find documentation or code samples how to use this property.
We consider purchasing Spire.DataExport, but I need to make this work... Please help.
Thanks,
Hugo