Yii框架使用PHPExcel导出Excel文件的方法分析【改进版】

本文实例讲述了yii框架使用phpexcel导出excel文件的方法。分享给大家供大家参考，具体如下：

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

1、首先在config\main.php中添加对phpexcel的引用，我的方式是这样：

// autoloading model and component class'import'=>array(    /*'application.modules.srbac.controllers.sbacontroller',*/    'application.models.*',    'application.components.*',    'application.extensions.phpexcel.*'励志233文章,),

另外也有人用components 这个配置，但是我的有问题，所以就用上面的方法。

2、按照下面的代码修改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 ourlves with spl    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;}  //  function register()

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

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

/*导出为excel*/public function actionexport(){    $objectphpexcel = new phpexcel();    $objectphpexcel->tactivesheetindex(0);    $page_size = 52;    //数据的取出    $model = yii::app()->ssion['printdata'];    $dataprovider = $model->arch();    $dataprovider->tpagination(fal);    $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()->tcellvalue('b1','产品信息表');        $objectphpexcel->tactivesheetindex(0)->tcellvalue('b2','产品信息表');        $objectphpexcel->tactivesheetindex(0)->tcellvalue('b2','产品信息表');        $objectphpexcel->tactivesheetindex(0)->getstyle('b1')->getfont()->tsize(24);        $objectphpexcel->tactivesheetindex(0)->getstyle('b1')          ->getalignment()->thorizontal(phpexcel_style_alignment::horizontal_center);        $objectphpexcel->tactivesheetindex(0)->tcellvalue('b2','日期：'.date("y年m月j日"));        $objectphpexcel->tactivesheetindex(0)->tcellvalue('g2','第'.$current_page.'/'.$page_count.'页');        $objectphpexcel->tactivesheetindex(0)->getstyle('g2')          ->getalignment()->thorizontal(phpexcel_style_alignment::horizontal_right);        //表格头的输出        $objectphpexcel->getactivesheet()->getcolumndimension('a')->twidth(5);        $objectphpexcel->tactivesheetindex(0)->tcellvalue('b3','编号');        $objectphpexcel->getactivesheet()->getcolumndimension('b')->twidth(6.5);   喜欢两个人歌词     $objectphpexcel->tactivesheetindex(0)->tcellvalue('c3','名称');        $objectphpexcel->getactivesheet()->getcolumndimension('c')->twidth(17);        $objectphpexcel->tactivesheetindex(0)->tcellvalue('d3','生产厂家');        $objectphpexcel->getactivesheet()->getcolumndimension('d')->twidth(22);        $objectphpexcel->tactivesheetindex(0)->tcellvalue('e3','单位');        $objectphpexcel->getactivesheet()->getcolumndimension('e')->twidth(15);        $objectphpexcel->tactivesheetindex(0)->tcellvalue('f3','单价');        $objectphpexcel->getactivesheet()->getcolumndimension('f')->twidth(15);        $objectphpexcel->tactivesheetindex(0)->tcellvalue('g3','在库数');        $objectphpexcel->getactivesheet()->getcolumndimension('g')->twidth(15);        //设置居中        $objectphpexcel->getactivesheet()->getstyle('b3:g3')          ->getalignment()->thorizontal(phpexcel_style_alignment::horizontal_center);        //设置边框        $objectphpexcel->getactivesheet()->getstyle('b3:g3' )          ->getborders()->gettop()->tborderstyle(phpexcel_style_border::border_thin);        $objectphpexcel->getactivesheet()->getstyle('b3:g3' )          ->getborders()->getleft()->tborderstyle(phpexcel_style_border::border_thin);        $objectphpexcel->getactivesheet()->getstyle('b3:g3高中生物必修一课本' )          ->getborders()->getright()->tborderstyle(phpexcel_style_border::border_thin);        $objectphpexcel->getactivesheet()->getstyle('b3:g3' )          ->getborders()->getbottom()->tborderstyle(phpexcel_style_border::border_thin);        $objectphpexcel->getactivesheet()->getstyle('b3:g3' )          ->getborders()->getvertical()->tborderstyle(phpexcel_style_border::border_thin);        //设置颜色        $objectphpexcel->getactivesheet()->getstyle('b3:g3')->getfill()          ->tfilltype(phpexcel_style_fill::fill_solid)->getstartcolor()->targb('ff66cccc');      }      //明细的输出      $objectphpexcel->getactivesheet()->tcellvalue('b'.($n+4) ,$product->id);      $objectphpexcel->getactivesheet()->tcellvalue('c'.($n+4) ,$product->product_name);      $objectphpexcel->getactivesheet()->tcellvalue('d'.($n+4) ,$product->product_agent->name);      $objectphpexcel->getactivesheet()->tcellvalue('e'.($n+4) ,$product->unit);      $objectphpexcel->getactivesheet()->tcellvalue('f'.($n+4) ,$product->unit_price);      $objectphpexcel->getactivesheet()->tcellvalue('g'.($n+4) ,$product->library_count);      //设置边框      $currentrownum = $n+4;      $objectphpexcel->getactivesheet()->getstyle('b'.($n+4).':g'.$currentrownum )          ->getborders()->gettop()->tborderstyle(phpexcel_style_border::border_thin);      $objectphpexcel->getactivesheet()->getst铁与硫反应yle('b'.($n+4).':g'.$currentrownum )          ->getborders()->getleft()->tborderstyle(phpexcel_style_border::bor指标生和统招生的区别der_thin);      $objectphpexcel->getactivesheet()->getstyle('b'.($n+4).':g'.$currentrownum )          ->getborders()->getright()->tborderstyle(phpexcel_style_border::border_thin);      $objectphpexcel->getactivesheet()->getstyle('b'.($n+4).':g'.$currentrownum )          ->getborders()->getbottom()->tborderstyle(phpexcel_style_border::border_thin);      $objectphpexcel->getactivesheet()->getstyle('b'.($n+4).':g'.$currentrownum )          ->getborders()->getvertical()->tborderstyle(phpexcel_style_border::border_thin);      $n = $n +1;    }    //设置分页显示    //$objectphpexcel->getactivesheet()->tbreak( 'i55' , phpexcel_worksheet::break_row );    //$objectphpexcel->getactivesheet()->tbreak( 'i10' , phpexcel_worksheet::break_column );    $objectphpexcel->getactivesheet()->getpagetup()->thorizontalcentered(true);    $objectphpexcel->getactivesheet()->getpagetup()->tverticalcentered(fal);    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，并提示下载或打开。