Failed to calculate formula field in Excel file

[StepanKutyrev]

What is the expected behavior?

I am trying to take information from Excel file. It works well with all field types except formulas. For example, I am trying to receive a column with the formula (A2+10%)/2 (field A2 = 48,34%) but instead of the calculated formula I am getting "#VALUE!"

What are the steps to reproduce?

        Settings::setLocale('fr_FR');
        $baseName = 'test';
        $tempAbsolutePath =  $this->parameterBag->get('kernel.project_dir') . '/public/uploads/files/temp/';
        $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($tempAbsolutePath . $baseName);
        
        foreach ($spreadsheet->getActiveSheet()->getRowIterator() as $key => $row) {
            $rowData = [];
            foreach ($row->getCellIterator() as $cell) {
                if ($cell->getDataType() === \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_FORMULA) {
                    $rowData[] = $cell->getCalculatedValue();;
                } else {
                    $rowData[] = trim($cell->getFormattedValue());
                }
            }
        }

And you need excel file with at least two rows
First row |=(10%+A2)/2|
Second row |48,34%|

What features do you think are causing the issue

Formula Calculations

Does an issue affect all spreadsheet file formats? If not, which formats are affected?

Faced only with Excel file

Which versions of PhpSpreadsheet and PHP are affected?

"php": "^7.4",
"phpoffice/phpspreadsheet": "dev-master"

[oleibman]

I suspect this has to do with your use of comma as the decimal separator in cell A2. Because of your different locale, I would need to see your spreadsheet (it only needs to contain the two cells in question), but I'm guessing that value is stored as a string rather than a numeric value.

[StepanKutyrev]

testxlsx.xlsx
COLUMN L @oleibman

[oleibman]

As suspected, cell L2 (and the rest of the cells with percentages) is stored as a string rather than a number. Out of curiosity, how were the cells entered into the spreadsheet in the first place? Regardless, when I open your spreadsheet using my normal en_US locale, Excel shows cell L1 as #VALUE, the same as PhpSpreadsheet. Granted, when I change my locale to fr_FR, Excel shows L1 as a calculated value. I notice that you have set a locale in your code. I am not sure how well that setting is, or can be, integrated into the Calculation engine.

[oleibman]

However, I don't think that's the best solution. Try this after your Settings::setLocale:

StringHelper::setThousandsSeparator('.');
StringHelper::setDecimalSeparator(',');

I am a bit surprised that doesn't happen automatically with Settings::setLocale. I will investigate further, but this seems like an easy enough workaround for now.

[StepanKutyrev]

Thanks