在thinkPHP3.2版本中应用

下载

官网下载phpExcel插件:

导入

ThinkPHP特意提供了导入第三方类库的功能,直接把PHPExcel放到vendor目录下:
image.png

引用

使用时用vendor方法引入:
image.png

备注

使用PHPExcel时报错：Class ‘Admin\Action\PHPExcel’ not found
因为框架内部引入部分把引入的内容因为 ‘import()’ 的问题，import()默认会给文件添加 ‘.class.php’的后缀。同时将 ‘.’ 替换为 ‘/‘，导致文件路径也解析错误。

解决方法为使用类名的时候前面加上一个’\’；

  1. vendor("PHPExcel.PHPExcel");
  2. $objPHPExcel = new \PHPExcel();

使用

demo:

  1. <?php
  2. $datas = array(
  3.  array('王城', '男', '18', '1997-03-13', '18948348924'),
  4.  array('李飞虹', '男', '21', '1994-06-13', '159481838924'),
  5.  array('王芸', '女', '18', '1997-03-13', '18648313924'),
  6.  array('郭瑞', '男', '17', '1998-04-13', '15543248924'),
  7.  array('李晓霞', '女', '19', '1996-06-13', '18748348924'),
  8. );
  10. vendor("PHPExcel.PHPExcel");
  13. // 创建一个excel
  14. $objPHPExcel = new PHPExcel();
  16. // 设置文档属性信息
  17. $objPHPExcel->getProperties()
  18.                       ->setCreator("Phpmarker")
  19.             ->setLastModifiedBy("Phpmarker")
  20.             ->setTitle("Phpmarker")
  21.             ->setSubject("Phpmarker")
  22.             ->setDescription("Phpmarker")
  23.             ->setKeywords("Phpmarker")
  24.             ->setCategory("Phpmarker");
  26. //将活动工作表索引设置为第一个工作表，以便Excel将其作为第一个工作表打开
  27. //并设置表头
  28. $objPHPExcel->setActiveSheetIndex(0)
  29.   ->setCellValue('A1', '名字')
  30.   ->setCellValue('B1', '性别')
  31.   ->setCellValue('C1', '年龄')
  32.   ->setCellValue('D1', '出生日期')
  33.   ->setCellValue('E1', '电话号码');
  35. //获取当前活动表
  36. $sheet = $objPHPExcel->getActiveSheet();
  38. // 设置工作表名称
  39. $sheet->setTitle('Phpmarker-' . date('Y-m-d'));
  41. //设置行高
  42. $sheet->getDefaultRowDimension()->setRowHeight(15);
  43. //锁定表头
  44. $sheet->freezePane('A2');
  46. //循环写入数据
  47. $i = 2;
  48. foreach($datas as $data){
  49.   $sheet->setCellValue('A' . $i, $data[0])
  50.             ->getStyle('A'.$i)->getAlignment()
  51.             ->setHorizontal(PHPExcel_Style_Alignment::VERTICAL_CENTER);
  53.   $sheet->setCellValue('B' . $i, $data[1]);
  55.   $sheet->setCellValue('C' . $i, $data[2]);
  57.   $sheet->setCellValueExplicit('D'. $i, $data[3],PHPExcel_Cell_DataType::TYPE_STRING);
  59.   $sheet->getStyle('D' . $i)
  60.           ->getNumberFormat()
  61.             ->setFormatCode("@");
  63.  // 设置文本格式
  64. $sheet->setCellValueExplicit('E'. $i, $data[4],PHPExcel_Cell_DataType::TYPE_STRING);
  65. $sheet->getStyle('E' . $i)->getAlignment()->setWrapText(true);
  66.  $i ;
  67. }
  69. $objActSheet = $objPHPExcel->getActiveSheet();
  71. // 设置CELL填充颜色
  72. $cell_fill = array(
  73.   'A1',
  74.   'B1',
  75.   'C1',
  76.   'D1',
  77.   'E1',
  78. );
  79. foreach($cell_fill as $cell_fill_val){
  80.  $cellstyle = $objActSheet->getStyle($cell_fill_val);
  81.  // background
  82.  // $cellstyle->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('fafa00');
  83.  // set align
  84.  $cellstyle->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT)->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
  85.  // font
  86.  $cellstyle->getFont()->setSize(12)->setBold(true);
  87.  // border
  88.  $cellstyle->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN)->getColor()->setARGB('FFFF0000');
  89.  $cellstyle->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN)->getColor()->setARGB('FFFF0000');
  90.  $cellstyle->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN)->getColor()->setARGB('FFFF0000');
  91.  $cellstyle->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN)->getColor()->setARGB('FFFF0000');
  92. }
  94. $objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(30);
  96. $objActSheet->getColumnDimension('A')->setWidth(18.5);
  97. $objActSheet->getColumnDimension('B')->setWidth(23.5);
  98. $objActSheet->getColumnDimension('C')->setWidth(12);
  99. $objActSheet->getColumnDimension('D')->setWidth(12);
  100. $objActSheet->getColumnDimension('E')->setWidth(12);
  102. $filename = '2015030423';
  103. ob_end_clean();//清除缓冲区,避免乱码 
  104. header('Content-Type: application/vnd.ms-excel');
  105. header('Content-Disposition: attachment;filename="'.$filename.'.xls"');
  106. header('Cache-Control: max-age=0');
  107. // If you're serving to IE 9, then the following may be needed
  108. header('Cache-Control: max-age=1');
  109. // If you're serving to IE over SSL, then the following may be needed
  110. header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
  111. header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified
  112. header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
  113. header('Pragma: public'); // HTTP/1.0
  114. $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
  115. $objWriter->save('php://output');
  116. ?>

