用PHPExcel读取excel并导入数据库


  1. php

  2. set_time_limit(20000);

  3. ini_set('memory_limit','-1');

  4. // by www.phpddt.com

  5. require_once './PHPExcel.php';

  6. require_once './PHPExcel/IOFactory.php';

  7. require_once './PHPExcel/Reader/Excel5.php';

  8.  

  9. //使用pdo连接数据库

  10. $dsn = "mysql:host=localhost;dbname=alumni;";

  11. $user = "root";

  12. $password = "";

  13. try{

  14. $dbh = new PDO($dsn,$user,$password);

  15. $dbh->query('set names utf8;');

  16. }catch(PDOException $e){

  17. echo "连接失败".$e->getMessage();

  18. }

  19. //pdo绑定参数操作

  20. $stmt = $dbh->prepare("insert into alumni(gid,student_no,name) values (:gid,:student_no,:name) ");

  21. $stmt->bindParam(":gid", $gid,PDO::PARAM_STR);

  22. $stmt->bindParam(":student_no", $student_no,PDO::PARAM_STR);

  23. $stmt->bindParam(":name", $name,PDO::PARAM_STR);

  24.  

  25. $objReader = new PHPExcel_Reader_Excel5(); //use excel2007

  26. $objPHPExcel = $objReader->load('bks.xls'); //指定的文件

  27. $sheet = $objPHPExcel->getSheet(0);

  28. $highestRow = $sheet->getHighestRow(); // 取得总行数

  29. $highestColumn = $sheet->getHighestColumn(); // 取得总列数

  30.  

  31. for($j=1;$j10;$j++)

  32. {

  33.  

  34. $student_no = $objPHPExcel->getActiveSheet()->getCell("A".$j)->getValue();//第一列学号

  35. $name = $objPHPExcel->getActiveSheet()->getCell("B".$j)->getValue();//第二列姓名

  36. $gid = $objPHPExcel->getActiveSheet()->getCell("C".$j)->getValue();//第三列gid

  37. }

  38. //将获取的excel内容插入到数据库

  39. $stmt->execute();

  40. ?>