基于PhalApi的PHPExcel扩展实现数据导入导出

于 2019-11-01 16:43:01 发布
阅读量1k 收藏
点赞数
分类专栏： php 文章标签： PHP PHPExcel PhalApi
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_32845825/article/details/102853807
版权

前言：

最近开发一款crm，使用PhalApi框架提供数据接口，要用公共接口来实现各个模块的数据导入导出功能。如果是单独的导入导出（一个模块一个导入导出接口）推荐大家使用 phpoffice/phpspreadsheet 。

我这里使用的是 PHPExcel，PhalApi官网上关于PHPExcel扩展，是基于PhalApi1.x 版本，这里使用的是 2.x 版本，所以这里使用的跟官网的略有不同。原因一：使用一个接口处理多个模块的导入或者导出；原因二：在导出模型的时候要做下拉选择框（在phpoffice/phpspreadsheet 文档中没有找到相关导出带下拉的内容，如果有了解的小伙伴可以给我留言哟）。crm后台各个模块都有各自对应的模型字段，可以自行设置字段的类型[文本、单选、多选、下拉、日期。。。]以及字段值。

例：客户模块的“客户意向”和“客户级别”字段：

首先，需要客户在导入数据之前，下载导入模型表，有灵活字段的情况下，需要在导出模型表的时候，对灵活字段进行处理，将单元格式设置为下拉选择框，从字段设置的值中选择，使用户只能从选项中选择，从根本上保证客户上传数据的有效性。

一：准备工作

（1）下载PHPExcel文件并解压，放在phalapi/vendor 文件夹下 。

         下载地址：https://download.csdn.net/download/qq_32845825/11949643

（2）在文件头部引入PHPExcel

require dirname(dirname(dirname(__DIR__))).'/vendor/phpexcel/PHPExcel.php';
require dirname(dirname(dirname(__DIR__))).'/vendor/phpexcel/PHPExcel/Writer/Excel5.php';
require dirname(dirname(dirname(__DIR__))).'/vendor/phpexcel/PHPExcel/Writer/Excel2007.php';
require dirname(dirname(dirname(__DIR__))).'/vendor/phpexcel/PHPExcel/IOFactory.php';
require dirname(dirname(dirname(__DIR__))).'/vendor/phpexcel/PHPExcel/Cell.php';
use PHPExcel as NOP;
use PHPExcel_IOFactory;
use PHPExcel_Writer_Excel2007;
use PHPExcel_Writer_Excel5;
use PHPExcel_Cell;

