ecshop导出订单导出excel订单表格的方法

很多时候，我们每月或者每年都需要做一个订单销售总结，这时要从ecshop订单管理里面拿订单详情，所以需要给ecshop订单管理加一个“导出订单”功能！
思路分析：ecshop后台的“订单管理”里面“打印订单”就是我们要的内容，只需要把内容用PHPExcel导出到一个excel表里面即可。
最终效果：所有信息版，为了能看全所有信息，我把列缩小了
1，admin\templates\order_list.htm 加入“导出订单”按钮

<input name="confirm" type="submit" id="btnSubmit" value="{$lang.op_confirm}" class="button" disabled="true" onclick="this.form.target = '_self'" />
<input name="invalid" type="submit" id="btnSubmit1" value="{$lang.op_invalid}" class="button" disabled="true" onclick="this.form.target = '_self'" />
<input name="cancel" type="submit" id="btnSubmit2" value="{$lang.op_cancel}" class="button" disabled="true" onclick="this.form.target = '_self'" />
<input name="remove" type="submit" id="btnSubmit3" value="{$lang.remove}" class="button" disabled="true" onclick="this.form.target = '_self'" />
<input name="print" type="submit" id="btnSubmit4" value="{$lang.print_order}" class="button" disabled="true" onclick="this.form.target = '_blank'" />
<!-- 导出订单功能开始 -->
<input name="export" type="submit" id="btnSubmit5" value="导出订单" class="button" disabled="true" onclick="this.form.target = '_blank'" />
<!-- 导出订单功能结束 -->
<input name="batch" type="hidden" value="1" />
<input name="order_id" type="hidden" value="" />

2、PHPExcel官方下载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导出订单导出excel订单表格的方法信息内容原创度和真实性等负责。如内容侵犯您的版权或其他权益，请留言并加以说明。站长审查之后若情况属实会及时为您删除。同时遵循 CC 4.0 BY-SA 版权协议，尊重和保护作者的劳动成果，转载请标明出处链接和本声明内容。本文作者：含玉» https://www.ebingou.cn/dmh/13713.html

很赞哦！ ()