mysql表结构
导出各班成绩到不同sheet表
public function testPHPExcel(){ set_time_limit(0);//防止超时 ini_set("memory_limit","512M");//防止内存过大 //加载PHPExcel的类 $this->load->library('PHPExcel'); $this->load->library('PHPExcel/IOFactory'); //创建PHPExcel实例 $excel = new PHPExcel(); /*获取数据库数据*/ $banji = $this->db->select('BanJi')->group_by('BanJi')->get('chengji_xuexiao_wenke')->result_array(); foreach ($banji as $key => $vbj) { if($key !== 0) $excel->createSheet(); $excel->setactivesheetindex($key); /** 设置工作表名称 */ $excel->getActiveSheet($key)->setTitle($vbj['BanJi']); //下面介绍项目中用到的几个关于excel的操作 $charActors = 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'); //用到的列 $widthSize = array(5, 10, 10, 10, 5,5,10,10,5,10,10,5,10,10,5,10,10,5,10,10,5,10,10,5,10,10); //列宽 $titleName = array('姓名', '考号', '学号','学校','班级','总分','总分联考排名','总分学校排名','语文','语文联考排名','语文学校排名','数学','数学联考排名','数学学校排名','英语','英语联考排名','英语学校排名','政治','政治联考排名','政治学校排名','历史','历史联考排名','历史学校排名','地理','地理联考排名','地理学校排名'); //写入表头 foreach ($charActors as $k => $v) { //加粗 $excel->getActiveSheet()->getStyle('A1:Z1')->getFont()->setBold(true); //对齐方式，水平居中 $excel->getActiveSheet()->getStyle('A1:Z1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $excel->getActiveSheet()->getStyle($v)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); //设置表格宽度 $excel->getActiveSheet()->getColumnDimension($v)->setWidth($widthSize[$k]); //设置单元格为文本 $excel->getActiveSheet()->getStyle($v)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT); //为单元格赋值 $excel->getActiveSheet()->setCellValue($v . 1, $titleName[$k]); } $xuexiao = $this->db->where('BanJi',$vbj['BanJi'])->get('chengji_xuexiao_wenke')->result_array(); $hangshu = 2;//从第二行开始写入数据 行数 //p($xuexiao); foreach ($xuexiao as $vxx) { $excel->getActiveSheet()->setCellValue('A' . $hangshu, $vxx['XingMing']); $excel->getActiveSheet()->setCellValue('B' . $hangshu, $vxx['KaoHao']); $excel->getActiveSheet()->setCellValue('C' . $hangshu, $vxx['XueHao']); $excel->getActiveSheet()->setCellValue('D' . $hangshu, $vxx['XueXiao']); $excel->getActiveSheet()->setCellValue('E' . $hangshu, $vxx['BanJi']); $excel->getActiveSheet()->setCellValue('F' . $hangshu, $vxx['ZongFen']); $excel->getActiveSheet()->setCellValue('G' . $hangshu, $vxx['ZongFenLianKaoPaiMing']); $excel->getActiveSheet()->setCellValue('H' . $hangshu, $vxx['ZongFenXueXiaoPaiMing']); $excel->getActiveSheet()->setCellValue('I' . $hangshu, $vxx['YuWen']); $excel->getActiveSheet()->setCellValue('J' . $hangshu, $vxx['YuWenLianKaoPaiMing']); $excel->getActiveSheet()->setCellValue('K' . $hangshu, $vxx['YuWenXueXiaoPaiMing']); $excel->getActiveSheet()->setCellValue('L' . $hangshu, $vxx['ShuXue']); $excel->getActiveSheet()->setCellValue('M' . $hangshu, $vxx['ShuXueLianKaoPaiMing']); $excel->getActiveSheet()->setCellValue('N' . $hangshu, $vxx['ShuXueXueXiaoPaiMing']); $excel->getActiveSheet()->setCellValue('O' . $hangshu, $vxx['YingYu']); $excel->getActiveSheet()->setCellValue('P' . $hangshu, $vxx['YingYuLianKaoPaiMing']); $excel->getActiveSheet()->setCellValue('Q' . $hangshu, $vxx['YingYuXueXiaoPaiMing']); $excel->getActiveSheet()->setCellValue('R' . $hangshu, $vxx['ZhengZhi']); $excel->getActiveSheet()->setCellValue('S' . $hangshu, $vxx['ZhengZhiLianKaoPaiMing']); $excel->getActiveSheet()->setCellValue('T' . $hangshu, $vxx['ZhengZhiXueXiaoPaiMing']); $excel->getActiveSheet()->setCellValue('U' . $hangshu, $vxx['LiShi']); $excel->getActiveSheet()->setCellValue('V' . $hangshu, $vxx['LiShiLianKaoPaiMing']); $excel->getActiveSheet()->setCellValue('W' . $hangshu, $vxx['LiShiXueXiaoPaiMing']); $excel->getActiveSheet()->setCellValue('X' . $hangshu, $vxx['DiLi']); $excel->getActiveSheet()->setCellValue('Y' . $hangshu, $vxx['DiLiLianKaoPaiMing']); $excel->getActiveSheet()->setCellValue('Z' . $hangshu, $vxx['DiLiXueXiaoPaiMing']); $hangshu++; } } //输出 $excel->setactivesheetindex(0);/** 设置第一个工作表为活动工作表 */ $write = new PHPExcel_Writer_Excel2007($excel); $file_name = '文科各班成绩表'; header("Pragma: public"); header("Expires: 0"); header("Cache-Control:must-revalidate, post-check=0, pre-check=0"); header("Content-Type:application/force-download"); header("Content-Type:application/vnd.ms-execl"); header("Content-Type:application/octet-stream"); header("Content-Type:application/download"); header('Content-Disposition:attachment;filename="' . $file_name . '.xlsx"'); header("Content-Transfer-Encoding:binary"); $write->save('php://output'); } public function testPHPE
