ECSHOP订单批量导出导成成excel格式
2016-06-13 13:05 来源:www.chinab4c.com 作者:ecshop专家
各位ECSHOP网店系统用户大家好,欢迎来到ECSHOP教程网图文教程,今天为大家详细解说一下ECSHOP订单批量导出订单列表订单导成成excel格式。 ECSHOP教程网ECSHOP视频教程也再不断的完善与跟进,期待大家的关注!希望在ECSHOP的道路上,ECSHOP教程网与您一路同行! 很多时候,我们每月或者每年都需要做一个订单销售总结,这时要从ecshop订单管理里面拿订单详情,所以需要给ecshop订单管理加一个“导出订单”功能! 思路分析:ecshop后台的“订单管理”里面“打印订单”就是我们要的内容,只需要把内容用PHPExcel导出到一个excel表里面即可。 最终效果:所有信息版,为了能看全所有信息,我把列缩小了 1,admin\templates\order_list.htm 加入“导出订单”按钮 2、下载PHPExcel包,解压,复制Classes文件夹到ecshop后台目录下 3,admin\order.php 里面加入PHPExcel /* 去发货 */ elseif (isset($_POST['to_delivery'])) { $url = 'order.php?act=delivery_list&order_sn='.$_REQUEST['order_sn']; ecs_header("Location: $url\n"); exit; } 在它的下面加入 /* 导出订单功能开始 */ elseif (isset($_POST['export'])) { if (empty($_POST['order_id'])) { sys_msg($_LANG['pls_select_order']); } /* 赋值公用信息 */ $smarty->assign('shop_name', $_CFG['shop_name']); $smarty->assign('shop_url', $ecs->url()); $smarty->assign('shop_address', $_CFG['shop_address']); $smarty->assign('service_phone',$_CFG['service_phone']); $smarty->assign('print_time', local_date($_CFG['time_format'])); $smarty->assign('action_user', $_SESSION['admin_name']); $html = ''; $order_sn_list = explode(',', $_POST['order_id']); require_once dirname(__FILE__) . '/Classes/PHPExcel.php'; require_once dirname(__FILE__) . '/Classes/PHPExcel/IOFactory.php'; $PHPExcel = new PHPExcel(); //设置excel属性基本信息 $PHPExcel->getProperties()->setCreator("Neo") ->setLastModifiedBy("Neo") ->setTitle("东莞XX系统有限公司") ->setSubject("订单列表") ->setDescription("") ->setKeywords("订单列表") ->setCategory(""); $PHPExcel->setActiveSheetIndex(0); $PHPExcel->getActiveSheet()->setTitle("订单列表"); //填入表头主标题 $PHPExcel->getActiveSheet()->setCellValue('A1', $_CFG['shop_name'].'订单列表'); //填入表头副标题 $PHPExcel->getActiveSheet()->setCellValue('A2', '操作者:'.$_SESSION['admin_name'].' 导出日期:'.date('Y-m-d',time()).' 地址:'.$_CFG['shop_address'].' 电话:'.$_CFG['service_phone']); //合并表头单元格 $PHPExcel->getActiveSheet()->mergeCells('A1:T1'); $PHPExcel->getActiveSheet()->mergeCells('A2:T2'); //设置表头行高 $PHPExcel->getActiveSheet()->getRowDimension(1)->setRowHeight(40); $PHPExcel->getActiveSheet()->getRowDimension(2)->setRowHeight(20); $PHPExcel->getActiveSheet()->getRowDimension(3)->setRowHeight(30); //设置表头字体 $PHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setName('黑体'); $PHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(20); $PHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true); $PHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setName('宋体'); $PHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setSize(14); $PHPExcel->getActiveSheet()->getStyle('A3:T3')->getFont()->setBold(true); //设置单元格边框 $styleArray = array( 'borders' => array( 'allborders' => array( //'style' => PHPExcel_Style_Border::BORDER_THICK,//边框是粗的 'style' => PHPExcel_Style_Border::BORDER_THIN,//细边框 //'color' => array('argb' => 'FFFF0000'), ), ), ); //表格宽度 $PHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(18);//订单编号 $PHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(20);//下单时间 $PHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(20);//付款时间 $PHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(20);//发货时间 $PHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(18);//发货单号 $PHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(20);//支付方式 $PHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(10);//配送方式 $PHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(10);//配送费用 $PHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(10);//收件人 $PHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(35);//收货地址 $PHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(15);//电话 $PHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(15);//手机 $PHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth(25);//邮箱 $PHPExcel->getActiveSheet()->getColumnDimension('N')->setWidth(15);//货号 $PHPExcel->getActiveSheet()->getColumnDimension('O')->setWidth(15);//商品名称 $PHPExcel->getActiveSheet()->getColumnDimension('P')->setWidth(15);//属性 $PHPExcel->getActiveSheet()->getColumnDimension('Q')->setWidth(10);//价格 $PHPExcel->getActiveSheet()->getColumnDimension('R')->setWidth(6);//数量 $PHPExcel->getActiveSheet()->getColumnDimension('S')->setWidth(15);//小计 $PHPExcel->getActiveSheet()->getColumnDimension('T')->setWidth(15);//应付款金额 //表格标题 $PHPExcel->getActiveSheet()->setCellValue('A3', '订单编号'); $PHPExcel->getActiveSheet()->setCellValue('B3', '下单时间'); $PHPExcel->getActiveSheet()->setCellValue('C3', '付款时间'); $PHPExcel->getActiveSheet()->setCellValue('D3', '发货时间'); $PHPExcel->getActiveSheet()->setCellValue('E3', '发货单号'); $PHPExcel->getActiveSheet()->setCellValue('F3', '支付方式'); $PHPExcel->getActiveSheet()->setCellValue('G3', '配送方式'); $PHPExcel->getActiveSheet()->setCellValue('H3', '配送费用'); $PHPExcel->getActiveSheet()->setCellValue('I3', '收件人'); $PHPExcel->getActiveSheet()->setCellValue('J3', '收货地址'); $PHPExcel->getActiveSheet()->setCellValue('K3', '电话'); $PHPExcel->getActiveSheet()->setCellValue('L3', '手机'); $PHPExcel->getActiveSheet()->setCellValue('M3', '邮箱'); $PHPExcel->getActiveSheet()->setCellValue('N3', '货号'); $PHPExcel->getActiveSheet()->setCellValue('O3', '商品名称'); $PHPExcel->getActiveSheet()->setCellValue('P3', '属性'); $PHPExcel->getActiveSheet()->setCellValue('Q3', '价格'); $PHPExcel->getActiveSheet()->setCellValue('R3', '数量'); $PHPExcel->getActiveSheet()->setCellValue('S3', '小计'); $PHPExcel->getActiveSheet()->setCellValue('T3', '商品总金额'); $hang = 4; foreach ($order_sn_list as $order_sn) { /* 取得订单信息 */ $order = order_info(0, $order_sn); if (empty($order)) { continue; } /* 根据订单是否完成检查权限 */ if (order_finished($order)) { if (!admin_priv('order_view_finished', '', false)) { continue; } } else { if (!admin_priv('order_view', '', false)) { continue; } } /* 如果管理员属于某个办事处,检查该订单是否也属于这个办事处 */ $sql = "SELECT agency_id FROM " . $ecs->table('admin_user') . " WHERE user_id = '$_SESSION[admin_id]'"; $agency_id = $db->getOne($sql); if ($agency_id > 0) { if ($order['agency_id'] != $agency_id) { continue; } } /* 取得用户名 */ if ($order['user_id'] > 0) { $user = user_info($order['user_id']); if (!empty($user)) { $order['user_name'] = $user['user_name']; } } /* 取得区域名 */ $sql = "SELECT concat(IFNULL(c.region_name, ''), ' ', IFNULL(p.region_name, ''), " . "' ', IFNULL(t.region_name, ''), ' ', IFNULL(d.region_name, '')) AS region " . "FROM " . $ecs->table('order_info') . " AS o " . "LEFT JOIN " . $ecs->table('region') . " AS c ON o.country = c.region_id " . "LEFT JOIN " . $ecs->table('region') . " AS p ON o.province = p.region_id " . "LEFT JOIN " . $ecs->table('region') . " AS t ON o.city = t.region_id " . "LEFT JOIN " . $ecs->table('region') . " AS d ON o.district = d.region_id " . "WHERE o.order_id = '$order[order_id]'"; $order['region'] = $db->getOne($sql); /* 其他处理 */ $order['order_time'] = local_date($_CFG['time_format'], $order['add_time']); $order['pay_time'] = $order['pay_time'] > 0 ? local_date($_CFG['time_format'], $order['pay_time']) : $_LANG['ps'][PS_UNPAYED]; $order['shipping_time'] = $order['shipping_time'] > 0 ? local_date($_CFG['time_format'], $order['shipping_time']) : $_LANG['ss'][SS_UNSHIPPED]; $order['status'] = $_LANG['os'][$order['order_status']] . ',' . $_LANG['ps'][$order['pay_status']] . ',' . $_LANG['ss'][$order['shipping_status']]; $order['invoice_no'] = $order['shipping_status'] == SS_UNSHIPPED || $order['shipping_status'] == SS_PREPARING ? $_LANG['ss'][SS_UNSHIPPED] : $order['invoice_no']; /* 此订单的发货备注(此订单的最后一条操作记录) */ $sql = "SELECT action_note FROM " . $ecs->table('order_action') . " WHERE order_id = '$order[order_id]' AND shipping_status = 1 ORDER BY log_time DESC"; $order['invoice_note'] = $db->getOne($sql); $shuliang = 0; /* 取得订单商品 */ $sql = "SELECT o.*, g.goods_number AS storage, o.goods_attr, IFNULL(b.brand_name, '') AS brand_name " . "FROM " . $ecs->table('order_goods') . " AS o " . "LEFT JOIN " . $ecs->table('goods') . " AS g ON o.goods_id = g.goods_id " . "LEFT JOIN " . $ecs->table('brand') . " AS b ON g.brand_id = b.brand_id " . "WHERE o.order_id = '$order[order_id]' "; $res = $db->query($sql); $shuliang = 0; $chanpin = $hang; while ($row = $db->fetchRow($res)) { $shuliang = $shuliang + 1; /* 虚拟商品支持 */ if ($row['is_real'] == 0) { /* 取得语言项 */ $filename = ROOT_PATH . 'plugins/' . $row['extension_code'] . '/languages/common_' . $_CFG['lang'] . '.php'; if (file_exists($filename)) { include_once($filename); if (!empty($_LANG[$row['extension_code'] . '_link'])) { $row['goods_name'] = $row['goods_name'] . sprintf($_LANG[$row['extension_code'] . '_link'], $row['goods_id'], $order['order_sn']); } } } $row['formated_subtotal'] = price_format($row['goods_price'] * $row['goods_number']); $row['formated_goods_price'] = price_format($row['goods_price']); //var_dump($order);die; //输出订单的商品,由于可能一个人购买多个商品,所以在这先输出了 $PHPExcel->getActiveSheet()->setCellValue('N' . $chanpin, $row['goods_sn']); $PHPExcel->getActiveSheet()->setCellValue('O' . $chanpin, $row['goods_name']); $PHPExcel->getActiveSheet()->setCellValue('P' . $chanpin, $row['goods_attr']); $PHPExcel->getActiveSheet()->setCellValue('Q' . $chanpin, $row['goods_price']); $PHPExcel->getActiveSheet()->setCellValue('R' . $chanpin, $row['goods_number']); $PHPExcel->getActiveSheet()->setCellValue('S' . $chanpin, $row['formated_subtotal']); $chanpin = $chanpin + 1; } for ($kk = $hang; $kk < ($hang + $shuliang); $kk++) { //合并单元格 $PHPExcel->getActiveSheet()->mergeCells('A' . $hang . ':A' . $kk); $PHPExcel->getActiveSheet()->mergeCells('B' . $hang . ':B' . $kk); $PHPExcel->getActiveSheet()->mergeCells('C' . $hang . ':C' . $kk); $PHPExcel->getActiveSheet()->mergeCells('D' . $hang . ':D' . $kk); $PHPExcel->getActiveSheet()->mergeCells('E' . $hang . ':E' . $kk); $PHPExcel->getActiveSheet()->mergeCells('F' . $hang . ':F' . $kk); $PHPExcel->getActiveSheet()->mergeCells('G' . $hang . ':G' . $kk); $PHPExcel->getActiveSheet()->mergeCells('H' . $hang . ':H' . $kk); $PHPExcel->getActiveSheet()->mergeCells('I' . $hang . ':I' . $kk); $PHPExcel->getActiveSheet()->mergeCells('J' . $hang . ':J' . $kk); $PHPExcel->getActiveSheet()->mergeCells('K' . $hang . ':K' . $kk); $PHPExcel->getActiveSheet()->mergeCells('L' . $hang . ':L' . $kk); $PHPExcel->getActiveSheet()->mergeCells('M' . $hang . ':M' . $kk); $PHPExcel->getActiveSheet()->mergeCells('T' . $hang . ':T' . $kk); } $PHPExcel->getActiveSheet()->setCellValue('A' . ($hang), $order['order_sn']." ");//加个空格,防止时间戳被转换 $PHPExcel->getActiveSheet()->setCellValue('B' . ($hang), $order['order_time']); $PHPExcel->getActiveSheet()->setCellValue('C' . ($hang), $order['pay_time']); $PHPExcel->getActiveSheet()->setCellValue('D' . ($hang), $order['shipping_time']); $PHPExcel->getActiveSheet()->setCellValue('E' . ($hang), $order['invoice_no']." "); $PHPExcel->getActiveSheet()->setCellValue('F' . ($hang), $order['pay_name']); $PHPExcel->getActiveSheet()->setCellValue('G' . ($hang), $order['shipping_name']); $PHPExcel->getActiveSheet()->setCellValue('H' . ($hang), $order['shipping_fee'].'元'); $PHPExcel->getActiveSheet()->setCellValue('I' . ($hang), $order['consignee']); $PHPExcel->getActiveSheet()->setCellValue('J' . ($hang), str_replace(" ","",$order['region']).$order['address']); $PHPExcel->getActiveSheet()->setCellValue('K' . ($hang), $order['tel']); $PHPExcel->getActiveSheet()->setCellValue('L' . ($hang), $order['mobile']); $PHPExcel->getActiveSheet()->setCellValue('M' . ($hang), $order['email']); $PHPExcel->getActiveSheet()->setCellValue('T' . ($hang), $order['formated_goods_amount']); $hang = $hang + $shuliang; } //设置单元格边框 $PHPExcel->getActiveSheet()->getStyle('A1:T'.$hang)->applyFromArray($styleArray); //设置自动换行 $PHPExcel->getActiveSheet()->getStyle('A4:T'.$hang)->getAlignment()->setWrapText(true); //设置字体大小 $PHPExcel->getActiveSheet()->getStyle('A4:T'.$hang)->getFont()->setSize(12); //垂直居中 $PHPExcel->getActiveSheet()->getStyle('A1:T'.$hang)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); //水平居中 $PHPExcel->getActiveSheet()->getStyle('A1:T'.$hang)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $Writer = PHPExcel_IOFactory::createWriter($PHPExcel, 'Excel5'); $Writer->save(str_replace('.php', '.xls', __FILE__)); $url = "order.xls"; ecs_header("Location: $url\n"); exit; } /* 导出订单功能结束 */ 4,最后更新缓存,订单管理效果 最后还是放上修改的2个文件,和PHPExcel提供下载,在里面搜索“导出订单”就能找到增加的那部分了。 如果新手后台无修改过可以直接覆盖! 附件:点击下载 ECSHOP订单批量导出插件
最近更新
常用插件
- ecshop2.7.2退换货申请插件
ecshop2.7.2退换货申请插件,主要是在 ecshop 现在的基础上,对ecshop的订单处...
- ecshop会员中心订单excel倒出
ecshop会员中心订单excel倒出...
- ecshop二次商品订购人信息
ecshop二次商品订购人信息填写插件,有时候给朋友送花,或者是送礼品的...
- ecshop二次开发商品购买备
ecshop销售统计 插件介绍:ecshop二次开发订单销售统计和商品购买备注服...
- ecshop注册推荐送现金插件
ecshop中的促销售功能还不是很完善,随着电子商务系统的发展和进步,...