[BUG] Error When Evaluating Formula Referencing Other Sheet

[archy-bold]

Prerequisites

• Checked if your Laravel Excel version is still supported: https://docs.laravel-excel.com/3.1/getting-started/support.html#supported-versions
• Able to reproduce the behaviour outside of your code, the problem is isolated to Laravel Excel.
• Checked that your issue isn't already filed.
• Checked if no PR was submitted that fixes this problem.
• Filled in the entire issue template

Versions

• PHP version: 7.1.28
• Laravel version: 5.6.39
• Package version: 3.1.15

Description

When importing a spreadsheet and including the trait WithCalculatedFormulas, I get the following error when a cell with the formula =metadata!B10 (ie referencing another worksheet) is evaluated:

Call to a member function has() on null

The full stack trace is below.

From what I can tell, the formula can't be evaluated because the other sheet has been disconnected before the cell gets evaluated. I confirmed this suspicion by commenting out the code within Sheet->disconnect(), it worked after that.

I presume the issue is that the importer is executed from another with the trait WithMultipleSheets and when moving onto the second sheet, it disconnects the first for performance reasons.

Steps to Reproduce

Attached is an offending sheet, with the cell tracks!G2 containing a formula referencing the first sheet.

bundle-metadata-reference.xlsx

Expected behavior:

I expect the sheet to import.

Actual behavior:

Get an error.

Additional Information

Stack trace:

