Spire.XLS is a professional Excel API that enables developers to create, manage, manipulate, convert and print Excel worksheets. Get free and professional technical support for Spire.XLS for .NET, Java, Android, C++, Python.

Tue Nov 07, 2017 6:26 am

We updated to spire.office_2.16.18 this morning and found that the DateTime format setting in Excel file doesn't work.
e.g. in Excel template file we've set range(3,17,3,37) format as "dd-MMM-yyyy" to display date value.
but after file downloaded, it displays all date value in "yyyy-MM-dd hh:mm:ss"

I've tried setting format in the code as well, it still doesn't work. Any idea about this issue?? Thanks!

Code: Select all
        ws.InsertDataTable(dt, False, 3, 1)
        ws.Range(3, 17, 3, 37).NumberFormat = "dd-MMM-yyyy"

echo.xiao
 
Posts: 1
Joined: Wed Nov 23, 2016 7:20 am

Tue Nov 07, 2017 8:49 am

Hello echo.xiao,

Thanks for your inquiry.
After an initial test with spire.office_2.16.18, sorry I didn't reproduce the issue you mentioned. To help us with a better investigation, could you please send your sample file as well as the Region and Language setting of your computer to us via email(support@e-iceblue.com)?

Sincerely,
Jane
E-iceblue support team
User avatar

Jane.Bai
 
Posts: 1156
Joined: Tue Nov 29, 2016 1:47 am

Wed Nov 08, 2017 3:35 am

Hello Echo,

Thanks for sharing the details via email.
After our dev team's further and thorough investigation, we found the result is correct and in fact should be that. The previous version got the wrong approach and we corrected it in the hotfix 2.16.18. When importing the data from the data table, all the format would be maintained, and since the date number format is invalid for the data of string type, you got the result. You could prove it in Excel, eg, insert a string "2017-11-12"(quotation marks needed) in a cell and format the cell as the custom "yyyy-MM-dd", you would find that nothing happened. Therefore, you need to ensure that correct format as Date Time in your data table.
Hope I make it clear.

Sincerely,
Jane
E-iceblue support team
User avatar

Jane.Bai
 
Posts: 1156
Joined: Tue Nov 29, 2016 1:47 am

Return to Spire.XLS