ThinkPHP/PHP框架实现导出excel数据的方法示例【基于PHPExcel】

<?php

header("Content-type: text/html; charset=utf-8");

class MesTestAction extends Action {

  //测试导出excel数据

  public function tpGetExcel() {

    //创建对象

    import("ORG.Util.PHPExcel"); //从PHPExcel目录导PHPExcel.php类文件

    $excel = new PHPExcel();

    $data = M()->query('SELECT userid,username,stepgoal FROM tp_data_user LIMIT 2775');

    //Excel表格式,这里简略写了3列

    $letter = array('A','B','C');

    //表头数组

    $tableheader = array('userid','用户名','目标步数');

    $count= count($data);//总的数据行数

    $listNum = 500;//每个sheet页最大数据行数

    $num = ceil($count/$listNum);//sheet页个数

    $MuitData = array_chunk($data,$listNum,false);//分割总的数据，每页最多$listNum行有效数据

  //var_dump($MuitData);//die('as');

  //缺省情况下,PHPExcel会自动创建第一个SHEET,其索引SheetIndex=0

  //设置 当前处于活动状态的SHEET 为PHPExcel自动创建的第一个SHEET

  $excel->setActiveSheetIndex(0); //objPHPExcel

  //设置sheet的title

  $excel->getActiveSheet()->setTitle('考核得分第'.'1'.'页');

  //设置sheet的列名称

    for($k = 0; $k < count($tableheader); ++$k) {

      $excel->getActiveSheet()->setCellValue("$letter[$k]".'1',"$tableheader[$k]");//第一行数据

    }

  //填充表格信息 处理第1块数据

  $crrntSheetLineNo = count($MuitData[0]) + 1;

  for ( $j = 2; $j <= $crrntSheetLineNo; ++$j) { //遍历每一行

    $k = 0;

    foreach ( $MuitData[0][$j - 2] as $key => $value ) {//遍历具体行的某一列

      $excel->getActiveSheet()->setCellValue("$letter[$k]".$j,"$value");//第$k列 第$j行

      $k++;

    }

  }

  //后续的sheet页及数据块

    for ( $i = 1; $i <$num; ++$i) {

      //创建第$i个sheet

      $msgWorkSheet = new PHPExcel_Worksheet($excel, '考核得分第'.($i + 1).'页'); //创建一个工作表

      $excel->addSheet($msgWorkSheet); //插入工作表

      $excel->setActiveSheetIndex($i); //切换到新创建的工作表

      //设置sheet的列名称

      for($k = 0; $k < count($tableheader); ++$k) {

        $excel->getActiveSheet()->setCellValue("$letter[$k]1","$tableheader[$k]");//第一行数据

      }

      //填充表格信息 处理第$i块数据

    $crrntSheetLineNo = count($MuitData[$i]) + 1; //var_dump($crrntSheetLineNo);var_dump($MuitData[$i-1]);die('as');

      for ( $j = 2; $j <= $crrntSheetLineNo; ++$j) { //遍历每一行

        $k = 0;

        foreach ( $MuitData[$i-1][$j - 2] as $key => $value ) {//遍历具体行的某一列

          $excel->getActiveSheet()->setCellValue("$letter[$k]$j","$value");//第$k列 第$j行

          ++$k;

        }

      }

      usleep(100);

    }

    //创建Excel输出对象

    $filename = "大奖赛培训考核得分.xls";

    $write = new PHPExcel_Writer_Excel5($excel);

    ob_end_clean();//清除缓冲区,避免乱码

  /*

  //输出到本地

    $write->save( iconv('utf-8', 'gbk', $filename) );

  */

    //输出到浏览器

    header("Pragma: public");

    header("Expires: 0");

    header("Cache-Control:must-revalidate, post-check=0, pre-check=0");

    header("Content-Type:application/force-download");

    header("Content-Type:application/vnd.ms-execl");

    header("Content-Type:application/download");

    header('Content-Type:application/octet-stream');

    $encoded_filename = urlencode($filename);

    $encoded_filename = str_replace("+", "%20", $encoded_filename);

    $ua = $_SERVER["HTTP_USER_AGENT"];

    if (preg_match("/MSIE/", $ua)) {

      header('Content-Disposition: attachment; filename="' . $encoded_filename . '"');

    } else if (preg_match("/Firefox/", $ua)) {

      header('Content-Disposition: attachment; filename*="utf8\'\'' . $filename . '"');

    } else {

      header('Content-Disposition: attachment; filename="' . $filename . '"');

    }

    header("Content-Transfer-Encoding:binary");

    $write->save('php://output');

  }

}