Object of class PhpOffice\PhpSpreadsheet\Calculation\Engine\Operands\StructuredReference could not be converted to string when accessing a cell object's getCalculatedValue()

[SieGeL2k16]

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)

What is the expected behavior?

To get the calculated value from the cell

What is the current behavior?

An exception is thrown:
Object of class PhpOffice\PhpSpreadsheet\Calculation\Engine\Operands\StructuredReference could not be converted to string

What are the steps to reproduce?

We have a formulae on an excel field:

=WENN([@BAUTEIL]="EK";"Entrauchungsklappe";(WENN([@BAUTEIL]="VE";"Entrauchungsventilator";(WENN([@BAUTEIL]="JK";"Jalousieklappe";(WENN([@BAUTEIL]="LK";"Lichtkuppel";"ok")))))))

When trying to access this cell and read the calculated value, the exception described above is thrown. However when dumping the cell value all is fine:

PhpOffice\PhpSpreadsheet\Cell\Cell {#4152 ▼
  -value: "=IF(Table_RLT_Schema_MRA05[[#This Row],[BAUTEIL]]="EK","Entrauchungsklappe",(IF(Table_RLT_Schema_MRA05[[#This Row],[BAUTEIL]]="VE","Entrauchungsventilator",(IF(Table_RLT_Schema_MRA05[[#This Row],[BAUTEIL]]="JK","Jalousieklappe",(IF(Table_RLT_Schema_MRA05[[#This Row],[BAUTEIL]]="LK","Lichtkuppel","ok"))))))) ◀"
  -calculatedValue: "Entrauchungsklappe"
  -dataType: "f"
  -parent: PhpOffice\PhpSpreadsheet\Collection\Cells {#3756 ▶}
  -xfIndex: 0
  -formulaAttributes: null
  -ignoredErrors: PhpOffice\PhpSpreadsheet\Cell\IgnoredErrors {#4144 ▼
    -numberStoredAsText: false
    -formula: false
    -twoDigitTextYear: false
    -evalError: false
  }
}

The "calculatedValue" property holds the correct value, but as soon as I'm trying to read that property with getCalculatedValue() the exception is thrown.

What features do you think are causing the issue

• Reader
• Writer
• Styles
• Data Validations
• Formula Calculations
• Charts
• AutoFilter
• Form Elements

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

Cannot say, I've tested with xlsx Excel format (latest version)

Which versions of PhpSpreadsheet and PHP are affected?

phpSpreadsheet 1.29.0
PHP 8.1.20

[kevin-verschaeve]

hey @oleibman as mentionned in our discussion here, I'm currently facing the same issue.

I've made a simple reproducer:

<?php

require_once('./vendor/autoload.php');

use PhpOffice\PhpSpreadsheet\IOFactory;

$file = IOFactory::load('token_converted_to_string_error.xlsx');
$data = $file->getActiveSheet()->toArray();

var_dump($data);

using the file token_converted_to_string_error.xlsx

Issue is on Calculation.php where a Stack is being fulfilled, with an object instead of a scalar value here.
I am able to reproduce it when using a StructuredReference inside a formula that will have some "pendingStoreKey" (using "IF" (in my case), or "WHEN" I guess) like in the file above.

As I mentionned also, adding this to the class StructuredReference, fix the issue:

public function __toString()
{
   return $this->value;
}

I think it is pretty much ok to fix it like this as the $stack variable looks like needs the value, but if you can confirm, it would be great :)

Thanks again

[bsdrago]

Any news about this issue? Im having the same problem, same version, same function :/

[oleibman]

If you can test against master, you should see that the issue is resolved. The last release was 1.29 on June 14, and the fix was merged in August. I am not sure when the next release is scheduled.

[bsdrago]

Thanks for your answer @oleibman.

I'm not the "most familiar" guy with Github. I tried to apply "by force" the given solution, without success. The current version still has the problem and despite everything I can do with PhpSpreadsheet, obviously the "important" part is stuck in another spreadsheet due to this error =)

Download directly from here, instead of composer, will this fix already be applied?

Thank you again

[oleibman]

To retrieve the master version, use the following command:

php composer.phar require phpoffice/phpspreadsheet:dev-master

The fix will be applied in that version.