## :-: **PHP导入Excle表** ``` vendor("PHPExcel"); //获取表单上传文件 $file =request()->file('file'); if ($file) { $info = $file ->validate(['ext'=>'xlsx,xls,csv']) ->move(ROOT_PATH . 'public/uploads' . DS . 'excel'); if($info){ $exclePath = $info->getSaveName(); //获取文件名 $file_name = ROOT_PATH . 'public/uploads' . DS . 'excel' . DS . $exclePath; //上传文件的地址 // 判断文件是什么格式 $type = pathinfo($file_name); $type = strtolower($type["extension"]); switch ($type) { case 'xlsx': $type = "Excel2007"; break; case 'xls': $type = "Excel5"; break; case 'csv': $type = "CSV"; break; } ini_set("max_execution_time", "0"); // 判断使用哪种格式 $objReader = \PHPExcel_IOFactory::createReader($type); $objPHPExcel = $objReader->load($file_name); $sheet = $objPHPExcel->getSheet(0); // 取得总行数 $highestRow = $sheet->getHighestRow(); // 取得总列数 $highestColumn = $sheet->getHighestColumn(); //循环读取excel文件,读取一条,插入一条 $data = array(); //从第一行开始读取数据 for ($j = 1; $j <= $highestRow; $j++) { //从A列读取数据 for ($k = "A"; $k <= $highestColumn; $k++) { // 读取单元格 $data[$j][] = $objPHPExcel->getActiveSheet()->getCell("$k$j")->getValue(); } } foreach ($data as $k => $v) { if (!array_key_exists(12,$v)) { return array('info' => "上传文件不符合模板规范", 'status' => -1); } } if ($data[1][1] != '编号'|| $data[1][12] != '包装内数量') { return array('info' => "上传文件不符合模板规范", 'status' => -1); } //验证数据库里是否已存在要导入的数据编号 foreach($data as $k=>$v){ if(is_float($v[1])){ $rec=db('nursing_consumables')->where('nursing_consumables_id',$v[1])->select(); } } if($rec){ return array('info' => "上传文件里有数据库里编号已存在！", 'status' => -1); } $map = []; for ($i = 2; count($data) >= $i; $i++) { $map[$i]['nursing_consumables_id'] = $data[$i][1]; $map[$i]['nursing_consumables_input_code'] = $data[$i][2]; $map[$i]['nursing_consumables_name'] = $data[$i][3]; $map[$i]['nursing_consumables_specifications'] = $data[$i][4]; $map[$i]['nursing_consumables_manufacturer_name'] = $data[$i][5]; $map[$i]['nursing_consumables_company'] = $data[$i][6]; $map[$i]['nursing_consumables_whole_number'] = $data[$i][7]; $map[$i]['nursing_consumables_bulk_number'] = $data[$i][8]; $map[$i]['nursing_consumables_wholesale_price'] = $data[$i][9]; $map[$i]['nursing_consumables_retail_price'] = $data[$i][10]; $map[$i]['nursing_consumables_use_company'] = $data[$i][11]; $map[$i]['nursing_consumables_packing_number'] = $data[$i][12]; } //删除空列 foreach($map as $k=>$v){ if(empty($v['nursing_consumables_id'])){unset($map[$k]);} } //dump($map);die(); $res=db('nursing_consumables')->insertAll($map); //dump($res);die(); if($res){ return array('info' => '上传成功', 'status' => 1, 'target' => 'back'); } }else{ return array('info' => '上传文件格式错误', 'status' => -1); } }else{ return array('info' => '没有上传文件', 'status' => -1); } } ```