Free Support Forum - aspose.com

Date formatting for column

Hello,

I am using below code for dateformatting for column 20 after the importdatatable method, database has datetime value.

Dim style As Aspose.Cells.Style

Dim flag As StyleFlag

flag = New StyleFlag()

style = wb.Styles(wb.Styles.Add())

style.Custom = "mm/dd/yyyy"

flag.NumberFormat = True

ws.Cells.ApplyColumnStyle(20, style, flag)

But it doesn't format, originally the column value appears as a number like 45678, if I manualy format this changes to date, need to get this formatted from the code, please advise.

Hi,

Well, I don’t find any problem. Following is my sample code that works fine. Please make sure that the values of those datetime fields should be in either datetime or numeric type (should not be string type) or otherwise you will have to convert them to numeric first to apply the datetime formatting.

Sample code:

1)

'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\output.xls”)


2)

'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)
//You can use this overloaded method to convert to numeric value and apply formatting
//formatting at the same time, see the last two parameter.
ws.Cells.ImportDataTable(dataTable, True, 0, 0, 2, 1, True, “mm/dd/yyyy”, True)


ws.AutoFitColumn(0)

wb.Save(“f:\test\output.xls”)



If you still find any issue, kindly simply import the data table to a worksheet and save the xls file using Aspose.Cells for .NET API to post it here, we will conduct a test to check your issue soon.


Thank you.