PHP 导出Excel示例分享

$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 '';
exit;
}
header('Location:http://'.$_SERVER['HTTP_HOST'].'/excel/'.$dirName.'/'.$fileName.'.xlsx');