Home > Article > Backend Development > PHPExcel reads excel and imports into database_PHP tutorial

PHPExcel reads excel and imports into database_PHP tutorial

WBOYOriginal: 2016-07-13 17:06:03924browse

PHPExcel is a great PHP plug-in for reading and writing excel data tables. Now I will introduce to you how to use PHPExcel to read excel and import it into the mysql database.

Example 1

Code Example

The code is as follows

Copy code

代码如下

复制代码

require_once 'phpexcel/Classes/PHPExcel.php';
require_once 'phpexcel/Classes/PHPExcel/IOFactory.php';
require_once 'phpexcel/Classes/PHPExcel/Reader/Excel5.php';
$objReader = PHPExcel_IOFactory::createReader('Excel5');//use excel2007 for 2007 format
$objPHPExcel = $objReader->load($filename); //$filename可以是上传的文件，或者是指定的文件
$sheet = $objPHPExcel->getSheet(0);
$highestRow = $sheet->getHighestRow(); // 取得总行数
$highestColumn = $sheet->getHighestColumn(); // 取得总列数
$k = 0;

//循环读取excel文件,读取一条,插入一条
for($j=2;$j<=$highestRow;$j++)
{

$a = $objPHPExcel->getActiveSheet()->getCell("A".$j)->getValue();//获取A列的值
$b = $objPHPExcel->getActiveSheet()->getCell("B".$j)->getValue();//获取B列的值
$sql = "INSERT INTO table VALUES(".$a.",".$b.")";
mysql_query($sql);

}

require_once 'phpexcel/Classes/PHPExcel.php';

require_once 'phpexcel/Classes/PHPExcel/IOFactory.php';
require_once 'phpexcel/Classes/PHPExcel/Reader/Excel5.php';

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

代码如下

复制代码

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();
?>

$objPHPExcel = $objReader->load($filename); //$filename can be an uploaded file or a specified file $sheet = $objPHPExcel->getSheet(0); $highestRow = $sheet->getHighestRow(); // Get the total number of rows $highestColumn = $sheet->getHighestColumn(); // Get the total number of columns $k = 0; //Loop through the excel file, read one item, and insert one item for($j=2;$j<=$highestRow;$j++)<🎜> {<🎜> <🎜>$a = $objPHPExcel->getActiveSheet()->getCell("A".$j)->getValue();//Get the value of column A $b = $objPHPExcel->getActiveSheet()->getCell("B".$j)->getValue();//Get the value of column B $sql = "INSERT INTO table VALUES(".$a.",".$b.")"; mysql_query($sql); }

Example 2

The code is as follows

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 numbers $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(); ?>

Example 3

The new database table is as follows:

-- Table structure `alumni`

The code is as follows

代码如下

复制代码

-- 数据库: `alumni`

-- 表的结构 `alumni`

CREATE TABLE IF NOT EXISTS `alumni` (

`id` bigint(20) NOT NULL AUTO_INCREMENT,

`gid` varchar(20) DEFAULT NULL COMMENT '档案编号',

`student_no` varchar(20) DEFAULT NULL COMMENT '学号',

`name` varchar(32) DEFAULT NULL,

PRIMARY KEY (`id`),

KEY `gid` (`gid`),

KEY `name` (`name`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Copy code

代码如下

复制代码

header("Content-Type:text/html;charset=utf-8");
require_once 'excel_reader2.php';
set_time_limit(20000);
ini_set("memory_limit","2000M");
//使用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);
//使用php-excel-reader读取excel内容
$data = new Spreadsheet_Excel_Reader();
$data->setOutputEncoding('UTF-8');
$data->read("stu.xls");
for ($i = 1; $i <= $data->sheets[0]['numRows']; $i++) {
for ($j = 1; $j <= 3; $j++) {
$student_no = $data->sheets[0]['cells'][$i][1];
$name = $data->sheets[0]['cells'][$i][2];
$gid = $data->sheets[0]['cells'][$i][3];
}
//将获取的excel内容插入到数据库
$stmt->execute();
}
echo "执行成功";
echo "最后插入的ID：".$dbh->lastInsertId();
?>

-- Database: `alumni`

CREATE TABLE IF NOT EXISTS `alumni` (

) ENGINE=MyISAM DEFAULT CHARSET=utf8;

php program

The code is as follows

Copy code

header("Content-Type:text/html;charset=utf-8");
require_once 'excel_reader2.php';
set_time_limit(20000);
ini_set("memory_limit","2000M");
//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);
//Use php-excel-reader to read excel content
$data = new Spreadsheet_Excel_Reader();
$data->setOutputEncoding('UTF-8');
$data->read("stu.xls");
for ($i = 1; $i <= $data->sheets[0]['numRows']; $i++) {
for ($j = 1; $j <= 3; $j++) {
$student_no = $data->sheets[0]['cells'][$i][1];
$name = $data->sheets[0]['cells'][$i][2];
$gid = $data->sheets[0]['cells'][$i][3];
}
//Insert the obtained excel content into the database
$stmt->execute();
}
echo "Execution successful";
echo "Last inserted ID:".$dbh->lastInsertId();
?> http://www.bkjia.com/PHPjc/630725.htmlwww.bkjia.comtruehttp: //www.bkjia.com/PHPjc/630725.htmlTechArticlePHPExcel is a very good plug-in for reading and writing Excel data tables in PHP. Let me tell you about it below. Introduce the method of using PHPExcel to read excel and import it into mysql database. Example 1 Code example...

Statement：

The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Previous article：When phpmyadmin connects to mysql, 2002 server is not responding_PHP tutorialNext article：When phpmyadmin connects to mysql, 2002 server is not responding_PHP tutorial

See more

PHPExcel reads excel and imports into database_PHP tutorial

Related articles