jya-oa-api/application/common/util/ExcelUtil.php

<?php
namespace app\common\util;


use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\Alignment;

class ExcelUtil
{
    /**
     * 获取导入文件数据
     * @param string $filepath 文件路径
     * @param array $cols 数据列表名 ['id','name','class']
     * @param int $row 数据从第几行开始取
     * @return array|bool
     * @throws \PhpOffice\PhpSpreadsheet\Reader\Exception
     */
    static public function read($filepath = '', $cols = [],$row = 2){
        $e = explode('.',$filepath);
        $ext = $e[count($e)-1];
        if($ext == 'xls'){
            $objread = IOFactory::createReader('Xls');
        }else{
            $objread = IOFactory::createReader('Xlsx');
        }
        $newArr=['xls','xlsx'];
        if(!in_array($ext,$newArr)){
            return false;
        }
        $objspreadsheet = $objread->load($filepath);
        $ret = $objspreadsheet->getActiveSheet()->toArray();
        $ret = $ret?$ret:[];

        $nret = [];
        foreach ($ret as $k=>$v){
            if($k + 1 < $row){
                continue;
            }
            if($cols){
                foreach ($cols as $kk=>$vv){
                    $v[$kk] = str_ireplace(',','',$v[$kk]);
                    $v[$kk] = str_ireplace(' ','',$v[$kk]);
                    if($vv == 'name'){
                        $a[$vv] = trim($v[$kk]);
                    }else{
                        $a[$vv] = trim($v[$kk])?floatval(trim($v[$kk])):0;
                    }
                }
                $nret[] = $a;
            }else{
                $nret[] = $v;
            }
        }
        return $nret;
    }

    /**
     * 普通数据导出
     * @param $filename 导出文件名
     * @param array $header 头数据
     * @param array $data 导出数据，数据过多会分散到多sheet中，数据过大注意响应时间和内存
     * @param string $savepath 保存路径，直接输出不用填
     * @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
     */
    static public function export($filename,$header = [],$data = [],$savepath = ''){
//        $header = [ // header格式,title和name必须存在
//            ['title' => '编号', 'name' => 'id','width'=>'20'],
//            ['title' => '姓名', 'name' => 'name','width'=>'20'],
//            ['title' => '班级', 'name' => 'class','width'=>'20'],
//        ];
        $spreadsheet = new Spreadsheet();
        $worksheet = $spreadsheet->getActiveSheet();
        $worksheet->setTitle($filename);
        $widths = [];
        $titles = [];
        $names = [];
        foreach ($header as $k=>$v){
            $width = isset($v['width'])&&$v['width'] > 0 ?$v['width']:20;
            $widths[] = $width;
            $titles[] = isset($v['title'])?$v['title']:'';
            $names[] = $v['name'];
        }

        $char = ['A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z'];
        $lins = [];
        for( $i=0;$i<count($header);$i++ ){
            $r1 = $i%26;
            $r2 = floor($i/26);
            if($r2 == 0){
                $lins[] = $char[$r1];
            }else if($r2 == 1){
                $lins[] = 'A'.$char[$r1];
            }else if($r2 == 2){
                $lins[] = 'B'.$char[$r1];
            }else if($r2 == 3){
                $lins[] = 'C'.$char[$r1];
            }else if($r2 == 4){
                $lins[] = 'D'.$char[$r1];
            }else if($r2 == 5){
                $lins[] = 'E'.$char[$r1];
            }else if($r2 == 6){
                $lins[] = 'F'.$char[$r1];
            }
        }

        $lists = self::chunkData($data);
        foreach ($lists as $k=>$v){
            if($k > 0){
                $sheet = $spreadsheet->createSheet($k+1)->setTitle($filename.($k+1));
            }else{
                $sheet = $worksheet;
            }
            self::writeSheet($sheet,$v,$titles,$widths,$lins,$names);
        }
        if(!$lists){
            self::writeSheet($worksheet,[],$titles,$widths,$lins,$names);
        }

        $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
        if($savepath){
            $res = $writer->save($savepath);
        }else{
            $path = $filename.'_'.date('YmdHis').'.xlsx';
            header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
            header('Content-Disposition: attachment;filename="'.$path.'"');
            header('Cache-Control: max-age=0');
            $writer->save('php://output');
        }
    }

