php - Laravel Excel Maatwebsite - SUM 函数不起作用

Question

我正在使用 Maatwebsite 3.1使用 Laravel 5.8。
我生成了一个包含价格列的电子表格，我想对其求和。为此，我有以下代码:

<?php

namespace App\Exports;

use Maatwebsite\Excel\Concerns\FromCollection;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
use Maatwebsite\Excel\Concerns\WithStyles;

use \Illuminate\Support\Collection;

class MenuExport implements FromCollection, WithStyles
{
    private $data;

    function __construct($data) {
        $this->data = $data;
    }

    public function styles(Worksheet $sheet)
    {
        $numOfRows = count($this->data);
        $totalRow = $numOfRows + 1;

        // Add cell with SUM formula to last row
        $sheet->setCellValue("B{$totalRow}", "=SUM(B1:B{$numOfRows})");
    }

    public function collection()
    {
        // Add new row with Total cell
        $extendedArr = [
            $this->data,
            ['Total:']
        ];
        
        return new Collection($extendedArr);
    }
}

生成的excel其实是有SUM函数的，但是显示的是0。例如，我使用以下数据调用导出器:

$data = [
    ['Coffee', 1.8],
    ['Cake', 3.6],
    ['Toast', 2.5]
];
        
return Excel::download(new MenuExport($data), "menu.xlsx");

这是结果:

如果我剪切函数单元格内容然后粘贴它 - 我会得到想要的结果:

知道哪里出了问题吗？

Answer 1

其实比我想象的要简单。
我所要做的就是添加 WithPreCalculateFormulas:

:
use Maatwebsite\Excel\Concerns\WithPreCalculateFormulas;

class MenuExport implements FromCollection, WithStyles, WithPreCalculateFormulas
{

来自 documentation :

Maatwebsite\Excel\Concerns\WithPreCalculateFormulas
Forces PhpSpreadsheet to recalculate all formulae in a workbook whensaving, so that the pre-calculated values are immediately available toMS Excel or other office spreadsheet viewer when opening the file.