ThinkCMF结合PhpOffile 实现导入导出

墨蓝 2022-11-26 01:50 269阅读 0赞

### 安装PhpOffile ###

composer require phpoffice/phpspreadsheet

### 导出excel文件到本地 ###

/** * 导出文件到本地 */
    public function output(){ 
        $model = new GraveModel();
        $data = $model->field('id,sn,open_name,mobile,status,dead_name1,dead_name2,type,temp_id')->all();
    
        $newData = [];
        foreach ($data as $key=>$val){ 
            $newData[] = [
                'id' => $val['id'],
                'sn' => $val['sn'],
                'type' => $val['type'] == 1 ? '单墓' : '双墓',
                'open_name' => $val['open_name'],
                'mobile' => $val['mobile'],
                'dead_name' => $val['dead_name1'].' '.$val['dead_name2'],
                'status' => $val['status'] == 1 ? '已领取' : '未领取'
            ];
        }
       
        $title = ['id','墓穴编号','类型','墓穴开通者','手机号码','亡者姓名','状态'];
    
        $filename = '墓穴数据';
    
        FileProcess::downFile($title,$newData,$filename);
    }

### 导入Excel数据到数据库 ###

/** * 导入墓穴数据【编号，亡者名称】 */
    public function import(){ 
       //先把文件上传到本地
       $file = $this->request->file('myFile');
       $info = $file->move('./upload/xsl');
       if(!$info){ 
           $this->error($file->getError());
       }
    
       $fileurl =  WEB_ROOT.'upload/xsl/'.$info->getSaveName();
       $data = FileProcess::toArray($fileurl);
    
       //检查编号有没有重复的，有的话就返回错误
       $snArr = [];
       $dataArr = []; //数据库的数据
       foreach ($data as $key=>$val){ 
           $snArr[] = $val[0];
           $dataArr[] = [
               'sn' => $val[0],
               'dead_name1' => $val[1]
           ];
       }
    
       $res = GraveModel::where('sn','in',$snArr)->find();
       if($res){ 
           $this->error('有重复的编号，不可导入');
       }
    
       //导入所有的数据到数据库
       $res = GraveModel::insertAll($dataArr);
    
       if(!$res){ 
           $this->error('导入失败');
       }
       $this->success('导入成功');
    }

### FileProcess 类 ###

