yii2 phpexcel使用教程 项目实战

把数据导出为excel格式的文件，过程很简单，加上下载 phpexcel 插件的时间，网速快的话。整个流程完成，十分钟左右。这是项目实例。

1.首先 composer require "phpoffice/phpexcel": "dev-develop" -vvv下载此插件

2.然后在Autoloader.php修改 register () 方法   路径、具体代码 如下

   路径：vendor/phpoffice/phpexcel/classes/phpexcel/autoloader.php

public static function register ()

{

/*if (function_exists('__autoload')) {

// Register any existing autoloader function with SPL, so we don't get any clashes

spl_autoload_register('__autoload');

}

// Register ourselves with SPL

if (version_compare(PHP_VERSION, '5.3.0') >= 0) {

return spl_autoload_register(array('PHPExcel_Autoloader', 'load'), true, true);

} else {

return spl_autoload_register(array('PHPExcel_Autoloader', 'load'));

}*/

//以上代码是原生的，注释掉，添加如下代码

$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.在控制其中按如下代码格式写就好了（改成自己的字段，表头）

（1）在控制器中引用命名空间

（2）其次在控制器中的代码如下，

// 导出mysql数据到 excel 文件

public function actionExport ()

{

$objectPHPExcel = new PHPExcel () ;

$objectPHPExcel -> setActiveSheetIndex ( 0 ) ;

// 数据开始

//mysql查询语

$products = Consumerinfo :: find () -> orderBy ( 'data_time DESC' ) -> all () ;

$n = 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',' 状态： 2[ 待审核 ],3[ 审核通过 ],4[ 审核未通过 ]');

$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 ( 10 ) ;

$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 ( 10 ) ;

$objectPHPExcel -> setActiveSheetIndex ( 0 ) -> setCellValue ( 'F3' , ' 数据时间 ' ) ;

$objectPHPExcel -> getActiveSheet () -> getColumnDimension ( 'F' ) -> setWidth ( 25 ) ;

$objectPHPExcel -> setActiveSheetIndex ( 0 ) -> setCellValue ( 'G3' , ' 创建时间 ' ) ;

$objectPHPExcel -> getActiveSheet () -> getColumnDimension ( 'G' ) -> setWidth ( 25 ) ;

$objectPHPExcel -> setActiveSheetIndex ( 0 ) -> setCellValue ( 'H3' , ' 总共人数 ' ) ;

$objectPHPExcel -> getActiveSheet () -> getColumnDimension ( 'H' ) -> setWidth ( 15 ) ;

$objectPHPExcel -> setActiveSheetIndex ( 0 ) -> setCellValue ( 'I3' , ' 减少人数 ' ) ;

$objectPHPExcel -> getActiveSheet () -> getColumnDimension ( 'I' ) -> setWidth ( 15 ) ;

$objectPHPExcel -> setActiveSheetIndex ( 0 ) -> setCellValue ( 'J3' , ' 新增人数 ' ) ;

$objectPHPExcel -> getActiveSheet () -> getColumnDimension ( 'J' ) -> setWidth ( 15 ) ;

$objectPHPExcel -> setActiveSheetIndex ( 0 ) -> setCellValue ( 'K3' , ' 状态 ' ) ;

foreach ( $products as $product ) {

// 数据

$n ++ ;

switch ( $product -> status ){

case 2 : $status = " 未审核 " ; break ;

case 3 : $status = " 未审核 " ; break ;

case 4 : $status = " 未审核 " ; break ;

default:break ;

}

$objectPHPExcel -> getActiveSheet () -> setCellValue ( 'B' . ( $n + 2 ) , $product -> group_name ) ;

$objectPHPExcel -> getActiveSheet () -> setCellValue ( 'C' . ( $n + 2 ) , $product -> acount_name ) ;

$objectPHPExcel -> getActiveSheet () -> setCellValue ( 'D' . ( $n + 2 ) , $product -> sellwechat_num ) ;

$objectPHPExcel -> getActiveSheet () -> setCellValue ( 'E' . ( $n + 2 ) , $product -> sources ) ;

$objectPHPExcel -> getActiveSheet () -> setCellValue ( 'F' . ( $n + 2 ) , date ( 'Y-m-d H:i:s' , $product -> data_time )) ;//这里加了个date函数处理时间数据

$objectPHPExcel -> getActiveSheet () -> setCellValue ( 'G' . ( $n + 2 ) , date ( 'Y-m-d H:i:s' , $product -> create_time )) ;

$objectPHPExcel -> getActiveSheet () -> setCellValue ( 'H' . ( $n + 2 ) , $product -> total_persons ) ;

$objectPHPExcel -> getActiveSheet () -> setCellValue ( 'I' . ( $n + 2 ) , $product -> reduce_persons ) ;

$objectPHPExcel -> getActiveSheet () -> setCellValue ( 'J' . ( $n + 2 ) , $product -> increase_persons ) ;

$objectPHPExcel -> getActiveSheet () -> setCellValue ( 'K' . ( $n + 2 ) , $status ) ;

}

// 数据结束

ob_end_clean () ;

ob_start () ;

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

header ( 'Content-Disposition:attachment;filename="' . 'sales_datas-' . date ( "Ymj" ) . '.xls"' ) ;//给文件命名

$objWriter = \ PHPExcel_IOFactory :: createWriter ( $objectPHPExcel , 'Excel5' ) ;

$objWriter -> save ( 'php://output' ) ;//输出excel文件，并自动下载

}