一个通用的导入导出excel的思路

nicowang 的个人博客 / 0 / 8 / 创建于 8个月前 / 更新于 8个月前

本文中的代码示例都使用的Yii2，其他框架可能不适用，不过可以参考下思路。

本人经常给企业做项目，excel导入导出的需求实在是不少，最近又收到了excel导出的一个需求，看项目组的小伙又是焦头烂额的，决定优化一下实现一个通用的导入导出的方法。由于精力有限，本文仅提供一个大概的思路，有些功能还未能完全实现。

当前导入导出的一些问题

下面列出了多年了，系统中的导入导出功能的痛点和解决的方案：

问题	解决思路
导出的excel如果客户要调整字段，非常麻烦，因为要依赖于硬编码，而且要同时改导入模板	可以根据字段动态导出excel模板
具备导出功能的接口遍布系统各处，难以统一管理	所有导出的地方应该一个地方统一管理，动态开关
导入的模板不是动态生成的，每次字段修改都需要重新上传模板	空白模板应该由程序动态生成
用户无法确定导入的内容范围，同时无法定位具体的错误行数，无法实现部分导入功能（即只导入数据验证正确的数据，不导入错误的）	提供导入预览功能
研发经验无法积累，每做一个导出功能都是重新开发	提供通用方法给前端，长期维护一套导出代码
用户导入失败时数据不入库，只能用户本人发excel过来发现问题	excel导入时，数据校验失败的也应存储，方便查证问题
数据量大时，导出时间太久	导出与下载应该分离开
模版更新后，用户使用旧版模版导入时报错	应提供模版版本检测

而其中还需要注意的就是导入的本质其实就是表单，应该跟页面上的表单验证走同一个方法同等的校验，而不是单独校验，导致两边的校验规则不一（这个实际中遇到过，导入时直接操作数据库，不做同等导致比页面上的表单权限大，产生错误），导出的本质就是从数据库显示数据而已。

表设计

为了解决上面的问题，我想到的思路就是利用数据库记录导入导出的字段和导入的日志。
这里面我用三个表实现这个需求:

1 导入日志表

这个表格比较简单，phase记录一个uuid，一次导入动作记录一次，field对应excel中的字母列，row对应行数，value就是值，category用于区分不同的导入模版：

2 活动记录表

这个表也非常简单，excel_name这里记录的就是excel文件的名称，parent_id sheet_name字段这里是考虑到了导出多个sheet到一个excel中的需求。重点关注model字段，这个是记录Yii active record表的命名空间的路径字段。

3 字段规则表

这个表比较关键，这里记录了导入导出的规则。考虑导出的话，我们使用field字段作为字母列，original_field是模型的动态属性，同时因为会有关联数据所以我们应该支持gmm->project_id这种写法，意思就是取关联对象中的某一属性。

代码实现

下面是一些代码实现的示例，重点提供一个思路，有些考虑还不到位。，

导出空白模板方法

这个是读取数据库中设置的规则，导出一个空白模板供用户填入信息，同时设置了一些简单的样式，例如合并单元格显示表格标题，有数据的列都是具备边框。

public function actionTemplate()
    {
        // 查到规则表 是否存在
        $params = Yii::$app->request->get();
        $model = GmmImportField::find()->where(['category'=>$params['category'], 'type' => 1])->all();
        $table = GmmImport::find()->where(['category'=>$params['category']])->one();
        if(!$table){
            throw new ServerErrorHttpException('未设定导入模版');
        }

        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $sheet->getRowDimension(1)->setRowHeight(40);
        // 设置大标题的样式
        $ATitle = $sheet->getCell('A1');                 /
        $ATitle->getStyle('A1')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER)->setVertical(Alignment::VERTICAL_CENTER);        // 内容水平居中
        $ATitle->getStyle('A1')->getFont()->setSize(22)->setBold(true);                              // 字体大小，加粗
        $ATitle->setValue($table->excel_name);

        // 设置有数据的列都增加边框，同时设置prompt字段
        $col_start = 'A';
        foreach($model as $index=>$item){
            $sheet->setCellValue($item->field.'2', $item->label);
            $validation = ($sheet->getCell($item->field.'2'))->getDataValidation();
            $validation->setShowInputMessage(true);
            $validation->setPrompt($item->prompt);
            $column = $sheet->getColumnDimension($item->field);
            $column->setWidth(20);
            if($index==0){
                $col_start = 'A';
            }else{
                $col_start++;
            }
        }

        // 设置边框
        $sheet->getStyle('A1:'.$col_start.'22')->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THIN);
        $sheet->mergeCells('A1:'.$col_start.'1');            // 合并单元格

        // 操作完成导出excel下载到浏览器
        $writer = new Xlsx($spreadsheet);
        $filename = $table->excel_name.".xlsx";
        header('Content-Type:application/vnd.ms-excel');
        header('Content-Disposition:attachment;filename=' . $filename);
        header('Cache-Control:max-age=0');
        $writer->save('php://output');

        $spreadsheet->disconnectWorksheets();
        unset($spreadsheet);
        exit;
    }

