Yii2框架中使用PHPExcel导出Excel文件的示例

　　最近在研究PHP的Yii框架，很喜欢，碰到导出Excel的问题，研究了一下，就有了下面的方法：

　　最简单的利用composer安装

　　 composer require phpoffice/phpexcel: *

　　如果没有安装conposer可以参考下面1.2步

　　1、引入PHPExcel

　　首先得要下载phpexcel地址:

　　你可以直接在入口文件index.php中引入，也可以在你定义的controller类之前，只要是在你使用之前引入就可以

　　 require dirname(dirname(__FILE__))./excel/PHPExcel.php;

　　或者在phpexcel类里修改相应的namespace也可。

　　2、按照下面的代码修改PHPExcel代码目录里的Autoloader.php文件，对比源文件改成：

　　 public static function Register() { $functions = spl_autoload_functions(); foreach ( $functions as $function) spl_autoload_unregister($function); $functions = array_merge(array(array(PHPExcel_Autoloader,Load)),$functions); foreach ( $functions as $function) $x = spl_autoload_register($function); return $x; }

　　上面的函数中，注释掉的是原有的代码。

　　3、下面的代码是输出Excel，以及一些常用的属性设置，在controller中：

　　 public function actionExport() { $objectPHPExcel = new PHPExcel(); $objectPHPExcel->setActiveSheetIndex(0); $page_size = 52; $model = new NewsSearch(); $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 ( $data as $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，并提示下载或打开。

　　以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持脚本之家。