Hi,
Thanks for your inquiry.
That’s what MS Excel’s behavior is all about. MS Excel saves/stores the dates in numbers formats, so when you import a datatable, it, by default, displays the dates in numeric format. I think you need to set formatting for your specific column to datetime for your need. You may use Aspose.Cells for .NET APIs (e.g Style.Number and Style.Custom attributes) to do the job (format the cells in the column to datetime formats), see the document for reference:
Data Formatting
List of Supported Number Formats
Applying Style on a Row or Column
Alternatively, you may use suitable overloaded version of ImportDataTable() method (if you are using it) for your requirement. See the example code below:
Examples:
Dim wb As New Workbook()
//…
Dim ws As Worksheet = wb.Worksheets(0)
//…
//Your code goes here.
//…
//You may apply a format to datetime format while importing data from a datatable.
//intRows is a sample variable having total number of rows and intColumns is for total num of cols.
ws.Cells.ImportDataTable(dataTable, True, 0, 0, intRows, intColumns, True, “mm/dd/yyyy”, True)
//…
- // *********** You may import the datatable and then apply format to specific col for your need.
'Instantiating a “Products” DataTable object
Dim dataTable As New DataTable(“Products”)
dataTable.Columns.Add(“Stock In Date”, GetType(DateTime))
'Creating an empty row in the DataTable object
Dim dr As DataRow = dataTable.NewRow()
'Adding data to the row
dr(0) = DateTime.Now
'Adding filled row to the DataTable object
dataTable.Rows.Add(dr)
'Creating another empty row in the DataTable object
dr = dataTable.NewRow()
'Adding data to the row
dr(0) = DateTime.Now
'Adding filled row to the DataTable object
dataTable.Rows.Add(dr)
Dim wb As New Workbook()
Dim ws As Worksheet = wb.Worksheets(0)
ws.Cells.ImportDataTable(dataTable, True, “A1”)
Dim style As Aspose.Cells.Style = Nothing
Dim flag As StyleFlag = Nothing
flag = New StyleFlag()
style = wb.Styles(wb.Styles.Add())
style.Custom = “mm/dd/yyyy”;
flag.NumberFormat = True
ws.Cells.ApplyColumnStyle(0, style, flag)
ws.AutoFitColumn(0)
wb.Save(“f:\test\outputfile.xls”)
Thank you.