cf-service/app/Helper/Exporter.php

<?php
declare(strict_types=1);

namespace App\Helper;

use App\Exception\BusinessException;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Style\Style;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;

class Exporter
{
    private $styleSettingClosure;

    /**
     * @param object      $export
     * @param string|null $fileName
     * @param string      $writerType
     * @param array       $headers
     *
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
     * @return BinaryFileResponse
     */
    public function download($export, string $fileName, string $writerType = null)
    {

    }

    /**
     * @param object      $export
     * @param string      $filePath
     * @param string|null $disk
     * @param string      $writerType
     * @param mixed       $diskOptions
     *
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
     * @return bool
     */
    public function store(Export $export, string $filePath, string $writerType = null)
    {
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();

        $this->fillHeading($export, $sheet);
        $this->fillData($export, $sheet);
        $this->setAutoSize($export, $sheet);
        $closure = $export->getStyleSetting();
        if ($closure) {
            $closure($export, $sheet);
        }

        $writer = new Xlsx($spreadsheet);
        $writer->save($filePath);
    }

    private function fillHeading(Export $export, Worksheet $sheet)
    {
        $rowIndex = 0;
        $firstCellKey = null;
        $lastCellKey = null;
        foreach ($export->headings() as $columnIndex => $name) {
            $cellKey = $this->getCellKey($rowIndex, $columnIndex);
            $sheet->setCellValue($cellKey, $name);
            if ($columnIndex == 0) {
                $firstCellKey = $cellKey;
            }
            $lastCellKey = $cellKey;
        }

        $this->setHeadingStyle($sheet, $firstCellKey . ':' . $lastCellKey);
    }

    private function fillData(Export $export, Worksheet $sheet, $rowIndex = 1)
    {
        $mode = $export->getMode();
        if ($mode == Export::MODE_QUERY) {
            $rowIndex = $this->fillFromQuery($export, $sheet, $rowIndex);
        } elseif ($mode == Export::MODE_ARRAY) {
            $rowIndex = $this->fillFromArray($export, $sheet, $rowIndex);
        } elseif ($mode == Export::MODE_COLLECTION) {
            $rowIndex = $this->fillFromCollection($export, $sheet, $rowIndex);
        }

        $summary = $export->withSummary ? $export->summary() : null;
        if (!empty($summary)) {
            $rowIndex = $this->fillItems($export, $sheet, [$summary],  $rowIndex);
        }
    }

    private function setHeadingStyle(Worksheet $sheet, $cellRange)
    {
        $sharedStyle = new Style();
        $sharedStyle->applyFromArray(
            [
                'fill' => [
                    'fillType' => Fill::FILL_SOLID,
                    'color' => ['argb' => 'd9d9d9'],
                ],
                'borders' => [
                    'allBorders' => ['borderStyle' => Border::BORDER_THIN],
                ],
                'font' => [
                    'name' => '黑体',
                    'bold' => true,
                    'size' => 12
                ]
            ]
        );
        $sheet->duplicateStyle($sharedStyle, $cellRange);
    }

    private function fillFromQuery(Export $export, Worksheet $sheet, $rowIndex)
    {
        $query = $export->query();
        if (is_null($query)) {
            throw new BusinessException('query 方法未实现');
        }
        $page = 1;
        $limit = 1000;
        $chunkCompareValue = null;
        $isFirstRound = true;
        do {
            $tmpQuery = clone $query;
            $offset = ($page-1) * $limit;
            $items = null;
            if ($export->chunkCompareEnable) {
                $options = $export->chunkCompareOptions;
                if (!$isFirstRound) {
                    $tmpQuery->where($options['key'], $options['symbol'], $chunkCompareValue);
                }
                $items = $tmpQuery->limit($limit)->get();
                $lastItem = $items->last();
                $chunkCompareValue = $lastItem ? $lastItem->{$options['key']} : null;
            } else {
                $items = $tmpQuery->offset($offset)->limit($limit)->get();
            }
            $items = $export->rangeQueryItems($items);
            $rowIndex = $this->fillItems($export, $sheet, $items, $rowIndex);
            $isFirstRound = false;
            $count = count($items);
            $page ++;
        } while ($count == $limit);
        return $rowIndex;
    }

    private function fillFromArray(Export $export, Worksheet $sheet, $rowIndex)
    {
        return $this->fillItems($export, $sheet, $export->array(), $rowIndex);
    }

    private function fillFromCollection(Export $export, Worksheet $sheet, $rowIndex)
    {
        return $this->fillItems($export, $sheet, $export->collection(), $rowIndex);
    }

    private function fillItems(Export $export, Worksheet $sheet, $items, $rowIndex)
    {
        foreach ($items as $item) {
            foreach ($export->map($item) as $columnIndex => $value) {
                $cellKey = $this->getCellKey($rowIndex, $columnIndex);
                $sheet->setCellValue($cellKey, $value);
            }
            $rowIndex += 1;
        }
        return $rowIndex;
    }

    private function concatCellKey($rowKey, $columnKey)
    {
        return  $columnKey . $rowKey;
    }

    private function getCellKey($rowIndex, $columnIndex)
    {
        return $this->concatCellKey($rowIndex + 1, Excel::getColumnKey($columnIndex));
    }

    private function setAutoSize(Export $export, Worksheet $sheet)
    {
        foreach ($export->headings() as $columnIndex => $name) {
            $columnKey = Excel::getColumnKey($columnIndex);
            $sheet->getColumnDimension($columnKey)->setAutoSize(true);
        }
    }
}