TP5.1导出和导入Excel

composer require phpoffice/phpexcel

自定义导出Excel （本地测试导出5W条数据还是可以的，5秒左右。如果电脑性能好的话，可以多尝试导出点数据，10W或者20W试一试。如果在Linux上会更快，想对来说导出的数据也会更多）

public function exportExcel()
    {
        $condition = input('condition',[]);

        $condition = array_flip($condition);

        foreach ($condition as $k=>&$item){
            $item = $k;
        }

        $excelName = '用户信息表';
        $column  = [
            ['id','序号'],
            ['name','姓名'],
            ['pwd','密码'],
            ['address','地址'],
            ['is_show','是否展示'],
            ['sex','性别'],
            ['number','数值'],
            ['create_time','创建时间'],
        ];

        $field = [];

        $field[] = 'id';


        if (isset($condition['name'])){
            $field[] = 'name';
        }
        if (isset($condition['pwd'])){
            $field[] = 'pwd';
        }
        if (isset($condition['address'])){
            $field[] = 'address';
        }
        if (isset($condition['is_show'])){
            $field[] = 'is_show';
        }
        if (isset($condition['sex'])){
            $field[] = 'sex';
        }
        if (isset($condition['number'])){
            $field[] = 'number';
        }
        if (isset($condition['create_time'])){
            $field[] = 'create_time';
        }

        $userModels = Db::name('user')->field($field)->order('id desc')->limit(50000)->select();

        foreach ($userModels as &$model){
            $model['sex'] = $model['sex'] == 0 ? '女' : '男';
             if ($model['pwd'] != ''){
                 $pwd = '******';
                 $model['pwd'] = $pwd;
             }
        }

        $res = PhpExcelUtils::exportData($excelName,$column,$userModels);

        if($res){
            return json_encode(['code' => 200, 'data' => '下载成功','message' => 'success']);
        }else{
            return json_encode(['code' => 404, 'data' => '请重试', 'message' => 'error']);
        }

    }




public static function exportData($Title, $CellNameList, $TableData)
    {
        $rand_arr = array_rand($TableData, 1);
        $arr = $TableData[$rand_arr];

        $list = [];
        foreach ($CellNameList as $key => $item) {
            foreach ($arr as $k => $v) {

                if (in_array($k, $item)) {
                    $list[] = $item;
                }
            }
        }
        $CellNameList = $list;

        $xlsTitle = iconv('utf-8', 'gb2312', $Title);  // excel标题
        $fileName = $Title;                  // 文件名称
        $cellNum = count($CellNameList);    // 单元格名 个数
        $dataNum = count($TableData);       // 数据 条数

        $obj = new \PHPExcel();

        $originCell = [                           // 所有原生名
            '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'
        ];

        //getActiveSheet(0) 获取第一张表
        $obj->getActiveSheet(0)
            ->mergeCells('A1:' . $originCell[$cellNum - 1] . '1');       //合并单元格A1-F1 变成新的A1

        $obj->getActiveSheet(0)->setCellValue('A1', $fileName);        // 设置第一张表中 A1的内容


        $obj->getActiveSheet()->getColumnDimension()->setWidth(25);        //设置单元格宽度
        $obj->getActiveSheet()->getDefaultRowDimension()->setRowHeight(25);  //设置单元格行高

        for ($i = 0; $i < $cellNum; $i++) {                                     // 设置第二行 ,值为字段名
            $obj->getActiveSheet(0)
                ->setCellValue($originCell[$i] . '2', $CellNameList[$i][1]);      //设置 A2-F2 的值
        }

        // Miscellaneous glyphs, UTF-8  循环写入数据
        for ($i = 0; $i < $dataNum; $i++) {
            for ($j = 0; $j < $cellNum; $j++) {                         // 设置第三行 ,每一行为 数据库一条数据
                $obj->getActiveSheet(0)                                 // 设 A3 值, 值为$TableData[0]['id']
                ->setCellValue($originCell[$j] . ($i + 3), $TableData[$i][$CellNameList[$j][0]]);
            }
        }
        //居中
        $obj->getActiveSheet(0)->getDefaultStyle()->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        ob_end_clean();//这一步非常关键，用来清除缓冲区防止导出的excel乱码
        header('pragma:public');
        header('Content-type:application/vnd.ms-excel;charset=utf-8;name="' . $xlsTitle . '.xlsx"');
        header("Content-Disposition:attachment;filename=$fileName.xlsx");   //"xls"参考下一条备注
        $objWriter = \PHPExcel_IOFactory::createWriter($obj, 'Excel2007');

        //"Excel2007"生成2007版本的xlsx，"Excel5"生成2003版本的xls 调用工厂类
        return $objWriter->save('php://output');
    }

导入Excel。注：自定义导入还未实现------

public function uploadExcel()
    {
        header("content-type:text/html;charset=utf-8");

        $file = request()->file('excel');
        $info = $file->validate(['size' => 1048576, 'ext' => 'xls,xlsx'])->move('./excels');    //文件上传

        if ($info){

            $fileName = $info->getSaveName();                    //文件名
            $filePath = app()->getRootPath().'public/' . 'excels/' . $fileName  ;          //绝对路径
            $suffix = $info->getExtension();                     //后缀

            //判断哪种类型
            if ($suffix == "xlsx")
                $reader = \PHPExcel_IOFactory::createReader('Excel2007');
            else
                $reader = \PHPExcel_IOFactory::createReader('Excel5');   //xls
        }else{
            return $file->getError();
        }


        $excel = $reader->load($filePath, $encode = 'utf-8');       //载入excel文件

        $res = PhpExcelUtils::uploadDatas($excel);                         //导入数据库

        if($res){
            return json_encode( ['code' => 200, 'data' => '导入成功','message' => 'success']);
        }else{
            return json_encode(['code' => 404, 'data' => '请重试', 'message' => 'error']);
        }
    }



public static function uploadDatas($excel)
    {
        //读取第一张表
        $sheet = $excel->getSheet(0);
        //获取总行数
        $row_num = $sheet->getHighestRow();
        //获取总列数
        $col_num = $sheet->getHighestColumn();

        $a_z = [];
        for ($i = 'A'; $i <= $col_num; $i++){
            $a_z[] = $i;
        }

        $datas = [];
        for ($i = 3; $i <= $row_num; $i++) {                        //i=2是为了知道是哪个列的，好插入字段
            foreach ($a_z as $item){
                $datas[$i][$item] = $sheet->getCell($item . $i)->getValue();
            }
        }

        $data = [];
        foreach ($datas as $key => $value) {
            $data[$key]['id'] = $value['A'];
            $data[$key]['name'] = $value['B'];
            $data[$key]['pwd'] = $value['C'];
            $data[$key]['sex'] = ($value['D'] == '女') ? 0 : 1;
            $data[$key]['create_time'] = $value['E'];
        }

        foreach ($data as $item){
            $userModel = Db::name('user')->where([['id','=',$item['id']]])->find();
            if ($userModel){
                $item['id'] = $userModel['id'];
                 $res = Db::name('user')->where([['id','=',$item['id']]])->update($item);
                 var_dump($res);
            }else{
                 $res = Db::name('user')->insert($item);
            }
        }

        return $res;

    }