使用php语言的hyperf框架写一个excel表格解析与整合导出的处理类

1.引用扩展包

oss用于文件上传

composer require aliyuncs/oss-sdk-php
composer require phpoffice/phpspreadsheet

2.定义excel模板字段

2.1模板接口定义

<?php
declare(strict_types=1);

namespace App\Service\ExcelTemplate;

interface ExcelTemplateInterface
{
    /**
     * 获取sheet名称
     * @return string
     */
    public function getSheetName():string;

    /**
     * 获取字段配置
     * @return array
     */
    public function getField():array;

    /**
     * 获取模板名称
     * @return string
     */
    public function getTemplateName():string;
}

2.2实现接口定义模板

<?php

declare(strict_types=1);

namespace App\Service\ExcelTemplate;

/**
 * 模板1
 * @package App\Service\ExcelTemplate;
 */
class TestTemplate implements ExcelTemplateInterface
{
    /**
     * 配置解析字段
     * @return string[]
     */
    public function getField(): array
    {
        return [
            "name" => "姓名",
            "remark" => "备注",
        ];
    }

    /**
     * 配置sheet名称
     * @return string
     */
    public function getSheetName(): string
    {
        return "测试";
    }

    /**
     * 配置模板名称
     * @return string
     */
    public function getTemplateName(): string
    {
        return "测试模板";
    }
}

3.模板处理类

<?php

declare(strict_types=1);

namespace App\Service;

use App\Exception\ErrorException;
use App\Service\ExcelTemplate\ExcelTemplateInterface;
use App\Utils\OssClient;
use Hyperf\Context\Context;
use Hyperf\HttpMessage\Upload\UploadedFile;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\Exception;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\RichText\RichText;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;

/**
 * 表格处理类
 * Class ExcelService
 * @package App\Service;
 */
class ExcelService
{
    /**
     * @var ExcelTemplateInterface
     */
    private ExcelTemplateInterface $template;

    /**
     * 传入解析模板类
     * ExcelService constructor.
     * @param ExcelTemplateInterface $template
     */
    public function __construct(ExcelTemplateInterface $template)
    {
        $this->template = $template;
    }

    /**
     * 解析excel文件内容（根据模板）
     * @param UploadedFile $file
     * @return array
     * @throws Exception
     */
    public function parsing(UploadedFile $file): array
    {
        $fileName = $file->getFilename();
        $fileName = $fileName.'-'.$file->getClientFilename();

        $this->initDir();
        
        $filePath = BASE_PATH .'/exls/'.$fileName;
        $file->moveTo($filePath);
        // 通过 IOFactory 加载 Excel 文件
        $spreadsheet = IOFactory::load($filePath);

        // 获取模板需要取值的工作表
        $sheetName = $this->template->getSheetName();
        $sheetNames = $spreadsheet->getSheetNames();
        if(!in_array($sheetName,$sheetNames)){
            throw new ErrorException("模板工作表[{$sheetName}]不存在，无法解析");
        }


        //获取单个tab对象
        $sheet = $spreadsheet->getSheetByName($sheetName);
        //获取单个tab对象的列数和行数
        $row = $sheet->getHighestRow();
        $column = $sheet->getHighestColumn();//英文最大值
        //数值最大值
        $columnIndex = Coordinate::columnIndexFromString($column);
        //头部字段解析
        $columnHeaders = [];
        $fieldList = array_flip($this->template->getField());
        for ($i = 1; $i <= $columnIndex; $i++) {
            $excelKey = Coordinate::stringFromColumnIndex($i);
            $headerName = $sheet->getCell($excelKey.'1')->getValue();
            if(isset($fieldList[$headerName])){
                $columnHeaders[$i] = [
                    "excel_key" => $excelKey,
                    "field" => $fieldList[$headerName]??'',
                    "field_name" => $headerName
                ];
            }
        }

        //头部以下的内容解析
        $dataList = [];
        for ($rowIndex=2;$rowIndex<=$row;$rowIndex++){
            foreach ($columnHeaders as $column){
                $value = $sheet->getCell($column['excel_key'].$rowIndex)->getValue();
                if($value instanceof RichText){
                    $value = $value->getPlainText();
                }
                $dataList[$rowIndex-2][$column['field']] = (string)$value;
            }
        }

        return $dataList;
    }

    /**
     * 数据写入excel文件类
     * @param array $dataList
     * @return Spreadsheet
     * @throws Exception
     */
    private function generate(array $dataList): Spreadsheet
    {
        $fieldList = $this->template->getField();
        $workSheet = new Worksheet();
        //sheet名称定义
        $workSheet->setTitle($this->template->getSheetName());
        //第一行写入头信息
        $columnIndex = 1;
        foreach ($fieldList as $fieldName){
            $excelKey = Coordinate::stringFromColumnIndex($columnIndex);var_dump($excelKey);
            $workSheet->setCellValue($excelKey.'1',$fieldName);
            $columnIndex++;
        }
        //遍历数据写入表格
        foreach ($dataList as $k=>$data){
            $columnIndex = 1;
            $rowIndex = $k+2;
            foreach ($fieldList as $field=>$fieldName){
                $excelKey = Coordinate::stringFromColumnIndex($columnIndex);
                $workSheet->setCellValue($excelKey.$rowIndex,$data[$field]??"");
                $columnIndex++;
            }
        }
        //创建excel文件类
        $spreadsheet = new Spreadsheet();
        $spreadsheet->addSheet($workSheet,0);
        $spreadsheet->setActiveSheetIndex(0);
        return $spreadsheet;
    }

    /**
     * 数据导出
     * @param array $dataList
     * @return mixed
     * @throws \PhpOffice\PhpSpreadsheet\Writer\Exception|Exception
     */
    public function exportExcel(array $dataList)
    {
        Log::getInstance()->info("导出数据",['count'=>count($dataList)]);
        $this->initDir();

        $fileName = $this->template->getSheetName().(Context::get("userInfo")["id"]??0).'.xls';
        $spreadsheet = $this->generate($dataList);
        $writer = IOFactory::createWriter($spreadsheet,IOFactory::WRITER_XLSX);
        $filePath = BASE_PATH .'/exls/'.$fileName;
        $writer->save($filePath);

        //上传OSS
        $result = (new OssClient())->uploadFile('zz_goods_center/'.$fileName,$filePath);
        if(!isset($result['oss-request-url'])){
            throw new ErrorException("OSS处理失败");
        }

        return $result['oss-request-url'];
    }
    /**
     * 获取模板下载链接
     * @return mixed
     * @throws \PhpOffice\PhpSpreadsheet\Writer\Exception|Exception
     */
    public function getTemplateDownloadUrl()
    {
        $this->initDir();
        $spreadsheet = $this->generate([]);
        $writer = IOFactory::createWriter($spreadsheet,IOFactory::WRITER_XLSX);
        $filePath = BASE_PATH .'/exls/'.$this->template->getTemplateName().'.xls';
        $writer->save($filePath);

        //上传OSS
        $result = (new OssClient())->uploadFile($this->template->getTemplateName().'.xls',$filePath);
        if(!isset($result['oss-request-url'])){
            throw new ErrorException("OSS处理失败");
        }

        return $result['oss-request-url'];
    }

    /**
     * 目录初始化
     */
    private function initDir()
    {
        if(!is_dir(BASE_PATH .'/exls/')){
            $dir = opendir(BASE_PATH);
            mkdir('exls');
            closedir($dir);
        }
    }
}