thinkphp3.2+phpexcel1.8.0导出表格
来源：互联网 发布：qihoo360 apk脱壳软件 编辑：程序博客网 时间：2024/06/03 16:00
准备工作：
1.进入phpexcel官网下载phpexcel1.8.0类包；
2.解压至TP3.2框架的ThinkPHP\Org\Util目录下，改类包文件夹名为PHPExcel180，目录结构如下图；
编写代码(以一个学生管理列表为例)：
1. 创建数据库及表;
2. 创建tp3.2项目，配置项目的数据库连接，这些基本的就不说了；
3. 在项目的Application\Home\Controller\下创建一个新的类文件ExportStatisticsController.class.php，然后在 student_excel方法中实现excel导出； 
4. 导出方法的步骤：
①查询数据
②导入phpexcel类库
③创建excel对象并设置excel对象的属性
④设置excel的行列样式(字体、高宽、颜色、边框、合并等)
⑤绘制报表表头
⑥将查询数据写入excel
⑦设置excel的sheet的名称
⑧设置excel报表打开后初始的sheet
⑨设置输出的excel的头参数及文件名
⑩调用创建excel的方法生成excel文件
代码如下：
<?php namespace Home\Controller;use Think\Controller; class ExportStatisticsController extends CommonController { public function student_excel(){ if($this->user['level']==1){ $where['tid']=array('neq',1); }else if($this->user['level']==2){ $where['tid']=$this->user['username']; } if(!empty($_GET['uids'])){ $where['uid']=array('in',$_GET['uids']); } //如果没有选中任何，则导出所有数据 $OrdersData=M('user')->field('uid,user_name,pwd,name')->where($where)->order('uid asc')->select(); trace($OrdersData); //导入phpexcel类方法 import("Org.Util.PHPExcel"); import("Org.Util.PHPExcel.Writer.Excel5"); import("Org.Util.PHPExcel.IOFactory.php"); //vendor("PHPExcel180.PHPExcel"); // Create new PHPExcel object $objPHPExcel = new \PHPExcel(); // Set properties $objPHPExcel->getProperties()->setCreator("ctos") ->setLastModifiedBy("ctos") ->setTitle("Office 2007 XLSX Test Document") ->setSubject("Office 2007 XLSX Test Document") ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.") ->setKeywords("office 2007 openxml php") ->setCategory("Test result file"); //set width $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(8); $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(15); $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(25); $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(12); //设置行高度 $objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(22); $objPHPExcel->getActiveSheet()->getRowDimension('2')->setRowHeight(20); //set font size bold $objPHPExcel->getActiveSheet()->getDefaultStyle()->getFont()->setSize(10); $objPHPExcel->getActiveSheet()->getStyle('A2:D2')->getFont()->setBold(true); $objPHPExcel->getActiveSheet()->getStyle('A2:D2')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('A2:D2')->getBorders()->getAllBorders()->setBorderStyle(\PHPExcel_Style_Border::BORDER_THIN); //设置水平居中 $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT); $objPHPExcel->getActiveSheet()->getStyle('A')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('B')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('C')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('D')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('A1:D1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); //合并cell $objPHPExcel->getActiveSheet()->mergeCells('A1:D1'); // set table header content $objPHPExcel->setActiveSheetIndex(0) ->setCellValue('A1', '学生数据汇总 时间:'.date('Y-m-d H:i:s')) ->setCellValue('A2', '用户ID') ->setCellValue('B2', '用户名') ->setCellValue('C2', '用户密码') ->setCellValue('D2', '学生姓名'); // Miscellaneous glyphs, UTF-8 // for($i=0;$i<count($OrdersData)-1;$i++){ // $objPHPExcel->getActiveSheet(0)->setCellValue('A'.($i+3), $OrdersData[$i]['uid']); // $objPHPExcel->getActiveSheet(0)->setCellValue('B'.($i+3), $OrdersData[$i]['user_name']); // $objPHPExcel->getActiveSheet(0)->setCellValue('C'.($i+3), $OrdersData[$i]['pwd']); // $objPHPExcel->getActiveSheet(0)->setCellValue('D'.($i+3), $OrdersData[$i]['name']); // $objPHPExcel->getActiveSheet()->getStyle('A'.($i+3).':D'.($i+3))->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER); // $objPHPExcel->getActiveSheet()->getStyle('A'.($i+3).':D'.($i+3))->getBorders()->getAllBorders()->setBorderStyle(\PHPExcel_Style_Border::BORDER_THIN); // $objPHPExcel->getActiveSheet()->getRowDimension($i+3)->setRowHeight(16); // } foreach ($OrdersData as $k => $v) { $objPHPExcel->getActiveSheet(0)->setCellValue('A'.($k+3), $v['uid']); $objPHPExcel->getActiveSheet(0)->setCellValue('B'.($k+3), $v['user_name']); $objPHPExcel->getActiveSheet(0)->setCellValue('C'.($k+3), $v['pwd']); $objPHPExcel->getActiveSheet(0)->setCellValue('D'.($k+3), $v['name']); $objPHPExcel->getActiveSheet()->getStyle('A'.($k+3).':D'.($k+3))->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('A'.($k+3).':D'.($k+3))->getBorders()->getAllBorders()->setBorderStyle(\PHPExcel_Style_Border::BORDER_THIN); $objPHPExcel->getActiveSheet()->getRowDimension($k+3)->setRowHeight(16); } // sheet命名 $objPHPExcel->getActiveSheet()->setTitle('学生汇总表'); // Set active sheet index to the first sheet, so Excel opens this as the first sheet $objPHPExcel->setActiveSheetIndex(0); // excel头参数 header('Content-Type: application/vnd.ms-excel'); header('Content-Disposition: attachment;filename="学生汇总表('.date('Ymd-His').').xls"'); //日期为文件名后缀 header('Cache-Control: max-age=0'); ob_clean();//关键 flush();//关键 $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); //excel5为xls格式，excel2007为xlsx格式 $objWriter->save('php://output'); } //导出老师列表 public function teacher_excel(){ // $model= D("OrdersView"); // $OrdersData= $model->select(); //查询数据得到$OrdersData二维数组 $where['level']=2; if(!empty($_GET['uids'])){ $where['id']=array('in',$_GET['uids']); } //如果没有选中任何，则导出所有数据 $OrdersData=M('admin')->field('id,username,name,password,level,midschool,class')->where($where)->order('id asc')->select(); //$OrdersData=M('user')->where("tid='".$this->user['username']."'") import("Org.Util.PHPExcel"); import("Org.Util.PHPExcel.Writer.Excel5"); import("Org.Util.PHPExcel.IOFactory.php"); //vendor("PHPExcel180.PHPExcel"); // Create new PHPExcel object $objPHPExcel = new \PHPExcel(); // Set properties $objPHPExcel->getProperties()->setCreator("ctos") ->setLastModifiedBy("ctos") ->setTitle("Office 2007 XLSX Test Document") ->setSubject("Office 2007 XLSX Test Document") ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.") ->setKeywords("office 2007 openxml php") ->setCategory("Test result file"); //set width $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(8); $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(25); $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(25); $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(25); $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(20); $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(10); $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(10); //设置行高度 $objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(22); $objPHPExcel->getActiveSheet()->getRowDimension('2')->setRowHeight(20); //set font size bold $objPHPExcel->getActiveSheet()->getDefaultStyle()->getFont()->setSize(10); $objPHPExcel->getActiveSheet()->getStyle('A2:G2')->getFont()->setBold(true); $objPHPExcel->getActiveSheet()->getStyle('A2:G2')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('A2:G2')->getBorders()->getAllBorders()->setBorderStyle(\PHPExcel_Style_Border::BORDER_THIN); //设置水平居中 $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT); $objPHPExcel->getActiveSheet()->getStyle('A')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('B')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('C')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('D')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('E')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('F')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('G')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('A1:G1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('A2:G2')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); //合并cell $objPHPExcel->getActiveSheet()->mergeCells('A1:G1'); // set table header content $objPHPExcel->setActiveSheetIndex(0) ->setCellValue('A1', '教师账号汇总 时间:'.date('Y-m-d H:i:s')) ->setCellValue('A2', '用户ID') ->setCellValue('B2', '用户名') ->setCellValue('C2', '用户密码') ->setCellValue('D2', '优惠码') ->setCellValue('E2', '所在中学') ->setCellValue('F2', '负责班级') ->setCellValue('G2', '教师姓名'); // Miscellaneous glyphs, UTF-8 // for($i=0;$i<count($OrdersData)-1;$i++){ // $objPHPExcel->getActiveSheet(0)->setCellValue('A'.($i+3), $OrdersData[$i]['uid']); // $objPHPExcel->getActiveSheet(0)->setCellValue('B'.($i+3), $OrdersData[$i]['user_name']); // $objPHPExcel->getActiveSheet(0)->setCellValue('C'.($i+3), $OrdersData[$i]['pwd']); // $objPHPExcel->getActiveSheet(0)->setCellValue('D'.($i+3), $OrdersData[$i]['name']); // $objPHPExcel->getActiveSheet()->getStyle('A'.($i+3).':D'.($i+3))->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER); // $objPHPExcel->getActiveSheet()->getStyle('A'.($i+3).':D'.($i+3))->getBorders()->getAllBorders()->setBorderStyle(\PHPExcel_Style_Border::BORDER_THIN); // $objPHPExcel->getActiveSheet()->getRowDimension($i+3)->setRowHeight(16); // } foreach ($OrdersData as $k => $v) { $objPHPExcel->getActiveSheet(0)->setCellValue('A'.($k+3), $v['id']); $objPHPExcel->getActiveSheet(0)->setCellValue('B'.($k+3), $v['username']); $objPHPExcel->getActiveSheet(0)->setCellValue('C'.($k+3), $v['password']); $objPHPExcel->getActiveSheet(0)->setCellValue('D'.($k+3), $v['password']); $objPHPExcel->getActiveSheet(0)->setCellValue('E'.($k+3), $v['midschool']); $objPHPExcel->getActiveSheet(0)->setCellValue('F'.($k+3), $v['class']); $objPHPExcel->getActiveSheet(0)->setCellValue('G'.($k+3), $v['name']); $objPHPExcel->getActiveSheet()->getStyle('A'.($k+3).':G'.($k+3))->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('A'.($k+3).':G'.($k+3))->getBorders()->getAllBorders()->setBorderStyle(\PHPExcel_Style_Border::BORDER_THIN); $objPHPExcel->getActiveSheet()->getRowDimension($k+3)->setRowHeight(16); } // sheet命名 $objPHPExcel->getActiveSheet()->setTitle('教师账号汇总表'); // Set active sheet index to the first sheet, so Excel opens this as the first sheet $objPHPExcel->setActiveSheetIndex(0); // excel头参数 header('Content-Type: application/vnd.ms-excel'); header('Content-Disposition: attachment;filename="教师账号汇总表('.date('Ymd-His').').xls"'); //日期为文件名后缀 header('Cache-Control: max-age=0'); ob_clean();//关键 flush();//关键 $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); //excel5为xls格式，excel2007为xlsx格式 $objWriter->save('php://output'); } }5.调用导出方法直接 http://项目/index.php/Student/index，项目中调用直接ExportStatistics/student_excel方法,生成的报表是下载方式来保存。phpexcel1.8.0没有发现什么编码问题，速度也很快，注意导出的方法中不能有任何页面输出信息或调试信息，否则导出的excel会提示格式不对。效果如下：
导出报表
0 0
- thinkphp3.2+phpexcel1.8.0导出表格
- ThinkPHP3.2+PHPExcel1.8版类库 实现导入导出excel表
- ThinkPHP3.1.3使用phpExcel1.8.0实现数据从Excel表格导入mysql数据库
- ThinkPHP3.2.3接入PHPExcel1.8.0控件导出Excel报表文件处理方法
- ThinkPHP3.2导出excel
- PHPExcel1.8.0 设置单元格格式
- thinkphp3.2导入与导出
- ThinkPHP3.2利用PHPExcel导出Excel
- ThinkPHP3.2 + PHPExcel 进行execl文件导出操作
- thinkphp3.2 运用PHPExcel将数据导出Excel
- extjs4.2导出excel表格
- ThinkPHP3.2.3 Excel 导入导出
- thinkPHP3.2 Excel的导入和导出以及Word的导出
- tp3.2中excel表格导出数据
- thinkphp3.2自定义常量
- thinkphp3.2学习记录
- Thinkphp3.2 表单令牌
- ThinkPHP3.2 分页
- python导入自定义模块
- 机器学习2
- 为什么我用tabhost，显示不了图标
- [计算机网络] 作业（1）
- 从一个简单的Java单例示例谈谈并发
- thinkphp3.2+phpexcel1.8.0导出表格
- 软件产品研发与软件项目开发的过程有何区别
- 五种常见的 PHP 设计模式
- TRACE用于debug模式下
- 设计模式汇总：结构型模型（上）
- IPTV盒子与网络机顶盒子的区别
- Linux基础知识集锦
- 时间管理四代理论发展
- C 与MYSQL