#0 \/var\/www\/vendor\/phpoffice\/phpspreadsheet\/src\/PhpSpreadsheet\/Calculation\/Calculation.php(3870): PhpOffice\\PhpSpreadsheet\\Worksheet\\Worksheet->cellExists('B10')
#1 \/var\/www\/vendor\/phpoffice\/phpspreadsheet\/src\/PhpSpreadsheet\/Calculation\/Calculation.php(2944): PhpOffice\\PhpSpreadsheet\\Calculation\\Calculation->processTokenStack(Array, 'G2', Object(PhpOffice\\PhpSpreadsheet\\Cell\\Cell))
#2 \/var\/www\/vendor\/phpoffice\/phpspreadsheet\/src\/PhpSpreadsheet\/Calculation\/Calculation.php(2734): PhpOffice\\PhpSpreadsheet\\Calculation\\Calculation->_calculateFormulaValue('metadata!B10', 'G2', Object(PhpOffice\\PhpSpreadsheet\\Cell\\Cell))
#3 \/var\/www\/vendor\/phpoffice\/phpspreadsheet\/src\/PhpSpreadsheet\/Cell\/Cell.php(262): PhpOffice\\PhpSpreadsheet\\Calculation\\Calculation->calculateCellValue(Object(PhpOffice\\PhpSpreadsheet\\Cell\\Cell), true)
#4 \/var\/www\/vendor\/maatwebsite\/excel\/src\/Cell.php(61): PhpOffice\\PhpSpreadsheet\\Cell\\Cell->getCalculatedValue()
#5 \/var\/www\/vendor\/maatwebsite\/excel\/src\/Row.php(65): Maatwebsite\\Excel\\Cell->getValue(NULL, true, false)
#6 \/var\/www\/vendor\/maatwebsite\/excel\/src\/Sheet.php(283): Maatwebsite\\Excel\\Row->toArray(NULL, true, false)
#7 \/var\/www\/vendor\/maatwebsite\/excel\/src\/Sheet.php(245): Maatwebsite\\Excel\\Sheet->toArray(Object(Wata\\Upload\\Imports\\AlbumTracksImport), 2, NULL, true)
#8 \/var\/www\/vendor\/maatwebsite\/excel\/src\/Reader.php(111): Maatwebsite\\Excel\\Sheet->import(Object(Wata\\Upload\\Imports\\AlbumTracksImport), 2)
#9 \/var\/www\/vendor\/laravel\/framework\/src\/Illuminate\/Database\/Concerns\/ManagesTransactions.php(29): Maatwebsite\\Excel\\Reader->Maatwebsite\\Excel\\{closure}(Object(Illuminate\\Database\\MySqlConnection))
#10 \/var\/www\/vendor\/maatwebsite\/excel\/src\/Transactions\/DbTransactionHandler.php(30): Illuminate\\Database\\Connection->transaction(Object(Closure))
#11 \/var\/www\/vendor\/maatwebsite\/excel\/src\/Reader.php(115): Maatwebsite\\Excel\\Transactions\\DbTransactionHandler->__invoke(Object(Closure))
#12 \/var\/www\/vendor\/maatwebsite\/excel\/src\/Excel.php(146): Maatwebsite\\Excel\\Reader->read(Object(Wata\\Upload\\Imports\\AlbumBundleImport), '\/tmp\/reqfile_Jj...', 'Xlsx', NULL)
#13 \/var\/www\/vendor\/maatwebsite\/excel\/src\/Concerns\/Importable.php(37): Maatwebsite\\Excel\\Excel->import(Object(Wata\\Upload\\Imports\\AlbumBundleImport), '\/tmp\/reqfile_Jj...', NULL, 'Xlsx')
#14 \/var\/www\/app\/Wata\/Upload\/BundleService.php(60): Wata\\Upload\\Imports\\AlbumBundleImport->import('\/tmp\/reqfile_Jj...', NULL, 'Xlsx')
#15 \/var\/www\/app\/Http\/Controllers\/Upload\/BundleController.php(70): Wata\\Upload\\BundleService->buildBundleFromExcelFileRequest(Object(Wata\\Upload\\Requests\\ExcelFileRequest), 's3')
#16 [internal function]: App\\Http\\Controllers\\Upload\\BundleController->store(Object(Wata\\Upload\\Requests\\ExcelFileRequest))
#17 \/var\/www\/vendor\/laravel\/framework\/src\/Illuminate\/Routing\/Controller.php(54): call_user_func_array(Array, Array)
#18 \/var\/www\/vendor\/laravel\/framework\/src\/Illuminate\/Routing\/ControllerDispatcher.php(45): Illuminate\\Routing\\Controller->callAction('store', Array)
#19 \/var\/www\/vendor\/laravel\/framework\/src\/Illuminate\/Routing\/Route.php(212): Illuminate\\Routing\\ControllerDispatcher->dispatch(Object(Illuminate\\Routing\\Route), Object(App\\Http\\Controllers\\Upload\\BundleController), 'store')
#20 \/var\/www\/vendor\/laravel\/framework\/src\/Illuminate\/Routing\/Route.php(169): Illuminate\\Routing\\Route->runController()
#21 \/var\/www\/vendor\/laravel\/framework\/src\/Illuminate\/Routing\/Router.php(665): Illuminate\\Routing\\Route->run()
#22 \/var\/www\/vendor\/laravel\/framework\/src\/Illuminate\/Routing\/Pipeline.php(30): Illuminate\\Routing\\Router->Illuminate\\Routing\\{closure}(Object(Illuminate\\Http\\Request))
#23 \/var\/www\/vendor\/laravel\/framework\/src\/Illuminate\/Routing\/Middleware\/ThrottleRequests.php(57): Illuminate\\Routing\\Pipeline->Illuminate\\Routing\\{closure}(Object(Illuminate\\Http\\Request))
#24 \/var\/www\/vendor\/laravel\/framework\/src\/Illuminate\/Pipeline\/Pipeline.php(151): Illuminate\\Routing\\Middleware\\ThrottleRequests->handle(Object(Illuminate\\Http\\Request), Object(Closure), 1000, '1')
#25 \/var\/www\/vendor\/laravel\/framework\/src\/Illuminate\/Routing\/Pipeline.php(53): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}(Object(Illuminate\\Http\\Request))
#26 \/var\/www\/vendor\/laravel\/framework\/src\/Illuminate\/Routing\/Middleware\/SubstituteBindings.php(41): Illuminate\\Routing\\Pipeline->Illuminate\\Routing\\{closure}(Object(Illuminate\\Http\\Request))
#27 \/var\/www\/vendor\/laravel\/framework\/src\/Illuminate\/Pipeline\/Pipeline.php(151): Illuminate\\Routing\\Middleware\\SubstituteBindings->handle(Object(Illuminate\\Http\\Request), Object(Closure))
#28 \/var\/www\/vendor\/laravel\/framework\/src\/Illuminate\/Routing\/Pipeline.php(53): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}(Object(Illuminate\\Http\\Request))
#29 \/var\/www\/vendor\/laravel\/framework\/src\/Illuminate\/Auth\/Middleware\/Authenticate.php(43): Illuminate\\Routing\\Pipeline->Illuminate\\Routing\\{closure}(Object(Illuminate\\Http\\Request))
#30 \/var\/www\/vendor\/laravel\/framework\/src\/Illuminate\/Pipeline\/Pipeline.php(151): Illuminate\\Auth\\Middleware\\Authenticate->handle(Object(Illuminate\\Http\\Request), Object(Closure), 'api')
#31 \/var\/www\/vendor\/laravel\/framework\/src\/Illuminate\/Routing\/Pipeline.php(53): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}(Object(Illuminate\\Http\\Request))
#32 \/var\/www\/vendor\/laravel\/framework\/src\/Illuminate\/Foundation\/Http\/Middleware\/TransformsRequest.php(31): Illuminate\\Routing\\Pipeline->Illuminate\\Routing\\{closure}(Object(Illuminate\\Http\\Request))
#33 \/var\/www\/vendor\/laravel\/framework\/src\/Illuminate\/Pipeline\/Pipeline.php(151): Illuminate\\Foundation\\Http\\Middleware\\TransformsRequest->handle(Object(Illuminate\\Http\\Request), Object(Closure))
#34 \/var\/www\/vendor\/laravel\/framework\/src\/Illuminate\/Routing\/Pipeline.php(53): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}(Object(Illuminate\\Http\\Request))
#35 \/var\/www\/vendor\/laravel\/framework\/src\/Illuminate\/Foundation\/Http\/Middleware\/TransformsRequest.php(31): Illuminate\\Routing\\Pipeline->Illuminate\\Routing\\{closure}(Object(Illuminate\\Http\\Request))
#36 \/var\/www\/vendor\/laravel\/framework\/src\/Illuminate\/Pipeline\/Pipeline.php(151): Illuminate\\Foundation\\Http\\Middleware\\TransformsRequest->handle(Object(Illuminate\\Http\\Request), Object(Closure))
#37 \/var\/www\/vendor\/laravel\/framework\/src\/Illuminate\/Routing\/Pipeline.php(53): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}(Object(Illuminate\\Http\\Request))
#38 \/var\/www\/app\/Http\/Middleware\/Localisation.php(38): Illuminate\\Routing\\Pipeline->Illuminate\\Routing\\{closure}(Object(Illuminate\\Http\\Request))
#39 \/var\/www\/vendor\/laravel\/framework\/src\/Illuminate\/Pipeline\/Pipeline.php(151): App\\Http\\Middleware\\Localisation->handle(Object(Illuminate\\Http\\Request), Object(Closure))
#40 \/var\/www\/vendor\/laravel\/framework\/src\/Illuminate\/Routing\/Pipeline.php(53): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}(Object(Illuminate\\Http\\Request))
#41 \/var\/www\/app\/Http\/Middleware\/ConvertToApiResponse.php(20): Illuminate\\Routing\\Pipeline->Illuminate\\Routing\\{closure}(Object(Illuminate\\Http\\Request))
#42 \/var\/www\/vendor\/laravel\/framework\/src\/Illuminate\/Pipeline\/Pipeline.php(151): App\\Http\\Middleware\\ConvertToApiResponse->handle(Object(Illuminate\\Http\\Request), Object(Closure))
#43 \/var\/www\/vendor\/laravel\/framework\/src\/Illuminate\/Routing\/Pipeline.php(53): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}(Object(Illuminate\\Http\\Request))
#44 \/var\/www\/vendor\/laravel\/framework\/src\/Illuminate\/Pipeline\/Pipeline.php(104): Illuminate\\Routing\\Pipeline->Illuminate\\Routing\\{closure}(Object(Illuminate\\Http\\Request))
#45 \/var\/www\/vendor\/laravel\/framework\/src\/Illuminate\/Routing\/Router.php(667): Illuminate\\Pipeline\\Pipeline->then(Object(Closure))
#46 \/var\/www\/vendor\/laravel\/framework\/src\/Illuminate\/Routing\/Router.php(642): Illuminate\\Routing\\Router->runRouteWithinStack(Object(Illuminate\\Routing\\Route), Object(Illuminate\\Http\\Request))
#47 \/var\/www\/vendor\/laravel\/framework\/src\/Illuminate\/Routing\/Router.php(608): Illuminate\\Routing\\Router->runRoute(Object(Illuminate\\Http\\Request), Object(Illuminate\\Routing\\Route))
#48 \/var\/www\/vendor\/laravel\/framework\/src\/Illuminate\/Routing\/Router.php(597): Illuminate\\Routing\\Router->dispatchToRoute(Object(Illuminate\\Http\\Request))
#49 \/var\/www\/vendor\/laravel\/framework\/src\/Illuminate\/Foundation\/Http\/Kernel.php(176): Illuminate\\Routing\\Router->dispatch(Object(Illuminate\\Http\\Request))
#50 \/var\/www\/vendor\/laravel\/framework\/src\/Illuminate\/Routing\/Pipeline.php(30): Illuminate\\Foundation\\Http\\Kernel->Illuminate\\Foundation\\Http\\{closure}(Object(Illuminate\\Http\\Request))
#51 \/var\/www\/vendor\/fideloper\/proxy\/src\/TrustProxies.php(57): Illuminate\\Routing\\Pipeline->Illuminate\\Routing\\{closure}(Object(Illuminate\\Http\\Request))
#52 \/var\/www\/vendor\/laravel\/framework\/src\/Illuminate\/Pipeline\/Pipeline.php(151): Fideloper\\Proxy\\TrustProxies->handle(Object(Illuminate\\Http\\Request), Object(Closure))
#53 \/var\/www\/vendor\/laravel\/framework\/src\/Illuminate\/Routing\/Pipeline.php(53): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}(Object(Illuminate\\Http\\Request))
#54 \/var\/www\/vendor\/barryvdh\/laravel-cors\/src\/HandleCors.php(36): Illuminate\\Routing\\Pipeline->Illuminate\\Routing\\{closure}(Object(Illuminate\\Http\\Request))
#55 \/var\/www\/vendor\/laravel\/framework\/src\/Illuminate\/Pipeline\/Pipeline.php(151): Barryvdh\\Cors\\HandleCors->handle(Object(Illuminate\\Http\\Request), Object(Closure))
#56 \/var\/www\/vendor\/laravel\/framework\/src\/Illuminate\/Routing\/Pipeline.php(53): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}(Object(Illuminate\\Http\\Request))
#57 \/var\/www\/vendor\/laravel\/framework\/src\/Illuminate\/Foundation\/Http\/Middleware\/CheckForMaintenanceMode.php(62): Illuminate\\Routing\\Pipeline->Illuminate\\Routing\\{closure}(Object(Illuminate\\Http\\Request))
#58 \/var\/www\/vendor\/laravel\/framework\/src\/Illuminate\/Pipeline\/Pipeline.php(151): Illuminate\\Foundation\\Http\\Middleware\\CheckForMaintenanceMode->handle(Object(Illuminate\\Http\\Request), Object(Closure))
#59 \/var\/www\/vendor\/laravel\/framework\/src\/Illuminate\/Routing\/Pipeline.php(53): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}(Object(Illuminate\\Http\\Request))
#60 \/var\/www\/vendor\/laravel\/framework\/src\/Illuminate\/Pipeline\/Pipeline.php(104): Illuminate\\Routing\\Pipeline->Illuminate\\Routing\\{closure}(Object(Illuminate\\Http\\Request))
#61 \/var\/www\/vendor\/laravel\/framework\/src\/Illuminate\/Foundation\/Http\/Kernel.php(151): Illuminate\\Pipeline\\Pipeline->then(Object(Closure))
#62 \/var\/www\/vendor\/laravel\/framework\/src\/Illuminate\/Foundation\/Http\/Kernel.php(116): Illuminate\\Foundation\\Http\\Kernel->sendRequestThroughRouter(Object(Illuminate\\Http\\Request))
#63 \/var\/www\/public\/index.php(55): Illuminate\\Foundation\\Http\\Kernel->handle(Object(Illuminate\\Http\\Request))
#64 {main}"]

