ThinkPHP5使用PHPExcel实现数据导出功能
1、将PHPExcel类库文件夹放入extend目录下
2、导出功能实现
1 public function download(){ 2 if(request()->isPost()){ 3 $val = input('dval',''); 4 if(!empty($val)){ 5 $where['phone'] = $val; 6 } 7 $list = db("withdraw")->where($where)->order('id desc')->select(); 8 if(empty($list)){ 9 $this->error('暂无数据打印'); 10 } 11 $data = array(); 12 foreach ($list as $k=>$value){ 13 $data[$k]['phone'] = $value['phone']; 14 $data[$k]['sl'] = $value['sl']; 15 $data[$k]['status'] = $value['status']; 16 $data[$k]['createtime'] = date('Y-m-d H:i:s',$value['createtime']); 17 } 18 foreach ($data as $field=>$v){ 19 if($field == 'phone'){ 20 $headArr[]='手机号'; 21 } 22 if($field == 'sl'){ 23 $headArr[]='数量'; 24 } 25 if($field == 'status'){ 26 $headArr[]='状态'; 27 } 28 if($field == 'createtime'){ 29 $headArr[]='时间'; 30 } 31 } 32 $filename="记录"; 33 return $this->getExcel($filename,$headArr,$data); 34 } 35 } 36 public function getExcel($fileName,$headArr,$data){ 37 //导入PHPExcel类库 38 require_once(Env::get('root_path') . 'extend/PHPExcel/Classes/PHPExcel.php'); 39 require_once(Env::get('root_path') . 'extend/PHPExcel/Classes/PHPExcel/IOFactory.php'); 40 41 $date = date("Y_m_d",time()); 42 $fileName .= "_{$date}.xls"; 43 44 //创建PHPExcel对象，注意，不能少了\ 45 $objPHPExcel = new \PHPExcel(); 46 $objProps = $objPHPExcel->getProperties(); 47 48 //设置表头 49 $key = ord("A"); 50 foreach($headArr as $v){ 51 $colum = chr($key); 52 $objPHPExcel->setActiveSheetIndex(0) ->setCellValue($colum.'1', $v); 53 $objPHPExcel->setActiveSheetIndex(0) ->setCellValue($colum.'1', $v); 54 $key += 1; 55 } 56 57 $column = 2; 58 $objActSheet = $objPHPExcel->getActiveSheet(); 59 60 foreach($data as $key => $rows){ //行写入 61 $span = ord("A"); 62 foreach($rows as $keyName=>$value){// 列写入 63 $j = chr($span); 64 $objActSheet->setCellValue($j.$column, $value); 65 $span++; 66 } 67 $column++; 68 } 69 70 $fileName = iconv("utf-8", "gb2312", $fileName); 71 72 //重命名表 73 //$objPHPExcel->getActiveSheet()->setTitle('test'); 74 //设置活动单指数到第一个表,所以Excel打开这是第一个表 75 $objPHPExcel->setActiveSheetIndex(0); 76 ob_end_clean();//清除缓冲区,避免乱码 77 header('Content-Type: application/vnd.ms-excel'); 78 header("Content-Disposition: attachment;filename=\"$fileName\""); 79 header('Cache-Control: max-age=0'); 80 81 $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007'); 82 $objWriter->save('php://output'); //文件通过浏览器下载 83 exit; 84 }
