ECShop订单导出excel功能 可以关联搜索条件 导出国家地区等

作者: 日期: 2011年11月02日 发表评论 (0) 查看评论

订单导出Excel的功能是很实用，本文只是一个例子，可供新手参考。

步骤一：
修改原程序中的：admin/templates/order_list.htm文件

找到如下代码：

<a href="order.php?act=list&composite_status={$cs_await_ship}">{$lang.cs.$cs_await_ship}</a>

在后面增加一句代码：

<a href="javascript:;">导出excel</a>

在该页面尾部添加JS处理函数：

function order_to_excel()
{
	var order_sn = document.getElementById('order_sn').value;
	var c**ignee = document.getElementById('c**ignee').value;
	var status = document.getElementById('status').value;
	window.location.href='../plugins/order_to_excel/index.php?order_sn='+order_sn+'&c**ignee='+c**ignee+'&status='+status;
}

步骤二：
新建php文件：plugins/order_to_excel/index.php，写入下面的代码，注意区分GBK和UTF8版本。

GBK：

<?php
/**
 * ECSHOP 导出订单插件
 * ============================================================================
 * 作者: cmsok
 *   QQ: 307150302
 * 网站: http://www.cmsok.com
*/
define('IN_ECS', true);
require(dirname(__FILE__) . '/../../includes/init.php');
include_once(ROOT_PATH . 'languages/' .$GLOBALS['_CFG']['lang']. '/admin/order.php');
include_once(ROOT_PATH . 'includes/lib_order.php');

$order_sn = trim($_GET['order_sn']);
$consignee = trim($_GET['consignee']);
$status = intval($_GET['status']);

if (!empty($order_sn))
{
	$where .= " AND o.order_sn like '%$order_sn%'";
}
if (!empty($consignee))
{
	$where .= " AND o.consignee like '%$consignee%'";
}

switch($status)
{
	case CS_AWAIT_PAY :
		$where .= order_query_sql('await_pay','o.');
		break;

	case CS_AWAIT_SHIP :
		$where .= order_query_sql('await_ship','o.');
		break;

	case CS_FINISHED :
		$where .= order_query_sql('finished','o.');
		break;

	case PS_PAYING :
		if ($status != -1)
		{
			$where .= " AND o.pay_status = '$status' ";
		}
		break;

	default:
		if ($status != -1)
		{
			$where .= " AND o.order_status = '$status' ";
		}
}

$sql = "SELECT o.*, ( o.goods_amount + o.tax + o.shipping_fee + o.insure_fee + o.pay_fee + o.pack_fee + o.card_fee ) AS total_fee, ".
		"ra.region_name AS country_name, ".
		"rb.region_name AS province_name, ".
		"rc.region_name AS city_name, ".
		"rd.region_name AS district_name ".
		"FROM " .$GLOBALS['ecs']->table('order_info'). " AS o ".
		"LEFT JOIN " .$GLOBALS['ecs']->table('region'). " AS ra ON ra.region_id=o.country ".
		"LEFT JOIN " .$GLOBALS['ecs']->table('region'). " AS rb ON rb.region_id=o.province ".
		"LEFT JOIN " .$GLOBALS['ecs']->table('region'). " AS rc ON rc.region_id=o.city ".
		"LEFT JOIN " .$GLOBALS['ecs']->table('region'). " AS rd ON rd.region_id=o.district ".
		"WHERE 1 $where ORDER BY o.order_id DESC";

header("Content-type:application/vnd.ms-excel");
header("Accept-Ranges:bytes");
header("Content-Disposition:filename=".time().".xls");
header("Pragma: no-cache");

echo '
	<html xmlns:o="urn:schemas-microsoft-com:office:office"
	xmlns:x="urn:schemas-microsoft-com:office:excel"
	xmlns="http://www.w3.org/TR/REC-html40">
	<head>
	<meta http-equiv="expires" content="Mon, 06 Jan 1999 00:00:01 GMT">
	<meta http-equiv=Content-Type content="text/html; charset=gb2312">
	<!--[if gte mso 9]><xml>
	<x:ExcelWorkbook>
	<x:ExcelWorksheets>
	<x:ExcelWorksheet>
	<x:Name></x:Name>
	<x:WorksheetOptions>
	<x:DisplayGridlines/>
	</x:WorksheetOptions>
	</x:ExcelWorksheet>
	</x:ExcelWorksheets>
	</x:ExcelWorkbook>
	</xml><![endif]-->
	</head>
