PHPExcel 升级到 PhpSpreadsheet

项目原本php7.2  集成PHPExcel 插件 使用没任何问题
但是现在php7.2升级到php7.4。

  composer  update 的时候总是提示 PHPExcel不兼容php7.4

最初解决方案是 把 vendor/phpoffice  下类似 $array{0} 改成  $array[0],改了多处代码后，execl导入导出功能在7.4下勉强能用。
但是每次部署新服务器总是这个操作感觉太low，最近时间充裕花了半天时间升级到PhpSpreadsheet，彻底解决埋了这个坑。

1，composer安装：composer require phpoffice/phpspreadsheet

2，备份旧的execl操作类文件 PHPExceler.php 
以下是基于PHPExcel的旧代码。

<?php
declare(strict_types=1);

namespace App\Service;
use Hyperf\Utils\Context;
use phpDocumentor\Reflection\File;
use Psr\Http\Message\ResponseInterface;


class PHPExceler
{
    /**
     * @param array $data
     * @param string $filename
     * @param string $frozen  冻结的单元格
     * @return string
     * @throws \PHPExcel_Exception
     * @throws \PHPExcel_Reader_Exception
     * @throws \PHPExcel_Writer_Exception
     */
    public function httpFile(array $data , string $filename='simple.xlsx',$frozen=''):string{
        $filename = explode('.',$filename);
        $ext = array_pop($filename);
        if(!$filename)
            $filename = $ext;
        else
            $filename = implode('.',$filename);

        $deep = $this->arrDepth($data);
        if( $deep > 3){
            return 'Data fail,must be two or three dimensional array!';
        }
        if($deep==0){
            $data = [$data];
            $data = [$data];
            $data = [$data];
        }
        if($deep == 1){
            $data = [$data];
            $data = [$data];
        }
        if($deep == 2){
            $data = [$data];
        }

        $data = $this->stringArray($data);

        switch ($ext){
            case "xlxs":
                $contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                $writerType='Excel2007';
                break;
            case "xls":
                $contentType = "application/vnd.ms-excel";
                $writerType='Excel5';
                break;
            case "csv":
                $contentType = "application/csv";
                $writerType='CSV';
                break;
            case "html":
                $contentType = "text/html";
                $writerType='HTML';
                break;
            default:
                $contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                $ext = 'xlsx';
                $writerType='Excel2007';
        }

        $objPHPExcel = new \PHPExcel();

        // Set document properties
        $objPHPExcel->getProperties()->setCreator("Kiwi Master")
            ->setLastModifiedBy("Kiwi Master")
            ->setTitle("config card")
            ->setSubject("config card")
            ->setDescription("Created at ".date("Y-m-d H:i:s"))
            ->setKeywords("kiwi master coin clash config excel")
            ->setCategory("coin clash config");

        $i = 0;
        foreach ($data as $sheet_k => $sheet_d){
            if($i<1) {
                $sheet = $objPHPExcel->setActiveSheetIndex();
            }else{
                $sheet = $objPHPExcel->createSheet();
            }
            $sheet_name = $sheet_k;
            if(is_numeric($sheet_k)){
                $sheet_name = "sheet".$sheet_k;
            }
            $sheet->fromArray($sheet_d);
            $sheet->setTitle($sheet_name);

            $i++;
        }

        if($frozen){  /*设置冻结*/
            $objPHPExcel->getActiveSheet()->freezePane($frozen);
        }


        /* 输出excel文件头信息 */
        $response = Context::get(ResponseInterface::class);
        $response = $response->withHeader('Content-Type', $contentType)
            ->withHeader('Content-Disposition', 'attachment;filename="'.$filename.'.'.$ext.'"')
            ->withHeader('Cache-Control', 'max-age=0');
        Context::set(ResponseInterface::class, $response);

        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, $writerType);
        ob_start();
        $objWriter->save('php://output');
        $string = ob_get_contents();
        ob_end_clean();
        return $string;
    }


    /**
     * 根据扩展名实例化读取对象
     * @param  $ext  string   扩展名
     * @param  $filename  string   文件名
     * @param  $data  object | array
     * @param  $column_arr  array  指定读取哪些列
     * @return object
     */
    public function import_obj($ext,$filename,&$data,$column_arr=[]){
        if($ext=='xls'){
            $FileType='Excel5';
        }elseif ($ext == 'xlsx'){
            $FileType = 'Excel2007';
        }elseif ($ext == 'csv'){
            $FileType = 'CSV';
        }else{
            return -120101; /*参数错误*/
        }

        /*指定单元格以序列化的方式保存在内存中*/
//        $r = \PHPExcel_CachedObjectStorageFactory::initialize(\PHPExcel_CachedObjectStorageFactory::cache_in_memory_serialized);
//        if (!$r) {
//            die('Unable to set cell cacheing');
//        }

        $PHPReader = \PHPExcel_IOFactory::createReader($FileType);
        $PHPReader->setReadDataOnly(true);  /*忽略里面各种格式等(对于Excel读取，有很大优化)*/

        if($column_arr){  /*过滤器 指定导入哪些列 非指定列不读取。 默认是导入所有列*/
            $filterSubset = new MyReadFilter();
            $filterSubset->column_arr=$column_arr;
            $PHPReader->setReadFilter($filterSubset);
        }


        if(is_object($data)){
            $data=$PHPReader->load($filename, $encode = 'utf-8');  /*读取文件 返回对象*/
            return 1;
        }else{
            $PHPExcel = $PHPReader->load($filename, $encode = 'utf-8');  /*读取文件 继续处理数据*/
        }
        /*以下是通用的读取execl文件的方式*/
        $currentSheet = $PHPExcel->getSheet(0);  //获取表中的第一个工作表，如果要获取第二个，把0改为1，依次类推
        $allColumn = $currentSheet->getHighestColumn();  //获取总列数
        $allRow = $currentSheet->getHighestRow();  //获取总行数
        //循环获取表中的数据，$currentRow表示当前行，从哪行开始读取数据，索引值从1开始
        for ($currentRow = 3; $currentRow <= $allRow; $currentRow++) {
            //从哪列开始，A表示第一列
            $tmp=[];
            for ($currentColumn = 'A'; $currentColumn <= $allColumn; $currentColumn++) {
                //数据坐标
                $address = $currentColumn . $currentRow;
                //读取到的数据，保存到数组$arr中
                $tmp[]=$currentSheet->getCell($address)->getValue();
                //$data[$currentRow][$currentColumn] = $currentSheet->getCell($address)->getValue();
            }
            $data[]="'".implode("','",$tmp)."'";
        }
        return 1;
    }
   


    /**
     * 返回数组深度
     * @param $array
     * @param int $deep
     * @return int
     */
    private function arrDepth($array,$deep=0){
        $num=[0];
        if(!is_array($array))
            return 0;
        foreach ($array as $row){
            if(is_array($row)){
                $num[] = $this->arrDepth($row,$deep+1);
            }else{
                return $deep+1;
            }
        }
        arsort($num);
        return current($num);
    }


    /**
     * 赋值转为string
     * @param array $array
     * @return array
     */
    private function stringArray(array $array):array{
        foreach ($array as &$row){
            if(is_array($row)){
                $row = $this->stringArray($row);
            }else{
                if(!is_string($row))
                    $row = (string)$row;
            }
        }
        return $array;
    }
}

