Spire.DataExport for .NET is a 100% pure data .NET library suit for exporting data into MS Word, Excel, RTF, Access, PDF, XPS, HTML, XML, Text, CSV, DBF, SYLK, SQL Script, DIF, Clipboard, etc.

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

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