PHPOffice/PhpSpreadsheet的导入导出操作基本使用

phpspreadsheet 引入

由于PHPExcel早就停止更新维护，所以适用phpspreadsheet。不知道如何通过composer拉取项目包的同学，可以查看Composer学习一文。引入方法：

composer require phpoffice/phpspreadsheet

注：可能会出现报错：Your requirements could not be resolved to an installable set of packages，这是因为不匹配composer.json要求的版本，可以在命令后面加上 –ignore-platform-req 来忽略版本匹配 或 加上具体版本号

PhpSpreadsheet的基本使用

注：我使用的是thinkphp5.0框架

引入命名空间(可按需引入)

<?php
namespace app\admin\controller;

use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Reader\Xlsx;
use PhpOffice\PhpSpreadsheet\Reader\Xls;

Excel导入操作 (例子)

/**
 * Excel导入人员信息
 */
public function import_excel()
{
    //获取表格的大小，限制上传表格的大小5M
    $file_size = $_FILES['file']['size'];
    if ($file_size > 5 * 1024 * 1024) {
        $this->error('文件大小不能超过5M');
        exit();
    }

    //限制上传表格类型
    $fileExtendName = substr(strrchr($_FILES['file']["name"], '.'), 1);
    if (!in_array($fileExtendName, ['xls','xlsx'])) {
        $this->error('必须为excel表格，且必须为xlsx或xls格式！');
        exit();
    }

    if (is_uploaded_file($_FILES['file']['tmp_name'])) {
        // 有Xls和Xlsx格式两种
        $objReader = IOFactory::createReader('Xlsx');

        $file_name = $_FILES['file']['name'];
        $filename = $_FILES['file']['tmp_name'];

        if (!file_exists($filename)) {
            die('no file!');
        }
        $extension = strtolower( pathinfo($file_name, PATHINFO_EXTENSION) );
        if ($extension =='xlsx') {
            $objReader = IOFactory::createReader('Xlsx');
            $objPHPExcel = $objReader ->load($filename);
        } else if ($extension =='xls') {
            $objReader = new \PhpOffice\PhpSpreadsheet\Reader\Xls();
            $objPHPExcel = $objReader ->load($filename);
        } else if ($extension=='csv') {
            $PHPReader = new \PhpOffice\PhpSpreadsheet\Reader\Csv();
            $PHPReader->setInputEncoding('GBK');    //默认输入字符集
            $PHPReader->setDelimiter(',');      //默认的分隔符
            $objPHPExcel = $PHPReader->load($filename);     //载入文件
        }
        
        $objReader->setReadDataOnly(true);	// 如果不需要获取特殊操作，则只读内容，可以大幅度提升读取Excel效率
        $sheet = $objPHPExcel->getSheet(0); //excel中的第一张sheet
        
        //已有用户列表
        $had_userlist = db('userinfo')->where($where)->select();
        
        $highestRow = $sheet->getHighestRow(); // 取得总行数
        $highestColumn = $sheet->getHighestColumn();   // 取得总列数
        

        $sheetData = $objPHPExcel->getActiveSheet()->ToArray();   // 转成数组
        
        // 测试获取值的方式（可忽略）
        // $sheetData = $objPHPExcel->getActiveSheet()->rangeToArray("A1:G".$highestRow);
        // $sheetData= $objPHPExcel->getActiveSheet()->getCell("E3")->getValue();
        // $sheetData= $objPHPExcel->getActiveSheet()->getCell("E3")->getCalculatedValue();
        // $sheetData= $objPHPExcel->getActiveSheet()->getCell("D3")->getOldCalculatedValue();
        // return $sheetData;
        
        //数据的顺序排列（为了获取对应字段的值）
        $sort = ['name' => '', 'stu' => '', 'sex' => '', 'number' => '', 'phone' => '', 'class' => ''];
        for($i=0; $i < 2; $i++){
            for($key = 0; $key < count($sheetData[$i]); $key++){
                $str = $sheetData[$i][$key];
                if( in_array($str, ['姓名','名字','学生姓名']) ){
                    $sort['name'] = $key;
                }elseif( in_array($str, ['工号','学号']) ){
                    $sort['stu'] = $key;
                }elseif( in_array($str, ['性别']) ){
                    $sort['sex'] = $key;
                }elseif( in_array($str, ['手机','手机号','手机号码']) ){
                    $sort['phone'] = $key;
                }elseif( in_array($str, ['身份证号','身份证','身份证号码','证件号码']) ){
                    $sort['number'] = $key;
                }elseif( in_array($str, ['班级','院系']) ){
                    $sort['class'] = $key;
                }
            }
        }
        // return $sort;

        // 定义$usersExits，循环表格的时候，找出已存在的用户。
        $usersExits = [];
        foreach($sheetData as $k => $v){
            if($k == 0){ unset($sheetData[0]);continue; }

            $id_number = empty($sort['number']) ? '' : $v[$sort['number']];
            if(!$id_number || !validateIDCard($id_number)){ unset($sheetData[$k]);continue; }

            //其他数据处理。。。
           
            $data[$k] = [        
                'stu_number' => empty($sort['stu']) ? '' : $v[$sort['stu']],
                'username' => empty($sort['name']) ? '' : $v[$sort['name']],
                'id_number' => $id_number,
                'phone' => empty($sort['phone']) ? '' : $v[$sort['phone']],            
                'create_time' => time()
            ];

            //看下用户是否存在。将存在的用户保存在数组里。
            foreach($had_userlist as $k2=>$v2){
                if($id_number == $v2['id_number']){
                    $data[$k]['id'] = $v2['id'];
                    array_push($usersExits, $data[$k]);
                    unset($data[$k]);
                    break;
                }
            }
        }
        
        //如果有已存在的用户名，就不插入数据库了,直接更新。
        if ($usersExits != []) {
            foreach($usersExits as $k3=>$v3){
                $udata = [          
                    'stu_number' => $v3['stu_number'],
                    'username' => $v3['username'],
                    'id_number' => $v3['id_number'],
                    'phone' => $v3['phone'],
                    'level' => $v3['level'],          
                    'sex' => $v3['sex'],         
                    'classroom_id' => $v3['classroom_id'],
                ];
                db('userinfo')->where($where)->update($udata);
            }
        }
        
        if($data){
            $res = db('userinfo')->insertAll($data);   //全部插入数据库
        }
        
        return writeJson(200, '', '导入成功！');
    }
}

