php excel reader reads excel content and stores it in the database implementation code

php excel reader reads excel content and stores it in the database implementation code_PHP tutorial

WBOY

Release： 2016-07-21 15:14:30

Original

1020 people have browsed it

The previous article introduced the method of php-excel-reader to read excel files. Because of need, excel data like this:

php-excel-reader读取excel内容存入数据库 The new database table is as follows:

-- Database: `alumni`

-- Table structure `alumni`

CREATE TABLE IF NOT EXISTS `alumni` (

`id` bigint(20) NOT NULL AUTO_INCREMENT,

`gid` varchar(20) DEFAULT NULL COMMENT 'File number',

`student_no` varchar(20) DEFAULT NULL COMMENT 'student number',

`name` varchar(32) DEFAULT NULL,

PRIMARY KEY (`id`),

KEY `gid` (`gid`),

KEY `name` (`name`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8;

The database results after importing are as follows:

php-excel-reader读取excel内容存入数据库结果 php source code is as follows:

Copy codeThe code is as follows:

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