PHPExcel导出表格

PHPExcel 导出

百度网盘链接:
链接：https://pan.baidu.com/s/1Ng7WH-_8KomWwgl_HD3U_Q
提取码：28jo 点击访问

// 前端Ajax请求
<script>
    $("#toolexcel").click(function(){
         var toolexcel = $("#toolexcel").val();
         var user_name  = $("#user_name").val();
         var truename = $("#truename").val();
         var status = $("#status").val();
         
         if($('#start').val() == '' ){
             var start = 0;
         }else{
             var start = $.myTime.DateToUnix($('#start').val());
         }
         if($('#end').val() == '' ){
             var end = 0;
         }else{
             var end = $.myTime.DateToUnix($('#end').val());
         }
         
         var params = {toolexcel:toolexcel,user_name:user_name,truename:truename,status:status,start:start,end:end};
         $.get("{:U('Admin/Deal/outExcels')}",params,function(res){
                 window.location.href = res.file;
         },'json');
    });
</script>

/**
 * 后端PHP导出
 * @throws PHPExcel_Exception
 * @throws PHPExcel_Reader_Exception
 * @throws PHPExcel_Writer_Exception
 * @author zhuyanbin
 */
public function outExcels()
{
    //导入phpexcel类方法
    include "ThinkPHP/Library/Vendor/Util/PHPExcel.class.php";
    include "ThinkPHP/Library/Vendor/Util/PHPExcel/Writer/Excel5.php";
    include "ThinkPHP/Library/Vendor/Util/PHPExcel/IOFactory.php";

    $toolexcel = I('get.toolexcel');
    if (empty($toolexcel)) {
        exit(json_encode(array('status' => false, 'url' => '', 'token' => '')));
    }

    $get = I('param.');
    $withdraws = M('withdraw');
    $where = array();
    if ($_GET['user_name'] != null) {
        $user_name = $_GET['user_name'];
        $where['user_name|id'] = array('like',"%$user_name%");
    }
    if ($_GET['truename'] != null) {
        $bankuser = $_GET['truename'];
        $where['bankuser'] = array('like',"%$bankuser%");
    }
    if ($_GET['status'] != null) {
        $status = $_GET['status'];
        $where['status'] = $status;
    }

    if ($_GET['start'] > 0) {
        $where[]['createtime'] = array('egt', $_GET['start']);
        $where[]['createtime'] = array('elt', $_GET['end']);
    }

    $withdraw = $withdraws
        ->where($where)
        ->order("createtime desc")
        ->select();

    $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(10);
    $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(20);
    $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(10);
    $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(30);
    $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(16);

    //设置水平居中
    $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);

//        //合并cell
    $objPHPExcel->getActiveSheet()->mergeCells('A1:E1');

    // set table header content
    $objPHPExcel->setActiveSheetIndex(0)
        ->setCellValue('A1', '支付宝批量付款文件模板（前面两行请勿删除）')
        ->setCellValue('A2', '序号（必填）')
        ->setCellValue('B2', '收款方支付宝账号（必填）')
        ->setCellValue('C2', '收款方姓名（必填）')
        ->setCellValue('D2', '金额（必填，单位：元）')
        ->setCellValue('E2', '备注（选填）');

    if (!empty($withdraw)) {
        foreach ($withdraw as $key => $val) {
            $objPHPExcel->getActiveSheet(0)->setCellValue('A' . ($key + 3), $key + 1);
            $objPHPExcel->getActiveSheet(0)->setCellValue('B' . ($key + 3), $key + 1);
            $objPHPExcel->getActiveSheet(0)->setCellValue('C' . ($key + 3), $key + 1);
            $objPHPExcel->getActiveSheet(0)->setCellValue('D' . ($key + 3), $key + 1);
            $objPHPExcel->getActiveSheet(0)->setCellValue('E' . ($key + 3), '提现');
        }
    }

    //  sheet命名
    $objPHPExcel->getActiveSheet()->setTitle('提现列表' . date('YmdHis', time()));

    // 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('YmdHis', time()) . '.xls');  //日期为文件名后缀
    header('Cache-Control: max-age=0');

    ob_clean();//关键

    flush();//关键

    $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');  //excel5为xls格式，excel2007为xlsx格式

    ob_end_clean();

    ob_start();

    //$objWriter->save('php://output');
    $file = 'excel/'.time() . '.xls';
    $path = 'http://'.$_SERVER['HTTP_HOST'].'/excel/'.time() . '.xls';
    $objWriter->save($file);
        $response = array(
            'file' => $path,
        );

    exit(json_encode($response));
}