PHPExcel reads excel and imports mysql database code

PHPExcel reads excel and imports mysql database code_PHP tutorial

WBOY

Release： 2016-07-13 17:06:18

Original

910 people have browsed it

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.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...