';

echo '<table>';
echo '<tr>';
echo '<td>订单号</td>';
echo '<td>下单时间</td>';
echo '<td>收货人</td>';
echo '<td>国家</td>';
echo '<td>省</td>';
echo '<td>市</td>';
echo '<td>区</td>';
echo '<td>地址</td>';
echo '<td>邮政编码</td>';
echo '<td>电话</td>';
echo '<td>手机</td>';
echo '<td>Email</td>';
echo '<td>总金额</td>';
echo '<td>应付金额</td>';
echo '<td>订单状态</td>';
echo '<td>付款状态</td>';
echo '<td>发货状态</td>';
echo '</tr>';

$res = $GLOBALS['db']->query($sql);
while ($row = $GLOBALS['db']->fetchRow($res))
{
	echo '<tr>';
	echo "<td style='vnd.ms-excel.numberformat:@'>$row[order_sn]</td>";
	echo "<td>".date("Y-m-d H:i:s",$row['add_time'])."</td>";
	echo "<td>$row[consignee]</td>";
	echo "<td>$row[country_name]</td>";
	echo "<td>$row[province_name]</td>";
	echo "<td>$row[city_name]</td>";
	echo "<td>$row[district_name]</td>";
	echo "<td>$row[address]</td>";
	echo "<td>$row[zipcode]</td>";
	echo "<td>$row[tel]</td>";
	echo "<td>$row[mobile]</td>";
	echo "<td>$row[email]</td>";
	echo "<td>$row[total_fee]</td>";
	echo "<td>$row[order_amount]</td>";
	echo "<td>".$_LANG['cs'][$row['order_status']]."</td>";
	echo "<td>".$_LANG['ps'][$row['pay_status']]."</td>";
	echo "<td>".$_LANG['ss'][$row['shipping_status']]."</td>";
	echo '</tr>';
}

echo '</table>';
?>

UTF-8：

<?php
/**
 * ECSHOP 导出订单插件
 * ============================================================================
 * 插件开发: http://www.cmsok.com
 * QQ: 307150302
*/
define('IN_ECS', true);
require(dirname(__FILE__) . '/../../includes/init.php');
include_once(ROOT_PATH . 'languages/' .$GLOBALS['_CFG']['lang']. '/admin/order.php');
include_once(ROOT_PATH . 'includes/lib_order.php');

$order_sn = trim($_GET['order_sn']);
$consignee = trim($_GET['consignee']);
$status = intval($_GET['status']);

if (!empty($order_sn))
{
	$where .= " AND o.order_sn like '%$order_sn%'";
}
if (!empty($consignee))
{
	$where .= " AND o.consignee like '%$consignee%'";
}

switch($status)
{
	case CS_AWAIT_PAY :
		$where .= order_query_sql('await_pay','o.');
		break;

	case CS_AWAIT_SHIP :
		$where .= order_query_sql('await_ship','o.');
		break;

	case CS_FINISHED :
		$where .= order_query_sql('finished','o.');
		break;

	case PS_PAYING :
		if ($status != -1)
		{
			$where .= " AND o.pay_status = '$status' ";
		}
		break;

	default:
		if ($status != -1)
		{
			$where .= " AND o.order_status = '$status' ";
		}
}

