PHP Excel 下载数据，并分页下载

直接上代码：

 

调用下载Excel：

$total=$duoduo->count(MOD.' as a',$where);
$objExcel= SelfExcelObject();

//导出
if($total !=false && $total >0 && in_array($_GET['otype'], array('tradelist', 'mall_order'))) {
    $result = localSettionData($objExcel,$duoduo,$total,$where);//取出要导出的数据
    set_time_limit(10);
    if($result==false){
        $result['msg'] = '操作异常！';
    }
    echo json_encode($result);exit;
}

 

 

预先定义

 

 

/**
 * 获取表头，及获取要设置的数据
 */
 function localSettionData($objExcel,$duoduoObj,$count_result,$where='',$otype="tradelist"){
     $result = array('count_num'=>$count_result);
     if(!$count_result|| !$objExcel ){return array('msg'=>'数据异常','count_num'=>$count_result);}
    $down_pagesize=5000;    /*下载 数据配置*/
    $otitle="淘宝订单";
    //data
    $checked_arr = include DDROOT.DIRECTORY_SEPARATOR.'data'.DIRECTORY_SEPARATOR.'checked_arr.php';
    $lm_arr = include DDROOT.DIRECTORY_SEPARATOR.'data'.DIRECTORY_SEPARATOR.'lm.php';
    //设置单元格内容    //由PHPExcel根据传入内容自动判断单元格内容类型
    $titlearr = array('a'=>'交易号','b'=>'下单时间','c'=>'商品名称','d'=>'单价','e'=>'数量','f'=>'成交额','g'=>'比例','h'=>'佣金','i'=>'易淘币','j'=>'积分','k'=>'结算时间','l'=>'认领','m'=>'会员','n'=>'店铺','o'=>'编号');
    if($otype == 'mall_order') {
        $otitle="商城订单";
        $titlearr = array('a'=>'下单时间','b'=>'商城','c'=>'订单号','d'=>'联盟','e'=>'活动id','f'=>'总价','g'=>'单价','h'=>'数量','i'=>'佣金','j'=>'返利','k'=>'积分','l'=>'状态','m'=>'会员','n'=>'店铺'/*,'o'=>'分享','p'=>'转发'*/);
    }
    $objActSheet = $objExcel->getActiveSheet(); //得到当前表的子页面
    $objActSheet->setTitle($otitle);//设置当前活动sheet的名称
    foreach($titlearr AS $k=>$v) {
            $objActSheet->setCellValue(strtoupper($k).'1', $v); //设置表头名称
            $objActSheet->getColumnDimension(strtoupper($k))->setWidth(30);
    }
    //计算页码
    $all_p_nums = ($count_result > $down_pagesize && $down_pagesize>0) ? ceil($count_result /$down_pagesize): 1 ; //采用分页来下载数据 防止内存溢出
    $result['all_p_nums'] = $all_p_nums;
    $write_excel_index = 1; //写入Excel的索引位置
    for($down_page=1; $down_page <= $all_p_nums ; $down_page ++ ){
        $down_tbindex=($down_page-1)*$down_pagesize;
        if($otype == 'tradelist') {
            $list=$duoduoObj->select_all('tradelist as a','*',$where.' LIMIT '.$down_tbindex .','.$down_pagesize);//所有数据
            foreach($list as $k=>$arr){
                $list[$k]['uname']=$duoduoObj->select('user','ddusername','id="'.$arr['uid'].'"');    // 店铺
                /*if($arr['checked']!=0 && $arr['uid']==0){ $duoduo->update('tradelist',array('checked'=>0),'id="'.$arr['id'].'"'); }*/
                $list[$k]['ucard_name']=$duoduoObj->select('user','ddusername','id="'.$arr['ucard_id'].'"');    // 会员名
            }
            //开始写数据到Excel
            $count = count($list);
            for($rows=0; $rows<$count; $rows++) {
                $write_excel_index ++;
                $i= $write_excel_index;    //增加的索引
                $objActSheet->setCellValue('A'.$i, " ".$list[$rows]['num_iid']."");
                $objActSheet->setCellValue('B'.$i, $list[$rows]['create_time']);
                $objActSheet->setCellValue('C'.$i, $list[$rows]['item_title']);
                $objActSheet->setCellValue('D'.$i, $list[$rows]['pay_price']);
                $objActSheet->setCellValue('E'.$i, $list[$rows]['item_num']);
                $objActSheet->setCellValue('F'.$i, $list[$rows]['real_pay_fee']);
                $commis_rate = $list[$rows]['commission_rate']*100;
                $objActSheet->setCellValue('G'.$i, $commis_rate.'%');
                $objActSheet->setCellValue('H'.$i, $list[$rows]['commission']);
                $objActSheet->setCellValue('I'.$i, $list[$rows]['jifenbao']);
                $objActSheet->setCellValue('J'.$i, $list[$rows]['jifen']);
                $objActSheet->setCellValue('K'.$i, $list[$rows]['pay_time']);
                $objActSheet->setCellValue('L'.$i, $checked_arr[$list[$rows]['checked']]);
                $objActSheet->setCellValue('M'.$i, $list[$rows]['ucard_name']."|".$write_excel_index);
                $objActSheet->setCellValue('N'.$i, $list[$rows]['uname']);
                $objActSheet->setCellValue('O'.$i, $write_excel_index);
                /*    $objActSheet->setCellValue('M'.$i, $duoduo->getUserName($list[$rows]['uid']));
                $objActSheet->setCellValue('N'.$i, $duoduo->getUserName($list[$rows]['ucard_id']));
                $objActSheet->setCellValue('O'.$i, $duoduo->getUserName($list[$rows]['share_id']));
                $objActSheet->setCellValue('P'.$i, $duoduo->getUserName($list[$rows]['send_id']));*/
            }
        }else{
            $list = $duoduoObj->select_all('mall_order', '*', "id IN ($id) ORDER BY order_time DESC");//' LIMIT '.$down_tbindex .','.$down_pagesize
            $count = count($list)+2;
            $rows = 0;
            $status_arr = array('0'=>'未确认','1'=>'确认');
            for($i=2; $i<$count; $i++) {
                $rows = $i - 2;
                $objActSheet->setCellValue('A'.$i, date('Y-m-d H:i:s',$list[$rows]["order_time"]));
                $objActSheet->setCellValue('B'.$i, $list[$rows]['mall_name']);
                $objActSheet->setCellValue('C'.$i, $list[$rows]['order_code']);
                $objActSheet->setCellValue('D'.$i, $lm_arr[$list[$rows]['lm']]['title']);
                $objActSheet->setCellValue('E'.$i, $list[$rows]['adid']);
                $objActSheet->setCellValue('F'.$i, $list[$rows]['sales']);
                $objActSheet->setCellValue('G'.$i, $list[$rows]['item_price']);
                $objActSheet->setCellValue('H'.$i, $list[$rows]['item_count']);
                $objActSheet->setCellValue('I'.$i, $list[$rows]['commission']);
                $objActSheet->setCellValue('J'.$i, $list[$rows]['fxje']);
                $objActSheet->setCellValue('K'.$i, $list[$rows]['jifen']);
                $objActSheet->setCellValue('L'.$i, $status_arr[$list[$rows]['status']]);
                $objActSheet->setCellValue('M'.$i, $duoduo->getUserName($list[$rows]['uid']));
                $objActSheet->setCellValue('N'.$i, $duoduo->getUserName($list[$rows]['ucard_id']));
                $objActSheet->setCellValue('O'.$i, $duoduo->getUserName($list[$rows]['share_id']));
                $objActSheet->setCellValue('P'.$i, $duoduo->getUserName($list[$rows]['send_id']));
            }
        }
        unset($list);
        sleep(1);//休息多久
    }//#end for
    $saveDir = 'exportexcelfile';//文件保存目录
    $dateFileName = $otype."-".date('YmdHis', time()).".xls";//文件名
    $outputFileName = DDROOT.DIRECTORY_SEPARATOR.$saveDir.DIRECTORY_SEPARATOR.$dateFileName;
    if(!file_exists(DDROOT.DIRECTORY_SEPARATOR.$saveDir)) {
        mkdir(DDROOT.DIRECTORY_SEPARATOR.$saveDir);
    }
    if(file_exists($outputFileName)) {//如果该文件存在则删除
        unlink($outputFileName);
    }
    //输出内容到文件
    $objWriter = PHPExcel_IOFactory::createWriter($objExcel, 'Excel5');
    //$objWriter = new PHPExcel_Writer_Excel2007($objExcel);//用于 2007 格式
    $objWriter->save($outputFileName);
    $result['msg'] = 'SUCCESS';
    $result['download'] = '<a href="'.SITEURL.'/'.$saveDir.'/'.$dateFileName.'">点击下载</a>';
    return $result;
 }


