phpexcel导入excel到mysql数据库(示例)

复制代码

2，执行页面insertdb.php

3. session_start();

4. header("Content-type:text/html;charset:utf-8");
5. //全局变量

6. $succ_result=0;

7. $error_result=0;
8. $file=$_FILES['filename'];
9. $max_size="2000000"; //最大文件限制（单位：byte）
10. $fname=$file['name'];
11. $ftype=strtolower(substr(strrchr($fname,'.'),1));
12. //文件格式
13. $uploadfile=$file['tmp_name'];
14. if($_SERVER['REQUEST_METHOD']=='POST'){
15. if(is_uploaded_file($uploadfile)){
16. if($file['size']>$max_size){
17. echo "Import file is too large";
18. exit;
19. }
20. if($ftype!='xls'){
21. echo "Import file type is error";
22. exit;
23. }
24. }else{
25. echo "The file is not empty!";
26. exit;
27. }
28. }
29. require("./conn.php"); //连接mysql数据库

30. //调用phpexcel类库

31. require_once 'phpexcel.php';
32. require_once 'PHPExcel\IOFactory.php';
33. require_once 'PHPExcel\Reader\Excel5.php';

34. $objReader = PHPExcel_IOFactory::createReader('Excel5');//use excel2007 for 2007 format

35. $objPHPExcel = $objReader->load($uploadfile);
36. $sheet = $objPHPExcel->getSheet(0);
37. $highestRow = $sheet->getHighestRow(); // 取得总行数
38. $highestColumn = $sheet->getHighestColumn(); // 取得总列数
39. $arr_result=array();
40. $strs=array();

41. for($j=2;$j {

42. unset($arr_result);
43. unset($strs);
44. for($k='A';$k {
45. //读取单元格
46. $arr_result .= $objPHPExcel->getActiveSheet()->getCell("$k$j")->getValue().',';
47. }
48. $strs=explode(",",$arr_result);
49. $sql="insert into student(typeId,name,sex,age) values ($strs[0],'$strs[1]','$strs[2]',$strs[3])";
50. echo $sql."
    ";
51. mysql_query("set names utf8");
52. $result=mysql_query($sql) or die("执行错误");

53. $insert_num=mysql_affected_rows();

54. if($insert_num>0){
55. $succ_result+=1;
56. }else{
57. $error_result+=1;
58. }
59. }

60. echo "插入成功".$succ_result."条数据！！！
    ";

61. echo "插入失败".$error_result."条数据！！！";
62. ?>

复制代码