PHPExcel read excel and import database _ PHP Tutorial

Source: Internet
Author: User
Tags import database
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...

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.