    // 数据分段
    static private function chunkData($data,$length = 50000){
        $ret = [];
        $arr = [];
        foreach ($data as $k=>$v){
            $arr[] = $v;
            if(count($arr) == $length){
                $ret[] = $arr;
                $arr = [];
            }
        }
        if($arr){
            $ret[] = $arr;
        }

        return $ret;
    }

    /**
     * 多sheet导出,不用数据分段
     * @param $filename 导出文件名
     * @param array $header 头数据
     * @param array $sheets sheet名称数组
     * @param array $data 导出的二维数组
     * @param string $savepath 保存路径，直接导出不用填
     * @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
     */
    static public function exportBySheet($filename,$header = [],$sheets = [],$data = [],$savepath = ''){
//        $header = [ // header格式,title和name必须存在
//            ['title' => '编号', 'name' => 'id','width'=>'20'],
//            ['title' => '姓名', 'name' => 'name','width'=>'20'],
//            ['title' => '班级', 'name' => 'class','width'=>'20'],
//        ];
//        $sheets = ['sheet1','sheet2'];
//        $data = [['id'=>1,'name'=>'aaa']]; 二维数组
        $spreadsheet = new Spreadsheet();
        $worksheet = $spreadsheet->getActiveSheet();
        $widths = [];
        $titles = [];
        $names = [];
        foreach ($header as $k=>$v){
            $width = isset($v['width'])&&$v['width'] > 0 ?$v['width']:20;
            $widths[] = $width;
            $titles[] = isset($v['title'])?$v['title']:'';
            $names[] = $v['name'];
        }

        $char = ['A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z'];
        $lins = [];
        for( $i=0;$i<count($header);$i++ ){
            $r1 = $i%26;
            $r2 = floor($i/26);
            if($r2 == 0){
                $lins[] = $char[$r1];
            }else if($r2 == 1){
                $lins[] = 'A'.$char[$r1];
            }else if($r2 == 2){
                $lins[] = 'B'.$char[$r1];
            }else if($r2 == 3){
                $lins[] = 'C'.$char[$r1];
            }else if($r2 == 4){
                $lins[] = 'D'.$char[$r1];
            }else if($r2 == 5){
                $lins[] = 'E'.$char[$r1];
            }else if($r2 == 6){
                $lins[] = 'F'.$char[$r1];
            }
        }

        $lists = $data;
        foreach ($lists as $k=>$v){
            if($k > 0){
                $sheet = $spreadsheet->createSheet($k+1)->setTitle($sheets[$k]);
            }else{
                $worksheet->setTitle($sheets[$k]);
                $sheet = $worksheet;
            }
            self::writeSheet($sheet,$v,$titles,$widths,$lins,$names);
        }

        if(!$lists){
            self::writeSheet($worksheet,[],$titles,$widths,$lins,$names);
        }

        $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
        if($savepath){
            $res = $writer->save($savepath);
        }else{
            $path = $filename.'_'.date('YmdHis').'.xlsx';
            header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
            header('Content-Disposition: attachment;filename="'.$path.'"');
            header('Cache-Control: max-age=0');
            $writer->save('php://output');
        }
    }

    // 按sheet写入数据
    static private function writeSheet($sheet,$data,$titles,$widths,$lins,$names){
        foreach ($titles as $key => $value) {
            $sheet->setCellValueByColumnAndRow($key+1, 1, $value);
        }
        $styleArray = [
            'font' => [
                'bold' => true
            ],
            'alignment' => [
                'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
            ],
        ];
        $styleArray2 = [
            'alignment' => [
                'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
            ],
        ];

        foreach ($lins as $k=>$v){
            $sheet->getStyle($v.'1')->applyFromArray($styleArray)->getFont()->setSize(12);
            $sheet->getStyle($v)->applyFromArray($styleArray2)->getAlignment()->setWrapText(true);//换行
            $sheet->getColumnDimension($v)->setWidth($widths[$k]);
        }

        foreach ($data as $k=>$v){
            foreach ($names as $kk=>$vv){
                $sheet->setCellValueByColumnAndRow($kk+1, $k + 2, self::filterEmoji($v[$vv]));
            }
        }
    }

    // 过滤表情
    static private function filterEmoji($str){
        $str = preg_replace_callback('/./u',function ($match){
            return strlen($match[0]) >= 4?'':$match[0];
        },$str);
        return $str;
    }

}