Exporting data to Excel

<?php

namespace App\Lib\Office;

use Hyperf\HttpMessage\Stream\SwooleStream;
use Hyperf\HttpServer\Response;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
use PhpOffice\PhpSpreadsheet\Writer\Exception;
use Psr\Http\Message\ResponseInterface;

class ExportExcelHandler
{
    /**
     * 表格制作实例.
     * @var Spreadsheet 实例
     */
    private Spreadsheet $spreadsheet;

    /**
     * 表格实例.
     * @var Worksheet 实例
     */
    private Worksheet $sheet;

    /**
     * 行数.
     * @var int 整型
     */
    private int $row;

    /**
     * 保存路径.
     * @var string 路径
     */
    private string $dir;

    public function __construct()
    {
        $this->spreadsheet = new Spreadsheet();
        $this->spreadsheet->getProperties()
            ->setCreator('Hyperf')
            ->setLastModifiedBy('Hyperf')
            ->setTitle('ExportExcelHandler 2007 XLSX Test Document')
            ->setSubject('ExportExcelHandler 2007 XLSX Test Document')
            ->setDescription('Test document for ExportExcelHandler 2007 XLSX, generated using PHP classes.')
            ->setKeywords('')
            ->setCategory('Excel');
        // Sets the active sheet index to the first sheet
        $this->spreadsheet->setActiveSheetIndex(0);
        // Active sheet
        $this->sheet = $this->spreadsheet->getActiveSheet();
        // Set name
        $this->spreadsheet->getActiveSheet()->setTitle('Sheet1');

        $this->dir = BASE_PATH . '/runtime/excel/';
        if (! is_dir($this->dir)) {
            mkdir($this->dir, 0755, true);
        }
    }

    /**
     * 设置表头.
     * @return $this
     * @example ['汽车品牌', '型号', '颜色', '价格', '经销商']
     */
    public function setHeaders(array $title): static
    {
        foreach ($title as $k => $item) {
            $this->sheet->setCellValue(chr($k + 65) . '1', $item);
        }
        $this->row = 2;
        return $this;
    }

    /**
     * 添加数据.
     * @return $this
     * @example [['宝马','X5','BLACK','54.12W','深圳宝马4S店'],[],[]]
     */
    public function setData(array $data): static
    {
        foreach ($data as $datum) {
            $col = 'A';
            foreach ($datum as $value) {
                // write col
                $this->sheet->setCellValue($col . $this->row, $value);
                ++$col;
            }
            ++$this->row;
        }
        return $this;
    }

    /**
     * 保存到本地.
     * @throws Exception
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     */
    public function saveToLocal(string $filename): string
    {
        $this->spreadsheet->setActiveSheetIndex(0);

        $filename .= '.xlsx';
        $filename = mb_convert_encoding($filename, 'UTF-8', 'auto');
        $outFileName = $this->dir . $filename;
        $writer = IOFactory::createWriter($this->spreadsheet, 'Xlsx');
        $writer->save($outFileName);

        $this->spreadsheet->disconnectWorksheets();
        unset($this->spreadsheet);

        return $outFileName;
    }

    /**
     * 输出到浏览器.
     * @throws Exception
     */
    public function saveToBrowser(string $filename): ResponseInterface
    {
        $filename .= '.xlsx';
        $filename = mb_convert_encoding($filename, 'UTF-8', 'auto');
        $unique = $this->dir . uniqid() . microtime() . '.xlsx';
        $writer = IOFactory::createWriter($this->spreadsheet, 'Xlsx');
        $writer->save($unique);

        $this->spreadsheet->disconnectWorksheets();
        unset($this->spreadsheet);

        $content = file_get_contents($unique);
        // 删除临时文件
        unlink($unique);

        $response = new Response();

        return $response->withHeader('content-description', 'File Transfer')
            ->withHeader('content-type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
            ->withHeader('content-disposition', 'attachment; filename="' . urlencode($filename) . '"')
            ->withHeader('content-transfer-encoding', 'binary')
            ->withBody(new SwooleStream((string) $content));
    }
}

<?php

namespace App\Exception\Handler;

use App\Constants\SystemCode;
use Hyperf\ExceptionHandler\ExceptionHandler;
use Hyperf\HttpMessage\Stream\SwooleStream;
use PhpOffice\PhpSpreadsheet\Exception;
use Psr\Http\Message\ResponseInterface;
use Throwable;

/**
 * phpoffice包异常处理器.
 * Class OfficeExceptionHandler.
 */
class OfficeExceptionHandler extends ExceptionHandler
{
    /**
     * 处理类.
     * @param Throwable $throwable 异常
     * @param ResponseInterface $response 响应接口实现类
     * @return ResponseInterface 响应接口实现类
     */
    public function handle(Throwable $throwable, ResponseInterface $response): ResponseInterface
    {
        // 禁止异常冒泡
        $this->stopPropagation();

        return $response->withHeader('Content-Type', 'application/json')
            ->withStatus(200)->withBody(new SwooleStream(json_encode([
                'code' => SystemCode::OFFICE_ERR,
                'msg' => SystemCode::getMessage(SystemCode::OFFICE_ERR),
                'status' => false,
                'data' => [],
            ], JSON_UNESCAPED_UNICODE)));
    }

    /**
     * 是否满足处理条件.
     * @param Throwable $throwable 异常
     * @return bool true|false
     */
    public function isValid(Throwable $throwable): bool
    {
        return $throwable instanceof Exception;
    }
}