支持自动识别表头名称和字段并生成对应的数据
目录结构
1.下载php的Excel扩展
扩展地址：/PHPOffice/PHPExcel
将下载的扩展放入到/extend目录下，并改名文件夹为Excel（如上图所示）
2.创建类库文件（\application\webapi\lib\Excel.php）
<?php// =================// excel 扩展// =================namespace app\Webapi\lib;class Excel {// 保存文件方法，可不用直接使用tp自带// function excel_extend_upload($uploadPath = null) {//if (!$uploadPath) {// $uploadPath = dirname(__FILE__) . "\\uploads\\";//}//\PHPExcel_CachedObjectStorageFactory::cache_in_memory_gzip;//if (is_uploaded_file($_FILES['upfile']['tmp_name'])) {// $upfile = $_FILES["upfile"];// //获取数组里面的值// $name = $upfile["name"]; //上传文件的文件名// $type = $upfile["type"]; //上传文件的类型// $size = $upfile["size"]; //上传文件的大小// $tmp_name = $upfile["tmp_name"]; //上传文件的临时存放路径// if (!is_dir($uploadPath)) {// mkdir($uploadPath, 0777, true);// }// $final_file_name = $uploadPath . rand(1000, 9999) . "-customers.xlsx";// move_uploaded_file($tmp_name, $final_file_name); //将上传到服务器临时文件夹的文件重新移动到新位置// $file_name = $final_file_name;// $info['name'] = $name;// $info['type'] = $type;// $info['size'] = $size;// $info['file_name'] = $file_name;// return $info;// $error = $upfile["error"]; //上传后系统返回的值// if ($error !== 0) {// return false;// die();// }//} else {// return false;//}// }// 获取excel对象function get_obj($file_name) {// dump(__DIR__ . '/../../extend/Excel/PHPExcel.php');$phpexcel_path= '../extend/Excel/PHPExcel.php';require_once $phpexcel_path;$objReader = null;$objPHPExcel = null;$extension = strtolower(pathinfo($file_name, PATHINFO_EXTENSION));if ($extension == 'xlsx') {$objReader = new \PHPExcel_Reader_Excel();$objPHPExcel = $objReader->load($file_name);// var_dump(666);// var_dump($objPHPExcel);} else if ($extension == 'xls') {$objReader = new \PHPExcel_Reader_Excel5();$objPHPExcel = $objReader->load($file_name);} else if ($extension == 'csv') {$PHPReader = new \PHPExcel_Reader_CSV();//默认输入字符集$PHPReader->setInputEncoding('GBK');//默认的分隔符$PHPReader->setDelimiter(',');//载入文件$objPHPExcel = $PHPReader->load($file_name);}return $objPHPExcel;}/*** 绑定标题和字段（最多对应到AZ）* @param object $objPHPExcel* @param array $title_field 示例：['员工编号'=>'coding']* @param array $cellKey 列序号['A','B','C',...]* @return {*}*/function band_title_field($objPHPExcel, $title_field, $cellKey = null) {$title_item_field = [];$cellKey = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M','N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z','AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ', 'AK', 'AL', 'AM','AN', 'AO', 'AP', 'AQ', 'AR', 'AS', 'AT', 'AU', 'AV', 'AW', 'AX', 'AY', 'AZ'];// 取出所有列标题foreach ($cellKey as $key => $value) {$title_item = $objPHPExcel->getActiveSheet()->getCell($value . 1)->getValue();if ($title_item) {$title[$title_item] = $value;} else {break;}}// // 定义标题列// $title_field = [//// '序号' => 'A' ,//'员工编号' => 'coding',//'姓名' => 'name',//'部门' => 'section_id',//// '组别' => 'E' ,//'岗位' => 'position_id',//// '职级' => 'G' ,//'手机号码' => 'phone',//'入职日期' => 'start_time',//'年龄' => 'age',// ];// 表头字母对应字段foreach ($title_field as $key => $value) {if (isset($title[$key])) {$title_item_field[$value] = $title[$key];}}return $title_item_field;}}
3.控制器代码（\application\webapi\controller\Device.php接口控制器代码截取）
// 导入表格public function importExcel(){//上传excel文件$file = request()->file('excel');//将文件保存到public/uploads目录下面$info = $file->validate(['size' => 1048576, 'ext' => 'xls,xlsx'])->move('../public/excel_uploads');$fileName = '../public/excel_uploads/'.$info->getSaveName();$excel_extend=new Excel();// 获取表格对象$objPHPExcel = $excel_extend->excel_extend_get_obj($fileName);// 定义标题列$title_field = ['姓名' => 'name','手机号码' => 'phone','入职日期' => 'start_time','年龄' => 'age','性别' => 'sex','身证号码' => 'id_card_number','出生日期' => 'birthday',];// 绑定表头和字段$title_item_field = $excel_extend->excel_extend_band_title_field($objPHPExcel, $title_field);$sheet = $objPHPExcel->getSheet(0); //取得sheet(0)表$highestRow = $sheet->getHighestRow(); // 取得总行数$highestColumn = $sheet->getHighestColumn(); // 取得总列数for ($i = 2; $i <= $highestRow; $i++) {$content[$i] = [];foreach ($title_item_field as $key => $value) {$content[$i][$key] = $objPHPExcel->getActiveSheet()->getCell($value . $i)->getValue();}// 获取到行数据字段对应值数据$data=$content[$i];// 可以在此进行字段验证解析if($data['sex']=="男"){$data['sex']=1;}else{$data['sex']=0;}// 保存数据到数据库// ......}// $this->apiSuccess();}
