Dates not correctly formatted with ImportDataTable

Aspose.Cells Product Family
1

Hi,

I am using the Aspose.Cells ImportDataTable method, but am finding the dates are not correctly represented. They are appearing as serial numbers. Manually changing the 'cell format' in Excel (and I assume I could do similar in code with aspose) works around this, but this is no good because the date fields will be in different columns each time. The below replicates the issue:

Type first = typeof(String);

Type second = typeof(DateTime);

DataTable test = new DataTable();

DataRow testRow = test.NewRow();

test.Columns.Add("name", typeof(String));

test.Columns.Add("date", typeof(DateTime));

testRow[0] = "Mark";

testRow[1] = new DateTime(2011, 02, 02);

test.Rows.Add(testRow);

//Worksheet sheet = new Worksheet();

int sheetIndex3 = book.Worksheets.AddCopy("Template");

Worksheet sheet3 = book.Worksheets[sheetIndex3];

sheet3.Name = "Test";

sheet3.Cells.ImportDataTable(test, true, "A10");

sheet3.Cells.CreateRange("10:10").SetStyle(DataHeaderStyle);

sheet3.AutoFilter.Range = "A10:" + CellsHelper.ColumnIndexToName(sheet3.Cells.MaxDataColumn) + "65000";

sheet3.AutoFitColumns();

sheet3 = null;

Please advise how to resolve this. Many thanks.

2

Hi,


Well, for your information Dates are stored in numeric notations. You may try to use other ImportDataTable() overloads, e.g you may try to specify your desired formatting for Date columns while importing data using the method. Here is the simplest code, please see the line in bold:

Sample code:

Workbook book = new Workbook();

Type first = typeof(String);

Type second = typeof(DateTime);

DataTable test = new DataTable();

DataRow testRow = test.NewRow();

test.Columns.Add(“name”, typeof(String));

test.Columns.Add(“date”, typeof(DateTime));

testRow[0] = “Mark”;

testRow[1] = new DateTime(2011, 02, 02);

test.Rows.Add(testRow);

Worksheet sheet3 = book.Worksheets[0];

sheet3.Name = “Test”;

sheet3.Cells.ImportDataTable(test, true, 9,0,test.Rows.Count,test.Columns.Count,false,“mm/dd/yyyy”);

sheet3.AutoFitColumns();

book.Save(“e:\test2\outputFile.xls”);
3

Many thanks - that works perfectly! What great support you all provide.

4

Hi,


Good to know that your issue is resolved.

Please feel free to contact us any time if you have any query or issue.

Have a good day!