[QUESTION] Is there any workaround to "Unable to access External Workbook" when exporting ?

[masiar-poistenie]

Prerequisites

• [X ] Have read and understood: https://github.com/Maatwebsite/Laravel-Excel/blob/3.1/.github/SUPPORT.md
• [X ] Checked if your Laravel Excel version is still supported: https://docs.laravel-excel.com/3.1/getting-started/support.html#supported-versions
• [ X] Checked that your question isn't already asked before.
• [ X] Filled in the entire issue template

Versions

• PHP version: 7.4.3
• Laravel version: 7.28.1
• Package version: 3.1.22

Description

I want to access external spreadsheet in toArray() method of Export class to receive number to multiply in formula. I´m getting error: Unable to access External Workbook. Source code says: // It's a Reference to an external spreadsheet (not currently supported) Is there any workaround?

Additional Information

I´m trying to create column filled with formula: "=D1*[file.xlsx]list'!D1"

[patrickbrouwers]

I don't think there is. It's functionality of the PhpSpreadsheet package, so it's also not something we can provide. Perhaps try a feature request over there.

[mesiarm]

Does anybody know, what means this part of code?

 if (($ex->getMessage() === 'Unable to access External Workbook') && ($this->calculatedValue !== null)) {
                    return $this->calculatedValue; // Fallback for calculations referencing external files.
                }

[patrickbrouwers]

As the code comments says: "Fallback for calculations referencing external files.". If the calculation fails because the workbook is external, it will use the cached value instead of trying to calculate again.

[mesiarm]

I suppose this is only usable when importing, because external workbook is not available during export. Is is true?

[patrickbrouwers]

Yes

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

[truncgil]

The problem still continues.

[MarkBaker]

And the "problem" will continue.

This is not something that will ever be addressed by PhpSpreadsheet. It isn't even straightforward in MS Excel itself, because Excel requires the external spreadsheet file to be open as well as the spreadsheet containing the formula.

[saeedkarimmi]

I also faced this problem that after checking, I realized that we had a bulk importer from an Excel file, where the cells with the formula used inside and linked to another sheet were directly stored in the database, which was saved when the spreadsheet was exported. It was trying to find that sheet and get in touch.
Finally, by fixing the saved data in my database, this problem was solved

[msslgomez]

Why does this occur? I'm not using formulas or doing anything complicated. It's happening on one of my export files ONLY when the export data is over 420 items (number that I tested could be lower, idk), I also tried to download a file with only 200 items and that one downloaded no issues.

This is the map method so you can see that the export is simple

public function map($row): array
    {
        return [
            $row->client_name ?? $row->sale_point?->name,
            $row->address,
            $row->sale_point?->district?->name,
            $row->sale_point?->district?->canton?->name,
            $row->sale_point?->district?->canton?->province?->name,
            $row->sale_point?->district?->canton?->province?->country?->name,
            $row->format->description,
            $row->format->chain->description,
            $row->format->chain->channel->description,
            $row->distributor?->description,
            $row->seller?->name,
            $row->type?->name,
            Date::dateTimeToExcel($row->created_at)
        ];
    }

Edit: I narrowed it down to $row->address as the problem, if I comment out that line 0 issues. It's just a regular string why it is failing?

Edit 2: I found the problem, I thought it was weird I was getting this error when it seems to be related to formulas which I'm not using, turns out the address field had some that started with = and they were actually excel formulas that got mistakenly added when doing an massive import.