搜索到
69
篇与
PHP开发
的结果
-
-
-
导出一万行数据（包括合并行，超过26个字段）到Excel花了三秒左右时间的代码文件2 <?php /** * @author cxwt * 导出Excel订单类(包含横向合并功能) */ class Export_excel { private $file_name;//导出的文件名 private $output_data;//导出的数据内容 public function __construct() { } /** * 设置文件名 * @author cxwt&basil * @DateTime 2019-05-10T11:19:33+0800 * @param [type] $file_name [description] */ public function set_file_name($file_name) { $this->file_name = $file_name; } /** * 设置表头 * @author cxwt&basil * @DateTime 2019-05-10T17:24:40+0800 * @param array $data 表头数据(一维数组) */ public function set_header($data) { array_walk($data,function(&$v,$k) { $v = "<th style='text-align:center;background-color:gray;color:#fff;font-size:15px;vnd.ms-excel.numberformat:@'>".$v."</th>"; }); $this->output_data .= "<tr>".join($data)."</tr>"; } /** * 设置表格数据 * @author cxwt&basil * @DateTime 2019-05-10T17:27:33+0800 * @param string $data 需要导出的数据(html) */ public function set_data($data) { $this->output_data .= $data; } /** * 下载Excel文件 * @author cxwt&basil * @DateTime 2019-05-13T18:38:35+0800 * @return [type] [description] */ public function download() { // Redirect output to a client’s web browser (Excel5) header('Content-Type: application/vnd.ms-excel'); header('Content-Disposition: attachment;filename='.$this->file_name.'_'.date('Y-m-d').'.xls'); header('Cache-Control: max-age=0'); // If you're serving to IE 9, then the following may be needed header('Cache-Control: max-age=1'); // If you're serving to IE over SSL, then the following may be needed header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1 header ('Pragma: public'); // HTTP/1.0 $result = "<table border='1'>".$this->output_data."</table>"; echo <<< OEF <html> <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> <body> {$result} </body> </html> OEF; } } ?>
-
导出一万行数据（包括合并行，超过26个字段）到Excel花了三秒左右时间的代码文件1 <?php /** * @author cxwt */ class Export_order_lib { private $CI; private $user_id; private $store_id; function __construct() { $this->CI = & get_instance(); $this->CI->load->model('x_grand_order_model','grand_model'); $this->CI->load->model('x_ordinary_pay_model','record_model'); $this->CI->load->model('x_ordinary_pay_goods_model','goods_model'); $this->CI->load->model('x_user_model','user_model'); $this->CI->load->model('c_commander_model','commander_model'); $this->CI->load->library('export_excel'); $this->CI->load->model('x_staff_model'); } /** * 导出入口 * @author basil&cxwt * @param number $user_id 商户ID * @param number $store_id 店铺ID * @param array $params 导出条件 * @return [type] [description] */ public function do_export($user_id,$store_id,$params) { $this->user_id = $user_id; $this->store_id = $store_id; $user_list = $this->get_user_info($user_id); $commander_list = $this->commander_list($user_id); $buyer_list = $this->get_buyer_info($user_id); $export_string = $this->get_export_string($params,$user_list,$commander_list,$buyer_list,$staff_list); $staff_list = $this->get_staff_list($store_id); $excel_header = ['序号','市场名称','所属团长','团长手机号','下单时间','配送时间','订单号','订单状态','配送员','运费','优惠','订单原价总额','实际支付金额','付款方式','配送方式','收货人','收货地址','联系电话','商品编码','商品二级编码','商品名','仓位','规格','属性分类','条码','单位','数量','单价','金额','备注']; $this->CI->export_excel->set_file_name('订单数据'); $this->CI->export_excel->set_header($excel_header); $this->CI->export_excel->set_data($export_string); $this->CI->export_excel->download(); } /** * 获取导出原始数据 * @author basil&cxwt * @param [type] $params [description] * @param [type] $user_list [description] * @param [type] $commander_list [description] * @param [type] $buyer_list [description] * @return [type] [description] */ private function get_export_string($params,$user_list,$commander_list,$buyer_list,$staff_list) { ini_set('memory_limit','512M'); $total_amount = $this->get_total_amount($params); $pay_way_arr = [2000=>'余额',2001=>'微信',2002=>'支付宝',2003=>'货到付款']; $takout_type_arr = [1=>'外卖配送',2=>'打包自带']; $state_arr = [101=>'待付款',102=>'已付款',103=>'配货中',104=>'已配货',105=>'已发货',106=>'已送达']; $export_group_string = ''; $export_string = ''; $grand_ids = []; $pay_ids = []; $page = 1; $limit = 300; $index = 0; $no = 0; while (true) { $offset = ($page-1)*$limit; $order_list = $this->get_export_data($params,$limit,$offset); if (empty($order_list)) { break; } $page++; for ($i=0; $i < count($order_list); $i++) { $v = $order_list[$i]; $v['user_name'] = isset($user_list[$v['user_id']]['user_name']) ? $user_list[$v['user_id']]['user_name'] : '未知'; $v['commander_name'] = isset($commander_list[$v['commander_id']]['name']) ? $commander_list[$v['commander_id']]['name'] : '未知'; $v['commander_mobilephone'] = isset($commander_list[$v['commander_id']]['mobilephone']) ? $commander_list[$v['commander_id']]['mobilephone'] : '无'; $v['buyer_name'] = isset($buyer_list[$v['buyer_id']]['buyer_name']) ? $buyer_list[$v['buyer_id']]['buyer_name'] : '未知'; $v['create_time'] = date("Y-m-d H:i:s",$v['create_time']); $v['delivery_time'] = date("Y-m-d H:i:s",$v['arrive_start_time']); $v['delivery_staff_name'] = isset($staff_list[$v['delivery_staff_id']]) ? $staff_list[$v['delivery_staff_id']]['staff_name'] : '暂无'; $v['state_desc'] = isset($state_arr[$v['state']]) ? $state_arr[$v['state']] : '未知'; $v['pay_way'] = isset($pay_way_arr[$v['pay_way']]) ? $pay_way_arr[$v['pay_way']] : "未知"; $v['takout_type'] = isset($takout_type_arr[$v['takout_type']]) ? $takout_type_arr[$v['takout_type']] : '未知'; $td_html = ''; if (!in_array($v['grand_id'],$grand_ids)) { $rowspan_amount = count($grand_ids); $export_group_string = str_replace('[ROWSPAN1]',$rowspan_amount,$export_group_string); $no++; $grand_ids = []; $td_html .= " <td style='text-align:center;font-size:14px;' rowspan='[ROWSPAN1]'>{$no}</td> <td style='text-align:center;font-size:14px;' rowspan='[ROWSPAN1]'>{$v['user_name']}</td> <td style='text-align:center;font-size:14px;' rowspan='[ROWSPAN1]'>{$v['commander_name']}</td> <td style='text-align:center;font-size:14px;' rowspan='[ROWSPAN1]'>{$v['commander_mobilephone']}</td> <td style='text-align:center;font-size:14px;' rowspan='[ROWSPAN1]'>{$v['create_time']}</td> <td style='text-align:center;font-size:14px;' rowspan='[ROWSPAN1]'>{$v['delivery_time']}</td> <td style='text-align:center;font-size:14px;vnd.ms-excel.numberformat:@' rowspan='[ROWSPAN1]'>{$v['pay_no']}</td> <td style='text-align:center;font-size:14px;' rowspan='[ROWSPAN1]'>{$v['state_desc']}</td> <td style='text-align:center;font-size:14px;' rowspan='[ROWSPAN1]'>{$v['delivery_staff_name']}</td> <td style='text-align:center;font-size:14px;' rowspan='[ROWSPAN1]'>{$v['extra_price']}</td> <td style='text-align:center;font-size:14px;' rowspan='[ROWSPAN1]'>{$v['grand_coupon']}</td> <td style='text-align:center;font-size:14px;' rowspan='[ROWSPAN1]'>{$v['amount']}</td> <td style='text-align:center;font-size:14px;' rowspan='[ROWSPAN1]'>{$v['actual_amount']}</td> <td style='text-align:center;font-size:14px;' rowspan='[ROWSPAN1]'>{$v['pay_way']}</td> <td style='text-align:center;font-size:14px;' rowspan='[ROWSPAN1]'>{$v['takout_type']}</td> <td style='text-align:center;font-size:14px;' rowspan='[ROWSPAN1]'>{$v['receiver_name']}</td> <td style='text-align:center;font-size:14px;' rowspan='[ROWSPAN1]'>{$v['receiver_province']}{$v['receiver_city']}{$v['receiver_district']}{$v['receiver_address']}</td> <td style='text-align:center;font-size:14px;' rowspan='[ROWSPAN1]'>{$v['receiver_mobilephone']}</td> "; $export_string .= $export_group_string; $export_group_string = ''; } // if ((!in_array($v['pay_id'], $pay_ids)&&!empty($pay_ids))||$k == 0) { // $rowspan_amount = count($pay_ids); // $export_group_string = str_replace('[ROWSPAN2]',$rowspan_amount,$export_group_string); // $pay_ids = []; // $td_html .= " // <td style='text-align:center;font-size:14px;' rowspan='[ROWSPAN2]'>{$v['takout_type']}</td> // <td style='text-align:center;font-size:14px;' rowspan='[ROWSPAN2]'>{$v['receiver_name']}</td> // <td style='text-align:center;font-size:14px;' rowspan='[ROWSPAN2]'>{$v['receiver_province']}{$v['receiver_city']}{$v['receiver_district']}{$v['receiver_address']}</td> // <td style='text-align:center;font-size:14px;' rowspan='[ROWSPAN2]'>{$v['receiver_mobilephone']}</td> // "; // } // if (!in_array($v['grand_id'], $grand_ids)) { // $export_string .= $export_group_string; // $export_group_string = ''; // $no ++; // } $grand_ids[] = $v['grand_id']; // $pay_ids[] = $v['pay_id']; $td_html .= " <td style='text-align:center;font-size:14px;'>{$v['goods_no']}</td> <td style='text-align:center;font-size:14px;vnd.ms-excel.numberformat:@'>{$v['goods_code']}</td> <td style='text-align:center;font-size:14px;'>{$v['goods_name']}</td> <td style='text-align:center;font-size:14px;'>{$v['warehouse_position']}</td> <td style='text-align:center;font-size:14px;'>{$v['goods_attr']}</td> <td style='text-align:center;font-size:14px;'>暂无</td> <td style='text-align:center;font-size:14px;vnd.ms-excel.numberformat:@'>{$v['goods_barcode']}</td> <td style='text-align:center;font-size:14px;'>{$v['unit']}</td> <td style='text-align:center;font-size:14px;'>{$v['goods_count']}</td> <td style='text-align:center;font-size:14px;'>{$v['goods_price']}</td> <td style='text-align:center;font-size:14px;'>{$v['pay_money']}</td> <td style='text-align:center;font-size:14px;'></td> "; $export_group_string .= "<tr>".$td_html."</tr>"; if ($index == $total_amount-1) {//最后一个 if (in_array($v['grand_id'], $grand_ids)) { $rowspan_amount = count($grand_ids); $export_group_string = str_replace('[ROWSPAN1]',$rowspan_amount,$export_group_string); } $export_string .= $export_group_string; } unset($v); $index++; } unset($order_list); } return $export_string; } /** * 获取导出数据 * @author basil&cxwt * @param array $params 查询参数 * @return [type] [description] */ private function get_export_data($params,$limit,$offset) { $field = " a.id,a.goods_no,a.goods_name,a.goods_attr,a.goods_code,a.goods_barcode,a.unit,a.goods_price,a.goods_count,a.commander_id,a.pay_money, b.pay_id,b.takout_type,b.receiver_name,b.receiver_mobilephone,b.receiver_province,b.receiver_city,b.receiver_district,b.receiver_address, c.grand_id,c.user_id,c.buyer_id,c.client_id,c.pay_no,c.state,c.pay_way,c.pay_state,c.delivery_staff_id,c.amount,c.actual_amount,c.extra_price,c.grand_coupon,c.create_time,c.arrive_start_time, d.warehouse_position "; $where = $this->link_where($params); $where[] = ['join','x_ordinary_pay_record as b','a.pay_id = b.pay_id','left']; $where[] = ['join','x_grand_order as c','b.grand_id = c.grand_id','left']; $where[] = ['join','x_user_store_goods as d','a.goods_id = d.goods_id','left']; $this->CI->goods_model->set_table_as('x_ordinary_pay_goods as a'); $res = $this->CI->goods_model->set_order_by('a.id desc')->get_record($where,$field,'multiple',$limit,$offset); return $res['data']; } /** * 获取总数 * @author basil&cxwt * @param [type] $params [description] * @return [type] [description] */ public function get_total_amount($params) { $field = "a.id"; $where = $this->link_where($params); $where[] = ['join','x_ordinary_pay_record as b','a.pay_id = b.pay_id','left']; $where[] = ['join','x_grand_order as c','b.grand_id = c.grand_id','left']; $where[] = ['join','x_user_store_goods as d','a.goods_id = d.goods_id','left']; $this->CI->goods_model->set_table_as('x_ordinary_pay_goods as a'); $res = $this->CI->goods_model->set_order_by('a.id desc')->get_record($where,$field,'num'); return $res['data']; } /** * 整合查询条件 * @author basil&cxwt * @param [type] $params [description] * @return [type] [description] */ private function link_where($params) { $where = [['where',['c.user_id'=>$this->user_id]]]; if (isset($params['pay_no'])&&!empty($params['pay_no'])) { $where[] = ['where',['c.pay_no'=>$params['pay_no']]]; } // $where[] = ['where',['c.grand_id <'=>1000]]; if (isset($params['client_name'])&&!empty($params['client_name'])) { $where[] = ['where_in','c.client_id',$this->get_client_ids_by_client_name($params['client_name'])]; } if (isset($params['receiver_mobilephone'])&&!empty($params['receiver_mobilephone'])) { $where[] = ['like','receiver_mobilephone',$params['receiver_mobilephone']]; } if (isset($params['time'])&&!empty($params['time'])) { $time_range = $this->time_range($params['time']); $where[] = ['where',['c.create_time >= '=>$time_range['start_time'],'c.create_time <='=>$time_range['end_time']]]; } if (isset($params['order_arrive_time'])&&!empty($params['order_arrive_time'])) { $time_range = $this->time_range($params['order_arrive_time']); $where[] = ['where',['c.arrive_start_time >='=>$time_range['start_time'],'c.arrive_start_time <='=>$time_range['end_time']]]; } if (isset($params['state'])) { $condition = $this->handle_state($params['state']); if (isset($condition['state'])) { if (!is_array($condition['state'])) { $where[] = ['where',['c.state'=>$condition['state']]]; }else{ $where[] = ['where_in','c.state',$condition['state']]; } } if (isset($condition['pay_state'])) { if (!is_array($condition['pay_state'])) { $where[] = ['where',['c.pay_state'=>$condition['pay_state']]]; }else{ $where[] = ['where_in','c.pay_state',$condition['pay_state']]; } } if (isset($condition['pay_way'])) { $where[] = ['where',['c.pay_way'=>$condition['pay_way']]]; } } if (isset($params['take_state'])&&in_array($params['take_state'], ['1','2'])) { $where[] = ['where',['b.takout_type'=>$params['take_state']]]; } if (isset($params['pay_type'])&&!empty($params['pay_type'])) { $where[] = ['where',['c.pay_way'=>$params['pay_type']]]; } if (isset($params['pay_state'])&&in_array($params['pay_state'], ['1','2'])) { $pay_state_arr = [1=>1000,2=>10001]; $where[] = ['where',['c.pay_state'=>$pay_state_arr[$params['pay_state']]]]; } if (isset($params['pay_source'])&&in_array($params['pay_source'], ['4','5'])) { $where[] = ['where',['b.pay_source'=>$params['pay_source']]]; } if (isset($params['buyer_id'])&&is_numeric($params['buyer_id'])) { $where[] = ['where',['c.buyer_id'=>$params['buyer_id']]]; } if (isset($params['delivery_staff_id'])&&is_numeric($params['delivery_staff_id'])) { $where[] = ['where',['delivery_staff_id'=>$params['delivery_staff_id']]]; } return $where; } /** * 处理时间范围 * @author basil&cxwt * @param [type] $time_string [description] * @return [type] [description] */ private function time_range($time_string) { $time_data = explode(' - ',$params['time']); $start_time = strtotime($time_data[0]); $end_time = strtotime($time_data[1]); return ['start_time'=>$start_time,'end_time'=>$end_time]; } private function handle_state($post) { $data = []; if (isset($post['state']) && trim($post['state']) != 0) { if ($post['state'] == 1) { $data['pay_state'] = array(1001, 1002); $data['state'] = 101; } else if ($post['state'] == 2) { $data['pay_state'] = 1000; $data['state'] = 101; } else if ($post['state'] == 3) { $data['state'] = 102; } else if ($post['state'] == 4) { $data['state'] = 103; } else if ($post['state'] == 5) { $data['state'] = 104; } else if ($post['state'] == 6) { $data['state'] = 105; } else if ($post['state'] == 7) { $data['state'] = 106; } else if ($post['state'] == 8) { $data['state'] = 100; } else if ($post['state'] == 9) { $data['state'] = array(500, 501, 502, 503); } else if ($post['state'] == 10) { $data['state'] = array(401, 402, 403); }else if($post['state'] == 11){ $data['pay_state'] = array(1001, 1002); $data['state'] = 101; $data['pay_way'] = 2003; } else { $data['pay_state'] = 1000; } } return $data; } /** * 获取商户信息 * @author basil&cxwt * @return [type] [description] */ private function get_user_info() { $arr = ['where',['user_id >'=>0]]; $res = $this->CI->user_model->get_record($arr,'user_id,user_name'); $user_info = $res['data']; if (!empty($user_info)) { $user_info = array_column($user_info,null,'user_id'); } return $user_info; } /** * 获取买手列表 * @author basil&cxwt * @param [type] $user_id [description] * @return [type] [description] */ public function get_buyer_info($user_id) { $this->CI->load->model('x_buyer_model'); $arr = ['where',['user_id'=>$user_id]]; $res = $this->CI->x_buyer_model->get_record($arr,'buyer_id,buyer_name'); $buyer_info = $res['data']; if (!empty($buyer_info)) { $buyer_info = array_column($buyer_info,null,'buyer_id'); } return $buyer_info; } /** * 获取团长列表 * @author basil&cxwt * @param [type] $user_id [description] * @return [type] [description] */ public function commander_list($user_id) { $arr = ['where',['user_id'=>$user_id]]; $res = $this->CI->commander_model->get_record($arr,'id,name,mobilephone'); $commander_list = $res['data']; if (!empty($commander_list)) { $commander_list = array_column($commander_list, null,'id'); } return $commander_list; } /** * 通过用户姓名查询用户ID集合 * @author basil&cxwt * @param string $client_name 用户名称 * @return [type] [description] */ private function get_client_ids_by_client_name($client_name) { $this->CI->load->model('x_client_model','client_model'); $arr = [ ['where',['user_id'=>$this->user_id,'store_id'=>$this->store_id]], ['like','name',$client_name] ]; $res = $this->CI->client_model->get_record($arr,'client_id'); $client_info = $res['data']; if (empty($client_info)) { $client_ids = [0]; }else{ $client_ids = array_column($client_info,'client_id'); } return $client_ids; } /** * 获取配送员列表 * @author basil&cxwt * @param [type] $user_id [description] * @return [type] [description] */ private function get_staff_list($store_id) { $res = $this->CI->x_staff_model->get_record(['where',['store_id'=>$store_id]],'staff_id,staff_name'); $staff_list = $res['data']; if (!empty($staff_list)) { $staff_list = array_column($staff_list, null,'staff_id'); } return $staff_list; } } ?>
-
-
-
-
