您的位置:首页 > 编程学习 > > 正文

thinkphp导入excel(Yii框架使用PHPExcel导出Excel文件的方法分析改进版)

更多 时间:2022-01-19 00:54:22 类别:编程学习 浏览量:2951

thinkphp导入excel

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

本文实例讲述了Yii框架使用PHPExcel导出Excel文件的方法。分享给大家供大家参考,具体如下:

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

1、首先在config\main.php中添加对PHPExcel的引用,我的方式是这样:

  • ?
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • // autoloading model and component classes
  • 'import'=>array(
  •     /*'application.modules.srbac.controllers.SBaseController',*/
  •     'application.models.*',
  •     'application.components.*',
  •     'application.extensions.phpexcel.*',
  • ),
  • 另外也有人用components 这个配置,但是我的有问题,所以就用上面的方法。

    2、按照下面的代码修改PHPExcel代码目录里的Autoloader.php文件:

  • ?
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 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
  •     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中:

  • ?
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 53
  • 54
  • 55
  • 56
  • 57
  • 58
  • 59
  • 60
  • 61
  • 62
  • 63
  • 64
  • 65
  • 66
  • 67
  • 68
  • 69
  • 70
  • 71
  • 72
  • 73
  • 74
  • 75
  • 76
  • 77
  • 78
  • 79
  • 80
  • 81
  • 82
  • 83
  • 84
  • 85
  • 86
  • 87
  • 88
  • 89
  • 90
  • 91
  • 92
  • 93
  • 94
  • 95
  • 96
  • 97
  • 98
  • 99
  • 100
  • /*
  • 导出为Excel
  • */
  • public function actionExport()
  • {
  •     $objectPHPExcel = new PHPExcel();
  •     $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 ( $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,并提示下载或打开。

    希望本文所述对大家基于Yii框架的PHP程序设计有所帮助。

    原文链接:https://www.cnblogs.com/dahuzizyd/archive/2012/07/06/2579738.html

    标签:phpExcel Excel Yii