ThinkPHP框架实现导出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');
  }
}
?>