Formatting a Date with TEXT() fails in a multilingual setting

Aspose.Cells Product Family
1

I have an Excel File on a German System which contains the following formula:

=TEXT(Durchführungsdatum; “TT.MM.JJJJ”)

I open this file with Aspose.Cells and execute workbook.CalculateFormula();

The formula works perfectly well on my machine.

However, when I execute the same code with the same excel file on a different machine with different language settings, some parts of the format string are not recognized. I Suppose this is because the format string is language dependent and the format TT works only on a German machine.

My Question: How can I make sure that my excel file works on every machine, no matter what language setting this machine has? My experiments have shown that Excel can handle the different formats, so how can we?

Best Regards
Matthias

2

@Matthias_Hess123,

Well, the formulas should be specified/set using English (US) style then Aspose.Cells would work as expected. See the document for your reference:

3

You simply cannot enter a formula like =IF(C5=0,42,C3/C4) into a german Excel. Excel marks this an invalid. You have to use =WENN(C5=0;42;C3/C4).

furthermore, the formula tokens are translated perfectly well when moving between differen machines with different language settings. Only the Format string causes problems.

4

@Matthias_Hess123,

Thanks for providing further details.

I think you may try to specify locale/regional settings when loading the workbook or after loading the file for your different locales on different machines accordingly, see the sample code segment for your reference:
e.g
Sample code:

LoadOptions opts = new LoadOptions(LoadFormat.Xlsx);

            opts.Region = CountryCode.Russia;

            Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook(filePath, opts);
            wb.Settings.Region = CountryCode.Russia
            wb.Settings.LanguageCode = CountryCode.Russia;


            wb.CalculateFormula(true); 
//.......
//your code goes here.
//.......

Hope, this helps a bit.

1 Like
5

This solved my problem, thanks a lot!

6

@Matthias_Hess123,

Good to know that your issue is sorted out by the suggested code. Feel free to contact us any time if you need further help or have some other issue or queries, we will be happy to assist you soon.