php+ajax导出订单、导出用户窗口示例
思路:在导出订单页面点击导出按钮,然后弹出窗口,ajax查询每次10条收集数据,收集完成后生成excel
html
<button class="layui-btn layui-btn-primary" onclick="exportOrder()" type="button" style="margin-left: 0;">导出</button>
javascript
function exportOrder(){ layui.use(['layer'], function() { var layer = layui.layer; layer.open({ type: 1, title: '导出订单', skin: 'layui-layer-rim', //加上边框 area: ['420px', '240px'], //宽高 content: '<style>.export-main{padding: 20px;}.process{width: 80%;margin: auto;height: 6px;border-radius: 15px;background: #f2f2f2;position: relative;}.process em{position: absolute;right: -35px;font-style: normal;top: -7px;}.process span{display: inline-block;width: 0%;height: 6px;background: #1E9FFF;border-radius: 15px;transition: all 0.5;}.export-tips{text-align: center;margin-bottom: 10px;}.export-res{text-align: center;margin-top: 10px;}.export-res a{color: #1E9FFF}</style><div class="export-main" data-rand-id="<?php echo rand(1,999999)?>"><p class="export-tips">正在导出订单</p><p class="process"><span data-page="1" data-total-page="0"></span><em>0%</em></p><p class="export-res"></p></div>', success: function() { $.ajax({ type: 'post', url: 'post.php', dataType: 'json', data: {'type': 'export', 'step': 'start', 'randId': $('.export-main').attr('data-rand-id')}, success: function(data){ if(data.status){ $('.process').children('span').attr('data-total-page', data.total); start_export(); } else { showmsg(data.msg); $('.process').hide(); $('.export-tips').hide(); } }, error: function(xhr, type){ showmsg('出错啦!'); } }); } }); }); } var loadingIndex = null; function show_loading(msg) { layui.use(['layer'], function() { var layer = layui.layer; loadingIndex = layer.msg(msg, { icon: 16 ,shade: 0.01 }); }); } function start_export() { var dom = $('.process').children('span'); var page = parseInt(dom.attr('data-page')); var total = parseInt(dom.attr('data-total-page')); if (page <= total) { show_loading('正在收集数据'); //此处发起ajax查询,page(当前页数)、total(总页数) 从后端返回, process = 当前页数/总页数,结果取整 $.ajax({ type: 'post', url: 'post.php', dataType: 'json', data: {'type': 'export', 'page': page, 'step': 'export', 'randId': $('.export-main').attr('data-rand-id')}, success: function(data){ layer.close(loadingIndex); if(data.status){ var process = parseInt(page / total * 100); page++; dom.width(process+'%').attr('data-page', page); dom.siblings('em').html(process+'%'); setTimeout(function () { start_export(); }, 100); } else { showmsg(data.msg); } }, error: function(xhr, type){ layer.close(loadingIndex); showmsg('出错啦!'); } }); } else { show_loading('正在生成报表'); //生成excel $.ajax({ type: 'post', url: 'post.php', dataType: 'json', data: {'type': 'export', 'step': 'done', 'randId': $('.export-main').attr('data-rand-id')}, success: function(data){ layer.close(loadingIndex); if(data.status){ $('.export-res').html('<a href="'+data.path+'">下载报表</a>'); } else { showmsg(data.msg); } }, error: function(xhr, type){ layer.close(loadingIndex); showmsg('出错啦!'); } }); } }
php
$step = clstring($_POST['step']); $randId = intval($_POST['randId']); $where = $_SESSION['order_where']; $row = isset($_POST['row']) ? intval($_POST['row']) : 10; $page = isset($_POST['page']) ? intval($_POST['page']) : 1; switch ($step) { case 'start': //开始收集数据,返回当前页数和总页数 $list = $db -> th_selectall($messageTable,$where,'*',array($row,$page)); if ($list) { $total = $db->page->pagenums; echoJson(['status'=>1, 'msg'=>'开始导出', 'total'=>$total]); } else { echoJson(['status'=>0, 'msg'=>'没有可导出的数据']); } break; case 'done': $file = __DIR__ . '/order_' . $randId . '.json'; $list = file_get_contents($file); if (empty($list)) { echoJson(['status'=>0, 'msg'=>'没有可生成的数据']); } $list = explode(PHP_EOL, $list); $data = []; foreach ($list as $v) { if ($v) { $v = json_decode($v, 1); foreach ($v as $item) { if ($item['mid']) { $minfo = $db->th_select('member', array('id'=>$item['mid']), 'phone'); if(empty($minfo)){ $mname = ''; } else { $mname = $minfo['phone']; } } else { $mname = ''; } $item['mname'] = $mname; $item['status'] = get_status_str($item['status']); $item['pay_time'] = !empty($item['pay_time']) ? date('Y-m-d H:i:s', $item['pay_time']) : ''; $item['total'] = format_money($item['total'], 0); if (empty($item['pay_way'])) { $item['pay_way'] = ''; } if ($item['pay_way'] == 'wechat') { $item['pay_way'] = '微信支付'; } if ($item['pay_way'] == 'alipay') { $item['pay_way'] = '支付宝支付'; } $item['cancel_time'] = !empty($item['cancel_time']) ? date('Y-m-d H:i:s', $item['cancel_time']) : ''; $item['discount_price'] = format_money($item['discount_price'], 0); $orderinfos = $db->th_selectall('order_data', array('order_id'=>$item['id']), 'num,gid,title,litpic,spec'); $good = ''; foreach ($orderinfos as $k1=>$v1) { $good .= $v1['title'] . ' ' . $v1['spec'] . ' X ' . $v1['num']; if ($k1 < count($orderinfos)-1) { $good .= PHP_EOL; } } $item['good'] = $good; $item['orderid'] = $item['orderid'] . ' '; $data[] = $item; } } } if (empty($data)) { echoJson(['status'=>0, 'msg'=>'没有可生成的数据']); } require_once __DIR__ . '/../../include/class/phpexcel.class.php'; $phpexcel = new myphpexcel(); $phpexcel->title = date('YmdHis'); $path = $phpexcel -> exportUserOrder($data); unlink($file); echoJson(['status'=>1, 'path'=>$path]); break; default: //收集数据,写入文本 $list = $db -> th_selectall($messageTable,$where,'id,orderid,total,status,mid,message,express,tnb,ship_time,receive_username,receive_phone,receive_address,pay_time,pay_way,cancel_time,discount,discount_price',array($row,$page)); if ($list) { file_put_contents('order_' . $randId . '.json', json_encode($list, JSON_UNESCAPED_UNICODE).PHP_EOL, FILE_APPEND); echoJson(['status'=>1, 'msg'=>'继续导出']); } else { echoJson(['status'=>0, 'msg'=>'数据收集完成']); } echoJson(['status'=>1, 'msg'=>'继续导出']); break; }
phpexcel 参考
public function exportUserOrder( $arr = array() ){ if (empty($arr)) { return false; } $this -> objPHPExcel->getActiveSheet()->setCellValue('A1', '订单号'); $this -> objPHPExcel->getActiveSheet()->setCellValue('B1', '商品'); $this -> objPHPExcel->getActiveSheet()->setCellValue('C1', '总金额'); $this -> objPHPExcel->getActiveSheet()->setCellValue('D1', '订单状态'); $this -> objPHPExcel->getActiveSheet()->setCellValue('E1', '支付时间'); $this -> objPHPExcel->getActiveSheet()->setCellValue('F1', '会员'); $this -> objPHPExcel->getActiveSheet()->setCellValue('G1', '收货人'); $this -> objPHPExcel->getActiveSheet()->setCellValue('H1', '收货人手机号'); $this -> objPHPExcel->getActiveSheet()->setCellValue('I1', '收货人地址'); $this -> objPHPExcel->getActiveSheet()->setCellValue('J1', '快递公司'); $this -> objPHPExcel->getActiveSheet()->setCellValue('K1', '运单号'); $i = 2; foreach($arr as $item){ $this -> objPHPExcel->getActiveSheet()->setCellValue('A' . $i, $item['orderid']); $this -> objPHPExcel->getActiveSheet()->setCellValue('B' . $i, $item['good']); $this -> objPHPExcel->getActiveSheet()->setCellValue('C' . $i, $item['total']); $this -> objPHPExcel->getActiveSheet()->setCellValue('D' . $i, $item['status']); $this -> objPHPExcel->getActiveSheet()->setCellValue('E' . $i, $item['pay_time']); $this -> objPHPExcel->getActiveSheet()->setCellValue('F' . $i, $item['mname']); $this -> objPHPExcel->getActiveSheet()->setCellValue('G' . $i, $item['receive_username']); $this -> objPHPExcel->getActiveSheet()->setCellValue('H' . $i, $item['receive_phone']); $this -> objPHPExcel->getActiveSheet()->setCellValue('I' . $i, $item['receive_address']); $this -> objPHPExcel->getActiveSheet()->setCellValue('J' . $i, $item['express']); $this -> objPHPExcel->getActiveSheet()->setCellValue('K' . $i, $item['tnb']); $i++; } $objActSheet = $this -> objPHPExcel->getActiveSheet(); //$objActSheet->getColumnDimension('C')->setAutoSize(true); $objActSheet->getColumnDimension('A')->setAutoSize(true); $objActSheet->getColumnDimension('B')->setAutoSize(true); $objActSheet->getColumnDimension('C')->setWidth(15); $objActSheet->getColumnDimension('D')->setWidth(15); $objActSheet->getColumnDimension('E')->setAutoSize(true); $objActSheet->getColumnDimension('F')->setAutoSize(true); $objActSheet->getColumnDimension('G')->setWidth(15); $objActSheet->getColumnDimension('H')->setAutoSize(true); $objActSheet->getColumnDimension('I')->setWidth(20); $objActSheet->getColumnDimension('J')->setWidth(20); $objActSheet->getColumnDimension('K')->setWidth(20); //样式美化 $this -> objPHPExcel->getActiveSheet()->getStyle('A1:K1')->applyFromArray( array( 'font' => array ( 'bold' => true ), 'alignment' => array ( 'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER , ), 'borders' => array ( 'top' => array ( 'style' => PHPExcel_Style_Border::BORDER_THIN ) ), 'fill' => array ( 'type' => PHPExcel_Style_Fill::FILL_GRADIENT_LINEAR , 'rotation' => 90, 'startcolor' => array ( 'argb' => 'FFA0A0A0' ), 'endcolor' => array ( 'argb' => 'FFFFFFFF' ) ) ) ); //输出文档 $objWriter = new PHPExcel_Writer_Excel2007($this -> objPHPExcel); $finalFileName = $this -> title.'.xlsx'; $path = '/upload/phpexcel/' . $finalFileName; $dirpath = __DIR__.'/../..' . $path; $objWriter->save($dirpath); //header('Content-Type: application/vnd.ms-excel'); //header("Content-Disposition:attachment; filename={$finalFileName}"); //header('Cache-Control: max-age=0'); return $path; }