we7微擎导出Excel,设置单元格文本格式

我就是我 2022-12-10 10:19 371阅读 0赞

$condition = " a.class_id=:class_id ";
    $params[':class_id'] = $_GPC['class_id'];
    	
    $name = trim($_GPC['realname']);
    if($name != ''){
    	$condition .= " AND a.name LIKE :name ";
    	$params[':name'] = "%".$name."%";
    }
    $id_card_no = trim($_GPC['id_card_no']);
    if($id_card_no != ''){
    	$condition .= " AND a.id_card_no LIKE :id_card_no ";
    	$params[':id_card_no'] = "%".$id_card_no."%";
    }
    $mobile = trim($_GPC['mobile']);
    if($mobile != ''){
    	$condition .= " AND a.mobile LIKE :mobile ";
    	$params[':mobile'] = "%".$mobile."%";
    }
    	
    $list = pdo_fetchall("SELECT a.*,b.lessonid,b.sectionid,b.playtime,b.addtime,b.updtime FROM ims_fy_lesson_register AS a,ims_fy_lesson_playrecord AS b,ims_fy_lesson_course AS c
    WHERE a.uid=b.uid AND a.class_id=c.class_id AND b.lessonid=c.course_id and {$condition} ORDER BY a.uid,b.addtime ASC", $params);
    
    require_once IA_ROOT . '/framework/library/phpexcel/PHPExcel.php';	
    	$Excel = new PHPExcel();
    	$arr = array();
        $arr_header = array('A'=>'姓名','B'=>'身份证号码','C'=>'账号','D'=>'班级名称','E'=>'总完成率','F'=>'总课程时长','G'=>'总学习时长','H'=>'开始学习时间','I'=>'完成时间',
    					  'J'=>'章节名称','K'=>'章节学习完成率','L'=>'章节时长','M'=>'学习时长','N'=>'开始学习时间','O'=>'完成时间','P'=>'进度','Q'=>'章节练习合格情况','R'=>'合格时间','S'=>'所属课程名称','T'=>'课程考试合格情况','U'=>'合格时间');
      
       //打印表头,需要将表头插入2次,原因未明?
       array_push($arr,$arr_header);
       array_push($arr,$arr_header);
      
       for($i= 0; $i< count($list); $i++){  
    	 $arr_row = array('A'=>$list[$i]['name'],'B'=>$list[$i]['id_card_no'],'C'=>$list[$i]['mobile'],'D'=>$list[$i]['class_name'],'E'=>$list[$i]['total_complete_rate'],'F'=>$list[$i]['term_obj'],'G'=>$list[$i]['sum_playtime'],'H'=>$list[$i]['addtime'],'I'=>$list[$i]['finish_time'],'J'=>$list[$i]['section_title'],'K'=>$list[$i]['section_finish_rate'],'L'=>$list[$i]['videotime'],'M'=>$list[$i]['playtime'],'N'=>$list[$i]['addtime'],'O'=>$list[$i]['updtime'],'P'=>$list[$i]['section_rate'],'Q'=>$list[$i]['section_pass'],'R'=>$list[$i]['section_pass_time'],'S'=>$list[$i]['bookname'],'T'=>$list[$i]['lesson_pass'],'U'=>$list[$i]['lesson_pass_time']);
    	 array_push($arr,$arr_row);				  
       }
    	
       // 设置
    	$Excel
    		->getProperties()
    		->setCreator("dee")
    		->setLastModifiedBy("dee")
    		->setTitle("数据EXCEL导出")
    		->setSubject("数据EXCEL导出")
    		->setDescription("数据EXCEL导出")
    		->setKeywords("excel")
    		->setCategory("result file");
    		
    		
    	foreach($arr as $key => $val) { // 注意 key 是从 0 还是 1 开始，此处是 0
    	   //print_r("key=".$key.',val='.$val);
    		//$num = $key + 1;
    		$Excel ->setActiveSheetIndex(0)
    			 //Excel的第A列，uid是你查出数组的键值，下面以此类推
    			  ->setCellValue('A'.$key, $val['A'])    
    			  ->setCellValueExplicit('B'.$key, $val['B'],PHPExcel_Cell_DataType::TYPE_STRING)  //设置单元格文本格式，避免身份证号显示为科学计数法
    			  ->setCellValue('C'.$key, $val['C'])
    			  ->setCellValue('D'.$key, $val['D'])
    			  ->setCellValue('E'.$key, $val['E'])
    			  ->setCellValue('F'.$key, $val['F'])
    			  ->setCellValue('G'.$key, $val['G'])
    			  ->setCellValue('H'.$key, $val['H'])
    			  ->setCellValue('I'.$key, $val['I'])
    			  ->setCellValue('J'.$key, $val['J'])
    			  ->setCellValue('K'.$key, $val['K'])
    			  ->setCellValue('L'.$key, $val['L'])
    			  ->setCellValue('M'.$key, $val['M'])
    			  ->setCellValue('N'.$key, $val['N'])
    			  ->setCellValue('O'.$key, $val['O'])
    			  ->setCellValue('P'.$key, $val['P'])
    			  ->setCellValue('Q'.$key, $val['Q'])
    			  ->setCellValue('R'.$key, $val['R'])
    			  ->setCellValue('S'.$key, $val['S'])
    			  ->setCellValue('T'.$key, $val['T'])
    			  ->setCellValue('U'.$key, $val['U']);
    	}
     
    	$Excel->getActiveSheet()->setTitle('export');
    	$Excel->setActiveSheetIndex(0);
    	$Excel->getActiveSheet()->getColumnDimension('A')->setAutoSize(true);
    	$Excel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);
    	$Excel->getActiveSheet()->getColumnDimension('C')->setAutoSize(true);
    	$Excel->getActiveSheet()->getColumnDimension('D')->setAutoSize(true);
    	$Excel->getActiveSheet()->getColumnDimension('E')->setAutoSize(true);
    	$Excel->getActiveSheet()->getColumnDimension('F')->setAutoSize(true);
    	$Excel->getActiveSheet()->getColumnDimension('G')->setAutoSize(true);
    	$Excel->getActiveSheet()->getColumnDimension('H')->setAutoSize(true);
    	$Excel->getActiveSheet()->getColumnDimension('I')->setAutoSize(true);
    	$Excel->getActiveSheet()->getColumnDimension('J')->setAutoSize(true);
    	$Excel->getActiveSheet()->getColumnDimension('K')->setAutoSize(true);
    	$Excel->getActiveSheet()->getColumnDimension('L')->setAutoSize(true);
    	$Excel->getActiveSheet()->getColumnDimension('M')->setAutoSize(true);
    	$Excel->getActiveSheet()->getColumnDimension('N')->setAutoSize(true);
    	$Excel->getActiveSheet()->getColumnDimension('O')->setAutoSize(true);
    	$Excel->getActiveSheet()->getColumnDimension('P')->setAutoSize(true);
    	$Excel->getActiveSheet()->getColumnDimension('Q')->setAutoSize(true);
    	$Excel->getActiveSheet()->getColumnDimension('R')->setAutoSize(true);
    	$Excel->getActiveSheet()->getColumnDimension('S')->setAutoSize(true);
    	$Excel->getActiveSheet()->getColumnDimension('T')->setAutoSize(true);
    	$Excel->getActiveSheet()->getColumnDimension('U')->setAutoSize(true);
    	
    	
    	
    	$fileName = $lesson_class['name']."-".date('Ymd',time()).".xls";
    	$ExcelWriter = PHPExcel_IOFactory::createWriter($Excel, 'Excel2007');
    	
    	$filePath = $_SERVER['DOCUMENT_ROOT'].'/attachment/temp/'.$fileName;
    	$ExcelWriter->save($filePath);
    	header('Location: /attachment/temp/'.$fileName);