I’ve been struggling with Excel export lately, and I finally solved most of the problems. Let me share them here. Method 1: first download the PHPExcel plugin package http://download.csdn.net/detail/w15875510692/7747765 Below is the export code for the ECShop order detail page. So far I haven’t found a way to export images directly from the website’s storage (images given as hyperlink addresses) — I can only export images from the local site.. htm page code 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18<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 file code /*******************excel export***************/
1
2
3elseif (isset($\_POST\['export'\])) { /* assign shared information */ 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"); /*set title properties*/ //font size $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(16); //bold $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true); //column width $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(20); //vertical center $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setVertical(PHPExcel\_Style\_Alignment::VERTICAL\_CENTER); //horizontal center $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel\_Style\_Alignment::HORIZONTAL\_CENTER); /////////////////////// //font size $objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setSize(16); //bold $objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setBold(true); //column width $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(20); //vertical center $objPHPExcel->getActiveSheet()->getStyle('B1')->getAlignment()->setVertical(PHPExcel\_Style\_Alignment::VERTICAL\_CENTER); //horizontal center $objPHPExcel->getActiveSheet()->getStyle('B1')->getAlignment()->setHorizontal(PHPExcel\_Style\_Alignment::HORIZONTAL\_CENTER); //////////////////// //font size $objPHPExcel->getActiveSheet()->getStyle('C1')->getFont()->setSize(16); //bold $objPHPExcel->getActiveSheet()->getStyle('C1')->getFont()->setBold(true); //column width $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(30); //row height $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); //vertical center $objPHPExcel->getActiveSheet()->getStyle('C1')->getAlignment()->setVertical(PHPExcel\_Style\_Alignment::VERTICAL\_CENTER); //horizontal center $objPHPExcel->getActiveSheet()->getStyle('C1')->getAlignment()->setHorizontal(PHPExcel\_Style\_Alignment::HORIZONTAL\_CENTER); /////////////////////// //font size $objPHPExcel->getActiveSheet()->getStyle('D1')->getFont()->setSize(16); //bold $objPHPExcel->getActiveSheet()->getStyle('D1')->getFont()->setBold(true); //column width $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(30); //vertical center $objPHPExcel->getActiveSheet()->getStyle('D1')->getAlignment()->setVertical(PHPExcel\_Style\_Alignment::VERTICAL\_CENTER); //horizontal center $objPHPExcel->getActiveSheet()->getStyle('D1')->getAlignment()->setHorizontal(PHPExcel\_Style\_Alignment::HORIZONTAL\_CENTER); //////////////////// //font size $objPHPExcel->getActiveSheet()->getStyle('E1')->getFont()->setSize(16); //bold $objPHPExcel->getActiveSheet()->getStyle('E1')->getFont()->setBold(true); //column width $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(30); //vertical center $objPHPExcel->getActiveSheet()->getStyle('E1')->getAlignment()->setVertical(PHPExcel\_Style\_Alignment::VERTICAL\_CENTER); //horizontal center $objPHPExcel->getActiveSheet()->getStyle('E1')->getAlignment()->setHorizontal(PHPExcel\_Style\_Alignment::HORIZONTAL\_CENTER); //////////////////// //font size $objPHPExcel->getActiveSheet()->getStyle('F1')->getFont()->setSize(16); //bold $objPHPExcel->getActiveSheet()->getStyle('F1')->getFont()->setBold(true); //column width $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(80); //vertical center $objPHPExcel->getActiveSheet()->getStyle('F1')->getAlignment()->setVertical(PHPExcel\_Style\_Alignment::VERTICAL\_CENTER); //horizontal center $objPHPExcel->getActiveSheet()->getStyle('F1')->getAlignment()->setHorizontal(PHPExcel\_Style\_Alignment::HORIZONTAL\_CENTER); //////////////////// //font size $objPHPExcel->getActiveSheet()->getStyle('G1')->getFont()->setSize(16); //bold $objPHPExcel->getActiveSheet()->getStyle('G1')->getFont()->setBold(true); //column width $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(10); //vertical center $objPHPExcel->getActiveSheet()->getStyle('G1')->getAlignment()->setVertical(PHPExcel\_Style\_Alignment::VERTICAL\_CENTER); //horizontal center $objPHPExcel->getActiveSheet()->getStyle('G1')->getAlignment()->setHorizontal(PHPExcel\_Style\_Alignment::HORIZONTAL\_CENTER); //////////////////// //font size $objPHPExcel->getActiveSheet()->getStyle('H1')->getFont()->setSize(16); //bold $objPHPExcel->getActiveSheet()->getStyle('H1')->getFont()->setBold(true); //column width $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(10); //vertical center $objPHPExcel->getActiveSheet()->getStyle('H1')->getAlignment()->setVertical(PHPExcel\_Style\_Alignment::VERTICAL\_CENTER); //horizontal center $objPHPExcel->getActiveSheet()->getStyle('H1')->getAlignment()->setHorizontal(PHPExcel\_Style\_Alignment::HORIZONTAL\_CENTER); //////////////////// //font size $objPHPExcel->getActiveSheet()->getStyle('I1')->getFont()->setSize(16); //bold $objPHPExcel->getActiveSheet()->getStyle('I1')->getFont()->setBold(true); //column width $objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(10); //vertical center $objPHPExcel->getActiveSheet()->getStyle('I1')->getAlignment()->setVertical(PHPExcel\_Style\_Alignment::VERTICAL\_CENTER); //horizontal center $objPHPExcel->getActiveSheet()->getStyle('I1')->getAlignment()->setHorizontal(PHPExcel\_Style\_Alignment::HORIZONTAL\_CENTER); //////////////////// //font size $objPHPExcel->getActiveSheet()->getStyle('J1')->getFont()->setSize(16); //bold $objPHPExcel->getActiveSheet()->getStyle('J1')->getFont()->setBold(true); //column width $objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(15); //vertical center $objPHPExcel->getActiveSheet()->getStyle('J1')->getAlignment()->setVertical(PHPExcel\_Style\_Alignment::VERTICAL\_CENTER); //horizontal 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) { /* get order information */ $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');//photo height $objDrawing->setWidth('90px'); //photo width $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 spreadsheet————end————————————————- */ Method 2: this one is simpler — no need to export images, and there’s much less code1
<?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>'; } //download image function function grabImage($goods\_thumb,$filename=""){ if ($goods\_thumb == "") return false; if($filename == "") { $ext=strrchr($goods\_thumb,"."); //get the extension $ext\_arr = array(".gif",".png",".jpg",".bmp"); //check whether the extension is an image if (!in\_array($ext, $ext\_arr)) return false; //save the image file name as a timestamp $filename = time().$ext; } ob\_start(); //open the browser buffer readfile($goods\_thumb); //read the image into the buffer $img = ob\_get\_contents(); //get the buffer contents and copy them to the variable $img ob\_end_clean(); //close and clear the buffer $fp = @fopen($filename,"a"); //bind the file to the stream fwrite($fp,$img); //write to the file fclose($fp); //close the file handle return $filename; } ?>
