各位ECSHOP網(wǎng)店系統(tǒng)用戶大家好，歡迎來到素材無憂圖文教程，今天為大家詳細(xì)解說一下ECSHOP訂單批量導(dǎo)出訂單列表訂單導(dǎo)成成excel格式。
素材無憂ECSHOP視頻教程也再不斷的完善與跟進(jìn)，期待大家的關(guān)注！希望在ECSHOP的道路上，素材無憂與您一路同行！
很多時(shí)候，我們每月或者每年都需要做一個(gè)訂單銷售總結(jié)，這時(shí)要從ecshop訂單管理里面拿訂單詳情，所以需要給ecshop訂單管理加一個(gè)"導(dǎo)出訂單"功能！
思路分析：ecshop后臺(tái)的"訂單管理"里面"打印訂單"就是我們要的內(nèi)容，只需要把內(nèi)容用PHPExcel導(dǎo)出到一個(gè)excel表里面即可。
最終效果：所有信息版，為了能看全所有信息，我把列縮小了
1，admintemplatesorder_list.htm 加入"導(dǎo)出訂單"按鈕

2、下載PHPExcel包，解壓， Classes文件夾到ecshop后臺(tái)目錄下
3，adminorder.php 里面加入PHPExcel

/* 去發(fā)貨 */
elseif (isset($_POST['to_delivery']))
{
$url = 'order.php?act=delivery_list&order_sn='.$_REQUEST['order_sn'];
ecs_header("Location: $urln");
exit;
}

在它的下面加入

