PHPExcel reads excel and imports it to the database. PHPExcel is a great plug-in for php to read and write excel data tables. next I will introduce how to use PHPExcel to read excel and import mysql databases. Example 1: PHPExcel is a great plug-in for php to read and write excel data tables. The following describes how to use PHPExcel to read excel and import mysql databases.
Example 1
Sample code
| The code is as follows: |
|
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 can be an uploaded file or a specified file
$ Sheet = $ objPHPExcel-> getSheet (0 );
$ HighestRow = $ sheet-> getHighestRow (); // gets the total number of rows.
$ HighestColumn = $ sheet-> getHighestColumn (); // gets the total number of columns
$ K = 0; // Read the excel file cyclically, read one, and insert one
For ($ j = 2; $ j <= $ highestRow; $ j ++)
{ $ A = $ objPHPExcel-> getActiveSheet ()-> getCell ("A". $ j)-> getValue (); // obtain the value of column
$ B = $ objPHPExcel-> getActiveSheet ()-> getCell ("B". $ j)-> getValue (); // obtain the value of column B
$ SQL = "insert into table VALUES (". $ a. ",". $ B .")";
Mysql_query ($ SQL ); } |
Example 2
| 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 ';
// 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 ();
}
// Parameter binding operation for 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 '); // specify the file
$ Sheet = $ objPHPExcel-> getSheet (0 );
$ HighestRow = $ sheet-> getHighestRow (); // gets the total number of rows.
$ HighestColumn = $ sheet-> getHighestColumn (); // gets the total number of columns
For ($ j = 1; $ j <= 10; $ j ++)
{
$ Student_no = $ objPHPExcel-> getActiveSheet ()-> getCell ("A". $ j)-> getValue (); // The first student ID
$ Name = $ objPHPExcel-> getActiveSheet ()-> getCell ("B". $ j)-> getValue (); // name of the second column
$ Gid = $ objPHPExcel-> getActiveSheet ()-> getCell ("C". $ j)-> getValue (); // The third column gid
}
// Insert the obtained excel content to the database
$ Stmt-> execute ();
?> |
Example 3
Create a database table as follows:
| The code 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 id ', 'Name' varchar (32) default null, Primary key ('id '), KEY 'gid' ('gid '), KEY 'name' ('name ') ) ENGINE = MyISAM default charset = utf8; |
Php program
| The 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", "2000 M ");
// 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 ();
}
// Parameter binding operation for 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 );
// 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 "execution successful ";
Echo "last inserted ID:". $ dbh-> lastInsertId ();
?>
|
Bytes. Example 1 code example...
