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.

Thu Oct 05, 2017 3:56 pm

Hello,

We have an issue with DateTime :
We store a DateTime in a cell and set the format "m/d/yyyy".
Spire is in Invariant culture and Excel is in French culture.

When we open Excel workbook, the cell is in Personalize format.
We need the predifined format (in the date tab)

How to give that ?

Code: Select all
Thread.CurrentThread.CurrentCulture = CultureInfo.InvariantCulture;

var workbook = new Workbook();
var worksheet = workbook.Worksheets.First();

worksheet.Range["B2"].Value2 = new DateTime(2017, 01, 30);
worksheet.Range["B2"].NumberFormat = "m/d/yyyy";

workbook.SaveToFile(Chemin + "Sample - Out.xlsx", ExcelVersion.Version2016);


Image 1.png
Image 1.png (12.88 KiB) Viewed 9270 times


Best regards.

Vincent
Last edited by VincentB on Wed Nov 08, 2017 2:20 pm, edited 1 time in total.

VincentB
 
Posts: 120
Joined: Thu Sep 15, 2016 9:44 am

Fri Oct 06, 2017 2:16 am

Hello,

Thanks for your inquiry. Please comment out the following line code.
Code: Select all
worksheet.Range["B2"].NumberFormat = "m/d/yyyy";


Sincerely,
Gary
E-iceblue support team
User avatar

Gary.zhang
 
Posts: 1380
Joined: Thu Apr 04, 2013 1:30 am

Fri Oct 06, 2017 6:58 am

Hello,

Without this line, we have the same problem.

Regards

VincentB
 
Posts: 120
Joined: Thu Sep 15, 2016 9:44 am

Fri Oct 06, 2017 9:22 am

Hello,

Thanks for you response. I have checked it again on my side, when that line has been commented out, the cell format is date format, please see the screenshot and check the document on your side.

Thanks,
Gary
E-iceblue support team
Attachments
Sample - Out2.zip
(4.92 KiB) Downloaded 264 times
118031.png
118031.png (21.45 KiB) Viewed 9256 times
User avatar

Gary.zhang
 
Posts: 1380
Joined: Thu Apr 04, 2013 1:30 am

Mon Oct 09, 2017 10:06 am

Hello,

Greetings from E-iceblue.
Did we resolve your issue?
Thanks in advance for your valuable feedback and time.

Sincerely,
Gary
E-iceblue support team
User avatar

Gary.zhang
 
Posts: 1380
Joined: Thu Apr 04, 2013 1:30 am

Fri Oct 13, 2017 7:08 am

Hello

We reproduce again this issue.

Maybe it's because our Excel is in french ?

Regards

VincentB
 
Posts: 120
Joined: Thu Sep 15, 2016 9:44 am

Fri Oct 13, 2017 7:44 am

Dear VincentB,

After further investigation, you are partly correct. The issue is caused by the culture setting, not just the French.
If you comment out the code below, the format would be correct.
Code: Select all
Thread.CurrentThread.CurrentCulture = CultureInfo.InvariantCulture;

Anyway, I have posted the issue to our Dev team. once there is any progress, we will let you know.

Sincerely,
Betsy
E-iceblue support team
User avatar

Betsy.jiang
 
Posts: 3099
Joined: Tue Sep 06, 2016 8:30 am

Wed Oct 18, 2017 6:35 am

Dear VincentB,

After further investigation, we found the solution. When setting the InvariantCulture, the date format should be equal to the format setting in control panel(please check on attachment). So please set the number format as below, which is date format in French.
Code: Select all
             worksheet.Range["B2"].NumberFormat = "dd/mm/yyyy";

If there is any question, please let us know.

Sincerely,
Betsy
E-iceblue support team
Attachments
FormatSettingInFrench.png
FormatSettingInFrench.png (19.45 KiB) Viewed 9192 times
User avatar

Betsy.jiang
 
Posts: 3099
Joined: Tue Sep 06, 2016 8:30 am

