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

时间:2023-04-23 09:26:32

直接上代码:

调用下载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 ;
}