PHP 导出Excel-php手册-php.cn

PHP 导出Excel

WBOY

Release： 2016-06-13 09:26:13

Original

1513 people have browsed it

PHP 导出Excel

在开发一个商城网站的时候，客户需要一个导出报表的功能，我使用PHPExcel来实现这个功能。

下载PHPExcel_1.8.0_doc.zip，将解压后的文件夹里的Classes上传到网站的根目录下，Classes目录内的内容如下：

导出文件Excel.php

require_once dirname(__FILE__).&#39;/Classes/PHPExcel.php&#39;;//引入PHPExcel
.....此处略去从数据库获取数据的过程，$a为需要导出的数组......
$numArr = array(&#39;A&#39;,&#39;B&#39;,&#39;C&#39;,&#39;D&#39;,&#39;E&#39;,&#39;F&#39;,&#39;G&#39;,&#39;H&#39;,&#39;I&#39;,&#39;J&#39;,&#39;K&#39;,&#39;L&#39;,&#39;M&#39;,&#39;N&#39;,&#39;O&#39;,&#39;P&#39;,&#39;Q&#39;,&#39;R&#39;,&#39;S&#39;,&#39;T&#39;,&#39;U&#39;,&#39;V&#39;,&#39;W&#39;,&#39;X&#39;,&#39;Y&#39;,&#39;Z&#39;,&#39;AA&#39;,&#39;AB&#39;,&#39;AC&#39;,&#39;AD&#39;,&#39;AE&#39;,&#39;AF&#39;,&#39;AG&#39;,&#39;AH&#39;,&#39;AI&#39;,&#39;AJ&#39;,&#39;AK&#39;,&#39;AL&#39;,&#39;AM&#39;,&#39;AN&#39;,&#39;AO&#39;,&#39;AP&#39;,&#39;AQ&#39;,&#39;AR&#39;,&#39;AS&#39;,&#39;AT&#39;,&#39;AU&#39;,&#39;AV&#39;,&#39;AW&#39;,&#39;AX&#39;,&#39;AY&#39;,&#39;AZ&#39;);
// Create new PHPExcel object
$objPHPExcel = new PHPExcel();

$arr = array(&#39;订单号&#39;,&#39;下单时间&#39;,&#39;城市&#39;,&#39;地区&#39;, &#39;客户名称&#39;, &#39;收货人&#39;, &#39;联系电话&#39;, &#39;收货地址&#39;, &#39;ERP客户名称&#39;, &#39;物流系统客户名称&#39;, &#39;活动项目&#39;,
 &#39;品牌&#39;, &#39;型号&#39;, &#39;颜色&#39;, &#39;物流系统型号&#39;, &#39;订货量&#39;, &#39;单价&#39;, &#39;代收货款&#39;, &#39;红包&#39;, &#39;价保返利&#39;, &#39;运费&#39;, &#39;实收金额&#39;, &#39;付款方式&#39;, &#39;订单来源&#39;,
 &#39;上游厂商&#39;, &#39;是否在仓&#39;, &#39;快递面单号&#39;, &#39;订单状态&#39;, &#39;确认时间&#39;, &#39;末次状态确认时间&#39;, &#39;描述&#39;, &#39;对应业务&#39;, &#39;对应客服&#39;, &#39;商家留言&#39;, &#39;下单摘要&#39;,
 &#39;业务员&#39;, &#39;联系方式&#39;);

Copy after login

// 输出标题
echo date(&#39;H:i:s&#39;) , " Add some data" , EOL;
//设置换行
$objPHPExcel->getActiveSheet()->getStyle(&#39;H&#39;)->getAlignment()->setWrapText(true);
$objPHPExcel->getActiveSheet()->getStyle(&#39;Y&#39;)->getAlignment()->setWrapText(true);
//设置相应列的宽度
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension(&#39;A&#39;)->setWidth(15);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension(&#39;B&#39;)->setWidth(20);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension(&#39;G&#39;)->setWidth(15);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension(&#39;H&#39;)->setWidth(40);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension(&#39;I&#39;)->setWidth(15);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension(&#39;J&#39;)->setWidth(15);
//输出第一行
$objPHPExcel->setActiveSheetIndex(0)
	    ->setCellValue(&#39;A1&#39;, $arr[0])
            ->setCellValue(&#39;B1&#39;, $arr[1])
            ->setCellValue(&#39;C1&#39;, $arr[2])
            ->setCellValue(&#39;D1&#39;, $arr[3])
            ->setCellValue(&#39;E1&#39;, $arr[4])
            ->setCellValue(&#39;F1&#39;, $arr[5])
            ->setCellValue(&#39;G1&#39;, $arr[6])
            ->setCellValue(&#39;H1&#39;, $arr[7])
            ->setCellValue(&#39;I1&#39;, $arr[8])
            ->setCellValue(&#39;J1&#39;, $arr[9])
	    ->setCellValue(&#39;K1&#39;, $arr[10])
	    ->setCellValue(&#39;L1&#39;, $arr[11]);


//输出内容
for($i=0;$i<count($a);$i++){
	$objPHPExcel->setActiveSheetIndex(0)
		->setCellValue($numArr[0].($i+2), $a[$i][&#39;order_sn&#39;])
		->setCellValue($numArr[1].($i+2), $a[$i][&#39;add_time&#39;])
		->setCellValue($numArr[2].($i+2), $a[$i][&#39;city&#39;])
		->setCellValue($numArr[3].($i+2), $a[$i][&#39;region_name&#39;])
		->setCellValue($numArr[4].($i+2), $a[$i][&#39;company&#39;])
		->setCellValue($numArr[5].($i+2), $a[$i][&#39;consignee&#39;])
		->setCellValue($numArr[6].($i+2), $a[$i][&#39;mobile&#39;])
		->setCellValue($numArr[7].($i+2), $a[$i][&#39;address&#39;])
		->setCellValue($numArr[8].($i+2), &#39;&#39;)
		->setCellValue($numArr[9].($i+2), &#39;&#39;)
		->setCellValue($numArr[10].($i+2), &#39;&#39;)
		->setCellValue($numArr[11].($i+2), $a[$i][&#39;brand_name&#39;]);
}

// Rename worksheet
echo date(&#39;H:i:s&#39;) , " Rename worksheet" , EOL;
$dirName = date("Ymd");//目录名
$fileName = date("YmdHis");//文件名
$objPHPExcel->getActiveSheet()->setTitle($fileName);


// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);


// Save Excel 2007 file
echo date(&#39;H:i:s&#39;) , " Write to Excel2007 format" , EOL;
$callStartTime = microtime(true);
if(!opendir(&#39;../excel/&#39;.$dirName)){
	mkdir(&#39;../excel/&#39;.$dirName);
}
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, &#39;Excel2007&#39;);
$objWriter->save(&#39;../excel/&#39;.$dirName.&#39;/&#39;.$fileName.&#39;.xlsx&#39;);

$end = getCurrentTime();
$spend = $end-$begin;
if ($spend > 30){
	echo &#39;<script>if(confirm("执行超时！")){ window.history.back(-1);}</script>&#39;;
	exit;
}
header(&#39;Location:http://&#39;.$_SERVER[&#39;HTTP_HOST&#39;].&#39;/excel/&#39;.$dirName.&#39;/&#39;.$fileName.&#39;.xlsx&#39;);

Copy after login