thinkphp中用PHPExcel导出excel表—ajax方法

首先在前台html页面中点击某个控件来导出excel，这里用ajax的click方法实现实现点击事件，那怎么跟PHPExcel联系起来呢，请看代码：

HTML中jQ部分：

//导出excel
$("#daochu").click(function(){
    var vkorg = $("#VKORG option:selected").val();
    if(vkorg == ""){
        var vkorgs = $("#VKORG option").map(function(){
            return $(this).val();
        }).get().join(",");
        vkorg = vkorgs;
    }
    var kkber = $("#KKBER option:selected").val();
    var kunnr = $("input[name='Kunnr']").val();
    var name1 = $("input[name='Name1']").val();
    var ym1 = $("input[name='YM1']").val();
    var ym2 = $("input[name='YM2']").val();
    $.ajax({
        type:'post',
        url:"{:U('OrderReleasReport/getexcel')}",
        data:{vkorg:vkorg,kkber:kkber,kunnr:kunnr,name1:name1,ym1:ym1,ym2:ym2},
        datatype:'json',
        success:function(data){
            location.href="../"+data.url;
        }
    })
})

将获取的值通过ajax传到后台，根据条件来查询相应的数据：

//导出excel
public function getexcel(){
    $vkorg = $_POST['vkorg'];
    $kkber = $_POST['kkber'];
    $kunnr = $_POST['kunnr'];
    $name1 = $_POST['name1'];
    $ym1 = $_POST['ym1'];
    $ym2 = $_POST['ym2'];
    $where = " and 1=1 ";
    if(!empty($vkorg)){
        $where .= " and a.vkorg in ('".$vkorg."')";
    }

    if(!empty($kkber)){
        $where .= " and a.kkber='".$kkber."'";
    }
    if(!empty($kunnr)){
        $where .= " and a.kunnr like '%".$kunnr."%'";
    }
    if(!empty($name1)){
        $where .= " and a.Name1 like '%".$name1."'";
    }
    if(!empty($ym1)){
        $where .= " and  ApplyDate >= '".$ym1."'";
    }
    if(!empty($ym2)){
        $where .= " and ApplyDate <= '".$ym2."'";
    }
    $expTitle = "放货查询";
    $expCellName = array(
        array('KKBER','信控范围'),
        array('Kunnr','客户代码'),
        array('name1','客户名称'),
        array('ApplyDate','申请日期'),
        array('Amount','订单金额'),
        array('Amount','最终审批人'),
        array('Amount','审批意见'),
        array('Amount','备注'),
    );
    $expTableData = M()->query("select row_number() OVER (order by a.vkorg,a.kkber,a.kunnr) as rn,a.*,b.name1 from TB_REPORT_OrderApplication2 a inner join tb_crm_kna1 b on a.kunnr=b.kunnr where 1>0 $where");
    $this->exportExcel($expTitle,$expCellName,$expTableData);
}

再调用exportExcel方法：

//PHPExcel导入导出的方法
protected function exportExcel($expTitle,$expCellName,$expTableData){
   $xlsTitle = iconv('utf-8', 'gb2312', $expTitle);//文件名称
   $fileName = time();//导出excal 文件名称
   $cellNum = count($expCellName);//有多少列
   $dataNum = count($expTableData);//有多少行
   vendor("Excel.PHPExcel");//引入PHPExcel文件
   ini_set("memory_limit", "1024M");
   $objPHPExcel = new \PHPExcel();//实例化PHPExcel类库，相当于新建一个Excel表
   $cellName = array('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O',
         'P','Q','R','S','T','U','V','W','X','Y','Z', 'AA','AB','AC','AD','AE',
         'AF','AG','AH','AI','AJ','AK','AL','AM','AN','AO','AP','AQ','AR','AS','AT',
         'AU','AV','AW','AX','AY','AZ');
   //在第二行插入每列的标题
   for($i=0;$i<$cellNum;$i++){
      $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$i].'1', $expCellName[$i][1]);
   }
   //从第三行开始插入数据
   for($i=0;$i<$dataNum;$i++){
      for($j=0;$j<$cellNum;$j++){
         $objPHPExcel->getActiveSheet(0)->setCellValue($cellName[$j].($i+2), $expTableData[$i][$expCellName[$j][0]]);
      }
   }
   $objSheet = $objPHPExcel->getActiveSheet();//获取当前活动sheet
   $objSheet->setTitle('sheet1');//给当前的活动sheet起个名称
   header('pragma:public');
   header('Content-type:application/vnd.ms-excel;charset=utf-8;name="'.$xlsTitle.'.xlsx"');
   header("Content-Disposition:attachment;filename=$fileName.xlsx");//attachment新窗口打印inline本窗口打印
   header('Cache-Control: max-age=0');
   $objWriter = new \PHPExcel_Writer_Excel2007($objPHPExcel);
   $response = array(
         'success' => true,
         'url' => $this->saveExcelToLocalFile($objWriter, $fileName)
   );
   if ($response) {
      $this->ajaxReturn($response, "json");
   }
}

得到文件名再来拼接文件的路径，并将其保存：

//ajax导出用到的方法
function saveExcelToLocalFile($objWriter,$filename){
   $filePath = './Public/excel/'.$filename.'.xlsx';
   $objWriter->save($filePath);
   return $filePath;
}

这样用ajax传递参数实现excel文件的导出就实现了。