二:下载数据导入模板

    // 17 导出Excel模板
    public function PostExportModelExcel($tablename) {
        // 读取数据表的字段
        $table_field = $this->config->get('common.TABLE_FIELD');
        $field_list = $table_field[$tablename];
        // 读取模型的模型ID
        $model_id = $field_list['model_id'];

        // 查询灵活字段以及值
        $model_field_arr = $this->di->model_field->select("field,name,type,is_require,setting")->where(array("modelid"=>$model_id,"master_field"=>1))->order("id DESC")->limit(15)->fetchRows();

        // 将模型字段增加到字段列表中
        if (!empty($model_field_arr)) {
            foreach ($model_field_arr as $key => $value) {
                $field_list['filed'][$value['field']]['name'] = $value['name'];
                $field_list['filed'][$value['field']]['type'] = $value['type'];
                $field_list['filed'][$value['field']]['is_require'] = $value['is_require'];
                $field_list['filed'][$value['field']]['setting'] = $value['setting'];
            }
        }
        $customer_field_list = $field_list['filed'];

        //引入phpexecl服务启动文件
        $objPHPExcel =new NOP();
        $objWriter = new \PHPExcel_Writer_Excel5($objPHPExcel);
        $objWriter = new \PHPExcel_Writer_Excel2007($objPHPExcel);
        $excelModel = new Excel();
        //设置属性
        $objProps = $objPHPExcel->getProperties();
        $objProps->setCreator("WJZ");
        $objProps->setLastModifiedBy("WJZ");
        $objProps->setTitle("WJZ");
        $objProps->setSubject("WJZ data");
        $objProps->setDescription("WJZ data");
        $objProps->setKeywords("WJZ data");
        $objProps->setCategory("WJZ");
        $objPHPExcel->setActiveSheetIndex(0);
        $objActSheet = $objPHPExcel->getActiveSheet();
        $objActSheet->setTitle($field_list['model_name'].'导入模板'.date('Y-m-d',time()));

        //填充边框
        $styleArray = [
            'borders'=>[
                'outline'=>[
                    'style'=>\PHPExcel_Style_Border::BORDER_THICK, //设置边框
                    'color' => ['argb' => '#F0F8FF'], //设置颜色
                ],
            ],
        ];

        $k = 0;
        $k = 0;
        foreach ($customer_field_list as $field) {
            $objActSheet->getColumnDimension($excelModel->stringFromColumnIndex($k))->setWidth(20); //设置单元格宽度
            // 如果数据格式为下拉、多选、单选，对字段值进行处理
            if ($field['type'] == 'select' || $field['type'] == 'checkbox' || $field['type'] == 'radio') {
                if (!empty($field['setting']) && !is_array($field['setting'])) {
                   $setting = json_decode($field['setting'],true) ? : [];
                } else {
                    $setting = $field['setting'];
                }
                $select_value = implode(',',$setting);
            }
            // 如果数据格式为sql，即该字段需要从其他表数据中获取值
            if ($field['type'] == 'sql') {
                $table_name = $field['from_table'];
                $sql = "SELECT id,CONCAT_WS('_',id,title) as title FROM ".$this->prefix.$table_name." WHERE status = 1 LIMIT 50 ";
                $setting_arr = $this->di->$table_name->queryAll($sql);
                $setting = array_column($setting_arr, "title");
                $select_value = implode(',',$setting);
            }
            
            // 如果字段有值，将该字段所在列设置为下拉选择框
            if ($select_value) {
                for ($c=3; $c<=70; $c++) {
                    //数据有效性 start
                    $objValidation = $objActSheet->getCell($excelModel->stringFromColumnIndex($k).$c)->getDataValidation(); //这一句为要设置数据有效性的单元格
                    $objValidation -> setType(\PHPExcel_Cell_DataValidation::TYPE_LIST)
                        -> setErrorStyle(\PHPExcel_Cell_DataValidation::STYLE_INFORMATION)
                        -> setAllowBlank(false)
                        -> setShowInputMessage(true)
                        -> setShowErrorMessage(true)
                        -> setShowDropDown(true)
                        -> setErrorTitle('输入的值有误')
                        -> setError('您输入的值不在下拉框列表内.')
                        -> setPromptTitle('--请选择--')
                        -> setFormula1('"'.$select_value.'"');
                        //数据有效性  end
                }
            }

            //检查该字段若必填，加上"*"
            $field['name'] = \App\sign_required($field['is_require'], $field['name']);
            $objActSheet->getStyle($excelModel->stringFromColumnIndex($k).'2')->applyFromArray($styleArray);//填充样式
            $objActSheet->setCellValue($excelModel->stringFromColumnIndex($k).'2', $field['name']);

            $k++;
        }
        $max_customer_column = $excelModel->stringFromColumnIndex($k-1);
        $mark_customer = $excelModel->stringFromColumnIndex($k);

        $objActSheet->mergeCells('A1:'.$max_customer_column.'1');
        $objActSheet->getStyle('A1:'.$mark_customer.'1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); //水平居中
        $objActSheet->getStyle('A1:'.$mark_customer.'1')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER); //垂直居中
        $objActSheet->getRowDimension(1)->setRowHeight(28); //设置行高
        $objActSheet->getStyle('A1')->getFont()->getColor()->setARGB('FFFF0000');
        $objActSheet->getStyle('A1')->getAlignment()->setWrapText(true);
        //给单元格填充背景色
        $objActSheet->getStyle('A1:'.$max_customer_column.'1')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('#ff9900');
        $content = $field_list['model_name'].'信息（*代表必填项）';
        $objActSheet->setCellValue('A1', $content);
        $objActSheet->getStyle('A1:'.$max_customer_column.'1')->getBorders()->getAllBorders()->setBorderStyle(\PHPExcel_Style_Border::BORDER_THIN);
        $objActSheet->getStyle('A1')->getBorders()->getRight()->getColor()->setARGB('#000000');
        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
        ob_end_clean();
        header("Content-Type: application/vnd.ms-excel;");
        header("Content-Disposition:attachment;filename=".$field_list['model_name']."信息导入模板".date('Y-m-d',time()).".xls");
        header("Pragma:no-cache");
        header("Expires:0");
        $objWriter->save('php://output');
        return $rs = array('code'=>1,'msg'=>'000000','data'=>array(),'info'=>array());
    }

