ecshop模板问答ecshop二次开发问答ecshop分享ecshop模板文档

ecshop订单批量导出到excel表方法

2016-09-07 22:02 来源：www.chinab4c.com 作者：ecshop专家

ecshop订单批量导出是通过PHPExcel类实现方法，效果见上图：

第一步，将PHPExcel类包解压到后台目录。

第二步，页面上部署操作按钮：打开admin/templates/order_list.htm文件，在“打印订单”按钮后面（约第73行）加上

第三步，部署代码：打开admin/order.php文件，在适当位置加上如下代码，（代码就不再一行行解析了，都有注释）

/* 批量导出订单 */ 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']);

//////////////////////////

error_reporting(E_ALL);

date_default_timezone_set('Europe/London');

require_once dirname(__FILE__) . '/Classes/PHPExcel.php';

echo date('H:i:s') . " Create new PHPExcel object\\n";

$objPHPExcel = new PHPExcel();

echo date('H:i:s') . " Set properties\\n";

$objPHPExcel->getProperties()->setCreator("wdz")->setLastModifiedBy("wdz")->setTitle("我的订单")->setSubject("我的订单")->setDescription(date('Y/m/d H:i:s') . "导出的订单")->setKeywords("我的订单")->setCategory("Test result file");

/*设置标题属性*/

//字体大小

$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(16);

//加粗

$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);

//表格宽度

$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(20);

//垂直居中

$objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

//水平居中

$objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

///////////////////////

//字体大小

$objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setSize(16);

//加粗

$objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setBold(true);

//表格宽度

$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(10);

//垂直居中

