DataTable, DateTime format in DataExport to XLS

Technical support for Spire.DataExport

Moderators: iceblue support, DataExport, Manager

DataTable, DateTime format in DataExport to XLS

Postby alarm 654 » Mon Jun 07, 2010 11:41 am


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

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.

alarm 654
Posts: 1
Joined: Sun Sep 10, 2006 7:19 pm
Location: UMAX-SE.NET

Re: DataTable, DateTime format in DataExport to XLS

Postby adtionsa » Sat Jun 19, 2010 1:06 am


Please try to using following way, add GetDataParams event to Spire.DataExport.

Code: Select all
   private void cellExport1_GetDataParams(object sender, Spire.DataExport.EventArgs.DataParamsEventArgs e)
                //e.sheet equals sheet index, e.col equals column index.
   if    ((e.Sheet == 0) && (e.Col == 6))
      e.FormatText = cellExport1.DataFormats.DateTime;
Posts: 5
Joined: Sat Sep 09, 2006 7:48 pm
Location: Basel

Return to Spire.DataExport

Who is online

Users browsing this forum: No registered users and 1 guest