PHP 导出Excel示例分享

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

导出文件Excel.php

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82

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' );