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