菜单

excel表格导出,ecshop订单详情页导出。

2014-10-29 - Developer

最近被excel 导出困扰了,终于解决了大部分为题,在这里分享一下

方法1

首先下载PHPexcel插件包

http://download.csdn.net/detail/w15875510692/7747765

以下是ecshop订单详细页的导出代码

目前没找到直接能到处网站空间的图片(超链接地址的图片),只能到处本地网站的图片。。

htm页面代码

<script language=”JavaScript”>
function check()
{
var snArray = new Array();
var eles = document.forms[‘listForm’].elements;
for (var i=0; i<eles.length; i++)
{
if (eles[i].tagName == ‘INPUT’ && eles[i].type == ‘hidden’ && eles[i].checked && eles[i].value != ‘on’)
{
snArray.push(eles[i].value);
}
}
if (snArray.length == 0)
{
return false;
}
else
{
eles[‘order_id’].value = snArray.toString();
return true;
}
}
</script>
<form action=”order.php?act=operate” method=”post” name=”listForm”  onsubmit=”return check()”>
<div class=”div_excel”>
<input type=”hidden” name=”checkboxes”checked=”checked”  value=”{$order.order_sn}” />
<input name=”export” type=”submit” id=”btnSubmit5″ value=”导出” class=”button”   onclick=”this.form.target = ‘_blank'” onclick=”window.open(‘order.php?act=operate’)” onsubmit=”return check()”/>
<input name=”order_id” type=”hidden” value=”{$order.order_sn}” />
</div>
</form>

.php文件代码

/*******************excel导出*************/

elseif (isset($_POST[‘export’]))
{

/* 赋值公用信息 */
require_once ROOT_PATH.’salesmans/Classes/PHPExcel.php’;
require_once ROOT_PATH.’salesmans/Classes/PHPExcel/IOFactory.php’;
// Create new PHPExcel object
$objPHPExcel = new PHPExcel();
// Set properties
$objPHPExcel->getProperties()->setCreator(“DizzyLion”)
->setLastModifiedBy(“DizzyLion”)
->setSubject(“Orders output Document”);
$filepaths= ROOT_PATH.”data/”.date(“Y-m”).’.xls’;
$filename = date(“Y-m”).’.xls’;
$rowix = 1;

error_reporting(E_ALL);

date_default_timezone_set(‘Europe/London’);

$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(20);
//垂直居中
$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(30);

//表格高度
$objPHPExcel->getActiveSheet()->getRowDimension(‘2’)->setRowHeight(75);
$objPHPExcel->getActiveSheet()->getRowDimension(‘3’)->setRowHeight(75);
$objPHPExcel->getActiveSheet()->getRowDimension(‘4’)->setRowHeight(75);
$objPHPExcel->getActiveSheet()->getRowDimension(‘5’)->setRowHeight(75);
$objPHPExcel->getActiveSheet()->getRowDimension(‘6’)->setRowHeight(75);
$objPHPExcel->getActiveSheet()->getRowDimension(‘7’)->setRowHeight(75);
$objPHPExcel->getActiveSheet()->getRowDimension(‘8’)->setRowHeight(75);

//垂直居中
$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(30);
//垂直居中
$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(80);
//垂直居中
$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(10);
//垂直居中
$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);
////////////////////

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’, ‘应付金额’);
$rowix++;
$i=0;
$list = array();
//         $order_id = intval($_REQUEST[‘order_id’]);
$order_sn_list = explode(‘,’, $_POST[‘order_id’]);

