Format A column as Date

I am populating a spreadsheet from a datatable using VB .Net, everything is fine except that some of the formatting is not correct, mainly my dates are set as general instead of date (displays 39983.5584483449 instead of 6/19/2009 1:24:10 PM for example, if I change the format on the spreadsheet all is well, but the initial formatting is incorrect. The datatable column is typed as System.DateTime.

My question is simple (I think) how do you set the type of a column in the spreadsheet, for example to be a date in the code?

I found the type property but it seems to be read only.

Any help is appreciated,
Mirek

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)

//…

  1. // *********** 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.

Thank you Amjad, worked like a charm, I didn’t even think about it being applied as a style.