In the previous article, we introduced how to use php-excel-reader to read excel files. to read data like excel, create a database table as follows: -- database: 'alumni' -- table structure 'Alumni
The previous article introduced how to use php-excel-reader to read excel files. as needed, convert data such as excel:
Create a database table 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 id ',
'Name' varchar (32) default null,
Primary key ('id '),
KEY 'gid' ('gid '),
KEY 'name' ('name ')
) ENGINE = MyISAM default charset = utf8;
The database results after import are as follows:
The php source code is as follows:
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 to the database $ stmt-> execute ();} echo "executed successfully"; echo "last inserted ID :". $ dbh-> lastInsertId ();?>
Considering that the excel volume is large, the PDO binding operation is used!