Tue Oct 24, 2017 9:59 am

Hello,

Greetings from E-iceblue.
Did we resolve your issue?
Thanks in advance for your valuable feedback and time.

Sincerely,
Gary
E-iceblue support team
User avatar

Gary.zhang
 
Posts: 1380
Joined: Thu Apr 04, 2013 1:30 am

Thu Oct 26, 2017 1:37 pm

Hello,

We need to define the format in an international style, because our application is used with Excel in English and French.
We also need the line below for other uses

Code: Select all
Thread.CurrentThread.CurrentCulture = CultureInfo.InvariantCulture;


Sincerely,

Vincent

VincentB
 
Posts: 120
Joined: Thu Sep 15, 2016 9:44 am

Fri Oct 27, 2017 3:23 am

Hi Vincent,

Thanks for your feedback.
We will investigate further, then give you update.

Sincerely,
Betsy
E-iceblue support team
User avatar

Betsy.jiang
 
Posts: 3099
Joined: Tue Sep 06, 2016 8:30 am

Tue Nov 07, 2017 6:53 am

Hi Vincent,

Thanks for waiting.
Now the issue has been fixed in Spire.XLS Pack(Hotfix) Version:7.12.109. And you could use the InvariantCulture and needn't to set the number format anymore. The result format will be date in any region.
Looking forward to your feedback.

Sincerely,
Betsy
E-iceblue support team
User avatar

Betsy.jiang
 
Posts: 3099
Joined: Tue Sep 06, 2016 8:30 am

Wed Nov 08, 2017 3:24 pm

Hello

We reproduce again with this code (All lines in this sample are required for us) :


Code: Select all
            Thread.CurrentThread.CurrentCulture = CultureInfo.InvariantCulture;

            var workbook = new Workbook();
            var worksheet = workbook.Worksheets.First();

            worksheet.Range["B2"].Value2 = new DateTime(2017, 01, 30);
            worksheet.Range["B2"].NumberFormat = "m/d/yyyy";

            workbook.SaveToFile(Chemin + "Sample - Out.xlsx", ExcelVersion.Version2016);


I attached our test result.
Value of B2 is correct (in formula editor : 30/01/2017)
NumBerFormat is set to Personalized "m/j/aaaa" (We need the NumberFormat "Date")

Do you need more informations ?

Best regards,

Vincent
Attachments
Sample.zip
(28.99 KiB) Downloaded 269 times

VincentB
 
Posts: 120
Joined: Thu Sep 15, 2016 9:44 am

Thu Nov 09, 2017 2:09 am

Hi VincentB,

Thanks for your feedback.
In the hotfix, if you use InvariantCulture setting, please don't set the NumberFormat again, then the result format will be date in any region.
Code: Select all
            Thread.CurrentThread.CurrentCulture = CultureInfo.InvariantCulture;
            var workbook = new Workbook();
            var worksheet = workbook.Worksheets.First();
            worksheet.Range["B2"].Value2 = new DateTime(2017, 01, 30);
            //comment out this code.
            //worksheet.Range["B2"].NumberFormat = "m/d/yyyy";
            workbook.SaveToFile(Chemin + "Sample - Out.xlsx", ExcelVersion.Version2016);

And if you use both InvariantCulture and NumberFormat setting, the result format will depend on the format setting in control panel and I provided a screenshot before.

Sincerely,
Betsy
E-iceblue suppport team
User avatar

Betsy.jiang
 
Posts: 3099
Joined: Tue Sep 06, 2016 8:30 am

Thu Nov 09, 2017 8:08 am

Hi,

Unfortunately, we need to set the NumberFormat : it's dynamically set in our app (Configured by User independently of the type of value)

After analysis, we can remove the line :
Code: Select all
Thread.CurrentThread.CurrentCulture = CultureInfo.InvariantCulture;



Regards,

Vincent

VincentB
 
Posts: 120
Joined: Thu Sep 15, 2016 9:44 am

Return to Spire.XLS