<?php
    /** * Created by PhpStorm. * User: Administrator * Date: 2020/8/10 * Time: 11:01 */
    
    namespace app\portal\service;
    
    use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
    use PhpOffice\PhpSpreadsheet\IOFactory;
    use PhpOffice\PhpSpreadsheet\Spreadsheet;
    use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
    
    class FileProcess
    { 
        /** * 文件 转 Array * @param string $filePath 文件路径 * @return array * @throws \PhpOffice\PhpSpreadsheet\Exception */
        public static function toArray($filePath)
        { 
    
            $spreadsheet = IOFactory::load($filePath);// 载入excel表格
    
            $worksheet = $spreadsheet->getActiveSheet();
    
            $highestRow = $worksheet->getHighestRow(); // 总行数
            $highestColumn = $worksheet->getHighestColumn(); // 总列数
    
            $highestColumnIndex = Coordinate::columnIndexFromString($highestColumn);
    
            $data = [];
            for ($row = 2; $row <= $highestRow; ++$row) {  // 从第二行开始
                $row_data = [];
                for ($column = 1; $column <= $highestColumnIndex; $column++) { 
                    $row_data[] = $worksheet->getCellByColumnAndRow($column, $row)->getValue();
                }
                $data[] = $row_data;
            }
    
            return $data;
        }
    
        /** * Array 转 文件 * @param array $title 标题 格式['title1', 'title2'] * @param array $data 导出数据 ['A1' => '***', 'B2' => '***'] * @param string $fileName 导出文件名称 * @param array $options 配置 [ 'fileType' => 'xls', 导出文件后缀 默认 xls * 'savePath' => '/web' 自定义保存地址 * ] * @return bool * @throws \PhpOffice\PhpSpreadsheet\Exception * @throws \PhpOffice\PhpSpreadsheet\Writer\Exception */
        public static function toFile($title, $data, $fileName, $options = [])
        { 
    
            /** 设置转义格式 */
            if (isset($options['fileType']) && !in_array($options['fileType'], ['xls', 'xlsx', 'ods', 'csv', 'html', 'tcpdf', 'dompdf', 'mpdf'])) { 
                return false;
            }
    
            $spreadsheet = new Spreadsheet();
    
            $worksheet = $spreadsheet->getActiveSheet();
            //设置标题名称
            $worksheet->setTitle($fileName);
    
            foreach ($title as $key => $value) { 
                $worksheet->setCellValueByColumnAndRow($key + 1, 1, $value);
            }
    
            $row = 2; //第二行开始
            foreach ($data as $item) { 
                $column = 1;
                foreach ($item as $value) { 
                    $worksheet->setCellValueByColumnAndRow($column, $row, $value);
                    $column++;
                }
                $row++;
            }
    
            /** 设置文件后缀名 */
            if (!isset($options['fileType'])) { 
                $fileName = $fileName . '.xls';
            } else { 
                $fileName = $fileName . '.' . $options['fileType'];
            }
    
            /** 设置文件路径 */
            if (!isset($options['savePath'])) { 
                //$savePath = \Yii::$app->basePath . '/web/' . $fileName;
            } else { 
                $savePath = $options['savePath'] . $fileName;
            }
    
            /** @var string $writerType 转义成PhpSpreadsheet能识别的后缀 */
            $writerType = ucfirst($options['fileType']);
    
            $writer = IOFactory::createWriter($spreadsheet, $writerType);
    
            $writer->save($savePath);
    
            /* 释放内存 */
            $spreadsheet->disconnectWorksheets();
            unset($spreadsheet);
            ob_end_flush();
    
            return true;
        }
    
        /** * 导出文件下载到本地 * @param $title ['title1','title2'] * @param $data array 对应的数据 * @param $filename string 导出的文件名 */
        public static function downFile($title,$data,$filename){ 
    
            $spreadsheet = new Spreadsheet();
            $sheet = $spreadsheet->getActiveSheet();
    
            //设置工作表标题名称
            $sheet->setTitle($filename);
            $sheet->getDefaultColumnDimension()->setWidth(18);
            $sheet->getDefaultRowDimension()->setRowHeight(25);
            //设置单元格内容 表头
            foreach($title as $key => $value){ 
                $sheet->setCellValueByColumnAndRow($key + 1, 1, $value);
            }
            $row = 2; //从第二行开始
            //设置单元格内容
            foreach ($data as $item) { 
                $column = 1;
                foreach ($item as $value) { 
                    $sheet->setCellValueByColumnAndRow($column, $row, $value);
                    $column++;
                }
                $row++;
            }
            $fileType = 'Xlsx';
    
            // 输出到浏览器下载
            header('Content-Type: application/vnd.ms-excel');
            header('Content-Disposition: attachment;filename='.$filename.'.'.$fileType.'');
            header('Cache-Control: max-age=0');
            $writer = new Xlsx($spreadsheet);
            $writer->save('php://output');
        }
    
        /** * 导入文件插入数据库 * @param string $tableName 表名 * @param array $filed 字段名 [ 'create_id', 'create_time' ] * @param string $filePath 文件路径 * @return bool|int * @throws \PhpOffice\PhpSpreadsheet\Exception * @throws \yii\db\Exception */
        public static function toSql($tableName, $filed, $filePath)
        { 
            $params = self::toArray($filePath);
    
            //$result = \Yii::$app->db->createCommand()->batchInsert($tableName, $filed, $params)->execute();
    
            //return $result;
        }
    }

### 前端文件 ###

//导入
    <input type="file" id="xslFile" style="display:none">
    <button type="button" class="btn btn-info btn-sm" onclick="F_Open_dialog()">导入</button>
    
    <script>
        function F_Open_dialog()
        { 
            document.getElementById("xslFile").click();
        }
    
        $(function(){ 
            $('#xslFile').change(function(res){ 
                var formData = new FormData();
                formData.append('myFile', $('#xslFile')[0].files[0]);
                $.ajax({ 
                    url:"{:url('import')}",
                    type:"POST",
                    data:formData,
                    processData: false,
                    contentType: false,
                    success:function(res){ 
                        if(res.code == 1){ 
                            noty({ 
                                text: "导入成功",
                                type: 'success',
                                layout: 'center',
                                callback: { 
                                    afterClose: function () { 
                                        reloadPage(window);
                                    }
                                }
                            });
                        }else{ 
                            noty({ 
                                text: res.msg,
                                type: 'error',
                                layout: 'center',
                                callback: { 
                                    afterClose: function () { 
                                        reloadPage(window);
                                    }
                                }
                            });
                        }
                    },
                    fail:function(res){ 
                        noty({ 
                            text: '导入失败',
                            type: 'error',
                            layout: 'center',
                            callback: { 
                                afterClose: function () { 
                                    reloadPage(window);
                                }
                            }
                        });
                    }
                });
            })
        })
    </script>
    
    
    //导出
    <a class="btn btn-default btn-sm " href="{:url('output')}" data-subcheck="true">导出
    </a>