yii excel上传验证

来源：互联网发布：snapchat网络无法连接编辑：程序博客网时间：2024/06/01 09:58
<?php/** * @desc    上传excel业务逻辑层 * @author  ezreal.yin * @date    2016/05/18 */class Import extends ImportBase{    private $table_filed;    /**     * 导入Excel     * @author  ezreal.yin     * @param   array $phpExcel phpexcel对象     * @param   bool $userInfo 用户信息     * @return  string     */    public function ImportChange($phpExcel, $userInfo)    {        $this->phpExcel = $phpExcel;        $this->userInfo = $userInfo;        $this->validationRule = $this->validationBaseRule('product');   // 获取验证规则        $this->table_filed = $this->getFiled('table_name'); //获取表字段值        $total_column = $phpExcel->getHighestColumn();  //获取excel表总列数        $total_row = $phpExcel->getHighestRow();   //获取excel表总行数        $ImportLogArr = array();    //获取的excel数据集合        $product_arr = array('W', 'X'); //产品相关行        $merge = $this->getMergeCell(2);    //获取Excel合并的信息        // 处理基本数据        if(!empty($merge)) {            foreach ($merge as $row => $v) {                for ($column = 'A'; $column <= $total_column; $column++)    // 循环列                {                    if (in_array($column, $product_arr)) //处理产品                    {                        for ($pro_row = $row; $pro_row <= $v['end_row']; $pro_row++) {                            $value = $this->getValue($column, $pro_row);                            $ImportLogArr[$row][$column . $pro_row] = $this->checkFiled($column, $value); //验证产品规则                        }                    } else {                        $value = $this->getValue($column, $row);                        $ImportLogArr[$row][$column] = $this->checkFiled($column, $value); //验证规则                        $ImportLogArr[$row][$column]['rowspan'] = $v['end_row'] - $v['start_row'] + 1;                    }                }            }        }        else        {            for ($row = 2; $row <= $total_row; $row++)    // 循环行,从第2行开始处理数据            {                for ($column = 'A'; $column <= 'Z'; $column++)    // 循环列                {                    $value = $this->getValue($column, $row);                    $ImportLogArr[$row][$column] = $this->checkFiled($column, $value); //验证规则                    if($column == 'Z'){echo '<pre>';print_r($ImportLogArr);exit;}                }            }        }        $result = $this->ProcessData($ImportLogArr);    //把数据转成html格式        return $result;    }    /**     * 导入海外仓退换货Excel     * @author  ezreal.yin     * @param   array $phpExcel phpexcel对象     * @param   bool $userInfo 用户信息     * @return  string     */    public function ImportsChange($phpExcel, $userInfo)    {        $this->phpExcel = $phpExcel;        $this->userInfo = $userInfo;        $this->validationRule = $this->validationBaseRule('oms_change');   // 获取验证规则        $this->table_filed = $this->getFiled('gzc_warehouse_user_product'); //获取表字段值        $total_column = $phpExcel->getHighestColumn();  //获取excel表总列数        $ImportLogArr = array();    //获取的excel数据集合        $product_arr = array('R', 'S');        $merge = $this->getMergeCell(2);    //获取Excel合并的信息        // 处理基本数据        foreach ($merge as $row => $v)        {            for ($column = 'A'; $column <= $total_column; $column++)    // 循环列            {                if(in_array($column, $product_arr)) //处理产品                {                    for($pro_row = $row;$pro_row<=$v['end_row'];$pro_row++)                    {                        $value = $this->getValue($column, $pro_row);                        $ImportLogArr[$row][$column.$pro_row] = $this->checkFiled($column, $value); //验证产品规则                    }                }                else                {                    $value = $this->getValue($column, $row);                    $ImportLogArr[$row][$column] = $this->checkFiled($column, $value); //验证规则                    $ImportLogArr[$row][$column]['rowspan'] = $v['end_row'] - $v['start_row'] + 1;                }            }        }        $result = $this->ProcessData($ImportLogArr);    //把数据转成html格式        return $result;    }}

<?php/** * @desc    上传excel基类 * @author  ezreal.yin * @date    2016/05/16 */class ImportBase extends ImportConfig{    protected $validationRule;  //验证规则    /**     *  获取数据表字段     * @param   string  表名     * @return  array   表字段数组     */    protected function getFiled($table_name)    {        $sql = "SHOW FULL COLUMNS FROM " . $table_name;        $fields = Yii::app()->db->createCommand($sql)->queryColumn();        unset($fields[0]);//去除主键自增字段        $fields = array_flip($fields);        $new_fields = array();        foreach ($fields as $field => $value)        {            $new_fields[$field] = NULL;        }        return $new_fields;    }    /**     * 获取Excel所有合并的信息     * @param   int  $row   从第几行开始     * @return  array   $merge  合并的数据集合     */    protected function getMergeCell($row)    {        $merge_info = $this->phpExcel->getMergeCells();        $merge = array();        foreach ($merge_info as $val)        {            $temp = explode(':', $val);            if (strpos($temp[0], 'A') !== false && intval(substr($temp[0], 1)) >= $row)            {                $temp_arr['start_row'] = intval(substr($temp[0], 1));                $temp_arr['end_row'] = intval(substr($temp[1], 1));                $merge[$temp_arr['start_row']] = $temp_arr;            }        }        return $merge;    }    /**     * 获取Excel单元格的值     * @param   string  $column  从第几列     * @param   int     $row     从第几行     * @return  string   Excel单元格的值     */    protected function getValue($column, $row)    {        return trim($this->phpExcel->getCell($column . $row)->getValue());    }    /**     * 验证规则，返回信息     * @param   string  $column 列     * @param   string  $value  值     * @return  array   $result     验证后的信息集合     */    protected function checkFiled($column, $value)    {        $validationRule = $this->validationRule;    //获取验证规则        //初始化返回信息        $ImportLog_arr['field'] = $validationRule[$column]['field'];        $ImportLog_arr['value'] = $value;        $ImportLog_arr['back_value'] = '';        $ImportLog_arr['message'] = '';        //验证数据合法性        if(!empty($validationRule[$column]['rule'])) {            foreach ($validationRule[$column]['rule'] as $action) {                if ($action == 'data_matching') {                    $data_source = $validationRule[$column]['data_source'];                    $check_result = $this->$action($data_source, $value);                } else {                    $check_result = $this->$action($value);                }                if (isset($check_result['message'])) {                    $ImportLog_arr['message'] = $check_result['message'];                    break;                }                if (isset($check_result['back_value'])) {                    $ImportLog_arr['back_value'] = $check_result['back_value'];                }            }        }        return $ImportLog_arr;    }}

<?php/** * @desc    上传excel配置类 * @author  ezreal.yin * @date    2016/05/16 */class ImportConfig{    protected $userInfo;    //用户信息    protected $phpExcel;    //Excel对象    /**     * 验证规则     *     * @return  array   计费单位     */    protected function validationBaseRule($type)    {        $validations = array(            'product' => array(                'A'=>array('field'=>'product_sn', 'rule'=>array('not_empty', 'sku_unique')), //产品sku                'B'=>array('field'=>'bar_code', 'rule'=>array('is_number')), //条形码                'C'=>array('field'=>'product_type', 'rule'=>array('data_matching'), 'data_source'=>$this->getProductType()), //产品类型                'D'=>array('field'=>'product_name', 'rule'=>array()), //产品中文名称                'E'=>array('field'=>'product_name_en', 'rule'=>array()), //产品中文名称                            ),        );        return $validations[$type];    }    /**     * 错误信息配置     * @param   string  $type   错误类型     * @return  string   错误信息     */    private function error_msg($type){        $result = array(            'is_empty'=>'不许为空',            'sku_unique'=>'已经添加过此sku的产品',            'data_exist'=>'无法对应到系统的数据',            'data_matching_error'=>'数据匹配错误',            'not_number'=>'数据必须为数字',            'not_date'=>'数据日期格式错误(正确格式YYYY-MM-DD)', //暂时没用            'expiration_time'=>'数据有效期过期',            'date_matching_error'=>'数据起始日期大于终止日期',        );        return $result[$type];    }    /**     * 判断是否与规则后面的data_source匹配     *     */    protected function data_matching($data_source, $value)    {        if (isset($data_source[$value]))        {            $result['back_value'] = $data_source[$value];        }        else        {            $result['message'] = $this->error_msg('data_matching_error');        }        return $result;    }}
0 0