$sql = "SELECT o.*, ( o.goods_amount + o.tax + o.shipping_fee + o.insure_fee + o.pay_fee + o.pack_fee + o.card_fee ) AS total_fee, ".
		"ra.region_name AS country_name, ".
		"rb.region_name AS province_name, ".
		"rc.region_name AS city_name, ".
		"rd.region_name AS district_name ".
		"FROM " .$GLOBALS['ecs']->table('order_info'). " AS o ".
		"LEFT JOIN " .$GLOBALS['ecs']->table('region'). " AS ra ON ra.region_id=o.country ".
		"LEFT JOIN " .$GLOBALS['ecs']->table('region'). " AS rb ON rb.region_id=o.province ".
		"LEFT JOIN " .$GLOBALS['ecs']->table('region'). " AS rc ON rc.region_id=o.city ".
		"LEFT JOIN " .$GLOBALS['ecs']->table('region'). " AS rd ON rd.region_id=o.district ".
		"WHERE 1 $where ORDER BY o.order_id DESC";

header("Content-type:application/vnd.ms-excel");
header("Accept-Ranges:bytes");
header("Content-Disposition:filename=".time().".xls");
header("Pragma: no-cache");

echo '
	<html xmlns:o="urn:schemas-microsoft-com:office:office"
	xmlns:x="urn:schemas-microsoft-com:office:excel"
	xmlns="http://www.w3.org/TR/REC-html40">
	<head>
	<meta http-equiv="expires" content="Mon, 06 Jan 1999 00:00:01 GMT">
	<meta http-equiv=Content-Type content="text/html; charset=gb2312">
	<!--[if gte mso 9]><xml>
	<x:ExcelWorkbook>
	<x:ExcelWorksheets>
	<x:ExcelWorksheet>
	<x:Name></x:Name>
	<x:WorksheetOptions>
	<x:DisplayGridlines/>
	</x:WorksheetOptions>
	</x:ExcelWorksheet>
	</x:ExcelWorksheets>
	</x:ExcelWorkbook>
	</xml><![endif]-->
	</head>
';

echo '<table>';
echo '<tr>';
echo '<td>'.gstr('订单号').'</td>';
echo '<td>'.gstr('下单时间').'</td>';
echo '<td>'.gstr('收货人').'</td>';
echo '<td>'.gstr('国家').'</td>';
echo '<td>'.gstr('省').'</td>';
echo '<td>'.gstr('市').'</td>';
echo '<td>'.gstr('区').'</td>';
echo '<td>'.gstr('地址').'</td>';
echo '<td>'.gstr('邮政编码').'</td>';
echo '<td>'.gstr('电话').'</td>';
echo '<td>'.gstr('手机').'</td>';
echo '<td>'.gstr('Email').'</td>';
echo '<td>'.gstr('总金额').'</td>';
echo '<td>'.gstr('应付金额').'</td>';
echo '<td>'.gstr('订单状态').'</td>';
echo '<td>'.gstr('付款状态').'</td>';
echo '<td>'.gstr('发货状态').'</td>';
echo '</tr>';

$res = $GLOBALS['db']->query($sql);
while ($row = $GLOBALS['db']->fetchRow($res))
{
	echo '<tr>';
	echo "<td style='vnd.ms-excel.numberformat:@'>$row[order_sn]</td>";
	echo "<td>".date("Y-m-d H:i:s",$row['add_time'])."</td>";
	echo "<td>".gstr($row['consignee'])."</td>";
	echo "<td>".gstr($row['country_name'])."</td>";
	echo "<td>".gstr($row['province_name'])."</td>";
	echo "<td>".gstr($row['city_name'])."</td>";
	echo "<td>".gstr($row['district_name'])."</td>";
	echo "<td>".gstr($row['address'])."</td>";
	echo "<td>$row[zipcode]</td>";
	echo "<td>".gstr($row['tel'])."</td>";
	echo "<td>".gstr($row['mobile'])."</td>";
	echo "<td>".gstr($row['email'])."</td>";
	echo "<td>$row[total_fee]</td>";
	echo "<td>$row[order_amount]</td>";
	echo "<td>".gstr($_LANG['cs'][$row['order_status']])."</td>";
	echo "<td>".gstr($_LANG['ps'][$row['pay_status']])."</td>";
	echo "<td>".gstr($_LANG['ss'][$row['shipping_status']])."</td>";
	echo '</tr>';
}

echo '</table>';

function gstr($str)
{
	return iconv('UTF-8', 'GB2312', $str);
}
?>
ECShop

发表评论


注意 - 你可以用以下 HTML tags and attributes:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>