foreach ($order_sn_list as $order_sn)
{
/* 取得订单信息 */

$sql = “select * from “.$GLOBALS[‘ecs’]->table(‘order_info’).” o  join “.$GLOBALS[‘ecs’]->table(‘users’).” u on o.user_id =u.user_id where order_sn=’$order_sn'”;
$row = $db->getRow($sql);
$goods= $db->getAll(“select goods_name,goods_id,goods_number,goods_price,goods_price,goods_attr_id,goods_attr *goods_number as je,goods_sn from “.$GLOBALS[‘ecs’]->table(‘order_goods’).” where order_id = ‘”.$row[‘order_id’].”‘”);
if($goods){
foreach($goods as $k=>$v){
$temp    = array();
$v[‘img’] = $db->getOne(“select goods_thumb from “.$GLOBALS[‘ecs’]->table(‘goods’).” where goods_id='”.$v[‘goods_id’].”‘”);
$temp[‘goods’] = $v;
$temp[‘order_info’] = $row;
$list[$row[‘order_id’]][] = $temp;
}
}
}

foreach ($list as $key => $t) {
foreach($t as $k1=>$value){

$objPHPExcel->setActiveSheetIndex(0)
->setCellValue(‘B’.$rowix ,  $value[‘order_info’][‘order_sn’])
->setCellValue(‘D’.$rowix ,  $value[‘goods’][‘img’])
->setCellValue(‘D’.$rowix ,  $value[‘goods’][‘goods_sn’])
->setCellValue(‘E’.$rowix ,  $value[‘goods’][‘goods_name’])
->setCellValue(‘F’.$rowix ,  $value[‘goods’][‘goods_attr_id’])
->setCellValue(‘G’.$rowix ,  $value[‘goods’][‘goods_number’])
->setCellValue(‘H’.$rowix ,  $value[‘goods’][‘goods_price’])
->setCellValue(‘A’.$rowix ,  local_date(“Y-m-d”,$value[‘order_info’][‘add_time’]))
->setCellValue(‘I’.$rowix ,  $value[‘order_info’][‘goods_amount’])
->setCellValue(‘J’.$rowix ,  $value[‘order_info’][‘order_amount’]);

$objDrawing = new PHPExcel_Worksheet_Drawing();
$objDrawing->setName(‘ZealImg’);
$objDrawing->setDescription(‘Image’);
$objDrawing->setPath(‘../images/img_excel/img10.jpg’);
$objDrawing->setHeight(’92px’);//照片高度
$objDrawing->setWidth(’90px’); //照片宽度
$objDrawing->setCoordinates(‘C’.$rowix);
$objDrawing->setWorksheet($objPHPExcel->getActiveSheet());

$rowix++;
}

}
$objPHPExcel->setActiveSheetIndex(0);
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, ‘Excel5’);
$objWriter->setTempDir(ROOT_PATH.”data”);

$objWriter->save($filepaths);

header(“Location:../data/”.$filename);
exit;

}

/*———————excel表格——–end——————————— */

方法2

这种就比较简单,不需要导出图片代码也很少

<?php
define(‘IN_ECS’, true);
include (‘includes/init.php’);
$filename=’订单’;
header(“Content-type: application/vnd.ms-excel; charset=gbk”);
header(“Content-Disposition: attachment; filename=$filename.xls”);
$order_id = intval($_REQUEST[‘order_id’]);
// $sql = “select a.user_id from” .$GLOBALS[‘ecs’]->table(‘users’).” a join (select p.order_id, o.order_sn, o.user_id from ” . $GLOBALS[‘ecs’]->table(‘order_goods’) . ” p join” . $GLOBALS[‘ecs’]->table(‘order_info’) .” o on p.order_id=o.order_id where user_id in”.
//         “(select user_id from” .$GLOBALS[‘ecs’]->table(‘users’). ” a join “.$GLOBALS[‘ecs’]->table(‘ex_user’).” b on a.s_user=b.username “.
//         “where 1=1 )) b on a.user_id=b.user_id”;
//$sql=”select * from ” . $GLOBALS[‘ecs’]->table(‘order_info’) .” g join (select  o.*, g.goods_number   AS storage,  IFNULL(b.brand_name, ”) AS brand_name ” .” from ” . $GLOBALS[‘ecs’]->table(‘order_goods’) . ” AS o ” . “LEFT JOIN ” . $GLOBALS[‘ecs’]->table(‘goods’) . ” AS g ON o.goods_id = g.goods_id ” . “LEFT JOIN ” . $GLOBALS[‘ecs’]->table(‘brand’) . ” AS b ON g.brand_id = b.brand_id ” . “WHERE o.order_id = ‘$order_id’ )  p on g.order_id=p.order_id”;
$sql=”select *, (g.goods_price*g.goods_number) as num_price, g.goods_attr from ” . $GLOBALS[‘ecs’]->table(‘goods’) .” gt, ” . $GLOBALS[‘ecs’]->table(‘order_info’) .” i, ” . $GLOBALS[‘ecs’]->table(‘order_goods’) .” g where g.order_id=i.order_id and gt.goods_id = g.goods_id and i.order_id = ‘$order_id'”;

