PHP 导出Excel示例分享

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

导出文件excel.php

require_once dirname(__file__).'/classes/phpexcel.php';//引入phpexcel
.....此处略去从数据库获取数据的过程，$a为需要导出的数组......
$numarr = array('a','b','c','d','e','f','g','h','i','j','k','l','m','n','o','p','q','r','s','t','u','v','w','x','y','z','aa','ab','ac','ad','ae','af','ag','ah','ai','aj','ak','al','am','an','ao','ap','aq','ar','as','at','au','av','aw','ax','ay','az');
// create new phpexcel object
$objphpexcel = new phpexcel();

$arr = array('订单号','下单时间','城市','地区', '客户名称', '收货人', '联系电话', '收货地址', 'erp客户名称', '物流系统客户名称', '活动项目',
 '品牌', '型号', '颜色', '物流系统型号', '订货量', '单价', '代收货款', '红包', '价保返利', '运费', '实收金额', '付款方式', '订单来源',
 '上游厂商', '是否在仓', '快递面单号', '订单状态', '确认时间', '末次状态确认时间', '描述', '对应业务', '对应客服', '商家留言', '下单摘要',
 '业务员', '联系方式');
// 输出标题
echo date('h:i:s') , " add some data" , eol;
//设置换行
$objphpexcel->getactivesheet()->getstyle('h')->getalignment()->setwraptext(true);
$objphpexcel->getactivesheet()->getstyle('y')->getalignment()->setwraptext(true);
//设置相应列的宽度
$objphpexcel->setactivesheetindex(0)->getcolumndimension('a')->setwidth(15);
$objphpexcel->setactivesheetindex(0)->getcolumndimension('b')->setwidth(20);
$objphpexcel->setactivesheetindex(0)->getcolumndimension('g')->setwidth(15);
$objphpexcel->setactivesheetindex(0)->getcolumndimension('h')->setwidth(40);
$objphpexcel->setactivesheetindex(0)->getcolumndimension('i')->setwidth(15);
$objphpexcel->setactivesheetindex(0)->getcolumndimension('j')->setwidth(15);
//输出第一行
$objphpexcel->setactivesheetindex(0)
   ->setcellvalue('a1', $arr[0])
 ->setcellvalue('b1', $arr[1])
 ->setcellvalue('c1', $arr[2])
 ->setcellvalue('d1', $arr[3])
 ->setcellvalue('e1', $arr[4])
 ->setcellvalue('f1', $arr[5])
 ->setcellvalue('g1', $arr[6])
 ->setcellvalue('h1', $arr[7])
 ->setcellvalue('i1', $arr[8])
 ->setcellvalue('j1', $arr[9])
   ->setcellvalue('k1', $arr[10])
   ->setcellvalue('l1', $arr[11]);


//输出内容
for($i=0;$i<count($a);$i++){
 $objphpexcel->setactivesheetindex(0)
  ->setcellvalue($numarr[0].($i+2), $a[$i]['order_sn'])
  ->setcellvalue($numarr[1].($i+2), $a[$i]['add_time'])
  ->setcellvalue($numarr[2].($i+2), $a[$i]['city'])
  ->setcellvalue($numarr[3].($i+2), $a[$i]['region_name'])
  ->setcellvalue($numarr[4].($i+2), $a[$i]['company'])
  ->setcellvalue($numarr[5].($i+2), $a[$i]['consignee'])
  ->setcellvalue($numarr[6].($i+2), $a[$i]['mobile'])
  ->setcellvalue($numarr[7].($i+2), $a[$i]['address'])
  ->setcellvalue($numarr[8].($i+2), '')
  ->setcellvalue($numarr[9].($i+2), '')
  ->setcellvalue($numarr[10].($i+2), '')
  ->setcellvalue($numarr[11].($i+2), $a[$i]['brand_name']);
}

// rename worksheet
echo date('h:i:s') , " 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('h:i:s') , " write to excel2007 format" , eol;
$callstarttime = microtime(true);
if(!opendir('../excel/'.$dirname)){
 mkdir('../excel/'.$dirname);
}
$objwriter = phpexcel_iofactory::createwriter($objphpexcel, 'excel2007');
$objwriter->save('../excel/'.$dirname.'/'.$filename.'.xlsx');

$end = getcurrenttime();
$spend = $end-$begin;
if ($spend > 30){
 echo '<script>if(confirm("执行超时！")){ window.history.back(-1);}</script>';
 exit;
}
header('location:http://'.$_server['http_host'].'/excel/'.$dirname.'/'.$filename.'.xlsx');