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.

Sat Nov 13, 2021 5:57 pm

Hi Spire team,
Now you have resolved my previous issue, i am trying to put some calculations in my excel.
But I have a problem since I succeeded to get my csv values, I am trying to use some formula.
In my calculation I have a square formula ( RACINE(Value) in french ), but when I do that I have a =@RACINE(....)
My set up is :
- Windows 10 Family - French
- Excel 2019 - French
- Visual Studio C# coding- NET 5.0 in Console (even in 4.7.5 .NET in Form)

Code: Select all
            CultureInfo cc = Thread.CurrentThread.CurrentCulture;
            Thread.CurrentThread.CurrentCulture = CultureInfo.InvariantCulture;
            //initialize workbook and worksheet
            Workbook wb = new Workbook();
            wb.Version = ExcelVersion.Version2016;
            wb.LoadFromFile("record.csv", ";", 1, 1, ExcelVersion.Version2016, System.Text.Encoding.UTF8);
            Worksheet sheet = wb.Worksheets[0];
            sheet.Range[1, 1, max_row, max_col].ConvertToNumber();
sheet.Range[2,2].Formula = "RACINE(2*0.01*(C2-1)))";

Result is :
Code: Select all
=@RACINE(2*0.01*(C2-1))


Thanks,
Simon

Simon_Emarre
 
Posts: 6
Joined: Tue Nov 09, 2021 4:49 pm

Mon Nov 15, 2021 8:31 am

Hello,

Thanks for your inquiry!

After testing the code you provided under the same environment as yours, I did not reproduce the issue of "@RACINE(....)", but I found that the workbook which produced by our Spire.Xls cannot recognize the formula added, like the following screenshot shows.

error.PNG
error.PNG (4.71 KiB) Viewed 1882 times


I have logged it in our issue tracking system with the ticket SPIREXLS-3536 for further investigation.

For the issue of "@RACINE(....)", could you please provide us with the error image, your input files and the generated file for further investigation? Thanks in advance!

Sincerely,
Marcia
E-iceblue support team
User avatar

Marcia.Zhou
 
Posts: 858
Joined: Wed Nov 04, 2020 2:29 am

Mon Nov 15, 2021 10:10 am

Hello,

I have received your e-mail, thanks for sharing more information!

I tested with the file and project you provided, but still not reproduce the “@” character error. Even I open the result file you generated directly, there is not the “@” character either.

I have attached my result file through e-mail, and I find that the issue of “#NOM?” also exists in your file. Once there is any progress regarding the issue SPIREXLS-3536, we will notify you immediately.

Sincerely,
Marcia
E-iceblue support team
User avatar

Marcia.Zhou
 
Posts: 858
Joined: Wed Nov 04, 2020 2:29 am

Mon Nov 22, 2021 8:49 am

Hello,

Thanks for your patience!

After further investigation of the Microsoft Excel standards, we found that when MS Excel is in French language settings, although we use “Racine” to create the square formula, the MS Excel will translate the French “RACINE” into the English “SQRT” internally firstly, and then use it. In other words, MS Excel actually only translated the display language according to the current language settings, rather than changing the name of the formula. Like the following screenshots shown.
O2inSheet.PNG
O2inSheet.PNG (5.39 KiB) Viewed 1676 times
P2inSheet.PNG
P2inSheet.PNG (4.16 KiB) Viewed 1676 times
O2&P2 in xml.png
O2&P2 in xml.png (4.66 KiB) Viewed 1676 times

When adding the formulas to Excel through our Spire.Xls, we directly write the input formula values into the corresponding XML of the Excel in fact. However, due to the formula names, the parameters, and even the punctuation in different language settings, it is impossible to reach a unified standard for translation, so we do not support direct insertion of French formulas. Hope you can understand.

So, please use “SQRT(2*0.01*(C2-1))” to calculate square directly. If you encounter other issues related to our product in the future, just feel free to contact us.

Sincerely,
Marcia
E-iceblue support team
User avatar

Marcia.Zhou
 
Posts: 858
Joined: Wed Nov 04, 2020 2:29 am

Mon Nov 22, 2021 12:49 pm

Hello Marcia,
It works so that's almost perfect.
Thank you.
But I tried some formula and the IF is not working. I have an exception (C#) : "sprḨ : 'Error in Cell: V1-Invalid formula:"IF(2>1;123;456)".'
What I tried :
Code: Select all
sheet.Range[1, 1].Formula = "IF(2>1;123;456)";

It doesn't work either in French or in English, I tried both.
It does work in an classic excel sheet.
Thanks,
Simon

Simon_Emarre
 
Posts: 6
Joined: Tue Nov 09, 2021 4:49 pm

Tue Nov 23, 2021 1:20 am

Hello Simon,

Glad to hear that the ”SQRT” is working.

For the issue of “IF” formula, please use “,” to split the parameters in it, instead of “;”. Please refer to the following code.

Code: Select all
sheet.Range[1, 1].Formula = "IF(2>1,123,456)";


Sincerely,
Marcia
E-iceblue support team
User avatar

Marcia.Zhou
 
Posts: 858
Joined: Wed Nov 04, 2020 2:29 am

Tue Nov 23, 2021 8:31 am

Hello,
It works perfectly with comma ',' instead of ';'.
In french excel formula we use ';'.
Thanks,
Simon

Simon_Emarre
 
Posts: 6
Joined: Tue Nov 09, 2021 4:49 pm

Tue Nov 23, 2021 9:32 am

Hello Simon,

You are welcome!

Yes, since our Spire.Xls add English formulas directly, you also need to change the symbols in the formulas to those that conform to the English formulas.

If you encounter any issues related to our product in the future, just feel free to contact us.

Have a nice day!

Sincerely,
Marcia
E-iceblue support team
User avatar

Marcia.Zhou
 
Posts: 858
Joined: Wed Nov 04, 2020 2:29 am

Return to Spire.XLS