一個通用的匯入匯出excel的思路

本文中的程式碼示例都使用的Yii2，其他框架可能不適用，不過可以參考下思路。

本人經常給企業做專案，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]);
    }
}

本作品採用《CC 協議》，轉載必須註明作者和本文連結