Excel导出操作 (例子)

/**
 * excel表格导出(封装)
 * @param string $fileName 文件名称 $name='测试导出';
 * @param array $headArr 表头名称 $header=['表头A','表头B'];
 * @param array $data 要导出的数据 $data=[['测试','测试'],['测试','测试']]
 * @param bool $auto 是否开启根据表头自适应宽度 默认开启
 * */
public function excelExport($fileName = '', $headArr = [], $data = [], $auto = true, $format="Xlsx")
{
    $dir = rtrim(str_replace('\\', '/', $_SERVER['DOCUMENT_ROOT']), '/') . '/public/excel';
    if (!is_dir($dir)) {
        mkdir($dir, 0777, true);
    }

    $fileName .= ".".$format;
    $objPHPExcel = new Spreadsheet();
    $objPHPExcel->getProperties();
    $key = ord("A"); // 设置表头
    $key2 = ord("@"); //	超过26列会报错的解决方案

    // 居中
    $objPHPExcel->getDefaultStyle()->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
    $objPHPExcel->getDefaultStyle()->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);

    // 设置表头
    foreach ($headArr as $v) {
        // 超过26列会报错的解决方案
        if ($key > ord("Z")) {
            $key2 += 1;
            $key = ord("A");
            $colum = chr($key2) . chr($key); //超过26个字母时才会启用
        } else {
            if ($key2 >= ord("A")) {
                $colum = chr($key2) . chr($key);
            } else {
                $colum = chr($key);
            }
        }
        // 写入表头
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue($colum . '1', $v);
        // 自适应宽度
        if ($auto) {
            // $len = strlen(iconv('utf-8','gb2312',$v));//会报错
            $len = strlen(iconv('utf-8', 'gbk', $v));
            $len = $len > 25 ? 30 : 20;
            $objPHPExcel->getActiveSheet()->getColumnDimension($colum)->setWidth($len+5);
        }
        $key += 1;
    }

    $column = 2;
    $objActSheet = $objPHPExcel->getActiveSheet();
    // 写入行数据
    foreach ($data as $key => $rows) {
        $span = ord("A");
        $span2 = ord("@");
        // 按列写入
        foreach ($rows as $keyName => $value) {
            // 超过26列会报错的解决方案
            if ($span > ord("Z")) {
                $span2 += 1;
                $span = ord("A");
                $tmpSpan = chr($span2) . chr($span); //超过26个字母时才会启用
            } else {
                if ($span2 >= ord("A")) {
                    $tmpSpan = chr($span2) . chr($span);
                } else {
                    $tmpSpan = chr($span);
                }
            }
            // 写入数据
            $objActSheet->setCellValue($tmpSpan . $column, $value);
            $span++;
        }
        $column++;
    }

    // 自动加边框
    $styleThinBlackBorderOutline = array(
        'borders' => array(
            'allborders' => array( //设置全部边框
                'style' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN //粗的是thick
            ),

        ),
    );
    $objPHPExcel->getActiveSheet()->getStyle('A1:' . $colum . --$column)->applyFromArray($styleThinBlackBorderOutline);
    // 重命名表
    $fileName = iconv("utf-8", "gbk", $fileName);
    // 设置活动单指数到第一个表,所以Excel打开这是第一个表
    $objPHPExcel->setActiveSheetIndex(0);
   
    // header("Content-Type: application/octet-stream");
    if ($format == 'Xlsx') {
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    } elseif ($format == 'Xls') {
        header('Content-Type: application/vnd.ms-excel');
    }
    header("Content-Disposition: attachment;filename=$fileName");
    header('Cache-Control: max-age=0');
    //以下为需要用到IE时候设置
    // If you're serving to IE 9, then the following may be needed
    //header('Cache-Control: max-age=1');
    // If you're serving to IE over SSL, then the following may be needed
    //header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
    //header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified
    //header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
    //header('Pragma: public'); // HTTP/1.0

    $writer = IOFactory::createWriter($objPHPExcel, 'Xlsx');
    // $writer = new Xlsx($objPHPExcel);
    // $writer->save('php://output'); // 文件通过浏览器下载（不知为何直接打印出来一大堆乱码，不能直接下载）
    
    $writer->save($dir.'/'.$fileName);	// 文件下载到服务器
   
    $result = [
        'fileurl' => 'http://'.$_SERVER['HTTP_HOST'].'/public/excel/'.$fileName,
        'filename' => $fileName,
    ];
    return $result;
    exit();
}

更多的Excel操作可以去查文档或百度┗( ▔, ▔ )┛

官网文档：https://phpspreadsheet.readthedocs.io/en/stable/ （注：这是英文文档，中文的暂未找到）