[patrickbrouwers]

Sounds like a duplicate of #2392. A PR was submitted, will be in upcoming release.

[eprop-aaron]

@patrickbrouwers I'm also getting this exception: Call to a member function has() on null When one sheet is referencing another for a cell formula.

I have required the latest 3.1 branch instead of a release, the PR makes no difference to this particular issue.

[eprop-aaron]

OP is correct that removing the disconnect call solves the problem.

Could this potentially be a concern? DoesNotDisconnect... something along them lines? then a check can be performed to not disconnect that particular sheet. Understandably this could present memory problems for larger spreadsheets.

This would also need changing the ReadChunk job.

[patrickbrouwers]

@plogic-aaron as said before, a fix has been merged and will be in next release.

[eprop-aaron]

@patrickbrouwers thanks, but as said i required 3.1 branch at latest commit to see the fix and still the same issue, so has this been merged in a different branch?

Edit: changing the order of the import so that sheet 2 (that references sheet 1) is processed first solves the problem also, this is clearly because both are still loaded before the first disconnect call.

[patrickbrouwers]

You are probably having another issue then. Perhaps in your case the reference value is not cached in the file. I'm okay with having a setting to determine if we should disconnect. Feel free to PR it

[ulver2812]

Hi @patrickbrouwers, same issue of @eprop-aaron and @archy-bold here, is not related with #2392, I'm on the 3.1.18 release. Did you fix this?

[patrickbrouwers]

Nobody PR'd it yet

[jpagarcia]

I'm sorry, I'm new to all of this but it seems there hasn't been a commit to fix this. So I tried making one, here it is.

https://github.com/jpagarcia/Laravel-Excel

[patrickbrouwers]

@jpagarcia you can open a PR from that commit, I'll review it there.