thinkPHP+phpexcel实现excel报表输出功能示例

本文实例讲述了thinkPHP+phpexcel实现excel报表输出功能。分享给大家供大家参考，具体如下：

准备工作：

1.下载phpexcel1.7.6类包；

2.解压至TP框架的ThinkPHPVendor目录下，改类包文件夹名为PHPExcel176，目录结构如下图；

编写代码(以一个订单汇总数据为例)：

  1. 创建数据库及表;
  2. 创建tp项目，配置项目的数据库连接，这些基本的就不说了；
  3. 在项目的LibAction下创建一个新的类文件ExportStatisticsAction.class.php，然后在  index方法中实现excel导出；
  4. 导出方法的步骤：
    ①查询数据
    ②导入phpexcel类库
    ③创建excel对象并设置excel对象的属性
    ④设置excel的行列样式(字体、高宽、颜色、边框、合并等)
    ⑤绘制报表表头
    ⑥将查询数据写入excel
    ⑦设置excel的sheet的名称
    ⑧设置excel报表打开后初始的sheet
    ⑨设置输出的excel的头参数及文件名
    ⑩调用创建excel的方法生成excel文件

代码如下：

<?php
/**
 * Created by lonm.shi.
 * Date: 2012-02-09
 * Time: 下午4:54
 * To change this template use File | Settings | File Templates.
 */
class ExportStatisticsAction extends Action {
  public function index(){
    $model= D("OrdersView");
    $OrdersData= $model->select(); //查询数据得到$OrdersData二维数组
    vendor("PHPExcel176.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(10);
    $objPHPExcel->getActiveSheet()->getColumnDimension("C")->setWidth(25);
    $objPHPExcel->getActiveSheet()->getColumnDimension("D")->setWidth(12);
    $objPHPExcel->getActiveSheet()->getColumnDimension("E")->setWidth(50);
    $objPHPExcel->getActiveSheet()->getColumnDimension("F")->setWidth(10);
    $objPHPExcel->getActiveSheet()->getColumnDimension("G")->setWidth(12);
    $objPHPExcel->getActiveSheet()->getColumnDimension("H")->setWidth(12);
    $objPHPExcel->getActiveSheet()->getColumnDimension("I")->setWidth(12);
    $objPHPExcel->getActiveSheet()->getColumnDimension("J")->setWidth(30);
    //设置行高度
    $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:J2")->getFont()->setBold(true);
    $objPHPExcel->getActiveSheet()->getStyle("A2:J2")->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
    $objPHPExcel->getActiveSheet()->getStyle("A2:J2")->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("D")->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("H")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
    $objPHPExcel->getActiveSheet()->getStyle("I")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
    //合并cell
    $objPHPExcel->getActiveSheet()->mergeCells("A1:J1");
    // 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", "需求交期")
      ->setCellValue("H2", "确认BOM料号")
      ->setCellValue("I2", "PMC确认交期")
      ->setCellValue("J2", "PMC交货备注");
    // Miscellaneous glyphs, UTF-8
    for($i=0;$i<count($OrdersData)-1;$i++){
      $objPHPExcel->getActiveSheet(0)->setCellValue("A".($i+3), $OrdersData[$i]["id"]);
      $objPHPExcel->getActiveSheet(0)->setCellValue("B".($i+3), $OrdersData[$i]["realname"]);
      $objPHPExcel->getActiveSheet(0)->setCellValue("C".($i+3), $OrdersData[$i]["customer_name"]);
      $objPHPExcel->getActiveSheet(0)->setCellValue("D".($i+3), toDate($OrdersData[$i]["create_time"])); //这里调用了common.php的时间戳转换函数
      $objPHPExcel->getActiveSheet(0)->setCellValue("E".($i+3), $OrdersData[$i]["require_product"]);
      $objPHPExcel->getActiveSheet(0)->setCellValue("F".($i+3), $OrdersData[$i]["require_count"]);
      $objPHPExcel->getActiveSheet(0)->setCellValue("G".($i+3), $OrdersData[$i]["require_time"]);
      $objPHPExcel->getActiveSheet(0)->setCellValue("H".($i+3), $OrdersData[$i]["product_bom_encoding"]);
      $objPHPExcel->getActiveSheet(0)->setCellValue("I".($i+3), $OrdersData[$i]["delivery_time"]);
      $objPHPExcel->getActiveSheet(0)->setCellValue("J".($i+3), $OrdersData[$i]["delivery_memo"]);
      $objPHPExcel->getActiveSheet()->getStyle("A".($i+3).":J".($i+3))->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
      $objPHPExcel->getActiveSheet()->getStyle("A".($i+3).":J".($i+3))->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
      $objPHPExcel->getActiveSheet()->getRowDimension($i+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");
    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, "Excel5"); //excel5为xls格式，excel2007为xlsx格式
    $objWriter->save("php://output");
  }
}

5.调用导出方法直接  http://项目/index.php/ExportStatistics/index，项目中调用直接__APP__/ExportStatistics/index,生成的报表是下载方式来保存。phpexcel1.7.6没有发现什么编码问题，速度也很快，注意导出的方法中不能有任何页面输出信息或调试信息，否则导出的excel会提示格式不对。效果如下：

导出报表

更多关于thinkPHP相关内容感兴趣的读者可查看本站专题：《ThinkPHP入门教程》、《thinkPHP模板操作技巧总结》、《ThinkPHP常用方法总结》、《codeigniter入门教程》、《CI(CodeIgniter)框架进阶教程》、《Zend FrameWork框架入门教程》及《PHP模板技术总结》。

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