选择当天一周一月导出excel表格
<a href="javascript:;" class="fr btn btn-primary radius mt-6 mr-10" onclick="window.location.reload()">刷新</a>
<a href="javascript:;" class="fr btn btn-primary radius mt-6 mr-10 btn_excel" title="导出Excel报表">导出Excel</a>
<select id="verifystatusd" class="select"> <option value="0">导出时间</option> <option value="1">一天</option> <option value="2">一周</option> <option value="3">本月</option> </select>
//导出excel
$(".btn_excel").click(function(){
var time = $('#verifystatusd').val();
console.log(time);
window.location.href = SITEURL+"member/exportfp_excel?time=" + time;
});
/** * 发票导出 */ public function exportfp_excel() { //本天 $beginToday=mktime(0,0,0,date('m'),date('d'),date('Y')); $endToday=mktime(0,0,0,date('m'),date('d')+1,date('Y'))-1; //本周 $weekf = mktime(0, 0 , 0,date("m"),date("d")-date("w")+1,date("Y")); $weeke = mktime(23,59,59,date("m"),date("d")-date("w")+7,date("Y")); //本月 $monthf = mktime(0, 0 , 0,date("m"),1,date("Y")); $monthe = mktime(23,59,59,date("m"),date("t"),date("Y")); $time = intval(Arr::get($_GET, 'time')); $where = 'WHERE'; switch ($time) { case '0': $where .= "`applytime` BETWEEN ". $monthf ." and ". $monthe; break; case '1': $where .= " `applytime` BETWEEN ". $beginToday ." and ". $endToday; break; case '2': $where .= " `applytime` BETWEEN ". $weekf ." and ". $weeke; break; case '3': $where .= " `applytime` BETWEEN ". $monthf ." and ". $monthe; break; default: $where .= " `applytime` BETWEEN ". $monthf ." and ". $monthe; break; }
//链表查询出用户信息以及发票信息。作为导出表格内容 $sql="select s.*,m.nickname,m.truename,m.mobile from sline_applyinvoice as s left join sline_member as m on s.mid = m.mid {$where} ORDER BY `applytime` DESC"; //$sql = "SELECT * FROM `sline_applyinvoice` {$where} ORDER BY `applytime` DESC"; $rows = DB::query(1, $sql) ->execute() ->as_array(); foreach ($rows as $key => &$value) { $value['applytime'] = date('Y-m-d H:i:s', $value['applytime']); } // print_r($rows);die; $table = "<table><tr>"; $table .= "<td>昵称</td>"; $table .= "<td>真实姓名</td>"; $table .= "<td>手机号</td>"; $table .= "<td>申请状态</td>"; $table .= "<td>申请金额</td>"; $table .= "<td>申请时间</td>"; $table .= "</tr>"; $verify_arr = array( 0 => '未通过', 1 => '已通过', ); foreach ($rows as $row) { $table .= "<tr>"; $table .= "<td>{$row['nickname']}</td>"; $table .= "<td>{$row['truename']}</td>"; $table .= "<td>{$row['mobile']}</td>"; $table .= "<td>{$verify_arr[$row['status']]}</td>"; $table .= "<td>{$row['amount']}</td>"; $table .= "<td>{$row['applytime']}</td>"; $table .= "</tr>"; } $table .= "</table>"; $filename = date('Ymdhis'); ob_end_clean();//清除缓冲区 header('Pragma:public'); header('Expires:0'); header('Content-Type:charset=utf-8'); header('Cache-Control:must-revalidate,post-check=0,pre-check=0'); header('Content-Type:application/force-download'); header('Content-Type:application/vnd.ms-excel'); header('Content-Type:application/octet-stream'); header('Content-Type:application/download'); header('Content-Disposition:attachment;filename=' . $filename . ".xls"); header('Content-Transfer-Encoding:binary'); $code = mb_detect_encoding($table, array('ASCII', 'GB2312', 'UTF-8')); if ($code != 'UTF-8') { $table = mb_convert_encoding($table, 'UTF-8', $code); } exit($table); }
注意：以上是我修改后的代码：源代码是：
/** * @function 导出会员 */ public function action_export_excel() { $verifystatus = intval(Arr::get($_GET, 'verifystatus')); $sex = Arr::get($_GET, 'sex'); $virtual = intval(Arr::get($_GET, 'virtual')); $where = 'WHERE 1 = 1'; if ($verifystatus != -1) { $where .= " AND `verifystatus` = {$verifystatus} "; } if ($sex != 0) { $where .= " AND `sex` = '{$sex}' "; } if ($virtual != 0) { $where .= " AND `virtual` = {$virtual} "; } $sql = "SELECT * FROM `sline_member` {$where} ORDER BY `jointime` DESC"; $rows = DB::query(1, $sql) ->execute() ->as_array(); //上面是根据需求查询出需要导出的数据。下面是表格的设定 $table = "<table><tr>"; $table .= "<td>昵称</td>"; $table .= "<td>真实姓名</td>"; $table .= "<td>性别</td>"; $table .= "<td>会员等级</td>"; $table .= "<td>钱包总额</td>"; $table .= "<td>邮箱</td>"; $table .= "<td>手机号</td>"; $table .= "<td>积分</td>"; $table .= "<td>加入时间</td>"; $table .= "<td>加入IP</td>"; $table .= "<td>邮箱验证</td>"; $table .= "<td>手机验证</td>"; $table .= "<td>省份</td>"; $table .= "<td>城市</td>"; $table .= "<td>身份证号</td>"; $table .= "<td>地址</td>"; $table .= "<td>邮政编码</td>"; $table .= "<td>备注</td>"; $table .= "<td>注册类型</td>"; $table .= "<td>出生年月</td>"; $table .= "<td>星座</td>"; $table .= "<td>QQ</td>"; $table .= "<td>个性签名</td>"; $table .= "<td>实名认证</td>"; $table .= "<td>微信号</td>"; $table .= "<td>籍贯</td>"; $table .= "<td>冻结金额</td>"; $table .= "<td>用户类型</td>"; $table .= "</tr>"; $verify_arr = array( 0 => '未认证', 1 => '审核中', 2 => '已认证', 3 => '未通过', ); foreach ($rows as $row) { $table .= "<tr>"; $table .= "<td>{$row['nickname']}</td>"; $table .= "<td>{$row['truename']}</td>"; $table .= "<td>{$row['sex']}</td>"; $table .= "<td>" . Common::member_rank($row['mid'],array('return'=>'rankname')) . "</td>"; $table .= "<td style='vnd.ms-excel.numberformat:@'>{$row['money']}</td>"; $table .= "<td>{$row['email']}</td>"; $table .= "<td style='vnd.ms-excel.numberformat:@'>{$row['mobile']}</td>"; $table .= "<td style='vnd.ms-excel.numberformat:@'>{$row['jifen']}</td>"; $jointime = $row['jointime'] ? date('Y-m-d H:i:s', $row['jointime']) : ''; $table .= "<td>" . $jointime . "</td>"; $table .= "<td>{$row['joinip']}</td>"; $checkmail = $row['checkmail'] == 1 ? '已验证' : '未验证'; $checkphone = $row['checkphone'] == 1 ? '已验证' : '未验证'; $table .= "<td>". $checkmail ."</td>"; $table .= "<td>". $checkphone ."</td>"; $table .= "<td>{$row['province']}</td>"; $table .= "<td>{$row['city']}</td>"; $table .= "<td style='vnd.ms-excel.numberformat:@'>{$row['cardid']}</td>"; $table .= "<td>{$row['address']}</td>"; $table .= "<td style='vnd.ms-excel.numberformat:@'>{$row['postcode']}</td>"; $table .= "<td>{$row['remarks']}</td>"; $regtype = $row['regtype'] == 1 ? '邮箱' : '手机'; $table .= "<td>". $regtype ."</td>"; $table .= "<td>{$row['birth_date']}</td>"; $table .= "<td>{$row['constellation']}</td>"; $table .= "<td>{$row['qq']}</td>"; $table .= "<td>{$row['signature']}</td>"; $table .= "<td>" . $verify_arr[$row['verifystatus']] . "</td>"; $table .= "<td>{$row['wechat']}</td>"; $table .= "<td>{$row['native_place']}</td>"; $table .= "<td style='vnd.ms-excel.numberformat:@'>{$row['money_frozen']}</td>"; $virtual = $row['virtual'] == 1 ? '普通用户' : '虚拟用户'; $table .= "<td>". $virtual ."</td>"; $table .= "</tr>"; } $table .= "</table>"; $filename = date('Ymdhis'); ob_end_clean();//清除缓冲区 header('Pragma:public'); header('Expires:0'); header('Content-Type:charset=utf-8'); header('Cache-Control:must-revalidate,post-check=0,pre-check=0'); header('Content-Type:application/force-download'); header('Content-Type:application/vnd.ms-excel'); header('Content-Type:application/octet-stream'); header('Content-Type:application/download'); header('Content-Disposition:attachment;filename=' . $filename . ".xls"); header('Content-Transfer-Encoding:binary'); $code = mb_detect_encoding($table, array('ASCII', 'GB2312', 'UTF-8')); if ($code != 'UTF-8') { $table = mb_convert_encoding($table, 'UTF-8', $code); } exit($table); }
