How can I solve this PhpSpreadsheet Error, when exporting array as .xlsx file? (problem with setting a formula)

Question

I was trying to use this code to export an Excel file:

$spreadsheet = new Spreadsheet();
$xlsx = $spreadsheet->getActiveSheet();
$xlsx->fromArray($myArray, null, 'A1');
$writer = new Xlsx($spreadsheet);

Unfortunately, this error has occurred:

PhpOffice\PhpSpreadsheet\Calculation\Exception
Worksheet!K3 -> Trying to access array offset on value of type null

In the K3 column I wanted to store a formula, "=G3+(H3+I3)*1,5+J3*2", and probably that's the problem.

I was looking for solution and I found similar problem here. I found very similar code in my DefaultValueBinder.php file and I tried to change it following best scored answer. I changed this:

elseif (is_string($value) && $value[0] === '=' && strlen($value) > 1){
    return DataType::TYPE_FORMULA;
}

into this:

elseif (is_string($value) && strpos($value,'=') === 0 && strlen($value) > 1){
    return DataType::TYPE_FORMULA;
}

But it didn't solve the problem in my case. I removed this part of code for testing and export was successful, but my formula wasn't calculated due the lack of removed part of code (I had to click on each cell with formula in file and then click enter to calculate it).

So I'm probably very close to find a problem, but I am stuck. How can I fix this issue?

Kida · Accepted Answer · 2021-06-24 13:15:28Z

1

I found the problem:

I used a comma in my formula string "=G3+(H3+I3)*1,5+J3*2". After replacing it with a dot like this "=G3+(H3+I3)*1.5+J3*2" everthing works fine.

Weird that the error in my case wasn't called Formula Error or something like that. When I was testing my formula before (I put there some incorrect values like =A3+fsfkldk), export was successful, only cells with formula were showed as #######. So I'm suprised that the comma was a problem, especcialy that in Excel floats are showed with a comma, not a dot.

answered Jun 24, 2021 at 13:15

Kida

8502 gold badges10 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How can I solve this PhpSpreadsheet Error, when exporting array as .xlsx file? (problem with setting a formula)

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related