We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy. (Last updated on: November 16, 2018).
Unfortunately, activation email could not send to your email. Please try again.
Syncfusion Feedback

ImportDataTable and formatted datetime fields

Thread ID:





102645 Feb 17,2012 10:08 AM UTC Feb 21,2012 12:48 PM UTC ASP.NET Web Forms (Classic) 1
Tags: XlsIO
Dennis van Mil
Asked On February 17, 2012 10:08 AM UTC

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 [Syncfusion]
Replied On February 21, 2012 12:48 PM UTC

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.




This post will be permanently deleted. Are you sure you want to continue?

Sorry, An error occured while processing your request. Please try again later.

Please sign in to access our forum

This page will automatically be redirected to the sign-in page in 10 seconds.

Warning Icon You are using an outdated version of Internet Explorer that may not display all features of this and other websites. Upgrade to Internet Explorer 8 or newer for a better experience.Close Icon