Laravel Excel - Formula adding extra @ Symbol, Breaking Formula

Question

I'm working on an Export method in Laravel to generate an XLSX file with included formulae. Everything is working, but on Export, the formula is getting an extra @ symbol added in a couple places, which then breaks the formula... The cell value is rendered as #VALUE.

This is the function:

namespace App\Exports\Reports;

use App\Exports\Sheets\Reports\SheetOne;
use App\Exports\Sheets\Reports\SheetTwo;
use App\Exports\Sheets\Reports\SheetThree;
use Maatwebsite\Excel\Concerns\WithMultipleSheets;

class ExcelReport implements WithMultipleSheets {
  public function sheets(): array {
    return [
      new SheetOne($this->sheetOneData),
      new SheetTwo($this->sheetTwoData),
      new SheetThree($this->sheetThreeData)
    ];
  }
}

The issue is on "Sheet 2":

namespace App\Exports\Sheets\Reports;

use App\Models\SheetTwoData;
use Illuminate\Support\Collection;
use Maatwebsite\Excel\Concerns\WithTitle;
use Maatwebsite\Excel\Concerns\WithMapping;
use Maatwebsite\Excel\Concerns\WithHeadings;
use Maatwebsite\Excel\Concerns\FromCollection;

class SheetTwo implements FromCollection, WithHeadings, WithMapping, WithTitle {
  protected Collection $sheetTwoData;
  private int $maxIndex;

  public function __construct(Collection $sheetTwoData) {
    $this->sheetTwoData = $sheetTwoData;
    $this->maxIndex = 2 + $sheetTwoData->count() - 1;
  }

  public function collection(): collection {
    $calcOne = "SheetTwo!D2:D{$this->maxIndex}";
    $calcTwo = "SheetTwo!G2:G{$this->maxIndex}";

    return $this->sheetTwoData->push([
      '',
      'Totals',
      "=SUBTOTAL(109, C2:C{$this->maxIndex})",
      "=SUM(IF({$calcOne}<>\"\",1/COUNTIF({$calcOne},{$calcOne}),0))",
      "=SUM(IF({$calcTwo}<>\"\",1/COUNTIF({$calcTwo},{$calcTwo}),0))",
      ''
    ]);
  }

  public function headings(): array {
    return [
      'Column A',
      'Column B',
      'Column C',
      'Column D',
      'Column E',
      'Column F',
      'Column G'
    ];
  }

  public function map($row): array {
    if ($row instanceof SheetTwoData) {
      return $row->toArray();
    }

    return $row;
  }

  public function title(): string {
    return 'SheetTwo';
  }

This is then hooked up to a POST Form via a Button, which when clicked, streams the generated XLSX as a Download through Chrome (or whatever browser):

if ($request->method() == 'POST') {
  return Excel::download(new ExcelReport([
    'sheetOneData' => SheetOneData::all(),
    'sheetTwoData' => SheetTwoData::all(),
    'sheetThreeData' => SheetThreeData::all()
  ]), 'excel-report.xlsx');
}

This all works just fine, and the file is downloaded, but the $calcOne and $calcTwo strings get adjusted. If I add dd($row); before return $row; (the handler that renders the row with these formulae), I see this:

array:6 [▼
  0 => ""
  1 => "Totals"
  2 => "=SUBTOTAL(109, C2:C50)"
  3 => "=SUM(IF(SheetTwo!D2:D50<>"",1/COUNTIF(SheetTwo!D2:D50,SheetTwo!D2:D50),0))"
  4 => "=SUM(IF(SheetTwo!G2:G50<>"",1/COUNTIF(SheetTwo!G2:G50,SheetTwo!G2:G50),0))"
  5 => ""
]

However, in the XLSX file, these formulae render as:

=SUM(IF(@SheetTwo!D2:D50<>"",1/COUNTIF(SheetTwo!D2:D50,@SheetTwo!D2:D50),0))

($calcTwo, targeting G2:G50 is also broken in the same way).

Has anyone seen this issue before? Google is being less than helpful since searching for @ or "at symbol" is very ambiguous, and Excel's help is laughably bad. Thanks!

Sidenote: If I manually remove this @ symbol from the generated XLSX file, then the formulae work as intended. The whole point of this XLSX file is so that I can generate it and send via Email, so currently I can modify it before send, but it might be fully automated in the future.

Answer 1

I'm not sure why that occurs but maatwebsite/excel is just a wrapper for phpoffice/phpspreadsheet. Whenever I've had to include formulas, I do so by getting the underlying Phpspreadsheet instance through events.

A problem though, is the methods are static so you can't really access $this->maxIndex without using an ugly hack.

namespace App\Exports\Sheets\Reports;

use Illuminate\Support\Collection;
use Maatwebsite\Excel\Concerns\{FromCollection, RegistersEventListeners, WithEvents, WithHeadings, WithMapping, WithTitle};
use Maatwebsite\Excel\Events\AfterSheet;

class SheetTwo implements FromCollection, WithEvents, WithHeadings, WithMapping, WithTitle,
{
    use RegistersEventListeners;

    protected Collection $sheetTwoData;

    public function __construct(Collection $sheetTwoData)
    {
        $this->sheetTwoData = $sheetTwoData;
    }

    public function collection(): Collection
    {
        return $this->sheetTwoData;
    }

    ...

    public static function afterSheet(AfterSheet $event)
    {
        // $event->sheet returns \Maatwebsite\Excel\Sheet which has all the methods of \PhpOffice\PhpSpreadsheet\Worksheet\Worksheet
        $highestRow = $event->sheet->getHighestRow();
        $newRow = $highestRow + 1; 

        $event->sheet->setCellValue('B' . $newRow, 'Totals');
        $event->sheet->setCellValue('C' . $newRow, "=SUBTOTAL(109, C2:C{})");
        $event->sheet->setCellValue('D' . $newRow, "=SUM(IF(D2:D{$highestRow}<>\"\",1/COUNTIF(D2:D{$highestRow},D2:D{$highestRow}),0))");
        $event->sheet->setCellValue('E' . $newRow, "=SUM(IF(G2:G{$highestRow}<>\"\",1/COUNTIF(G2:G{$highestRow},G2:G{$highestRow}),0))");
    }
}

Considering these are formulas on the SheetTwo worksheet, you shouldn't need to specify SheetTwo! in their values.

• https://docs.laravel-excel.com/3.1/exports/extending.html
• https://phpspreadsheet.readthedocs.io/en/latest/topics/accessing-cells/#setting-a-formula-in-a-cell