/* 導(dǎo)出訂單功能開始 */
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();
//設(shè)置excel屬 基本信息
$PHPExcel->getProperties()->setCreator("Neo")
->setLastModifiedBy("Neo")
->setTitle("東莞XX系統(tǒng)有限公司")
->setSubject("訂單列表")
->setDescription("")
->setKeywords("訂單列表")
->setCategory("");
$PHPExcel->setActiveSheetIndex(0);
$PHPExcel->getActiveSheet()->setTitle("訂單列表");
//填入表頭主標(biāo)題
$PHPExcel->getActiveSheet()->setCellValue('A1', $_CFG['shop_name'].'訂單列表');
//填入表頭副標(biāo)題
$PHPExcel->getActiveSheet()->setCellValue('A2', '操作者：'.$_SESSION['admin_name'].' 導(dǎo)出日期：'.date('Y-m-d',time()).' 地址：'.$_CFG['shop_address'].' 電話：'.$_CFG['service_phone']);
//合并表頭單元格
$PHPExcel->getActiveSheet()->mergeCells('A1:T1');
$PHPExcel->getActiveSheet()->mergeCells('A2:T2');
//設(shè)置表頭行高
$PHPExcel->getActiveSheet()->getRowDimension(1)->setRowHeight(40);
$PHPExcel->getActiveSheet()->getRowDimension(2)->setRowHeight(20);
$PHPExcel->getActiveSheet()->getRowDimension(3)->setRowHeight(30);
//設(shè)置表頭字體
$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);
//設(shè)置單元格邊框
$styleArray = array(
'borders' => array(
'allborders' => array(
//'style' => PHPExcel_Style_Border::BORDER_THICK,//邊框是粗的
'style' => PHPExcel_Style_Border::BORDER_THIN,//細(xì)邊框
//'color' => array('argb' => 'FFFF0000'),
),
),
);
//表格寬度
$PHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(18);//訂單編號(hào)
$PHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(20);//下單時(shí)間
$PHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(20);//付款時(shí)間
$PHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(20);//發(fā)貨時(shí)間
$PHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(18);//發(fā)貨單號(hào)
$PHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(20);//支付方式
$PHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(10);//配送方式
$PHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(10);//配送費(fèi)用
$PHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(10);//收件人
$PHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(35);//收貨地址
$PHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(15);//電話
$PHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(15);//手機(jī)
$PHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth(25);//郵箱
$PHPExcel->getActiveSheet()->getColumnDimension('N')->setWidth(15);//貨號(hào)
$PHPExcel->getActiveSheet()->getColumnDimension('O')->setWidth(15);//商品名稱
$PHPExcel->getActiveSheet()->getColumnDimension('P')->setWidth(15);//屬
$PHPExcel->getActiveSheet()->getColumnDimension('Q')->setWidth(10);//價(jià)格
$PHPExcel->getActiveSheet()->getColumnDimension('R')->setWidth(6);//數(shù)量
$PHPExcel->getActiveSheet()->getColumnDimension('S')->setWidth(15);//小計(jì)
$PHPExcel->getActiveSheet()->getColumnDimension('T')->setWidth(15);//應(yīng)付款金額
//表格標(biāo)題
$PHPExcel->getActiveSheet()->setCellValue('A3', '訂單編號(hào)');
$PHPExcel->getActiveSheet()->setCellValue('B3', '下單時(shí)間');
$PHPExcel->getActiveSheet()->setCellValue('C3', '付款時(shí)間');
$PHPExcel->getActiveSheet()->setCellValue('D3', '發(fā)貨時(shí)間');
$PHPExcel->getActiveSheet()->setCellValue('E3', '發(fā)貨單號(hào)');
$PHPExcel->getActiveSheet()->setCellValue('F3', '支付方式');
$PHPExcel->getActiveSheet()->setCellValue('G3', '配送方式');
$PHPExcel->getActiveSheet()->setCellValue('H3', '配送費(fèi)用');
$PHPExcel->getActiveSheet()->setCellValue('I3', '收件人');
$PHPExcel->getActiveSheet()->setCellValue('J3', '收貨地址');
$PHPExcel->getActiveSheet()->setCellValue('K3', '電話');
$PHPExcel->getActiveSheet()->setCellValue('L3', '手機(jī)');
$PHPExcel->getActiveSheet()->setCellValue('M3', '郵箱');
$PHPExcel->getActiveSheet()->setCellValue('N3', '貨號(hào)');
$PHPExcel->getActiveSheet()->setCellValue('O3', '商品名稱');
$PHPExcel->getActiveSheet()->setCellValue('P3', '屬 ');
$PHPExcel->getActiveSheet()->setCellValue('Q3', '價(jià)格');
$PHPExcel->getActiveSheet()->setCellValue('R3', '數(shù)量');
$PHPExcel->getActiveSheet()->setCellValue('S3', '小計(jì)');
$PHPExcel->getActiveSheet()->setCellValue('T3', '商品總金額');
$hang = 4;
foreach ($order_sn_list as $order_sn) {
/* 取得訂單信息 */
$order = order_info(0, $order_sn);
if (empty($order)) {
continue;
}
/* 根據(jù)訂單是否完成檢查權(quán)限 */
if (order_finished($order)) {
if (!admin_priv('order_view_finished', '', false)) {
continue;
}
} else {
if (!admin_priv('order_view', '', false)) {
continue;
}
}
/* 如果管理員屬于某個(gè)辦事處，檢查該訂單是否也屬于這個(gè)辦事處 */
$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'];
}
}
/* 取得區(qū)域名 */
$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'];
/* 此訂單的發(fā)貨備注(此訂單的最后一條操作記錄) */
$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) {
/* 取得語言項(xiàng) */
$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;
//輸出訂單的商品，由于可能一個(gè)人購(gòu)買多個(gè)商品，所以在這先輸出了
$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']." ");//加個(gè)空格，防止時(shí)間戳被轉(zhuǎn)換
$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;
}
//設(shè)置單元格邊框
$PHPExcel->getActiveSheet()->getStyle('A1:T'.$hang)->applyFromArray($styleArray);
//設(shè)置自動(dòng)換行
$PHPExcel->getActiveSheet()->getStyle('A4:T'.$hang)->getAlignment()->setWrapText(true);
//設(shè)置字體大小
$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: $urln");
exit;
}
/* 導(dǎo)出訂單功能結(jié)束 */

4，最后更新緩存，訂單管理效果
最后還是放上修改的2個(gè)文件，和PHPExcel提供下載，在里面搜索"導(dǎo)出訂單"就能找到增加的那部分了。
如果新手后臺(tái)無修改過可以直接覆蓋！ 附件：點(diǎn)擊下載 ECSHOP訂單批量導(dǎo)出插件