PHP 导出Excel示例分享
下载PHPExcel_1.8.0_doc.ziphttp://phpexcel.codeplex.com/,将解压后的文件夹里的Classes上传到网站的根目录下,Classes目录内的内容如下:
导出文件Excel.php
require_oncedirname(__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'); //CreatenewPHPExcelobject $objPHPExcel=newPHPExcel(); $arr=array('订单号','下单时间','城市','地区','客户名称','收货人','联系电话','收货地址','ERP客户名称','物流系统客户名称','活动项目', '品牌','型号','颜色','物流系统型号','订货量','单价','代收货款','红包','价保返利','运费','实收金额','付款方式','订单来源', '上游厂商','是否在仓','快递面单号','订单状态','确认时间','末次状态确认时间','描述','对应业务','对应客服','商家留言','下单摘要', '业务员','联系方式'); //输出标题 echodate('H:i:s'),"Addsomedata",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']); } //Renameworksheet echodate('H:i:s'),"Renameworksheet",EOL; $dirName=date("Ymd");//目录名 $fileName=date("YmdHis");//文件名 $objPHPExcel->getActiveSheet()->setTitle($fileName); //Setactivesheetindextothefirstsheet,soExcelopensthisasthefirstsheet $objPHPExcel->setActiveSheetIndex(0); //SaveExcel2007file echodate('H:i:s'),"WritetoExcel2007format",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');