Set culture information for workbook formula using Aspose.Cells for .NET in C#

Aspose.Cells Product Family
1

Is there a way to set culture information by which formula operations will behave in Aspose Cells?

I have non english culture where decimal dot is ',' instead of '.'

This creates problems because when setting cell formula using local culture math operations are executed on english culture and result is different.

2

Hi,

Well, I am afraid, you need to make sure that the formulas should be specified/set in English (US) style only. And, if you use non-English attributes while setting the formulas, it might creates problems/issues.

See the document:
https://docs.aspose.com/display/cellsnet/Setting+Formulas±+Notice+for+Non+English+Users


Thank you.

3

:(

That's too bad.

This means that I cannot have reliable cell template which has fields like

field1: "= [number1] + [number2]"

field2: "[number2]"

and replace those [number1] and [number2] with native culture formats.


This works in Office automation. I hoped you can do at least as good as MS :)

Can you at least look at the code it if it can be done? I don't need culture formulas, just culture math.

From developers perspective, it looks doable and not too hard.

4

Hi,

We will get back to you soon.

Thank you.

5

Hi,

field1: "= [number1] + [number2]"

field2: "[number2]"

and replace those [number1] and [number2] with native culture formats.

We could not understand your cell fields. Could you explain us more and post us a sample template file for your prior need. We can check it soon.


Thank you.
6

I attached one simple example:

If you open this excel on us culture it will display

B12: 3.1

B13: 1.1

If you open it in culture where ',' is a dot sign it will display

B12: 3,1

B13: 1,1


7

Hi,

Thanks for providing us the template file.

We will check it soon.

Thank you.

8

Hi,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

Thank you for considering Aspose.

We only support the formulas specified/set in English (US) style. And the stored number value in the xls file is Invariant Culture; MS Excel will display the different result according to the language setting of your machine.

Please try the following codes, you will get the formula in the cell A1 is "=1,1+2" in MS Excel if the language setting is <?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-com:office:smarttags" />France.

Workbook workbook = new Workbook();

Cells cells = workbook.Worksheets[0].Cells;

cells["A1"].Formula = "=1.1+2";

workbook.Save(@"F:\FileTemp\dest.xls");

Thank You & Best Regards,

9

I can't do that.

I mean, I could, but than the other cell would also have english number format.

If I do as you propose first formula will look like = 1.1 + 2 which will result in correct 3,1

but, second cell which is not formula will set 1.1 as string which will result in incorrect 1.1 (instead of 1,1).


Oh, well. Too bad.

I guess I'll have to stick to Office API.

Thanks,

Rikard

10

Hi Rikard,

When setting formulas, we require our users to use English number in formula string, no matter the culture information. For example:

cell.Formula = "=1.1 + 2";

not

cell.Formula = "=1,1 + 2";

Do you mean when you use Workbook.CalculateFormula to retrieve the result, the result is not correct? Or the generated file doesn't display well?

We tried but cannot figure out what's the really problem. Can you create a simple project to demonstrate your problem? We will do our best to serve our user's need.

11

I know that you require English numbers in formula string.

That was my first question: "Is there a way to set culture information by which formula operations will behave in Aspose Cells?"

I'm trying to port our software from Office automation to Aspose and this is the last issue I have.

MS Office API calculates the formulas based on current culture, so I was asking if I can do the same in Aspose.

I'll try to write a simple application which demonstrates the problem. I will attach it here in next few days.

Thanks for your patience :)