tp5导出，导出Excel

爱被打了一巴掌 2021-08-29 19:56 524阅读 0赞

PHP代码部分

//导出考试成绩
    public function daochu()
       { 
    	$cid = $_GET['cid'];
    	$this_course =  Db::name('course') -> where(array('id' => $cid))->find();
    	$mid = $_GET['mid'];
    	$this_user =  Db::name('store_member') -> where(array('id' => $mid))->find();
    	$this_xiaojie = Db::name('course_xiaojie') -> where(array('cid' =>$cid , 'type' => 2))-> find();
    	$xid = $this_xiaojie['id'];
    	$kid = $this_xiaojie['kid'];
    	$this_xuexi_jilu = Db::name('course_xuexi_jilu') -> where(array('mid' =>$mid ,'cid' =>$cid , 'kid' => $kid , 'xid' => $xid))-> find();
    	if($this_xuexi_jilu){ 
    		$where[] = ['id','in', $this_xuexi_jilu['wenti_ids']]; 
    		$wenti_ids = $this_xuexi_jilu['wenti_ids'];
    		$exp = new \think\Db\Expression('field(id,'.$wenti_ids.')');
    		$list = Db::name('course_wenti') ->order($exp) -> where($where)-> select();
    		include "../vendor/PHPExcel/PHPExcel.php";
    		$objPHPExcel = new \PHPExcel();
    		
    		// 所有单元格默认高度
    		//$objPHPExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(50);
    		// 自适应行高 
    		$objPHPExcel->getActiveSheet()->getRowDimension()->setRowHeight(-1);
    		// 垂直居中
    		$objPHPExcel->getActiveSheet()->getDefaultStyle()->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
    		// 设置水平居中
    		//$objPHPExcel->getActiveSheet()->getDefaultStyle()->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
    		
    		$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(40);
    		$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(20);
    		$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(20);
    		$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(20);
    		$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(20);
    		$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(10);
    		$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(10);
    		
    		$objPHPExcel->getActiveSheet()->getStyle("A")->getAlignment()->setWrapText(true); // 自动换行
    		$objPHPExcel->getActiveSheet()->getStyle("B")->getAlignment()->setWrapText(true); // 自动换行
    		$objPHPExcel->getActiveSheet()->getStyle("C")->getAlignment()->setWrapText(true); // 自动换行
    		$objPHPExcel->getActiveSheet()->getStyle("D")->getAlignment()->setWrapText(true); // 自动换行
    		$objPHPExcel->getActiveSheet()->getStyle("E")->getAlignment()->setWrapText(true); // 自动换行
    		$objPHPExcel->getActiveSheet()->getStyle("F")->getAlignment()->setWrapText(true); // 自动换行
    		$objPHPExcel->getActiveSheet()->getStyle("G")->getAlignment()->setWrapText(true); // 自动换行
    		
    		
    		$objPHPExcel->getProperties()
    		->setCreator("ctos")
    		->setLastModifiedBy("ctos")
    		->setTitle("Office 2007 XLSX Test Document")
    		->setSubject("Office 2007 XLSX Test Document")
    		->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")
    		->setKeywords("office 2007 openxml php")
    		->setCategory("Test result file");
    		$arr = ['A','B','C','D','E','F','G'];
    		
    		/*$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', '问题的标题'); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('B1', '选项1'); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('C1', '选项2'); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('D1', '选项3'); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('E1', '选项4'); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('F1', '选择的答案'); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('G1', '答案是否正确');*/
    		$defen = 0;
    		
    		$objPHPExcel->getActiveSheet(0)->setCellValue('A'.(2), "问题的标题");
    		$objPHPExcel->getActiveSheet(0)->setCellValue('B'.(2), "选项1");
    		$objPHPExcel->getActiveSheet(0)->setCellValue('C'.(2), "选项2");
    		$objPHPExcel->getActiveSheet(0)->setCellValue('D'.(2), "选项3");
    		$objPHPExcel->getActiveSheet(0)->setCellValue('E'.(2), "选项4");
    		$objPHPExcel->getActiveSheet(0)->setCellValue('F'.(2), "选择的答案");
    		$objPHPExcel->getActiveSheet(0)->setCellValue('G'.(2), "答案是否正确");
    		
    		foreach ($list as $k => $v) { 
    			$objPHPExcel->getActiveSheet(0)->setCellValue('A'.($k+3), "\r\n".$v['title']."\r\n");
    			$list[$k]['daans'] = Db::name('course_wenti_daan') -> where(array('wid' =>$list[$k]['id']))  -> order('sort desc id asc') -> limit(4)-> select();
    			foreach($list[$k]['daans'] as $k1 => $v1){ 
    				if($k1 == 0){ 
    					$objPHPExcel->getActiveSheet(0)->setCellValue('B'.($k+3), "\r\n".$v1['title']."：".$v1['text']."\r\n");
    				}
    				if($k1 == 1){ 
    					$objPHPExcel->getActiveSheet(0)->setCellValue('C'.($k+3), "\r\n".$v1['title']."：".$v1['text']."\r\n");
    				}
    				if($k1 == 2){ 
    					$objPHPExcel->getActiveSheet(0)->setCellValue('D'.($k+3), "\r\n".$v1['title']."：".$v1['text']."\r\n");
    				}
    				if($k1 == 3){ 
    					$objPHPExcel->getActiveSheet(0)->setCellValue('E'.($k+3), "\r\n".$v1['title']."：".$v1['text']."\r\n");
    				}
    				
    				$this_xuanze = Db::name('course_kaoshi_daan_jilu') -> where(array('mid' =>$mid , 'cid' => $cid , 'kid' => $kid , 'wid' => $v['id'] , 'did' => $v1['id'])) -> find();
    				
    				if($this_xuanze){ 
    					$objPHPExcel->getActiveSheet(0)->setCellValue('F'.($k+3), "\r\n".$v1['title']."\r\n");
    					if($this_xuanze['is_right'] == 0){ 
    						$objPHPExcel->getActiveSheet(0)->setCellValue('G'.($k+3), "\r\n".'错误'."\r\n");
    					}else{ 
    						$objPHPExcel->getActiveSheet(0)->setCellValue('G'.($k+3), "\r\n".'正确'."\r\n");
    						$defen += 1*1;
    					}
    				}
    			}
    		}
    		
    		//计算总得分
    		/*$objPHPExcel->getActiveSheet(0)->setCellValue('A'.(count($list)+2), "考试时间：".date("Y-m-d H:i" ,$this_xuexi_jilu['addtime'])); $objPHPExcel->getActiveSheet(0)->setCellValue('B'.(count($list)+2), "总分：".$defen,"分");*/
    		
    		$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', "\r\n考试时间：".date("Y-m-d H:i" ,$this_xuexi_jilu['addtime'])."\r\n");
    		$objPHPExcel->setActiveSheetIndex(0)->setCellValue('B1', "\r\n总分：".$defen."分\r\n");
    		
    		
    		$expTitle = $this_user['nickname']."-".$this_user['username']."-".$this_course['name'];
    		//$userBrowser = $_SERVER['HTTP_USER_AGENT']; //判断是否是ie内核
               //if( preg_match( '/MSIE/i', $userBrowser ) ) { 
    		// var_dump(0);
                   $xlsTitle = urlencode($expTitle);
               //}else{ 
    		// var_dump(1);
               // $xlsTitle = iconv('utf-8', 'gb2312', $expTitle);
               //}
    		//die;
    		ob_end_clean();
    		header("Cache-Control: public");
    		header("Pragma: public");
    		header("Content-Type: application/force-download");
    		header("Content-Type: application/octet-stream");
    		header("Content-Type:application/download");
    		header("Content-type:application/vnd.ms-excel");
    		header("Content-Disposition:attachment;filename=".$xlsTitle.".xls");
    		header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
    		
    		
    		$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
    		$objWriter->save('php://output');
    		exit;
    		
    	}
    }