Incorrect output of _translateFormulaToEnglish() function

[aleks-samurai]

This is:

- [x] a bug report
- [ ] a feature request
- [ ] **not** a usage question (ask them on https://stackoverflow.com/questions/tagged/phpspreadsheet or https://gitter.im/PHPOffice/PhpSpreadsheet)

Incorrect output of _translateFormulaToEnglish() if function name in Russian ends with "И" and translation of this function is below line 214 in src/PhpSpreadsheet/Calculation/locale/ru/functions.

What is the expected behavior?

=ЕСЛИ(1;1;1) -> =IF(1;1;1)
=ИСКЛИЛИ(1;1) -> =XOR(1;1)

What is the current behavior?

=ЕСЛИ(1;1;1) -> =ЕСЛAND(1;1;1)
=ИСКЛИЛИ(1;1) -> =ИСКЛИЛAND(1;1)

What are the steps to reproduce?

Please provide a Minimal, Complete, and Verifiable example of code that exhibits the issue without relying on an external Excel file or a web server:

<?php

require __DIR__ . '/vendor/autoload.php';

// Create new Spreadsheet object
$spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();

echo Calculation::getInstance()->_translateFormulaToEnglish("=ЕСЛИ(1;1;1)");

### Which versions of PhpSpreadsheet and PHP are affected?
// php 7.4, phpoffice/phpspreadsheet 1.21

[oleibman]

This is an interesting problem. The translations use regular expressions to find the function name preceded by a non-word character. This works for Latin alphabets, but not so well for Cyrillic. I think that, instead of \w, the regexp could use \p{L} to match a letter in any script (and add 0-9_ to pick up the rest of the word characters); I believe a u (Unicode) modifier is also needed, but the i (case insensitive) modifier would no longer be needed. I need to do more testing to validate all of this.

[aleks-samurai]

@oleibman Thanks for the reply. I hope this bug will be fixed in near future. Thanks for your work.

[MarkBaker]

I'm afraid the formula language translation code is a bit basic, dating back to when a only very few languages were available; and the code may not handle languages that have been added more recently. The regexp should probably be greedy and check for a word boundary, and should (as @oleibman says) use the /u modifier.

It might also be useful if you could check the contents of the ru language set in /src/PhpSpreadsheet/Calculation/locale/Translations.xlsx which is used to generate the locale settings for each language. I updated this last year, but it's never easy extracting the function names from MS Excel by hand to build that spreadsheet (trying to double check everything in the documentation in each language); and as I don't understand Russian, it's difficult to know if I've made any mistakes or not. It doesn't help that some languages have functions missing, or additional functions that aren't available in other locales.

[MarkBaker]

It would probably also be neater and less risky if it was modified to use a preg_replace_callback() as well. I'll take a look at it tonight. I've already added tests using your examples

[MarkBaker]

On the good news side... the fact that you've identified this issue justifies the effort in providing a mechanism to handle formula translations, and in maintaining the translations language locale data

[aleks-samurai]

On the good news side... the fact that you've identified this issue justifies the effort in providing a mechanism to handle formula translations, and in maintaining the translations language locale data

I will provide you updated /src/PhpSpreadsheet/Calculation/locale/Translations.xlsx file for the ru locale ASAP, because I see the functions provided in Excel and Microsoft 365 supported by your package, but not translated to ru.

[MarkBaker]

I will provide you updated /src/PhpSpreadsheet/Calculation/locale/Translations.xlsx file for the ru locale ASAP, because I see the functions provided in Excel supported by your package, but not translated to ru.

Thanks, much appreciated; every new release of Excel seems to add new functions, or new aliases for existing functions

[aleks-samurai]

I will provide you updated /src/PhpSpreadsheet/Calculation/locale/Translations.xlsx file for the ru locale ASAP, because I see the functions provided in Excel supported by your package, but not translated to ru.

Thanks, much appreciated; every new release of Excel seems to add new functions, or new aliases for existing functions
Translations.xlsx
Updated file. Cells with orange-yellow background - additions, cells with red background (only one) - changes. I also checked the function naming in Russian. Everything is ok, no mistakes.

[aleks-samurai]

@MarkBaker @oleibman Any news?

[MarkBaker]

The code change is now in the master branch; and your additional function translations will be built from the spreadsheet on the next release (thank you for that)

[aleks-samurai]

Изменение кода теперь находится в masterветке; и ваши дополнительные переводы функций будут построены из электронной таблицы в следующем выпуске (спасибо за это)

Thank you guys!

[oleibman]

Issue seems resolved; closing ticket.