How to retain DateTime format while exporting a Data Table to excel using Aspose.Cells

Aspose.Cells Product Family
1

Hi,


I am trying to export a datatable(c#) to excel using Aspose.Cells. But When I try to export it all the DateTime fields are converted it into numbers.

The Format in DataTable: 6/4/2009 12:00:00 AM
The Format in Excel: 40101

Can you please let me know how can I prevent this.

Thanks in Advance.

Regards,
Nikhil
2

Hi Nikhil,

Thank you for contacting Aspose support.

There are two ways to prevent the Excel from converting DateTime strings to numbers while importing data from a DataTable.

  1. Specify the DateTime format while importing the data : This mechanism will allow you to specify the format within the Cells.ImportDataTable function. However, once specified, the format will be applied to all values of DateTime type. Please check the below provided code snippet & attached resultant spreadsheet for your reference.

C#

DataTable dt = new DataTable(“sample”);
dt.Columns.Add(“Date”, typeof(DateTime));
dt.Rows.Add(new object[] { “6/4/2009 12:00:00 AM” });

Workbook book = new Workbook();
Worksheet sheet = book.Worksheets[0];
sheet.Cells.ImportDataTable(dt, true, 0, 0, dt.Rows.Count, dt.Columns.Count, true, “mm/dd/yyyy hh:mm:ss”);
book.Save(myDir + “output.xlsx”, SaveFormat.Xlsx);
  1. Format the Cells/Columns/Rows after importing the data : Once you have imported the data, you may apply the formatting to specified cell, column and/or row according to your need. Please check the below linked technical articles for your reference.