[BUG]Cell shared formula does not exists when the import is queued

[ghost]

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: php-7.4.10-Win32-vc15-x64
• Laravel version: Laravel Framework 8.5.0
• Package version: 3.1.23

Description

We have an excel xlsx file with a column C1=ROUND(A1 + B1, 2).
From the C1 we drag down, and excel autofills the C2,C3,C4,C5....,C999 cells with the formula.

That means the xml from the xslx zip ( you can extract the xslx by changing the extension to .zip) should look like:
C1 XML : <f t="shared" ref="C1:C999" si="0">ROUND(A1 + B1, 2)</f><v>22.22</v> ... Cn XML : <f t="shared" si="0"/><v>99.99</v> ... C999 XML : <f t="shared" si="0"/><v>11.11</v>

When importing this file over laravel queue chunked, with SheetImport, the getCalculatedValue in Cell.php line 63 comes as empty string.

Steps to Reproduce

Expected behavior:

The cell value to be the calculated formula value.

Actual behavior:

The cell value is empty string.

Additional Information

I found that the cell type C2,C3,..C999 is shared, so it goes in castToFormula() in Xlsx.php:269 when loading and reading the file.

Because the shared formula C1 is not loaded yet in $sharedFormulas ( if condition in Xlsx.php:271), and the $value ( defined in Xlsx.php:264 ) is equal to '=', the return value will have no formula like "=ROUND(An + Bn, 2)" where N = { 2,3,4,5,6,7...,999 }

I think because the file is queued, the initial formula is never loaded for some chunk, which will result in an empty value instead of a computed value when using toCollection, on some batches.

[patrickbrouwers]

There's isn't a lot we can do about that when using chunked imports, because the entire idea of chunking the import is that you only load the sheets that are actually needed.

[ghost]

The data is in the same sheet.

If what you are saying is correct that means laravel-excel cannot correctly parse function cells.

And in this case it should appear in the docs as a note.

[patrickbrouwers]

We just use the PhpSpreadsheet chunk filter, I'm not 100% sure how it works behind the scenes with references to other cells in other chunks. Perhaps if you need this kind of logic, using chunks is not a good fit.

[ghost]

I just found the problem explained better here:

https://github.com/nuovo/spreadsheet-reader/blob/master/SpreadsheetReader_XLSX.php

Notes about library performance

XLSX files use so called "shared strings" internally to optimize for cases where the same string is repeated multiple times. Internally XLSX is an XML text that is parsed sequentially to extract data from it, however, in some cases these shared strings are a problem - sometimes Excel may put all, or nearly all of the strings from the spreadsheet in the shared string file (which is a separate XML text), and not necessarily in the same order. Worst case scenario is when it is in reverse order - for each string we need to parse the shared string XML from the beginning, if we want to avoid keeping the data in memory. To that end, the XLSX parser has a cache for shared strings that is used if the total shared string count is not too high. In case you get out of memory errors, you can try adjusting the SHARED_STRING_CACHE_LIMIT constant in SpreadsheetReader_XLSX to a lower one.

Maybe XSLX Reader can be extended?

[patrickbrouwers]

You are refering to a package that we don't use

[ghost]

i know, maybe you can improve.

[patrickbrouwers]

I don't have this use case myself, when we have formulas we don't use chunk. If this is important for you to work, feel free to PR improvements or consider our commercial support.

[ghost]

It's okay, i can find a workaround.

If you could put a note in Chunk Reading that the formulas do not work, that would be great.

[patrickbrouwers]

The docs are open source, feel free to PR the note.

[ghost]

For others who are having the same problem, and also for you if there is a need to fix laravel-excel:

PHPOffice/PhpSpreadsheet#1669
PHPOffice/PhpSpreadsheet#1680

[stale]

This bug report has been automatically closed because it has not had recent activity. If this is still an active bug, please comment to reopen. Thank you for your contributions.