/*自定义过滤器 返回指定列*/
class MyReadFilter implements \PHPExcel_Reader_IReadFilter
{
    public $column_arr=['A','B'];   //默认读取两列
    public function readCell($column, $row, $worksheetName = '') {
        // Read rows 1 to 7 and columns A to E only
        //if ($row >= 1 && $row <= 7) {
            if (in_array($column,$this->column_arr)) {
                return true;
            }
        //}
        return false;
    }
}

3，使用PhpSpreadsheet 对上述代码进行 精简优化

<?php
declare(strict_types=1);

namespace App\Service;
use Hyperf\Utils\Context;
use phpDocumentor\Reflection\File;
use Psr\Http\Message\ResponseInterface;

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Reader\IReadFilter;

class PHPExceler
{
    /**
     * @param array $data
     * @param string $filename
     * @param string $frozen  冻结的单元格
     * @return string
     */
    public function httpFile(array $data , string $filename='simple.xlsx',$frozen=''):string{

        $data = $this->stringArray($data);

        $spreadsheet = new Spreadsheet();
        $i=0;
        foreach ($data as $sheet_k => $sheet_d){
            if(is_numeric($sheet_k)){
                $sheet_k = "sheet".$sheet_k;
            }
            if($i==0){
                $sheet =$spreadsheet->setActiveSheetIndex($i);  /*使用默认的第一个工作簿*/
            }else{
                $sheet =$spreadsheet->createSheet($i);  /*创建一个新的工作簿*/
            }

            $sheet->setTitle($sheet_k);  /*设置工作簿名字*/
            $sheet->fromArray($sheet_d);  /*填充行列数据*/

            if($frozen){  /*设置冻结单元格*/
                $sheet->freezePane($frozen);
            }
            $i++;
        }

        /* 输出excel文件头信息 */
        $response = Context::get(ResponseInterface::class);
        $response = $response->withHeader('Content-Type',"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
            ->withHeader('Content-Disposition', 'attachment;filename="'.$filename.'"')
            ->withHeader('Cache-Control', 'max-age=0');
        Context::set(ResponseInterface::class, $response);


        ob_start();  /*打开输出控制缓冲*/
        $writer = new Xlsx($spreadsheet);
        $writer->save('php://output');
        $string = ob_get_contents();
        $spreadsheet->disconnectWorksheets(); /*如果没有这句，直接运行unset会报“内存泄漏”错误*/
        unset($spreadsheet);  /*从内存中清除工作簿*/
        ob_end_clean();  /*清空（擦除）缓冲区并关闭输出缓冲*/
        return $string;
    }


    /**
     * 根据扩展名实例化读取对象
     * @param  $ext  string   扩展名
     * @param  $filename  string   文件名
     * @param  $data  object | array
     * @param  $column_arr  array  指定读取哪些列
     * @return object
     */
    public function import_obj($ext,$filename,&$data,$column_arr=[]){
        if($ext=='xls'){
            $ext1='Xls';
        }elseif ($ext == 'xlsx'){
            $ext1 = 'Xlsx';
        }elseif ($ext == 'csv'){
            $ext1 = 'Csv';
        }else{
            return -120101; /*参数错误*/
        }

        $reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader($ext1);

        $reader->setReadDataOnly(true);  /*忽略里面各种格式等(对于Excel读取，有很大优化)*/
        if($column_arr){  /*过滤器 指定导入哪些列 非指定列不读取。 默认是导入所有列*/
            $filterSubset = new MyReadFilter();
            $filterSubset->column_arr=$column_arr;
            $reader->setReadFilter($filterSubset);
        }

        if(is_object($data)){
            $data=$reader->load($filename, $encode = 'utf-8');  /*读取文件 返回对象*/
            return 1;
        }else{
            $PHPExcel = $reader->load($filename, $encode = 'utf-8');  /*读取文件 继续处理数据*/
        }
        /*以下是通用的读取execl文件的方式*/
        $currentSheet = $PHPExcel->getSheet(0);  //获取表中的第一个工作表，如果要获取第二个，把0改为1，依次类推
        $allColumn = $currentSheet->getHighestColumn();  //获取总列数
        $allRow = $currentSheet->getHighestRow();  //获取总行数
        //循环获取表中的数据，$currentRow表示当前行，从哪行开始读取数据，索引值从1开始
        for ($currentRow = 3; $currentRow <= $allRow; $currentRow++) {
            //从哪列开始，A表示第一列
            $tmp=[];
            for ($currentColumn = 'A'; $currentColumn <= $allColumn; $currentColumn++) {
                //数据坐标
                $address = $currentColumn . $currentRow;
                //读取到的数据，保存到数组$arr中
                $tmp[]=$currentSheet->getCell($address)->getValue();
                //$data[$currentRow][$currentColumn] = $currentSheet->getCell($address)->getValue();
            }
            $data[]="'".implode("','",$tmp)."'";
        }
        return 1;
    }


    /**
     * 赋值转为string
     * @param array $array
     * @return array
     */
    private function stringArray(array $array):array{
        foreach ($array as &$row){
            if(is_array($row)){
                $row = $this->stringArray($row);
            }else{
                if(!is_string($row))
                    $row = (string)$row;
            }
        }
        return $array;
    }
}

/*自定义过滤器 返回指定列*/
class MyReadFilter implements IReadFilter
{
    public $column_arr=['A','B'];   //默认读取两列
    public function readCell($column, $row, $worksheetName = '') {
        // Read rows 1 to 7 and columns A to E only
        //if ($row >= 1 && $row <= 7) {
            if (in_array($column,$this->column_arr)) {
                return true;
            }
        //}
        return false;
    }
}

3，新旧方法名称一致，业务端代码完全不用修改。
     
先测试下方法导出execl

use App\Service\PHPExceler;

$model=new PHPExceler();
$data['test_sheet_0']=[[1,2,3,4,5],[6,7,8,9,10]];
return $model->httpFile($data,'test.xlsx');

4，再用刚才导出的test.xlst测试下导入代码

use App\Service\PHPExceler;
use Hyperf\HttpServer\Annotation\Controller;
use Hyperf\HttpServer\Annotation\RequestMapping;

$upload=$this->request->file('files');
$pathname=$upload->getPathname();/*文件完整路径*/
$ext=$upload->getExtension(); /*文件扩展名*/
$model=new PHPExceler();
$column=['A','B','C'];  /*限制读取列*/
$data=[];  /*存储读取结果*/
$res=$model->import_obj($ext,$pathname,$data,$column);

if($res0){
    print_r($data);
}else{
    return $this->fail('错误',500);
}

结果非常完美