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_Excel($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');}
附：可能出现超时和内存过大情况
Maximum execution time of 30 seconds exceeded，
简单总结一下解决办法：
报错一：内存超限，具体报错语句忘了，简单说一下解决办法。
利用循环分批导入；
每个循环内部开始处使用sleep(5);语句，做延迟执行，防止服务器内存同一时间占用过多，里面数字据情况修改；
每个循环内部结束地方使用 ob_flush();刷新输出缓冲
flush();将当前为止程序的所有输出发送到用户的浏览器
两者必须同时使用来刷新输出缓冲
报错二：30秒运行超时的错误（Maximum execution time of 30 seconds exceeded）
解决办法：
方法一，修改php.ini文件
max_execution_time = 30; Maximum execution time of each script, in seconds
把它设置成需要的值就可以了。如果设置成0的话，就是永不过期。
方法二，修改php执行文件（推荐）
加上
<?
set_time_limit(0);
?>
max_execution_time = 30; Maximum execution time of each script, in seconds
把它设置成需要的值就可以了。如果设置成0的话，就是永不过期。
Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 24 bytes) in
出现该错误的原因：
是因为php页面消耗的最大内存默认是为 8M (在PHP的ini件里可以看到) ,如果文件太大 或图片太大 在读取的时候 会发生上述错误。
解决办法：
1，修改php.ini
将memory_limit由 8M 改成 16M（或更大），重启apache服务
2，在PHP 文件中 加入ini_set(”memory_limit”,”100M”);（推荐）
注意:为了系统的其它资源的正常使用 请您不要将 memory_limit设置太大，其中-1为不限
3，修改.htaccess 文档（前提是该目录支持.htaccess）
在文档中新增一句：php_value memory_limit 16M(或更大)
导入
public function daoruwen(){set_time_limit(0);//防止超时ini_set("memory_limit","512M");//防止内存过大 //处理上传的文件$config['upload_path'] = './';$config['allowed_types'] = 'xls';$config['max_size'] = '200000';$config['max_width'] = '2000';//1280*407$config['max_height'] = '2000';$config['max_filename'] = '40';$config['encrypt_name'] = false;$this->load->library('upload',$config);if ( ! $this->upload->do_upload('myfile')){/*p($this->upload->display_errors());*/echo "<script>alert('文件上传不成功!');window.history.back(-1)</script>";}else{//上传成功后处理$filedata = $this->upload->data();$filename = $filedata['file_name'];//$filePath = base_url().'public/'.$filename;$filePath = $filename;$this->load->library('PHPExcel');$this->load->library('PHPExcel/IOFactory');//创建PHPExcel实例$excel = new PHPExcel();$objReader = IOFactory::createReader('Excel5');//use excel for format$objPHPExcel = $objReader->load($filePath); //$filename可以是上传的文件，或者是指定的文件$sheet = $objPHPExcel->getSheet(0);$highestRow = $sheet->getHighestRow(); // 取得总行数$highestColumn = $sheet->getHighestColumn(); // 取得总列数//$k = 0;//循环读取excel文件,读取一条,插入一条for($rowCurrent=2;$rowCurrent<=$highestRow;$rowCurrent++){$a = $objPHPExcel->getActiveSheet()->getCell("A".$rowCurrent)->getValue();//获取A列的值$b = $objPHPExcel->getActiveSheet()->getCell("B".$rowCurrent)->getValue();//获取B列的值$c = $objPHPExcel->getActiveSheet()->getCell("C".$rowCurrent)->getValue();//获取C列的值$d = $objPHPExcel->getActiveSheet()->getCell("D".$rowCurrent)->getValue();//获取D列的值$e = $objPHPExcel->getActiveSheet()->getCell("E".$rowCurrent)->getValue();//获取A列的值$f = $objPHPExcel->getActiveSheet()->getCell("F".$rowCurrent)->getValue();//获取B列的值$g = $objPHPExcel->getActiveSheet()->getCell("G".$rowCurrent)->getValue();//获取C列的值$h = $objPHPExcel->getActiveSheet()->getCell("H".$rowCurrent)->getValue();//获取D列的值$i = $objPHPExcel->getActiveSheet()->getCell("I".$rowCurrent)->getValue();//获取A列的值$j = $objPHPExcel->getActiveSheet()->getCell("J".$rowCurrent)->getValue();//获取B列的值$k = $objPHPExcel->getActiveSheet()->getCell("K".$rowCurrent)->getValue();//获取C列的值$l = $objPHPExcel->getActiveSheet()->getCell("L".$rowCurrent)->getValue();//获取D列的值$m = $objPHPExcel->getActiveSheet()->getCell("M".$rowCurrent)->getValue();//获取A列的值$n = $objPHPExcel->getActiveSheet()->getCell("N".$rowCurrent)->getValue();//获取B列的值$o = $objPHPExcel->getActiveSheet()->getCell("O".$rowCurrent)->getValue();//获取C列的值$p = $objPHPExcel->getActiveSheet()->getCell("P".$rowCurrent)->getValue();//获取D列的值$q = $objPHPExcel->getActiveSheet()->getCell("Q".$rowCurrent)->getValue();//获取A列的值$r = $objPHPExcel->getActiveSheet()->getCell("R".$rowCurrent)->getValue();//获取B列的值$s = $objPHPExcel->getActiveSheet()->getCell("S".$rowCurrent)->getValue();//获取C列的值$t = $objPHPExcel->getActiveSheet()->getCell("T".$rowCurrent)->getValue();//获取D列的值$u = $objPHPExcel->getActiveSheet()->getCell("U".$rowCurrent)->getValue();//获取A列的值$v = $objPHPExcel->getActiveSheet()->getCell("V".$rowCurrent)->getValue();//获取B列的值$w = $objPHPExcel->getActiveSheet()->getCell("W".$rowCurrent)->getValue();//获取C列的值$x = $objPHPExcel->getActiveSheet()->getCell("X".$rowCurrent)->getValue();//获取D列的值$y = $objPHPExcel->getActiveSheet()->getCell("Y".$rowCurrent)->getValue();//获取C列的值$z = $objPHPExcel->getActiveSheet()->getCell("Z".$rowCurrent)->getValue();//获取D列的值$this->db->insert('chengji_quanshi_wenke',array('XingMing'=>$a,'KaoHao'=>$b,'XueHao'=>$c,'XueXiao'=>$d,'BanJi'=>$e,'ZongFen'=>$f,'ZongFenLianKaoPaiMing'=>$g,'ZongFenXueXiaoPaiMing'=>$h,'YuWen'=>$i,'YuWenLianKaoPaiMing'=>$j,'YuWenXueXiaoPaiMing'=>$k,'ShuXue'=>$l,'ShuXueLianKaoPaiMing'=>$m,'ShuXueXueXiaoPaiMing'=>$n,'YingYu'=>$o,'YingYuLianKaoPaiMing'=>$p,'YingYuXueXiaoPaiMing'=>$q,'ZhengZhi'=>$r,'ZhengZhiLianKaoPaiMing'=>$s,'ZhengZhiXueXiaoPaiMing'=>$t,'LiShi'=>$u,'LiShiLianKaoPaiMing'=>$v,'LiShiXueXiaoPaiMing'=>$w,'DiLi'=>$x,'DiLiLianKaoPaiMing'=>$y,'DiLiXueXiaoPaiMing'=>$z));}success('导入成功！',site_url('home/showquanshiwen'));}
