基于PhalApi的PHPExcel扩展实现数据导入导出
前言：
最近开发一款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;
}
四：总结
经过上面的代码处理，就可以完成下载导入模型以及数据的导入了。如果需要导出数据，只需要在第二步的处理中，查询需要导出的数据，并且稍作修改就可以了。今天就到这里了，下次会掌握什么技能呢？下次见咯~