/**
 * PHP Excel 对象 
 * 
 */
 function SelfExcelObject(){
     // 创建一个处理对象实例
    $objExcel = new PHPExcel();
    //设置文档基本属性
    $objExcel->getProperties()->setCreator("Maarten Balliauw")
                             ->setLastModifiedBy("Maarten Balliauw")
                             ->setTitle("Office 2007 XLSX Test Document")
                             ->setSubject("Office 2007 XLSX Test Document")
                             ->setDescription("Document for Office 2007 XLSX, generated using PHP classes.")
                             ->setKeywords("office 2007 openxml php")
                             ->setCategory("Test result file");
    //设置当前的sheet索引，用于后续的内容操作。
    //一般只有在使用多个sheet的时候才需要显示调用。
    //缺省情况下，PHPExcel会自动创建第一个sheet被设置SheetIndex=0
    $objExcel->setActiveSheetIndex(0);
    $objExcel->getActiveSheet()->getStyle('A1:P1')->getFont()->setBold(true);
    $objExcel->getActiveSheet()->getStyle('A1:P1')->getFont()->setSize(13);
    $objExcel->getActiveSheet()->getStyle('A1:P1')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_RED);
    $objExcel->getActiveSheet()->getStyle('A1:P1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
    $objExcel->getActiveSheet()->getStyle('A1:P1')->getFill()->getStartColor()->setARGB('00FFFFE3');
    $objExcel->getActiveSheet()->getStyle('A1:P1')->getBorders();
    $objExcel->getActiveSheet()->getStyle('A1:P1')->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
    $objExcel->getActiveSheet()->getStyle('A1:P1')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
    $objExcel->getActiveSheet()->getStyle('A1:P1')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
     return $objExcel ;
 }