ball/extend/excel/ExcelWriter.php

<?php
namespace excel;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

class ExcelWriter
{
    private $spreadsheet;
    private $sheet;
    private $fileUrl;

    public function __construct()
    {
        $this->spreadsheet = new Spreadsheet();
        $this->sheet = $this->spreadsheet->getActiveSheet();
    }

    public function setHeaders(array $headers, $row = 1)
    {
        foreach ($headers as $column => $header) {
            $this->sheet->setCellValue($this->getColumnLetter($column + 1) . $row, $header);
        }
    }

    public function addData(array $data, $startRow = 2)
    {
        foreach ($data as $rowIndex => $row) {
            foreach ($row as $column => $value) {
                $this->sheet->setCellValue($this->getColumnLetter($column + 1) . ($startRow + $rowIndex), $value);
                if (is_numeric($value) && strlen($value) > 10) {
                    $this->sheet->getCell($this->getColumnLetter($column + 1) . ($startRow + $rowIndex))->setValueExplicit($value);
                }
                $this->sheet->getColumnDimension($this->getColumnLetter($column + 1))->setWidth(20);
            }
        }
    }

    public function save($filename)
    {
        $savePath = "./exports/";
        $filePath = $savePath . $filename;
        $writer = new Xlsx($this->spreadsheet);
        $writer->save($filePath);
        $this->fileUrl = "/exports/". $filename;
    }
    public function fileUrl()
    {
        return $this->fileUrl;
    }
    public function getCellValue($row,$value)
    {
        $values = explode('.',$value);
        $count = count($values);
        switch ($count){
            case 1:
                return $row[$values[0]]??'';
            case 2:
                return $row[$values[0]][$values[1]]??'';
            case 3:
                return $row[$values[0]][$values[1]][$values[2]]??'';
            default:
                return '';
        }
    }

    /**
     * 生成并保存 Excel 文件
     * @return void
     */
    function generateExcelFile(array $headers, array $lists,string $filename, array $map = [],string $suffix = 'xlsx'): void
    {
        $filename = ($filename?:'').('-'.date('YmdHis'));
        //if(!in_array($suffix,['xlsx','xls'])) throw new \Exception('文件格式不正确');
        $this->setHeaders($headers);
        $data = array_map(function($row) use ($map) {
            if(empty($map)){
                return $row;
            }
            $tmp_arr = [];
            foreach ($map as $value){
                if (is_callable($value)){
                    $tmp_arr[] = $value($row);
                }elseif (is_string($value)){
                    $tmp_arr[] = $this->getCellValue($row,$value);
                }
            }
            return $tmp_arr;
        }, $lists);
        $this->addData($data);
        $this->save($filename.".".$suffix);
    }


    private function getColumnLetter($columnIndex)
    {
        return \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($columnIndex);
    }
}