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

2014-10-29

字数统计: 3k字 | 阅读时长: 17分 | 本文总阅读量0次

最近被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导出*************/

1
2
3

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
本文链接： https://erik.xyz/2014/10/29/excel-biao-ge-dao-chu-ecshop-ding-dan-xiang-qing-ye-dao-chu/
版权声明： 本作品采用知识共享署名-非商业性使用-相同方式共享 4.0 国际许可协议进行许可。转载请注明出处！