Yii框架使用PHPExcel导出Excel文件的方法分析【改进版】
本文实例讲述了Yii框架使用PHPExcel导出Excel文件的方法。分享给大家供大家参考,具体如下:
最近在研究PHP的Yii框架,很喜欢,碰到导出Excel的问题,研究了一下,就有了下面的方法:
1、首先在config\main.php中添加对PHPExcel的引用,我的方式是这样:
//autoloadingmodelandcomponentclasses 'import'=>array( /*'application.modules.srbac.controllers.SBaseController',*/ 'application.models.*', 'application.components.*', 'application.extensions.phpexcel.*', ),
另外也有人用components这个配置,但是我的有问题,所以就用上面的方法。
2、按照下面的代码修改PHPExcel代码目录里的Autoloader.php文件:
publicstaticfunctionRegister(){ /*if(function_exists('__autoload')){ //RegisteranyexistingautoloaderfunctionwithSPL,sowedon'tgetanyclashes spl_autoload_register('__autoload'); } //RegisterourselveswithSPL returnspl_autoload_register(array('PHPExcel_Autoloader','Load'));*/ $functions=spl_autoload_functions(); foreach($functionsas$function) spl_autoload_unregister($function); $functions=array_merge(array(array('PHPExcel_Autoloader','Load')),$functions); foreach($functionsas$function) $x=spl_autoload_register($function); return$x; }//functionRegister()
上面的函数中,注释掉的是原有的代码。
3、下面的代码是输出Excel,以及一些常用的属性设置,在你的controller中:
/* 导出为Excel */ publicfunctionactionExport() { $objectPHPExcel=newPHPExcel(); $objectPHPExcel->setActiveSheetIndex(0); $page_size=52; //数据的取出 $model=Yii::app()->session['printdata']; $dataProvider=$model->search(); $dataProvider->setPagination(false); $data=$dataProvider->getData(); $count=$dataProvider->getTotalItemCount(); //总页数的算出 $page_count=(int)($count/$page_size)+1; $current_page=0; $n=0; foreach($dataas$product) { if($n%$page_size===0) { $current_page=$current_page+1; //报表头的输出 $objectPHPExcel->getActiveSheet()->mergeCells('B1:G1'); $objectPHPExcel->getActiveSheet()->setCellValue('B1','产品信息表'); $objectPHPExcel->setActiveSheetIndex(0)->setCellValue('B2','产品信息表'); $objectPHPExcel->setActiveSheetIndex(0)->setCellValue('B2','产品信息表'); $objectPHPExcel->setActiveSheetIndex(0)->getStyle('B1')->getFont()->setSize(24); $objectPHPExcel->setActiveSheetIndex(0)->getStyle('B1') ->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objectPHPExcel->setActiveSheetIndex(0)->setCellValue('B2','日期:'.date("Y年m月j日")); $objectPHPExcel->setActiveSheetIndex(0)->setCellValue('G2','第'.$current_page.'/'.$page_count.'页'); $objectPHPExcel->setActiveSheetIndex(0)->getStyle('G2') ->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT); //表格头的输出 $objectPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(5); $objectPHPExcel->setActiveSheetIndex(0)->setCellValue('B3','编号'); $objectPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(6.5); $objectPHPExcel->setActiveSheetIndex(0)->setCellValue('C3','名称'); $objectPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(17); $objectPHPExcel->setActiveSheetIndex(0)->setCellValue('D3','生产厂家'); $objectPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(22); $objectPHPExcel->setActiveSheetIndex(0)->setCellValue('E3','单位'); $objectPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(15); $objectPHPExcel->setActiveSheetIndex(0)->setCellValue('F3','单价'); $objectPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(15); $objectPHPExcel->setActiveSheetIndex(0)->setCellValue('G3','在库数'); $objectPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(15); //设置居中 $objectPHPExcel->getActiveSheet()->getStyle('B3:G3') ->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); //设置边框 $objectPHPExcel->getActiveSheet()->getStyle('B3:G3') ->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN); $objectPHPExcel->getActiveSheet()->getStyle('B3:G3') ->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN); $objectPHPExcel->getActiveSheet()->getStyle('B3:G3') ->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN); $objectPHPExcel->getActiveSheet()->getStyle('B3:G3') ->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN); $objectPHPExcel->getActiveSheet()->getStyle('B3:G3') ->getBorders()->getVertical()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN); //设置颜色 $objectPHPExcel->getActiveSheet()->getStyle('B3:G3')->getFill() ->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('FF66CCCC'); } //明细的输出 $objectPHPExcel->getActiveSheet()->setCellValue('B'.($n+4),$product->id); $objectPHPExcel->getActiveSheet()->setCellValue('C'.($n+4),$product->product_name); $objectPHPExcel->getActiveSheet()->setCellValue('D'.($n+4),$product->product_agent->name); $objectPHPExcel->getActiveSheet()->setCellValue('E'.($n+4),$product->unit); $objectPHPExcel->getActiveSheet()->setCellValue('F'.($n+4),$product->unit_price); $objectPHPExcel->getActiveSheet()->setCellValue('G'.($n+4),$product->library_count); //设置边框 $currentRowNum=$n+4; $objectPHPExcel->getActiveSheet()->getStyle('B'.($n+4).':G'.$currentRowNum) ->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN); $objectPHPExcel->getActiveSheet()->getStyle('B'.($n+4).':G'.$currentRowNum) ->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN); $objectPHPExcel->getActiveSheet()->getStyle('B'.($n+4).':G'.$currentRowNum) ->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN); $objectPHPExcel->getActiveSheet()->getStyle('B'.($n+4).':G'.$currentRowNum) ->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN); $objectPHPExcel->getActiveSheet()->getStyle('B'.($n+4).':G'.$currentRowNum) ->getBorders()->getVertical()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN); $n=$n+1; } //设置分页显示 //$objectPHPExcel->getActiveSheet()->setBreak('I55',PHPExcel_Worksheet::BREAK_ROW); //$objectPHPExcel->getActiveSheet()->setBreak('I10',PHPExcel_Worksheet::BREAK_COLUMN); $objectPHPExcel->getActiveSheet()->getPageSetup()->setHorizontalCentered(true); $objectPHPExcel->getActiveSheet()->getPageSetup()->setVerticalCentered(false); ob_end_clean(); ob_start(); header('Content-Type:application/vnd.ms-excel'); header('Content-Disposition:attachment;filename="'.'产品信息表-'.date("Y年m月j日").'.xls"'); $objWriter=PHPExcel_IOFactory::createWriter($objectPHPExcel,'Excel5'); $objWriter->save('php://output'); }
代码执行后,会直接生成Excel,并提示下载或打开。
更多关于Yii相关内容感兴趣的读者可查看本站专题:《Yii框架入门及常用技巧总结》、《php优秀开发框架总结》、《smarty模板入门基础教程》、《php面向对象程序设计入门教程》、《php字符串(string)用法总结》、《php+mysql数据库操作入门教程》及《php常见数据库操作技巧汇总》
希望本文所述对大家基于Yii框架的PHP程序设计有所帮助。
声明:本文内容来源于网络,版权归原作者所有,内容由互联网用户自发贡献自行上传,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任。如果您发现有涉嫌版权的内容,欢迎发送邮件至:czq8825#qq.com(发邮件时,请将#更换为@)进行举报,并提供相关证据,一经查实,本站将立刻删除涉嫌侵权内容。