$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);