$res=$db->getAll($sql);
//print_r($res);die;

$data.=’订单号’.”\t”;
$data.=’商品图’.”\t”;
$data.=’商品名称’.”\t”;
$data.=’商品编号’.”\t”;
$data.=’商品单价’.”\t”;
$data.=’商品数量’.”\t”;
$data.=’合计价格’.”\t”;
$data.=’应付金额’.”\t”;
$data.=’属性备注’.”\t\n”;

while (!empty($res))
{
foreach ($res as $key=>$var)
{
$a=$var[‘goods_id’];
foreach ($res as $keys=>$vars)
{
if($a==$vars[‘goods_id’])
{
$res1[0][‘order_sn’]=$vars[‘order_sn’];
$res1[$key][‘goods_thumb’]=$vars[‘goods_thumb’];
$res1[$key][‘goods_name’]=$vars[‘goods_name’];
$res1[$key][‘goods_sn’]=$vars[‘goods_sn’];
$res1[$key][‘goods_price’]=$vars[‘goods_price’];
$res1[$key][‘goods_number’]=$vars[‘goods_number’];
$res1[0][‘goods_amount’]=$vars[‘goods_amount’];
$res1[0][‘order_amount’]=$vars[‘order_amount’];
$res1[$key][‘goods_attr_id’]=$vars[‘goods_attr_id’];
unset ($res[$keys]);
}
}
}
}
foreach ($res1 as $key =>$var)
{
$data.=$var[‘order_sn’].”\t”;
$data.=$var[‘goods_thumb’].”\t”;
$data.=$var[‘goods_name’].”\t”;
$data.=$var[‘goods_sn’].”\t”;
$data.=$var[‘goods_price’].”\t”;
$data.=$var[‘goods_number’].”\t”;
$data.=$var[‘goods_amount’].”\t”;
$data.=$var[‘order_amount’].”\t”;
$data.=$var[‘goods_attr_id’].”\t”;
$data.=”\t\n”;
}
if (EC_CHARSET != ‘gbk’)
{
echo $data.ecs_iconv(EC_CHARSET, ‘gbk’, $data) . “\t”;
}
else
{
echo $data.$data. “\t”;
}
?>

if (!empty(    $order_sn_list)){
$goods_thumb = $_POST[‘goods_thumb’];
$img = grabImage($goods_thumb,””);
echo $img;
echo ‘<pre><img src=”‘.$img.'”></pre>’;
}
//下载图片函数
function grabImage($goods_thumb,$filename=””){
if ($goods_thumb == “”) return false;
if($filename == “”) {
$ext=strrchr($goods_thumb,”.”); //获取扩展名
$ext_arr = array(“.gif”,”.png”,”.jpg”,”.bmp”);
//判断扩展名是否为图片
if (!in_array($ext, $ext_arr)) return false;
//将图片文件名保存为时间戳
$filename = time().$ext;
}
ob_start(); //打开浏览器的缓冲区
readfile($goods_thumb); //将图片读入缓冲区
$img = ob_get_contents(); //获取缓冲区的内容复制给变量$img
ob_end_clean(); //关闭并清空缓冲
$fp = @fopen($filename,”a”); //将文件绑定到流
fwrite($fp,$img); //写入文件
fclose($fp); //关闭文件之争
return $filename;
}

?>

转载请注明: 转载自—艾瑞可erik

本文链接地址: http://erik.xyz/233.html

标签:,

发表评论