常用配置及操作

  1. // 创建一个excel
  2. $objPHPExcel = new \PHPExcel();
  4. //设置当前活动表并设置表头
  5. $objPHPExcel->setActiveSheetIndex(0)
  6.                       ->setCellValue('A1', '姓名')
  7.             ->setCellValue('B2', '年龄')
  8.             ->setCellValue('C2', '性别')
  10. //获取当前活动表
  11. $firstSheet = $objPHPExcel->getActiveSheet();
  13. //设置工作表名称
  14. $firstSheet->setTitle('漏洞数量统计');
  16. //合并单元格
  17. $firstSheet->mergeCells('A1:E1');
  19. //分离单元格
  20. $firstSheet->unmergeCells('A28:B28');
  22. //设置列宽
  23. $firstSheet->getColumnDimension('A')->setWidth(30);
  24. $firstSheet->getColumnDimension('B')->setWidth(20);
  25. $firstSheet->getColumnDimension('C')->setWidth(20);
  27. //填充单元格背景色
  28. $firstSheet->getStyle('A2:C2')
  29.             ->getFill()
  30.             ->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)
  31.             ->getStartColor()
  32.             ->setRGB('4F81BD');
  35. //设置居中
  36. $firstSheet->getStyle('A1:C3')
  37.           ->getAlignment()
  38.           ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  39. //垂直居中
  40. $firstSheet->getStyle('A18')
  41.             ->getAlignment()
  42.             ->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
  44. //设置字体颜色
  45. $firstSheet->getStyle('A2:C2')->getFont()->getColor()->setRGB('FFFFFF');
  47. //设置字体
  48. $firstSheet->getStyle('A2:'.$endCol.'2')->getFont()->setName('宋体');
  50. //字体加粗
  51. $firstSheet->getStyle('A1')->getFont()->setName('宋体')->setBold(true);
  53. //设置边框
  54. $styleThinBlackBorder = array(
  55.         'borders' => array (
  56.             'allborders' => array (
  57.                 'style' => \PHPExcel_Style_Border::BORDER_THIN,   //设置border样式
  58.                 'color' => array ('rgb' => '000000'),             //设置border颜色
  59.             ),
  60.         ),
  61.     );
  62. $firstSheet->getStyle( 'A1:C5')->applyFromArray($styleThinBlackBorder);