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?
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.