导入预览功能的实现

这部分代码中，我们将实现两个功能，一个是将前端传来的excel先暂存到导入记录表中，先不论数据验证通过与否全部入库，同时将这些数据按照返回给前端，我们这边里商定的是使用and design的表格的数据格式。

    /**
     * 导入预览
     * 存入导入记录表中，读取出数据
     */
    public function actionImportPreSheet()
    {
        $params = Yii::$app->request->get();
        $reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx();
        $spreadsheet = $reader->load($_FILES['file']['tmp_name']);

        $field_group = GmmImport::find()->where(['category'=>$params['category']])->one();
        if($field_group){
            $field_group_child = GmmImport::find()->where(['parent_id'=>$field_group->id])->orWhere(['id'=>$field_group->id])->all();
        }else{
            $field_group_child = $field_group;
        }

        $batchArray = [];

        # 期数
        $phase = Uuid::uuid4()->toString();

        foreach ($field_group_child as $k=>$v) {
            $fields=GmmImportField::findAll(['category'=>$v->category, 'type' => 1]);
            $last_letter= 'A';
            foreach($fields as $field){
                $last_letter ++;
            }
            $spreadsheet->setActiveSheetIndex($k);
            $sheetDataCount = $spreadsheet->getActiveSheet()->toArray();
            $last_row =count($sheetDataCount);
            $sheetData = $spreadsheet->getActiveSheet()->rangeToArray(
                'A3:'.$last_letter.$last_row,
                null,        // Value that should be returned for empty cells
            true,        // Should formulas be calculated (the equivalent of getCalculatedValue() for each cell)
            true,        // Should values be formatted (the equivalent of getFormattedValue() for each cell)
            true
            );
            $fields=[];
            foreach (array_values($sheetData)[0]  as $key=>$item) {
                $fields[]=$key;
            }
            foreach ($sheetData as $key =>$item) {
                $item_arr = array_values($item);
                if (empty(array_filter($item_arr))) {
                    continue;
                }
                $num =0;
                foreach ($fields as $field) {
                    $batchArray[] =[
                    'field'=>$field,
                    'value'=>$item_arr[$num],
                    'row'=>$key,
                    'phase'=>$phase,
                    'operator_id'=>Yii::$app->user->id,
                    'category'=>$v->category,
                ];
                    $num +=1;
                }
            }
        }


        Yii::$app->db->createCommand()->batchInsert('gmm_import_model_log', ['field', 'value','row','phase','operator_id','category'], $batchArray)->execute();

        $rows =  GmmImportLog::findAll(['phase'=>$phase]);
        $rows_arr =  ArrayHelper::index(ArrayHelper::toArray($rows),null,'category');
        $dataSourceArr = [];
        foreach ($rows_arr as $category => $sheet_item) {
            $sheet_model = GmmImport::findOne(['category'=>$category]);

            $row =  ArrayHelper::index(ArrayHelper::toArray($sheet_item), null, 'row');
            $dataSourceArrOne = [];
            foreach ($row as $item) {
                $dataSource = [];
                foreach ($item as $v) {
                    $dataSource[$v['field']] =$v['value'];
                }
                $multi_rows_arr[] = $dataSource;
            }
            $dataSourceArrOne['dataSource']=$multi_rows_arr;
            $dataSourceArrOne['category'] = $sheet_item[0]['category'];
            $dataSourceArrOne['sheet_name'] = $sheet_model->sheet_name;
            $dataSourceArr[] = $dataSourceArrOne;
        }

        $columns=GmmImportFieldIndex::find()->where(['like','category',$params['category'],'type'=>1])->orderBy(['field'=>SORT_ASC])->all();
        $columns_arr =  ArrayHelper::index(ArrayHelper::toArray($columns),null,'category');
        foreach($dataSourceArr as $key =>&$item){
            $dataSourceArr[$key]['columns'] = $columns_arr[$item['category']];
            unset($item['category']);
        }
        return $dataSourceArr;
        return [
                'dataSource'=>$dataSourceArr,
                'columns'=>$columns,
                'phase'=>$phase
        ];

    }

实际导入功能