三：导入数据

// 18 Excel文件导入
public function PostImportExcel($type,$file,$uid) {
    // 查看文件格式是否正确
    if ($file['error'] == 4) {
        return array('code'=>0,'msg'=>'000080','data'=>array(),'info'=>array());
    }
    $ext = \APP\getExtension($file['name']);//获取文件后缀
    $excel_type = array('xls','csv','xlsx');
    if (!in_array($ext,$excel_type)){
        return array('code'=>0,'msg'=>'000081','data'=>array(),'info'=>array());
    }

    // 上传文件
    $upload_arr = $this->UploadFile($file,4);
    if ($upload_arr['data']['file_path'] == "") {
        return array('code'=>0,'msg'=>'000082','data'=>array(),'info'=>array());
    }
    $savePath = dirname(dirname(dirname(__DIR__))).$upload_arr['data']['file_path'];
    $admin_common = new AdminCommon();
    $customer_common = new CustomerCommon();

    // 数据处理
    $table_field = $this->config->get('common.TABLE_FIELD');
    $field_arr = $table_field[$type];
    $field_list = $field_arr['filed'];//字段列表
    $model_id = $field_arr['model_id'];//模型ID


    // 查询灵活字段以及值
    $model_field_arr = $this->di->model_field->select("field,name,type,is_require,setting")->where(array("modelid"=>$model_id,"master_field"=>1))->order("id DESC")->limit(15)->fetchRows();
    $model_field_name_arr = array();
    if (!empty($model_field_arr)) {
        foreach ($model_field_arr as $key => $value) {
            $model_field_name_arr[] = $value['name'];
            $field_list[$value['field']]['name'] = $value['name'];
            $field_list[$value['field']]['type'] = $value['type'];
            $field_list[$value['field']]['is_require'] = $value['is_require'];
            $field_list[$value['field']]['setting'] = $value['setting'];
        }
    }

    $fieldArr = [];
    $requireField = []; //必填字段
    foreach ($field_list as $key => $value) {
        $fieldArr[$value['name']]['name'] = $key;
        $fieldArr[$value['name']]['type'] = $value['type'];
        if ($value['is_require'] == 1) $requireField[] = $key;
        if (isset($value['setting'])) {
            $fieldArr[$value['name']]['setting'] = $value['setting'];
        }
    }

    $field_num = count($fieldArr);//字段个数
    
    //默认数据
    $defaultData = array(); 
    $user_info = $this->di->user->select("username,structure_id")->where(array("id"=>$uid))->fetchOne();
    $defaultData['publisher'] = $user_info['username'];
    $defaultData['addtime'] = time();
    $defaultData['updatetime'] = time();
    $defaultData['status'] = 1;
    $defaultData['sort'] = 0;

    // 验重字段
    switch ($type) {
        case 'customer':
            $uniqueField = 'cname';
            break;
        default:
            $uniqueField = 'title';
            break;
    }

    $objPHPExcel =new NOP();
    if ($ext =='xlsx') {
        $objWriter = new \PHPExcel_Writer_Excel2007($objPHPExcel);
        $objRender = \PHPExcel_IOFactory::createReader('Excel2007');
        // $objRender->setReadDataOnly(true);
        $ExcelObj = $objRender->load($savePath);
    } elseif ($ext =='xls') {
        $objWriter = new \PHPExcel_Writer_Excel2005($objPHPExcel);
        $objRender = \PHPExcel_IOFactory::createReader('Excel2005');
        // $objRender->setReadDataOnly(true);
        $ExcelObj = $objRender->load($savePath);
    } elseif ($ext=='csv') {
        $objWriter = new \PHPExcel_Reader_CSV($objPHPExcel);
        //默认输入字符集
        $objWriter->setInputEncoding('UTF-8');
        //默认的分隔符
        $objWriter->setDelimiter(',');
        //载入文件
        $ExcelObj = $objWriter->load($savePath);
    }

    $currentSheet = $ExcelObj->getSheet(0);
    //查看有几个sheet
    $sheetContent = $ExcelObj->getSheet(0)->toArray();
    //读取表头
    $excelHeader = $sheetContent[1];

    unset($sheetContent[0]);
    unset($sheetContent[1]);

    $keys = 2;
    $errorMessage = [];

    foreach ($sheetContent as $kk => $val) {
        $data = '';
        $k = 0;
        $resNameIds = '';
        $keys++;
        $name = ''; //客户、线索、联系人等名称
        $data = $defaultData; //导入数据
        $resWhere = ''; //验重条件
        $resInfo = false; //Excel列是否有数据
        $resultInfo = true;
        foreach ($excelHeader as $aa => $header) {
            if (empty($header)) break;
            $fieldName = trim(str_replace('*','',$header));//去掉表头的*
            
            $info = '';
            $info = $val[$k];
            if ($info) $resInfo = true;
            // 判断必填项是否填写
            if ( in_array($fieldArr[$fieldName]['name'],$requireField) && !$info ) {
                $errorMessage[] = '第'.$keys.'行导入错误,失败原因：'.$fieldName.'必填';
                $resultInfo = false;
                break;                           
            }
            // 验重
            if (strstr($fieldName,"名称") !== false ) {
                $info_id = $this->di->$type->where(array($uniqueField=>$val[$k]))->fetchOne("id");
                if ($info_id) {
                    $errorMessage[] = '第'.$keys.'行导入错误,失败原因：'.$field_arr['model_name'].'名称已存在！';
                    $resultInfo = false;
                    break;
                }
            }
            
            // 整理数据
            if (empty($fieldArr[$fieldName]['name'])) continue;
            if ($aa <= $field_num) {
                $resList = [];
                $resList = $this->sheetData($k, $fieldArr, $fieldName, $info, $model_field_name_arr, $type);
                $resData[] = $resList['data'];
                $k = $resList['k'];
            }
        }
            
        if ($resultInfo) {
            $result = $this->changeArr($resData); //二维数组转一维数组
            $data = $result ? array_merge($data,$result) : [];
            $resDataId = $this->di->$type->insert($data);
            $resDataId = $this->di->$type->insert_id();

            if (!$resDataId) {
                $errorMessage[] = '第'.$keys.'行导入错误,失败原因：数据错误';
                break;
            }
        }
        
    }
    return $rs = array('code'=>1,'msg'=>'000000','data'=>$errorMessage,'info'=>$errorMessage);
}

