数据导出已经有了,怎么能没有数据导入呢,同样使用TP5框架,首先需要下载phpexcel.zip,放到第三方类库目录vendor目录下,然后有一个页面可以让你选择要导入的Excel文件,然后点击导入按钮调用接口:
我的模板内的数据如下图:
后端接口代码如下:
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
|
public function uplExcel(Request $request ) { if (! empty ( $_FILES [ 'excel' ][ 'name' ])) { $fileName = $_FILES [ 'excel' ][ 'name' ]; //得到文件全名 $dotArray = explode ( '.' , $fileName ); //把文件名安.区分,拆分成数组 $type = end ( $dotArray );<br> if ( $type != "xls" && $type != "xlsx" ) { $ret [ 'res' ] = "0" ; $ret [ 'msg' ] = "不是Excel文件,请重新上传!" ; return json_encode( $ret ); } //取数组最后一个元素,得到文件类型 $uploaddir = "../uploads2/" . date ( "Y-m-d" ) . '/' ; //设置文件保存目录 注意包含 if (! file_exists ( $uploaddir )) { mkdir ( $uploaddir , 0777, true); } $path = $uploaddir . md5(uniqid(rand())) . '.' . $type ; //产生随机文件名 //$path = "images/".$fileName; //客户端上传的文件名; //下面必须是tmp_name 因为是从临时文件夹中移动 move_uploaded_file( $_FILES [ 'excel' ][ 'tmp_name' ], $path ); //从服务器临时文件拷贝到相应的文件夹下 $file_path = $path ; if (! file_exists ( $path )) { $ret [ 'res' ] = "0" ; $ret [ 'msg' ] = "上传文件丢失!" . $_FILES [ 'excel' ][ 'error' ]; return json_encode( $ret ); } //文件的扩展名 $ext = strtolower ( pathinfo ( $path , PATHINFO_EXTENSION)); if ( $ext == 'xlsx' ) { $objReader = PHPExcel_IOFactory::createReader( 'Excel2007' ); $objPHPExcel = $objReader ->load( $file_path , 'utf-8' ); } elseif ( $ext == 'xls' ) { $objReader = PHPExcel_IOFactory::createReader( 'Excel5' ); $objPHPExcel = $objReader ->load( $file_path , 'utf-8' ); } $sheet = $objPHPExcel ->getSheet(0); $highestRow = $sheet ->getHighestRow(); // 取得总行数 $highestColumn = $sheet ->getHighestColumn(); // 取得总列数 $ar = array (); $i = 0; $importRows = 0; for ( $j = 2; $j <= $highestRow ; $j ++) { $importRows ++;<br> $realName = (string) $objPHPExcel ->getActiveSheet()->getCell( "A$j" )->getValue(); //需要导入的realName $phone = (string) $objPHPExcel ->getActiveSheet()->getCell( "B$j" )->getValue(); //需要导入的phone $company = (string) $objPHPExcel ->getActiveSheet()->getCell( "C$j" )->getValue(); //需要导入的company $job = (string) $objPHPExcel ->getActiveSheet()->getCell( "D$j" )->getValue(); //需要导入的job $email = (string) $objPHPExcel ->getActiveSheet()->getCell( "E$j" )->getValue(); //需要导入的email $ret [ 'mdata' ] = $this ->addMemb( $phone , $realName , $company , $job , $email ); //这里就是我的数据库添加操作定义的一个方法啦,对应替换为自己的 if ( $ret [ 'mdata' ] && ! is_Bool ( $ret [ 'mdata' ])) { $ar [ $i ] = $ret [ 'mdata' ]; $i ++; } } if ( $i > 0) { $ret [ 'res' ] = "0" ; $ret [ 'errNum' ] = $i ; $ret [ 'allNum' ] = $importRows ; $ret [ 'sucNum' ] = $importRows - $i ; $ret [ 'mdata' ] = $ar ; $ret [ 'msg' ] = "导入完毕!" ; return json_encode( $ret ); } $ret [ 'res' ] = "1" ; $ret [ 'allNum' ] = $importRows ; $ret [ 'errNum' ] = 0; $ret [ 'sucNum' ] = $importRows ; $ret [ 'mdata' ] = "导入成功!" ; return json_encode( $ret ); } else { $ret [ 'res' ] = "0" ; $ret [ 'msg' ] = "上传文件失败!" ; return json_encode( $ret ); } } |