由于上面我们将导入预览和真正的导入分开了，这里还需要实现一个真正往业务数据库中写入数据的方法

    public function actionImportConfirm()
    {
        $params = Yii::$app->request->post();
        $rows = GmmImportLog::findAll(['phase'=>$params['phase']]);
        if($rows == null){
            throw new BadRequestHttpException('找不到导入实体');
        }
        $rows_arr =  ArrayHelper::index(ArrayHelper::toArray($rows),null,'row');
        $dataSourceArr = [];
        foreach($rows_arr as $item){
                $dataSource = [];
                foreach($item as $v){
                    $dataSource[$v['target_field']] =$v['value'];

                }
                $dataSourceArr[] = $dataSource;
        }
        $target_model = GmmImport::findOne(['category'=>$rows[0]->category]);
        $target_model_class = $target_model->model;
        foreach($dataSourceArr as $item){
            if ($item['UUID']) {
                $model = $target_model_class::find()->where(['UUID' => $item['UUID']])->one();
                if(!$model){
                    throw new BadRequestHttpException('未找到对应UUID');
                }
            } else {
                $model = new $target_model_class;
                $item['UUID'] = Uuid::uuid4()->toString();
            }
            $model->scen = 'import';
            $model->load($item,'');
            if(!$model->save()){
                throw new BadRequestHttpException(implode(' ', $model->getFirstErrors()));
            }
        }
        return true;
    }

这里是excel的导出功能，目前实现的是单个sheet的导出，多个sheet导出功能也类似，但是还不是非常完善就不贴了。注意这里导出时，仍需要增加上导出空白模板时设置的必填项规则和下拉选项等，因为有时用户需要导出一组数据，然后在这组数据上面改一下，再导入回去，这也要支持。

    public function actionExportExcel()
    {
        $params = Yii::$app->request->get();
        $model = GmmImportField::find()->where(['category'=>$params['category'],'type'=>1])->all();
        $table = GmmImport::find()->where(['category'=>$params['category']])->one();
        if(!$table){
            throw new ServerErrorHttpException('未设定导出模版');
        }
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $sheet->getRowDimension(1)->setRowHeight(40);
        // 设置标题
        $ATitle = $sheet->getCell('A1');                 // 获取单元格
        $ATitle->getStyle('A1')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER)->setVertical(Alignment::VERTICAL_CENTER);        // 内容水平居中
        $ATitle->getStyle('A1')->getFont()->setSize(22)->setBold(true);                              // 字体大小，加粗
        $ATitle->setValue($table->excel_name);

        $col_start = 'A';
        foreach($model as $index=>$item){
            $sheet->setCellValue($item->field.'2', $item->label??'');
            $validation = ($sheet->getCell($item->field.'2'))->getDataValidation();
            $validation->setShowInputMessage(true);
            if(!empty($item->prompt)){
                $validation->setPrompt($item->prompt);
            }
            $column = $sheet->getColumnDimension($item->field);
            $column->setWidth(20);
            if($index==0){
                $col_start = 'A';
            }else{
                $col_start++;
            }
        }
        $field_list = ArrayHelper::toArray($model);
        // $field_arr = array_column($field_list,'original_field');

        $field_arr =[];
        foreach($field_list as $k=>$item){
            $field_arr = array_merge($field_arr,[trim($item['original_field'])=>trim($item['field'])]);
        }

        $rows = $table->model::findItems();

        $count = count($rows);
        $count = $count+2;
        $start_row = 3;
        foreach($rows as $index => $item_obj){
            $items= $item_obj->toArray(array_keys($field_arr));
            foreach($items as $k=>$v){
                $sheet->setCellValue($field_arr[$k].$start_row, $v);
                $sheet->setCellValue($field_arr['row_number'].$start_row, $index + 1);
            }
            $start_row ++;
        }

        // 设置边框
        $sheet->getStyle('A1:'.$col_start.$count)->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THIN);
        $sheet->mergeCells('A1:'.$col_start.'1');            // 合并单元格

        $this->outputFile($spreadsheet,$table->excel_name.".xlsx");
          $writer = new Xlsx($spreadsheet);
        header('Content-Type:application/vnd.ms-excel');
        header('Content-Disposition:attachment;filename=' . $filename);
        header('Cache-Control:max-age=0');
        $writer->save('php://output');

        $spreadsheet->disconnectWorksheets();
        unset($spreadsheet);
        exit;
    }

模型的实现

这里面我为了简洁，删减了大部分字段，其中需要给外注意的是这里面实现了一个接口，这个接口里面要求每个模型都需要具备两个方法getId()和findItems()，getId()是获取原始表的主键ID，findItems可以设置查询条件，例如要查询那些特定的数据等等。

<?php
namespace app\models;
class  GmmCrawlProject extends AreaProject implements GmmPushTemplate
{
    public function fields()
    {
        return [
            'STAGE_ID',
        ];
    }

    public function getSTAGE_ID()
    {
        return $this->statusCode->code;
    }

    public function getId()
    {
        return $this->id;
    }

    public static function findItems($ids)
    {
       return  self::find()->where(['lev'=>4,'status'=>1,'project_type'=>[1,2],'id'=>$ids]);
    }
}