将数据 导出excel表格式

 

百度网盘依赖文件下载：

 

链接：https://pan.baidu.com/s/1L8HBtDtiBisAikQKbRnZIg 
提取码：cjje 
复制这段内容后打开百度网盘手机App，操作更方便哦

 

 

我的考试完提交生成的数据

 

这是我的考试题类型

 

 

 

 

//导出调查评议的数据
    public function diaocha(){
        $xlsName = '表格形式 调查评议 信息';
        $xlsTitle = array(
            array('ming','试题所属分类'),
            array('renshu','共评价人'),
            array('fenshu','总评价分'),
            array('ping','总平均分'),
            array('liang','良好(%)'),
            array('yiban','一般(%)'),
            array('cha','差(%)'),
        );
        $leiid=M('linzi_kaoshi_user')->where(array('ti_id'=>2))->group('lei_id')->getField('lei_id',true);
        $xlsData = array();
        foreach($leiid as $k=>$r){
            //试题分类
            $lei_title = M('linzi_tijian_lei')->where(array('id'=>$r['lei_id']))->find();
            $lei_title2 = M('linzi_tijian')->where(array('id'=>$lei_title['tj_id']))->getField('title');
            $xlsData[$k]['ming'] = $lei_title2 .'——'. $lei_title['title'];//试题所属分类
            $data['ti_id'] = 2;
            $data['lei_id'] = $r;
            $list = M('linzi_kaoshi_user')->where($data)->select();
            $xlsData[$k]['renshu'] = count($list);//总人数
            $fenshu = M('linzi_kaoshi_user')->where($data)->sum('sum');
            $xlsData[$k]['fenshu'] = round($fenshu,2);//总分数
            $xlsData[$k]['ping'] = round($xlsData[$k]['fenshu']/$xlsData[$k]['renshu'],2);//总平均分
            //良好，一般，很差
            $liang = 0;
            $yiban = 0;
            $cha = 0;
            $tishu = 0;
            foreach($list as $kk=>$v){
                $wode = json_decode($v['wode'],true);
                $tishu = $tishu+count($wode);//总题数；
                foreach($wode as $kkk=>$vv){
                    $ti_id = explode('xuanze',$vv['tihao']);
                    $kaoshi = M('linzi_kaoshi')->where(array('id'=>$ti_id[1]))->find();
                    $ks_xuan = json_decode($kaoshi['xuan'],true);
                    //循环我选的答案，索引（0 良好）的有几个  索引（1 一般）的有几个  索引（2 很差）的有几个，这是我固定好的数据值 
                    switch($vv['daan'][0]){
                        case 0:
                            $liang=$liang+1;
                            break;
                        case 1:
                            $yiban=$yiban+1;
                            break;
                        case 2:
                            $cha=$cha+1;
                            break;    
                    }
                }
            }
            
            $xlsData[$k]['liang'] = round($liang/$tishu,2)*100;
            $xlsData[$k]['yiban'] = round($yiban/$tishu,2)*100;
            $xlsData[$k]['cha'] = round($cha/$tishu,2)*100;
        }
//        dump($xlsData);die();
        exportExcel($xlsName,$xlsTitle,$xlsData);
    }

 

exportExcel方法是放在function.php里的  此方法可以导入导出

//导出excel表格
function exportExcel($expTitle,$expCellName,$expTableData){
    $xlsTitle = iconv('utf-8', 'gb2312', $expTitle);//文件名称
    $fileName = $_SESSION['account'].date('_YmdHis');//or $xlsTitle 文件名称可根据自己情况设定
    $cellNum = count($expCellName);
    $dataNum = count($expTableData);
    vendor("PHPExcel.PHPExcel");

    $objPHPExcel = new PHPExcel();
    $cellName = array('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z','AA','AB','AC','AD','AE','AF','AG','AH','AI','AJ','AK','AL','AM','AN','AO','AP','AQ','AR','AS','AT','AU','AV','AW','AX','AY','AZ','BA','BB','BC','BD','BE','BF','BG','BH','BI');

    //$objPHPExcel->getActiveSheet(0)->mergeCells('A1:'.$cellName[$cellNum-1].'1');//合并单元格
    // $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', $expTitle.'  Export time:'.date('Y-m-d H:i:s'));
    for($i=0;$i<$cellNum;$i++){
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$i].'1', $expCellName[$i][1]);
    }
    // Miscellaneous glyphs, UTF-8
    for($i=0;$i<$dataNum;$i++){
        for($j=0;$j<$cellNum;$j++){
            $objPHPExcel->getActiveSheet(0)->setCellValue($cellName[$j].($i+2), $expTableData[$i][$expCellName[$j][0]]);
        }
    }

    header('pragma:public');
    header('Content-type:application/vnd.ms-excel;charset=utf-8;name="'.$xlsTitle.'.xls"');
    header("Content-Disposition:attachment;filename=$fileName.xls");//attachment新窗口打印inline本窗口打印
    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
    $objWriter->save('php://output');
    exit;
}