ImportDataTable and formatted datetime fields

[Dennis van Mil]

I am creating a xlsx based on data contained in a DataTable using the following code:

worksheet.ImportDataTable(CType(Me.exportData, DataTable), True, 1, 1, True, -1)

This works fine and the sheet gets created. However in my DataTable I have a column that displays a time value. This column is of the datetime type in the database and I retrieve the value from the database by using the following SQL code:

CONVERT(char(8), dbo.MyTab.MyDateTime, 108) AS [Time]

When I export this the column shows in Excel with the current date added before it (not even the date that is contained in the field from the database) and the time not visible at all (this is due to how Excel chooses to display the field). Is there a way to prevent the date of today to be added as I am only interested in the time value.

[Sridhar]

Hi Dennis,

Thank you for using Syncfusion products.

For setting the Time format in MS Excel we need to set the Time Custom format. XlsIO also follows the same and please use the following code snippet to set the Number format for Setting the Time value.

Code Snippet[C#]:
sheet.Range["D2:D4"].NumberFormat = "h:mm:ss"; //OR "h:mm:ss AM/PM" to display with AM & PM
sheet.Range["D5:D6"].NumberFormat = "h:mm:ss AM/PM";

We have also attached the sample with this mail for your reference. Please try teh attached sample and let us know if this helps you.

Please let me know if you require any further clarifications.

Thanks,
Sridhar.S



ImportDataTable_4634c5e3.zip