导出数据到excel多个sheet

时间:2023-01-21 09:35:03
    public function getTjDocDt(){
$sStartDate = (Input::has('start_date') && Input::get('start_date')) ? Input::get('start_date') : date('Y-m-01', strtotime(date("Y-m-d")));//起始日期,默认当月第一天
$sEndDate = (Input::has('end_date') && Input::get('end_date'))? Input::get('end_date') : date("Y-m-d");//截止日期,默认当天

header("Content-Type: application/vnd.ms-excel");
header("Expires: 0");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("content-disposition: attachment;filename=医生答题情况".$sStartDate.'-'.$sEndDate.".xls");

require_once app_path().'/include/excel/excel.class.php';
$xls = new Excel_XML('UTF-8', false);
//答题明细
$oAnsLists = RepresentMeetingAnswer::join('represent_meeting_them','represent_meeting_answer.themid','=','represent_meeting_them.id')
->join('doctor', 'represent_meeting_answer.doctor_id', '=', 'doctor.id')
->whereDate('represent_meeting_answer.created_at', '>=', $sStartDate)//根据时间提取
->whereDate('represent_meeting_answer.created_at', '<=', $sEndDate)
->select('doctor.weixin_id',
'doctor.name',
'doctor.hospital',
'doctor.subject',
'represent_meeting_them.id as them_id',
'represent_meeting_them.meeting_them',
'represent_meeting_answer.answer',
'represent_meeting_them.right')
->get();

$aInfo = array();//明细信息
$aTongji = array();//统计信息
$aInfo[] = array('openid','姓名','医院','科室','主题','有无正确答案','正确答案','医生答案','是否正确');
foreach($oAnsLists as $obj){
$aInfo[] = array($obj->weixin_id,$obj->name ,$obj->hospital , $obj->subject , $obj->meeting_them,($obj->right) ? '有' : '无', $obj->right,$obj->answer,
($obj->right && trim($obj->answer) != trim($obj->right)) ? '错误' :'正确' );

//统计数据,按主题
if(!isset($aTongji[$obj->them_id])){
$aTongji[$obj->them_id] = array('meeting_them' => $obj->meeting_them, 'right'=>$obj->right,'all_count' =>0, 'right_count' =>0, 'zql' =>0);
}
$aTongji[$obj->them_id]['all_count'] = $aTongji[$obj->them_id]['all_count'] +1;
if(!$obj->right || trim($obj->right) == trim($obj->answer)){
$aTongji[$obj->them_id]['right_count'] = $aTongji[$obj->them_id]['right_count'] +1;
}
$aTongji[$obj->them_id]['zql'] = ($aTongji[$obj->them_id]['all_count'] == 0) ? 0 : round(100*$aTongji[$obj->them_id]['right_count'] /$aTongji[$obj->them_id]['all_count'],2);
}
$xls->addSheet("医生答题明细",$aInfo);


//统计数据整理
$aResult = array();
$aResult[] = array('主题','正确答案','答题人数','答案正确人数','正确率');
foreach ($aTongji as $arr){
$aResult[] = array($arr['meeting_them'], $arr['right'], $arr['all_count'], $arr['right_count'], $arr['zql']);
}

$xls->addSheet("医生答题统计",$aResult);

$contents = $xls->generateXML('医生答题数据.xls');//创建XML文件
exit;

}