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