准备工作:
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会提示格式不对。效果如下:
导出报表