formula error with worksheet containing German Umlaute (äöü)

[G-Kevin]

This is:

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

What is the expected behavior?

Getting a value from a call in a worksheek with German Umalute works.

What is the current behavior?

Getting a formular error:

PHP Fatal error:  Uncaught PhpOffice\PhpSpreadsheet\Calculation\Exception: Konten!AB16 -> Konten!AC16 -> Übersicht!S57 -> Formula Error: An unexpected error occurred in C:\...\vendor\phpoffice\phpspreadsheet\src\PhpSpreadsheet\Cell\Cell.php:275

What are the steps to reproduce?

<?php

require 'vendor/autoload.php';

$reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx();
$spreadsheet = $reader->load( $excelFilePath );

$worksheet = $spreadsheet->getSheetByName( 'Konten' );
echo $worksheet->getCell( 'AB16')->getCalculatedValue();

Cell AB16 contains the formula
=Übersicht!S57
Renaming the worksheet from Übersicht to Uebersicht works.

Which versions of PhpSpreadsheet and PHP are affected?

php 8.0.8
PhpSpreadsheet 1.18.0

[MarkBaker]

And what is the formula in cell Übersicht!S57?

[G-Kevin]

Just 20, but formatted as 20,00€. No formula in this cell.

[MarkBaker]

The umlaute should not cause any problem; and I've just tested to verify that: once with a spreadsheet created using PHPSpreadsheet itself, and once with a spreadsheet created using MS Excel.

Also, the error that you're getting Konten!AB16 -> Konten!AC16 -> Übersicht!S57 -> Formula Error shows a calculation "chain"; the Formula error occurs when trying to evaluate cell Übersicht!S57, which is referenced in a formula in cell Konten!AC16, which is referenced by the formula in cell Konten!AB16; so (for whatever reason) PHPSpreadsheet's Calculation Engine believes that Übersicht!S57 contains a formula.

The Calculation Engine does have a built in debugger, similar to Excel's . and this gist demonstrates how to use it. You might try loading your file and using the debug output from that to help diagnose the problem.

Without more information, such as having a sample spreadsheet that demonstrates this problem, I'm unable to determine what the cause could be; but it certainly isn't that PHPspreadsheet can't handle an umlaute .

[G-Kevin]

Thank you very much for your detailed analysis! :)

Indeed the debugging tool could find an error, I'll post it here.

I have used the script:

<?php

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Calculation\Calculation;

error_reporting(E_ALL);
set_time_limit(0);

date_default_timezone_set('UTC');

// Adjust the path as required to reference the PHPSpreadsheet Bootstrap file
// require_once __DIR__ . '/../src/Bootstrap.php';
require 'vendor/autoload.php';
require '_config.php';

$reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx();
$spreadsheet = $reader->load( $excelFilePath );

$worksheet = $spreadsheet->getSheetByName( 'Konten' );
// Initialise the calculation engine for debug logging
$spreadSheet = new Spreadsheet();
$calculationEngine = Calculation::getInstance($spreadSheet);
$debugLog = $calculationEngine->getDebugLog();

$calculationEngine->flushInstance();
$debugLog->setWriteDebugLog(true);


// The cell address in the worksheet that contains the formula
$cell = 'AC5';

$formulaValue = $worksheet->getCell($cell)->getValue();
echo PHP_EOL, 'Formula value for evaluation is ', $formulaValue, PHP_EOL;

$canExecuteCalculation = false;
try {
    $tokens = $calculationEngine->parseFormula($formulaValue, $worksheet->getCell($cell));
    echo 'Parser Stack :-', PHP_EOL;
    print_r($tokens);
    $canExecuteCalculation = true;
} catch (Exception $e) {
    echo "PARSER ERROR: ", $e->getMessage(), PHP_EOL;

    echo 'Parser Stack :-';
    print_r($tokens);
}

if ($canExecuteCalculation) {
    //  calculate
    try {
        $cellValue = $worksheet->getCell($cell)->getCalculatedValue();

		echo PHP_EOL, 'Result is ', $cellValue, PHP_EOL;

        echo 'Evaluation Log:', PHP_EOL;
        print_r($debugLog->getLog());
    } catch (Exception $e) {
        echo "CALCULATION ENGINE ERROR: ", $e->getMessage(), PHP_EOL;

        echo 'Evaluation Log:', PHP_EOL;
        print_r($debugLog->getLog());
    }
}

I have used AB5 instead of AB16 - all the ABs look similar. Please don't be confused.

Formula value for evaluation is =AC5+AD5
Parser Stack :-
Array
(
    [0] => Array
        (
            [type] => Cell Reference
            [value] => AC5
            [reference] => AC5
        )

    [1] => Array
        (
            [type] => Cell Reference
            [value] => AD5
            [reference] => AD5
        )

    [2] => Array
        (
            [type] => Binary Operator
            [value] => +
            [reference] => 
        )

)
CALCULATION ENGINE ERROR: Konten!AB5 -> Konten!AC5 -> Übersicht!S389 -> Formula Error: An unexpected error occurred
Evaluation Log:
Array
(
)

