ecshop微信商城
  官方微博: 騰訊  
首頁 > ECSHOP教程 > ECSHOP二次開發 > ECSHOP二次開發教程 > ECSHOP訂單批量導出導成成excel格式

ECSHOP訂單批量導出導成成excel格式

ECSHOP教程 / ecshop 教程網(www.kkesgv.icu) 2015-02-03

各位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訂單批量導出插件

轉載請注明:ECshop119模板屋-ECshop教程網(全國最專業的ECSHOP插件制作商-專業ECSHOP二次開發)

幫助目錄

在線反饋

加ECSHOP模板屋微信語音幫助。

ecshop微信群

與網友交流ECSHOP開發修改。

加入QQ群交流