/**
 * excel数据处理
 * @author W J Z
 * @DateTime 2019-11-01T16:31:44+0800
 * @param    integer                   $k                    [行号]
 * @param    [array]                   $fieldArr             [字段列表]
 * @param    [type]                    $fieldName            [当前字段名称]
 * @param    [type]                    $info                 [当前数据]
 * @param    [type]                    $model_field_name_arr [灵活字段列表]
 * @param    [type]                    $type                 [类型(区分表)]
 * @return   [array]                   $res                  [处理好的数据类型]
 */
public function sheetData($k = 0, $fieldArr, $fieldName, $info, $model_field_name_arr,$type) {
    if ($info) {
        if ($fieldArr[$fieldName]['type'] == 'address') {
            $address = array();
            for ($i=0; $i<3; $i++) {
                $address[] = $val[$k];
                $k++;
            }
            $data[$fieldArr[$fieldName]['name']] =  implode(chr(10), $address);
        } elseif ($fieldArr[$fieldName]['type'] == 'sql') {
            // 如果字段类型为sql 存储关联ID
            $info_arr = explode('_',$info);
            $data[$fieldArr[$fieldName]['name']] = $info_arr[0];
            $k++;
        } elseif ($fieldArr[$fieldName]['type'] == 'date') {
            // 如果字段类型为date 
            $data[$fieldArr[$fieldName]['name']] = $info ? date('Y-m-d',strtotime($info)) : '';
            $k++;
        } elseif ($fieldArr[$fieldName]['type'] == 'datetime') {
            // 如果字段类型为datetime
            $data[$fieldArr[$fieldName]['name']] = $info ? strtotime($info) : 0;
            $k++;
        } elseif (!in_array($fieldName,$model_field_name_arr) && $fieldArr[$fieldName]['type'] == 'radio' ) {
            // 非自定义字段&&选择框类型
            $setting = $fieldArr[$fieldName]['setting'];
            foreach ($setting as $sk => $sv) {
                if ($info == $sv) {
                    $setting_value = $sk;
                    break;
                }
            }
            $data[$fieldArr[$fieldName]['name']] = $setting_value ? $setting_value : 0;
            $k++;
        } elseif (in_array($fieldName,$model_field_name_arr) && ($fieldArr[$fieldName]['type'] == 'radio' || $fieldArr[$fieldName]['type'] == 'select') ) {
            // 自定义字段&&[单选||下拉]
            $data[$fieldArr[$fieldName]['name']] = strval($info);
            $k++;
        } elseif (in_array($fieldName,$model_field_name_arr) && $fieldArr[$fieldName]['type'] == 'checkbox') {
            // 自定义字段&&多选
            $info_arr = explode(',',$info);
            $data[$fieldArr[$fieldName]['name']] = json_encode($info_arr,JSON_UNESCAPED_UNICODE);
            $k++;
        }else {
            // 文本类[这里注意，要使用strval函数对数据进行处理，否则，遇到手机号、金钱这种类型的，存储到数据库会末尾多出很多0]   
            $data[$fieldArr[$fieldName]['name']] = strval($info) ? : '';
            $k++;
        }                       
    } else {
        // 如果数据为空，对特定的一些数据进行处理
       if ($fieldArr[$fieldName]['type'] == 'price' || $fieldArr[$fieldName]['type'] == 'datetime') {
            $data[$fieldArr[$fieldName]['name']] = 0;
        } else {
            $data[$fieldArr[$fieldName]['name']] = '';
        }
        $k++;
    }
    $res['data'] = $data;
    $res['k'] = $k;
    return $res;
}

//二维数组转一维数组
public function changeArr($arr)
{
    $newArr = [];
    foreach ($arr as $v) {
        if ($v && is_array($v)) {
            $newArr = array_merge($newArr,$v);
        } else {
            continue;
        }
    }
    return $newArr;
}

四：总结

经过上面的代码处理，就可以完成下载导入模型以及数据的导入了。如果需要导出数据，只需要在第二步的处理中，查询需要导出的数据，并且稍作修改就可以了。今天就到这里了，下次会掌握什么技能呢？下次见咯~

蹦跶哒的王小哲
关注
专栏目录
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值