tp5做的excel导入导出（用的PHPexcel类）
//ajax没有流类型，需要另外的处理方式
// 应用公共文件 function excelExport($fileName = '', $headArr = [], $data = []) { //引入phpExecl类 vendor("PHPExcel.PHPExcel"); $fileName .= "_" . date("Y_m_d").'.xls'; $objPHPExcel = new \PHPExcel(); $objPHPExcel->getProperties(); $key = ord("A"); // 设置表头 foreach ($headArr as $v) { $colum = chr($key); $objPHPExcel->setActiveSheetIndex(0)->setCellValue($colum . '1', $v); $objPHPExcel->setActiveSheetIndex(0)->setCellValue($colum . '1', $v); $key += 1; } $column = 2; $objActSheet = $objPHPExcel->getActiveSheet(); foreach ($data as $key => $rows) { // 行写入 $span = ord("A"); foreach ($rows as $keyName => $value) { // 列写入 $objActSheet->setCellValue(chr($span) . $column, $value); $span++; } $column++; } $fileName = iconv("utf-8", "gb2312", $fileName); // 重命名表 $objPHPExcel->setActiveSheetIndex(0); // 设置活动单指数到第一个表,所以Excel打开这是第一个表 header('Content-Type: application/vnd.ms-excel'); header("Content-Disposition: attachment;filename='$fileName'"); header('Cache-Control: max-age=0'); $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); $objWriter->save('php://output'); // 文件通过浏览器下载 exit(); }
public function export() { $data = \think\Cache::get('res_key'); if(!$data) { $this->error('请设置要导出的数据','/student_y'); } $name='xxxx'; $header=['考点代码','考点名称','考试时间','考试级别','身份证','中文名','英文名','性别(1男2女)','座位号','缴费']; excelExport($name,$header,$data); }
导入
//import public function excelInsert() { if($this->request->isPost()) { $filestr = $this->request->param(); Loader::import('PHPExcel',EXTEND_PATH); Loader::import('PHPExcel.PHPExcel.IOFactory',EXTEND_PATH); Loader::import('PHPExcel\PHPExcel\Reader\Excel5',EXTEND_PATH); Loader::import('PHPExcel\PHPExcel\Reader\Excel2007',EXTEND_PATH); $file = request()->file('file'); if($file=='') { $this->success('请选择要导入的数据'); } $info = $file->validate(['size'=>156780,'ext'=>'xlsx,xls,csv'])->move(ROOT_PATH . 'public' . DS . 'excel'); if($info) { //echo $info->getFilename(); $exclePath = $info->getSaveName(); //获取文件名 $str=explode('.', $exclePath); $extension = end($str); $file_name = ROOT_PATH . 'public' . DS . 'excel' . DS . $exclePath; //$objReader =\PHPExcel_IOFactory::createReader("Excel5"); if( $extension =='xlsx' ) { $objReader =\PHPExcel_IOFactory::createReader("Excel2007"); } else { $objReader =\PHPExcel_IOFactory::createReader("Excel5"); } $obj_PHPExcel =$objReader->load($file_name, $encode = 'utf-8'); //加载文件内容,编码utf-8 $excel_array=$obj_PHPExcel->getsheet(0)->toArray(); //转换为数组格式 array_shift($excel_array); $data = []; foreach($excel_array as $k=>$v) { $data[$k]['room_number'] = isset($v[0])==true ? $v[0] : '';//考点代码 $data[$k]['idcard'] = isset($v[1])==true ? $v[1] : ''; //身份证 $data[$k]['en_name'] = isset($v[2])==true ? $v[2] : ''; $data[$k]['cn_name'] = isset($v[3])==true ? $v[3] : ''; $data[$k]['sex'] = intval(isset($v[4])==true ? $v[4] : ''); $data[$k]['seat_number'] = isset($v[5])==true ? $v[5] : ''; $data[$k]['birthday'] = isset($v[6])==true ? $v[6] : ''; $data[$k]['mobile'] = intval(isset($v[7])==true ? $v[7] : ''); $data[$k]['house_address'] = isset($v[8])==true ? $v[8] : ''; $data[$k]['email'] = isset($v[9])==true ? $v[9] : ''; $data[$k]['places'] = isset($v[10])==true ? $v[10] : ''; $data[$k]['exam_num'] = isset($v[11])==true ? $v[11] : ''; $data[$k]['res'] = isset($v[12])==true ? $v[12] : ''; $data[$k]['res2'] = isset($v[13])==true ? $v[13] : ''; $data[$k]['school'] = isset($v[14])==true ? $v[14] : ''; $data[$k]['res_time'] = isset($v[15])==true ? $v[15] : '0'; $data[$k]['price'] = isset($v[16])==true ? $v[16] : ''; $data[$k]['group'] = isset($v[17])==true ? $v[17] : ''; $data[$k]['flag'] = isset($v[18])==true ? $v[18] : ''; $data[$k]['status'] = 1; } $rtn = Db::table('ncet_student')->insertAll($data); //批量插入数据 if($rtn) { $this->success('导入完成'); } else { $this->success('导入失败'); } } else { echo $file->getError(); } } }
//改进版
function downExplode() { $batchid = $this->input->get('batchid'); if($batchid) { $batchArr = $this->db->select('*')->from('mba_interview_result')->where(['batch_id'=>$batchid])->get()->result_array(); $userArr = array(); $res = array(); foreach($batchArr as $k=>$v) { if(is_string($v['personal_interview'])) { $result = json_decode($v['personal_interview'],true); $res[] = $result; foreach($result as $k2=>$v2) { $userArr[$k2]['name'] = $this->name($k2,'name'); $userArr[$k2]['idcard'] = $this->name($k2,'idcard'); $userArr[$k2]['kaochang_id'] = $this->kaochang($v['session_id']); /* 获取学生的考官id */ $groupid = $this->db->select('id')->from('mmmmmm')->where(['session_id'=>$v['session_id'],'kaochang_id'=>$v['kaochang_id']])->get()->result_array(); //教师数量 $tArr = $this->db->select('teacher_id')->from('mmmmmm')->where(['group_id'=>$groupid[0]['id']])->get()->result_array(); $i=0; foreach($tArr as $k3=>$v3) { $userArr[$k2]["t_".$i] = $this->getRecommend($k2,$v['session_id'],$v['batch_id'],$v3); $i++; } } } } $count=0; foreach($userArr as $k=>$v) { $i=0; foreach($v as $k2=>$v2) { $count+=$v["t_".$i]; $i++; } $userArr[$k]['recommend'] = $count; $count=0; } //导出 include($_SERVER['DOCUMENT_ROOT']."/resource/PHPExcel/Classes/PHPExcel.php"); include($_SERVER['DOCUMENT_ROOT']."/resource/PHPExcel/Classes/PHPExcel/IOFactory.php"); $objPHPExcel = new PHPExcel(); $i=1; foreach($tArr as $index=>$val) { $title['name'] = '姓名'; $title['idcard']='身份证号'; $title['session']='考场'; $title["interview".$i] = '考官'.$i; //$title["recommend"] = '推荐数量'; $i++; } $title["recommend"] = '推荐数量'; $i=1; foreach($title as $index=>$val) { $y = chr(64+$i); $x = 1; $objPHPExcel->setActiveSheetIndex(0)->setCellValue($y.$x,$val); $i++; } foreach($userArr as $k=>$v) { if(!$v['recommend']) { unset($userArr[$k]); } } $x=1; foreach($userArr as $k=>$v) { if($v['name']) { $x++; } $i=1; foreach($v as $k2=>$v2) { $i++; $y = chr(63+$i); $objPHPExcel->getActiveSheet()->setCellValueExplicit($y.$x,$v2,PHPExcel_Cell_DataType::TYPE_STRING); } } //die; $fileName = iconv("utf-8", "gb2312", '考官推荐'); // 重命名表 $objPHPExcel->setActiveSheetIndex(0); // 设置活动单指数到第一个表,所以Excel打开这是第一个表 header('Content-Type: application/vnd.ms-excel'); header("Content-Disposition: attachment;filename='$fileName'.xls"); header('Cache-Control: max-age=0'); $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); $objWriter->save('php://output'); // 文件通过浏览器下载 exit(); } }
function downExplode(){$batchid = $this->input->get('batchid');if($batchid){$batchArr = $this->db->select('*')->from('mba_interview_result')->where(['batch_id'=>$batchid])->get()->result_array();$userArr = array();$res = array();foreach($batchArr as $k=>$v){if(is_string($v['personal_interview'])){$result = json_decode($v['personal_interview'],true);$res[] = $result;foreach($result as $k2=>$v2){$userArr[$k2]['name'] = $this->name($k2,'name');$userArr[$k2]['idcard'] = $this->name($k2,'idcard');$userArr[$k2]['kaochang_id'] = $this->kaochang($v['session_id']);/* 获取学生的考官id */$groupid = $this->db->select('id')->from('mba_interview_group')->where(['session_id'=>$v['session_id'],'kaochang_id'=>$v['kaochang_id']])->get()->result_array();//教师数量$tArr = $this->db->select('teacher_id')->from('mba_interview_group_teacher')->where(['group_id'=>$groupid[0]['id']])->get()->result_array();$i=0;foreach($tArr as $k3=>$v3){$userArr[$k2]["t_".$i] = $this->getRecommend($k2,$v['session_id'],$v['batch_id'],$v3);$i++;}}}}/* echo '<pre>';print_r($userArr);echo '</pre>';die; */$count=0;foreach($userArr as $k=>$v){$i=0;foreach($v as $k2=>$v2){$count+=$v["t_".$i];$i++;}$userArr[$k]['recommend'] = $count;$count=0;} //导出include($_SERVER['DOCUMENT_ROOT']."/resource/PHPExcel/Classes/PHPExcel.php");include($_SERVER['DOCUMENT_ROOT']."/resource/PHPExcel/Classes/PHPExcel/IOFactory.php");$objPHPExcel = new PHPExcel();$i=1;foreach($tArr as $index=>$val){$title['name'] = '姓名';$title['idcard']='身份证号';$title['session']='考场';$title["interview".$i] = '考官'.$i;//$title["recommend"] = '推荐数量';$i++;}$title["recommend"] = '推荐数量';$i=1;foreach($title as $index=>$val){$y = chr(64+$i);$x = 1;$objPHPExcel->setActiveSheetIndex(0)->setCellValue($y.$x,$val);$i++;}foreach($userArr as $k=>$v){if(!$v['recommend']){unset($userArr[$k]);}}$x=1;foreach($userArr as $k=>$v){if($v['name']){$x++;}$i=1;foreach($v as $k2=>$v2){$i++;$y = chr(63+$i);$objPHPExcel->getActiveSheet()->setCellValueExplicit($y.$x,$v2,PHPExcel_Cell_DataType::TYPE_STRING);}}//die;$fileName = iconv("utf-8", "gb2312", '考官推荐'); // 重命名表
$objPHPExcel->setActiveSheetIndex(0); // 设置活动单指数到第一个表,所以Excel打开这是第一个表
header('Content-Type: application/vnd.ms-excel');
header("Content-Disposition: attachment;filename='$fileName'.xls");
header('Cache-Control: max-age=0');
$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output'); // 文件通过浏览器下载
exit();}}