$objPHPExcel->getActiveSheet()->getStyle('B1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

//水平居中

$objPHPExcel->getActiveSheet()->getStyle('B1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

////////////////////

//字体大小

$objPHPExcel->getActiveSheet()->getStyle('C1')->getFont()->setSize(16);

//加粗

$objPHPExcel->getActiveSheet()->getStyle('C1')->getFont()->setBold(true);

//表格宽度

$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(40);

//垂直居中

$objPHPExcel->getActiveSheet()->getStyle('C1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

//水平居中

$objPHPExcel->getActiveSheet()->getStyle('C1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

///////////////////////

//字体大小

$objPHPExcel->getActiveSheet()->getStyle('D1')->getFont()->setSize(16);

//加粗

$objPHPExcel->getActiveSheet()->getStyle('D1')->getFont()->setBold(true);

//表格宽度

$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(30);

//垂直居中

$objPHPExcel->getActiveSheet()->getStyle('D1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

//水平居中

$objPHPExcel->getActiveSheet()->getStyle('D1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

////////////////////

//字体大小

$objPHPExcel->getActiveSheet()->getStyle('E1')->getFont()->setSize(16);

//加粗

$objPHPExcel->getActiveSheet()->getStyle('E1')->getFont()->setBold(true);

//表格宽度

$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(10);

//垂直居中

$objPHPExcel->getActiveSheet()->getStyle('E1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

//水平居中

$objPHPExcel->getActiveSheet()->getStyle('E1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

////////////////////

//字体大小

$objPHPExcel->getActiveSheet()->getStyle('F1')->getFont()->setSize(16);

//加粗

$objPHPExcel->getActiveSheet()->getStyle('F1')->getFont()->setBold(true);

//表格宽度

$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(10);

//垂直居中

$objPHPExcel->getActiveSheet()->getStyle('F1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

//水平居中

$objPHPExcel->getActiveSheet()->getStyle('F1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

////////////////////

//字体大小

$objPHPExcel->getActiveSheet()->getStyle('G1')->getFont()->setSize(16);

//加粗

$objPHPExcel->getActiveSheet()->getStyle('G1')->getFont()->setBold(true);

//表格宽度

$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(10);

//垂直居中

$objPHPExcel->getActiveSheet()->getStyle('G1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

//水平居中

$objPHPExcel->getActiveSheet()->getStyle('G1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

////////////////////

//字体大小

$objPHPExcel->getActiveSheet()->getStyle('H1')->getFont()->setSize(16);

//加粗

$objPHPExcel->getActiveSheet()->getStyle('H1')->getFont()->setBold(true);

//表格宽度

$objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(10);

//垂直居中

$objPHPExcel->getActiveSheet()->getStyle('H1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

//水平居中

$objPHPExcel->getActiveSheet()->getStyle('H1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

////////////////////

//字体大小

$objPHPExcel->getActiveSheet()->getStyle('I1')->getFont()->setSize(16);

//加粗

$objPHPExcel->getActiveSheet()->getStyle('I1')->getFont()->setBold(true);

//表格宽度

$objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(40);

//垂直居中

$objPHPExcel->getActiveSheet()->getStyle('I1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

//水平居中

$objPHPExcel->getActiveSheet()->getStyle('I1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

////////////////////

//字体大小

$objPHPExcel->getActiveSheet()->getStyle('J1')->getFont()->setSize(16);

//加粗

$objPHPExcel->getActiveSheet()->getStyle('J1')->getFont()->setBold(true);

//表格宽度

$objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(15);

//垂直居中

$objPHPExcel->getActiveSheet()->getStyle('J1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

//水平居中

$objPHPExcel->getActiveSheet()->getStyle('J1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

////////////////////

//字体大小

$objPHPExcel->getActiveSheet()->getStyle('K1')->getFont()->setSize(16);

//加粗

$objPHPExcel->getActiveSheet()->getStyle('K1')->getFont()->setBold(true);

//表格宽度

$objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(15);

//垂直居中

$objPHPExcel->getActiveSheet()->getStyle('K1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

//水平居中

$objPHPExcel->getActiveSheet()->getStyle('K1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

////////////////////

//字体大小

$objPHPExcel->getActiveSheet()->getStyle('L1')->getFont()->setSize(16);

//加粗

$objPHPExcel->getActiveSheet()->getStyle('L1')->getFont()->setBold(true);

//表格宽度

$objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(15);

//垂直居中

$objPHPExcel->getActiveSheet()->getStyle('L1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

//水平居中

$objPHPExcel->getActiveSheet()->getStyle('L1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

////////////////////

echo date('H:i:s') . " Add some data\\n";

$objPHPExcel->setActiveSheetIndex(0);

$objPHPExcel->getActiveSheet()->setCellValue('A1', '订货日期');

$objPHPExcel->getActiveSheet()->setCellValue('B1', '款号');

$objPHPExcel->getActiveSheet()->setCellValue('C1', '商品名称');

$objPHPExcel->getActiveSheet()->setCellValue('D1', '属性');

$objPHPExcel->getActiveSheet()->setCellValue('E1', '数量');

$objPHPExcel->getActiveSheet()->setCellValue('F1', '价格');

$objPHPExcel->getActiveSheet()->setCellValue('G1', '收件人');

$objPHPExcel->getActiveSheet()->setCellValue('H1', '地址');

$objPHPExcel->getActiveSheet()->setCellValue('I1', '电话');

$objPHPExcel->getActiveSheet()->setCellValue('J1', '邮箱');

$objPHPExcel->getActiveSheet()->setCellValue('K1', '发货日期');

$hang = 2;

///////////////////////

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);

/* 参数赋值：订单 */

$smarty->assign('order', $order);

$shuliang = 0;

/* 取得订单商品 */

$goods_list = array();

$goods_attr = array();

$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']);

}

$objPHPExcel->getActiveSheet()->setCellValue('B' . $chanpin, $row['goods_sn']);

$objPHPExcel->getActiveSheet()->setCellValue('C' . $chanpin, $row['goods_name']);

$objPHPExcel->getActiveSheet()->setCellValue('D' . $chanpin, $row['goods_attr']);

$objPHPExcel->getActiveSheet()->setCellValue('E' . $chanpin, $row['goods_number']);

$objPHPExcel->getActiveSheet()->setCellValue('F' . $chanpin, $row['goods_price']);

$row['formated_subtotal'] = price_format($row['goods_price'] * $row['goods_number']);

$row['formated_goods_price'] = price_format($row['goods_price']);

$goods_attr[] = explode(' ', trim($row['goods_attr'])); //将商品属性拆分为一个数组

$goods_list[] = $row;

$chanpin = $chanpin + 1;

}

$attr = array();

$arr = array();

foreach ($goods_attr AS $index => $array_val) {

foreach ($array_val AS $value) {

$arr = explode(':', $value); //以 : 号将属性拆开

$attr[$index][] = @array(

'name' => $arr[0],

'value' => $arr[1]

);

}

$smarty->assign('goods_attr', $attr);

$smarty->assign('goods_list', $goods_list);

$smarty->template_dir = '../' . DATA_DIR;

for ($kk = $hang; $kk < ($hang + $shuliang); $kk++) {

$objPHPExcel->getActiveSheet()->mergeCells('A' . $hang . ':A' . $kk);

$objPHPExcel->getActiveSheet()->mergeCells('H' . $hang . ':H' . $kk);

$objPHPExcel->getActiveSheet()->mergeCells('I' . $hang . ':I' . $kk);

$objPHPExcel->getActiveSheet()->mergeCells('J' . $hang . ':J' . $kk);

$objPHPExcel->getActiveSheet()->mergeCells('K' . $hang . ':K' . $kk);

$objPHPExcel->getActiveSheet()->mergeCells('G' . $hang . ':G' . $kk);

$objPHPExcel->getActiveSheet()->getStyle('A' . $hang . ':A' . $kk)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

$objPHPExcel->getActiveSheet()->getStyle('A' . $hang . ':A' . $kk)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

$objPHPExcel->getActiveSheet()->getStyle('H' . $hang . ':H' . $kk)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

$objPHPExcel->getActiveSheet()->getStyle('H' . $hang . ':H' . $kk)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

$objPHPExcel->getActiveSheet()->getStyle('I' . $hang . ':I' . $kk)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

$objPHPExcel->getActiveSheet()->getStyle('I' . $hang . ':I' . $kk)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

$objPHPExcel->getActiveSheet()->getStyle('J' . $hang . ':J' . $kk)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

$objPHPExcel->getActiveSheet()->getStyle('J' . $hang . ':J' . $kk)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

$objPHPExcel->getActiveSheet()->getStyle('K' . $hang . ':K' . $kk)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

$objPHPExcel->getActiveSheet()->getStyle('K' . $hang . ':K' . $kk)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

$objPHPExcel->getActiveSheet()->getStyle('G' . $hang . ':G' . $kk)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

$objPHPExcel->getActiveSheet()->getStyle('G' . $hang . ':G' . $kk)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

}

$objPHPExcel->getActiveSheet()->setCellValue('A' . ($hang), $order['order_time']);

$objPHPExcel->getActiveSheet()->setCellValue('G' . ($hang), $order['consignee']);

$objPHPExcel->getActiveSheet()->setCellValue('H' . ($hang), $order['address']);

$objPHPExcel->getActiveSheet()->setCellValue('I' . ($hang), $order['tel']);

$objPHPExcel->getActiveSheet()->setCellValue('J' . ($hang), $order['email']);

$objPHPExcel->getActiveSheet()->setCellValue('K' . ($hang), $order['shipping_time']);

$hang = $hang + $shuliang;

}

$objPHPExcel->getActiveSheet()->setTitle(("我的订单"));

$objPHPExcel->setActiveSheetIndex(0);

require_once 'Classes/PHPExcel/IOFactory.php';

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');

$objWriter->save(str_replace('.php', '.xls', __FILE__));

$url = "order.xls";

ecs_header("Location: $url\\n");

exit;

}

最后运行测试吧！

还可以有更多的拓展，如：表格样式，插入产品图片等等，这个类包几乎可以实现所有手工制作excel的效果。

(责任编辑：chinab4c)

相关标签：

常用插件

ecshop2.7.1邮件发送插件
ecshop2.7.1邮件发送插件:该插件主要的开发思想是源于ecshop短信发送系统...
ecshop2.7.2生成虚拟订单2.
以前我们开发过ecshop下的虚拟订单，就是客户在访问的时候，会自动生...
ecshop最小购买数量控制插
ecshop最小购买数量控制插件，这个插件主要是为我们提供一个十分方便...
ecshop二次开发商品购买增
图片1香...
ecshop没登陆情况下订单查
ecshop没登陆情况下订单查询插件，主要是针对ecshop在没有登陆的情况下...

ecshop热门问答

ecshop字体下载 ecshop笔记本 ecshop横版 ecshop降序 ecshop物流公司 ecshop勾字 ecshop手机评测 ecshop罪恶都市 ecshop语言学习 ecshop店长 ecshop者神 ecshop最低价 ecshop浪差 ecshop量子 ecshop摊位 ecshop燒包 ecshop魔法师 kingshard kingshard插件 weedfs ecshop小京东小京东bug 小京东购物车 ec助理数据导入导出网站 ecshop跳转 sqlserver 商城求解！！

ecshop热门资料

ecshop合唱 ecshopECSHOP2。62 ecshop状况 ecshopmvpdata ecshoptry ecshop价格区间 ecshop用于 ecshop咖啡 ecshop全站 ecshop友情 ecshop手机模版 ecshop首页文章分类 ecshopLOGO上传 ecmallconfget ecshop中国梦 ecshop码字 ecshop分类乱了 ecshop简体字 ecshop户外家具网站美工 ecshop史蒂芬 ecshopadwords nginxbeego ecshop专题功能 ecshop郁闷 ecshop温度 ecshop游览 ecshopagent ecshop静态缓存 eschop烧包模板 ecshop大小

ecshop订单批量导出到excel表方法

最近更新

常用插件

ecshop热门问答

ecshop热门资料