ecshop模板

ECSHOP订单批量导出导成成excel格式

分类:ECSHOP二次开发 来源: https://www.ecshopjcw.com 时间:2015-02-02

各位ECSHOP网店系统用户大家好,欢迎来到ECSHOP教程网图文教程,今天为大家详细解说一下ECSHOP订单批量导出订单列表订单导成成excel格式

ECSHOP教程网ECSHOP视频教程也再不断的完善与跟进,期待大家的关注!希望在ECSHOP的道路上,ECSHOP教程网与您一路同行!

很多时候,我们每月或者每年都需要做一个订单销售总结,这时要从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包,解压,复制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订单批量导出插件

你可能还会喜欢

qq code back_top
温馨提示

确定取消
温馨提示

关闭
您尚未登录

用户登陆

立即注册
忘记密码?
ecshop
ecshop下载 ecshop模板 ecshop多用户 ecshop免费模板
ecshop二次开发手册
ecshop二次开发 ecshop模板教程 ecshop微信配置 ecshop安装教程
网站建设制作
网站源码下载 网站建设教程 网站SEO优化 网站制作软件
ecshop教程网
ecshop模板定制 常见问题 ecshop二次开发. 关于我们
服务咨询 ECSHOP模板修改 微信:php2029
ECSHOP二次开发 moban2000@qq.com

可信网站 诚信网站 实名验证网站 实名网站 360网站安全检测认证