php导出俩个Sheet

来源:互联网 发布:重庆鹏业软件 编辑:程序博客网 时间:2024/06/08 15:56
public function actionExcel(){
        //设置内存
        ini_set("memory_limit", "2048M");
        set_time_limit(0);

        //去用户表获取用户信息
        $user = UserInfo::find()->where(['ui_id'=>1])->asArray()->all();
        $objectPHPExcel = new \PHPExcel();
        $objectPHPExcel->setActiveSheetIndex(0);
        //给Sheet设置名字
        $objectPHPExcel->getActiveSheet()->setTitle("报表统计");
        //设置表格头的输出
        $objectPHPExcel->setActiveSheetIndex()->setCellValue('A1', '座席');
        $objectPHPExcel->setActiveSheetIndex()->setCellValue('B1', '工号');
        $objectPHPExcel->setActiveSheetIndex()->setCellValue('C1', '座席来电数');
        $objectPHPExcel->setActiveSheetIndex()->setCellValue('D1', '座席来电接听数');
        $objectPHPExcel->setActiveSheetIndex()->setCellValue('E1', '座席来电未听数');
        $objectPHPExcel->setActiveSheetIndex()->setCellValue('F1', '接通率');
        $objectPHPExcel->setActiveSheetIndex()->setCellValue('G1', '总接通量占比');
        $objectPHPExcel->setActiveSheetIndex()->setCellValue('H1', '总外呼数');
        $objectPHPExcel->setActiveSheetIndex()->setCellValue('I1', '外呼双方接听数');
        $objectPHPExcel->setActiveSheetIndex()->setCellValue('J1', '呼入总通话时间');
        $objectPHPExcel->setActiveSheetIndex()->setCellValue('K1', '呼入平均通话时长');
        $objectPHPExcel->setActiveSheetIndex()->setCellValue('L1', '外呼总通话时间');
        $objectPHPExcel->setActiveSheetIndex()->setCellValue('M1', '外呼平均通话时间');
        $objectPHPExcel->setActiveSheetIndex()->setCellValue('N1', '总登录时长');
        $objectPHPExcel->setActiveSheetIndex()->setCellValue('O1', '总置忙时长');
        $objectPHPExcel->setActiveSheetIndex()->setCellValue('P1', '总空闲时长');


        $n = 2;
        foreach ($user as $v){
            $objectPHPExcel->getActiveSheet()->setCellValue('A'.($n) ,$v['ui_id']);
            $objectPHPExcel->getActiveSheet()->setCellValue('B'.($n) ,$v['ui_id']);
            $objectPHPExcel->getActiveSheet()->setCellValue('C'.($n) ,$v['ui_id']);
            $objectPHPExcel->getActiveSheet()->setCellValue('D'.($n) ,$v['ui_id']);
            $objectPHPExcel->getActiveSheet()->setCellValue('E'.($n) ,$v['ui_id']);
            $objectPHPExcel->getActiveSheet()->setCellValue('F'.($n) ,$v['ui_id']);
            $objectPHPExcel->getActiveSheet()->setCellValue('G'.($n) ,$v['ui_id']);
            $objectPHPExcel->getActiveSheet()->setCellValue('H'.($n) ,$v['ui_id']);
            $objectPHPExcel->getActiveSheet()->setCellValue('I'.($n) ,$v['ui_id']);
            $objectPHPExcel->getActiveSheet()->setCellValue('J'.($n) ,$v['ui_id']);
            $objectPHPExcel->getActiveSheet()->setCellValue('K'.($n) ,$v['ui_id']);
            $objectPHPExcel->getActiveSheet()->setCellValue('L'.($n) ,$v['ui_id']);
            $objectPHPExcel->getActiveSheet()->setCellValue('M'.($n) ,$v['ui_id']);
            $objectPHPExcel->getActiveSheet()->setCellValue('N'.($n) ,$v['ui_id']);
            $objectPHPExcel->getActiveSheet()->setCellValue('O'.($n) ,$v['ui_id']);
            $objectPHPExcel->getActiveSheet()->setCellValue('P'.($n) ,$v['ui_id']);
            $n = $n +1;
        }
        $i = 2;
        $objectPHPExcel->createSheet();
        $objectPHPExcel->setActiveSheetIndex(1);
        //给Sheet设置名字
        $objectPHPExcel->getActiveSheet()->setTitle("满意度统计");
        $objectPHPExcel->setActiveSheetIndex(1)->setCellValue('A1', '座席');
        $objectPHPExcel->setActiveSheetIndex(1)->setCellValue('B1', '工号');
        $objectPHPExcel->setActiveSheetIndex(1)->setCellValue('C1', '调查总数');
        $objectPHPExcel->setActiveSheetIndex(1)->setCellValue('D1', '按键数');
        $objectPHPExcel->setActiveSheetIndex(1)->setCellValue('E1', '放弃数');
        $objectPHPExcel->setActiveSheetIndex(1)->setCellValue('F1', '非常满意');
        $objectPHPExcel->setActiveSheetIndex(1)->setCellValue('G1', '满意');
        $objectPHPExcel->setActiveSheetIndex(1)->setCellValue('H1', '不满意');
        foreach ($user as $v){
            $objectPHPExcel->getActiveSheet()->setCellValue('A'.($i) ,$v['ui_id']);
            $objectPHPExcel->getActiveSheet()->setCellValue('B'.($i) ,$v['ui_id']);
            $objectPHPExcel->getActiveSheet()->setCellValue('C'.($i) ,$v['ui_id']);
            $objectPHPExcel->getActiveSheet()->setCellValue('D'.($i) ,$v['ui_id']);
            $objectPHPExcel->getActiveSheet()->setCellValue('E'.($i) ,$v['ui_id']);
            $objectPHPExcel->getActiveSheet()->setCellValue('F'.($i) ,$v['ui_id']);
            $objectPHPExcel->getActiveSheet()->setCellValue('G'.($i) ,$v['ui_id']);
            $objectPHPExcel->getActiveSheet()->setCellValue('H'.($i) ,$v['ui_id']);
            $i = $i +1;
        }

        ob_end_clean();
        ob_start();
        header('Content-Type : application/vnd.ms-excel');

        //设置输出文件名及格式
        header('Content-Disposition:attachment;filename="座席报表统计'.'.xls"');

        //导出.xls格式的话使用Excel5,若是想导出.xlsx需要使用Excel2007
        $objWriter= \PHPExcel_IOFactory::createWriter($objectPHPExcel,'Excel5');
        $objWriter->save('php://output');
        ob_end_flush();

        //清空数据缓存
        unset($data);
    }
原创粉丝点击