PHPExcel reads excel and imports mysql database code_PHP tutorial

This article will introduce to you a PHPExcel code implementation for reading excel and importing it into the database. Friends who need to know more can refer to it. What we introduce here is to create a mysql connection after reading the table, and then save it to the mysql database.

PHPExcel is a very powerful MS Office Excel document generation class library. When you need to output data in a more complex format, PHPExcel is a good choice. However, its usage is relatively cumbersome

The code is as follows

代码如下

复制代码

set_time_limit(20000); ini_set('memory_limit','-1'); require_once './PHPExcel.php'; require_once './PHPExcel/IOFactory.php'; require_once './PHPExcel/Reader/Excel5.php'; //使用pdo连接数据库 $dsn = "mysql:host=localhost;dbname=alumni;"; $user = "root"; $password = ""; try{ $dbh = new PDO($dsn,$user,$password); $dbh->query('set names utf8;');     }catch(PDOException $e){     echo "连接失败".$e->getMessage();     }     //pdo绑定参数操作     $stmt = $dbh->prepare("insert into alumni(gid,student_no,name) values (:gid,:student_no,:name) ");     $stmt->bindParam(":gid", $gid,PDO::PARAM_STR);     $stmt->bindParam(":student_no", $student_no,PDO::PARAM_STR);     $stmt->bindParam(":name", $name,PDO::PARAM_STR);          $objReader = new PHPExcel_Reader_Excel5(); //use excel2007     $objPHPExcel = $objReader->load('bks.xls'); //指定的文件     $sheet = $objPHPExcel->getSheet(0);     $highestRow = $sheet->getHighestRow(); // 取得总行数     $highestColumn = $sheet->getHighestColumn(); // 取得总列数          for($j=1;$j<=10;$j++) { $student_no = $objPHPExcel->getActiveSheet()->getCell("A".$j)->getValue();//第一列学号     $name = $objPHPExcel->getActiveSheet()->getCell("B".$j)->getValue();//第二列姓名     $gid = $objPHPExcel->getActiveSheet()->getCell("C".$j)->getValue();//第三列gid     }     //将获取的excel内容插入到数据库     $stmt->execute(); ?>

Copy code

set_time_limit(20000);
ini_set('memory_limit','-1');
​ require_once './PHPExcel.php';
​ require_once './PHPExcel/IOFactory.php';
​ require_once './PHPExcel/Reader/Excel5.php';
         
//Use pdo to connect to the database
$dsn = "mysql:host=localhost;dbname=alumni;";
$user = "root";
$password = "";
Try{
$dbh = new PDO($dsn,$user,$password);
$dbh->query('set names utf8;');
}catch(PDOException $e){
echo "Connection failed".$e->getMessage();
}
//pdo binding parameter operation
$stmt = $dbh->prepare("insert into alumni(gid,student_no,name) values ​​(:gid,:student_no,:name) ");
$stmt->bindParam(":gid", $gid,PDO::PARAM_STR);
$stmt->bindParam(":student_no", $student_no,PDO::PARAM_STR);
$stmt->bindParam(":name", $name,PDO::PARAM_STR);
         
$objReader = new PHPExcel_Reader_Excel5(); //use excel2007
$objPHPExcel = $objReader->load('bks.xls'); //Specified file
$sheet = $objPHPExcel->getSheet(0);
$highestRow = $sheet->getHighestRow(); // Get the total number of rows
$highestColumn = $sheet->getHighestColumn(); // Get the total number of columns
         
for($j=1;$j<=10;$j++)
{
         
$student_no = $objPHPExcel->getActiveSheet()->getCell("A".$j)->getValue();//The first column of student number
$name = $objPHPExcel->getActiveSheet()->getCell("B".$j)->getValue();//Second column name
$gid = $objPHPExcel->getActiveSheet()->getCell("C".$j)->getValue();//The third column gid
}
//Insert the obtained excel content into the database
$stmt->execute();
?>

http://www.bkjia.com/PHPjc/630718.htmlwww.bkjia.comtrue

http: //www.bkjia.com/PHPjc/630718.html

TechArticle

This article will introduce to you a PHPExcel code implementation for reading excel and importing it into the database. Friends who need to know more can For reference, what we introduce here is to create a mysql connection after reading the table...