优化下项目中的Excel导入

由于业务需要，项目涉及到导入电费、租金、服务费、协调费，老代码还是比较臃肿、不易维护的，做了一定优化，采用策略模式。

类图：

ImportExcel来做Excel文件的基础操作，以及根据解析结果返回固定格式消息及数据；

ImportExcelStrategyInterface定义了解析策略的标准方法级函数；

ImportExcelStrategyBase定义了解析策略的通用方法；

ImportExcelStrategyDFPayAll/ImportExcelStrategyZJAll/ImportExcelStrategyDFPaybackAll是具体的解析策略；

class ImportExcel
{
    private $_filePath;
    private $_importStrategy;

    public function __construct($filePath, $importStrategy)
    {
        $this->_filePath = $filePath;//上传文件地址,含文件名
        $this->_importStrategy = $importStrategy;//数据解析策略
    }

    /**
     * 保存数据到数据库
     * @return mixed
     */
    public function getData(){
        if(is_file($this->_filePath)) {
            $objPHPExcel = \PHPExcel_IOFactory::load($this->_filePath);
            $data = $objPHPExcel->getActiveSheet()->toArray();

            $newData = $this->_importStrategy->fatchImportData($data);

            if(!$newData['data'] && !$newData['dataAdd']){
                return ['code' => 100, 'message' => '没有导入任何数据'];
            }

            return ['code' => 200, 'message' => $newData];
        }else {
            return ['code' => 101, 'message' => '导入的文件不存在！'];
        }
    }
}

interface ImportExcelStrategyInterface
{
    public function fatchImportData($data);
}

class ImportExcelStrategyBase
{
    protected $checkLabels;

    /**
     * @param mixed $checkLabels
     */
    protected function setCheckLabels($checkLabels)
    {
        $this->checkLabels = $checkLabels;
    }//需要校验非空的数据列名称: ['本期抄表度数', '上期抄表度数']


    /**
     * @param array $importDataLables //导入的列名称
     * @param array $rowData //导入的当前行数据
     * @return bool
     */
    protected function _checkLabels(array $importDataLables = [], array $rowData = []){
        foreach($this->checkLabels as $checkLabel){
            $labelIndex = array_search($checkLabel, $importDataLables);
            if(array_key_exists($labelIndex, $rowData) === false || !$rowData[$labelIndex])return false;
        }

        return true;
    }

    /**
     * string to date
     * @param $data
     * @return Date|string
     */
    protected function _stringToDate($data){
        return empty($data) ? '' : new Date($data);
    }
}

class ImportExcelStrategyDFPayAll extends ImportExcelStrategyBase implements ImportExcelStrategyInterface
{
    public function fatchImportData($data)
    {
        $this->setCheckLabels([
            '周期开始日期',
            '周期结束日期',
            '上期抄表度数',
            '本期抄表度数',
            '应付金额',
        ]);

        //导入数据
        $arrUpdate = [];//更新数据
        $arrAdd = [];//新增数据
        $ids = [];
//        return ['data' => $arrUpdate, 'dataAdd' => $arrAdd, 'ids' => $ids];

        $len = count($data);
        if ($len < 1) return [];
        for ($i = 1; $i <$len; $i++) {
            if ($this->_checkLabels($data[0], $data[$i])) {
                $tmpArr = array(
                    'id' => (int)$data[$i][0],
                    'pay_period' => (int)$data[$i][6],//付款期数
                    'period_start_date' => $this->_stringToDate($data[$i][8]),//周期开始日期
                    'period_end_date' => $this->_stringToDate($data[$i][9]),//周期结束日期
                    'pre_meter_num' => $data[$i][10],//上期抄表度数
                    'meter_num' => $data[$i][11],//本期抄表度数
                    'payable_amount' => $data[$i][13],//应付金额
                    'is_deduction' => $data[$i][14] === '是'?1:0,//是否扣点
                    'deduction_tax' => (int)$data[$i][15],//扣点税率
                    'deduction_amount' => $data[$i][16],//扣点金额
                    'created' => $this->_stringToDate($data[$i][17]),//申请日期
                    'paied_amount' => $data[$i][18],//实付金额
                    'paied_date' => $this->_stringToDate($data[$i][19]),//实付日期

                    'invoice_type' => $data[$i][20] == '专票'? 2:1,//发票类型,2专票,1普票
                    'billing_amount' => (float)$data[$i][21],//开票金额
                    'billing_date' => $this->_stringToDate($data[$i][22]),//开票日期
                    'billing_num' => $data[$i][23],//票号
                    'tax_account' => $data[$i][24],//进项税
                    'remark' => $data[$i][26],//备注
                    'status' => 1//状态默认为应付
                );

                //账户信息
                $accountArray = explode('/', $data[$i][25]);
                if(count($accountArray)>2){
                    $tmpArr['account_name'] =$accountArray[0];
                    $tmpArr['account_bank'] =$accountArray[1];
                    $tmpArr['account_num'] =$accountArray[2];
                }

                //去除空时间字段
                if($tmpArr['period_start_date'] === '')unset($tmpArr['period_start_date']);
                if($tmpArr['period_end_date'] === '')unset($tmpArr['period_end_date']);
                if($tmpArr['created'] === '')unset($tmpArr['created']);
                if($tmpArr['paied_date'] === '')unset($tmpArr['paied_date']);
                if($tmpArr['billing_date'] === '')unset($tmpArr['billing_date']);

                if ((float)$data[$i][15])
                    $tmpArr['status'] = 2;//更新状态为"实收"

                if (!empty($data[$i][0])){//更新数据
                    $ids[] = (int)$data[$i][0];
                    $arrUpdate[] = $tmpArr;
                }else{//新增数据
                    unset($tmpArr['id']);//去掉id
                    $tmpArr['contract_internal_num'] = $data[$i][1];//合同内部编号
                    $tmpArr['project_name'] = $data[$i][2];//项目名称
                    $arrAdd[] = $tmpArr;
                }
            }
        }
        return ['data' => $arrUpdate, 'dataAdd' => $arrAdd, 'ids' => $ids];
    }
}

Controller中使用：

$excelFile = $filePath . $fileName;
$importExcel = new ImportExcel($excelFile, new ImportExcelStrategyDFPayAll());
$importData = $importExcel->getData();

//没有导入任何数据
if ($importData['code'] !== 200) {
     $this->Flash->error($importData['message']);
     return $this->redirect(['action' => 'importPay']);
}