The calculated fomula seems legit to me, but to my mind, an operator is missing between array element 0 and 1?

Debug for AC5:

Formula value for evaluation is =F52+F53+F54+F112+F139+Übersicht!S389+F181+Übersicht!S577+F204+F267
Parser Stack :-
Array
(
    [0] => Array
        (
            [type] => Cell Reference
            [value] => F52
            [reference] => F52
        )

    [1] => Array
        (
            [type] => Cell Reference
            [value] => F53
            [reference] => F53
        )

    [2] => Array
        (
            [type] => Binary Operator
            [value] => +
            [reference] => 
        )

    [3] => Array
        (
            [type] => Cell Reference
            [value] => F54
            [reference] => F54
        )

    [4] => Array
        (
            [type] => Binary Operator
            [value] => +
            [reference] => 
        )

    [5] => Array
        (
            [type] => Cell Reference
            [value] => F112
            [reference] => F112
        )

    [6] => Array
        (
            [type] => Binary Operator
            [value] => +
            [reference] => 
        )

    [7] => Array
        (
            [type] => Cell Reference
            [value] => F139
            [reference] => F139
        )

    [8] => Array
        (
            [type] => Binary Operator
            [value] => +
            [reference] => 
        )

    [9] => Array
        (
            [type] => Cell Reference
            [value] => Übersicht!S389
            [reference] => Übersicht!S389
        )

    [10] => Array
        (
            [type] => Binary Operator
            [value] => +
            [reference] => 
        )

    [11] => Array
        (
            [type] => Cell Reference
            [value] => F181
            [reference] => F181
        )

    [12] => Array
        (
            [type] => Binary Operator
            [value] => +
            [reference] => 
        )

    [13] => Array
        (
            [type] => Cell Reference
            [value] => Übersicht!S577
            [reference] => Übersicht!S577
        )

    [14] => Array
        (
            [type] => Binary Operator
            [value] => +
            [reference] => 
        )

    [15] => Array
        (
            [type] => Cell Reference
            [value] => F204
            [reference] => F204
        )

    [16] => Array
        (
            [type] => Binary Operator
            [value] => +
            [reference] => 
        )

    [17] => Array
        (
            [type] => Cell Reference
            [value] => F267
            [reference] => F267
        )

    [18] => Array
        (
            [type] => Binary Operator
            [value] => +
            [reference] => 
        )

)
CALCULATION ENGINE ERROR: Konten!AC5 -> Übersicht!S389 -> Formula Error: An unexpected error occurred
Evaluation Log:
Array
(
)

Debug for Übersicht!S389:

Formula value for evaluation is =IF(Wettdaten[[#This Row],[Status]]="","",
IF(OR(Wettdaten[[#This Row],[Status]]="Offen",Wettdaten[[#This Row],[Status]]="ToDo"),0,
Wettdaten[[#This Row],[Zurück]]-Wettdaten[[#This Row],[Einsatz]]))
PARSER ERROR: Formula Error: An unexpected error occurred
Parser Stack :-PHP Notice:  Undefined variable: tokens in /.../debug.php on line 45

Indeed there is a formular here 🤔
Excel is calculating it correctly, I can see the value in the file. Is this an error with the formula or with PHPSpreadsheet?

Is this enough information?

[MarkBaker]

There's no missing operator between the first/second elements of the stack array, this is reverse polish notation.

The problem lies in the formula =IF(Wettdaten[[#This Row],[Status]]="","", IF(OR(Wettdaten[[#This Row],[Status]]="Offen",Wettdaten[[#This Row],[Status]]="ToDo"),0, Wettdaten[[#This Row],[Zurück]]-Wettdaten[[#This Row],[Einsatz]])) which is using newer features of Excel calculation that haven't yet been implemented in PHPSpreadsheet's calculation engine. The use of expressions like [[#This Row],[Status]] suggests that you are using Excel's new Data Types that were introduced for Office365
Certainly it isn't yet supported by the calculation engine, so will throw an exception

[G-Kevin]

Alright, thank you very much for your support and analysis! :)

Are there any plans to implement the new data types yet?

[MarkBaker]

It won't be soon, because it's a fairly major change... the equivalent of switching from a language that had only ever used scalar datatypes before to using objects with properties. I've had a brief look at it, but it isn't at the top of my "todo" list, which includes the new array functions, new operators like implicit row intersection and spillage, and new functions like LAMBDA() and LET()

[G-Kevin]

Alright, thank you very much!! :)

There is one last question before this issue can be closed: I would like to support you but cannot find any donation information. Could you please provide some info how to support you? :)