Example 1
code example
The code is as follows |
Copy Code |
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 (); Get total number of rows $highestColumn = $sheet->gethighestcolumn (); Total number of columns obtained $k = 0; Iterate through the Excel file, read one, insert a for ($j =2; $j <= $highestRow; $j + +) { $a = $objPHPExcel->getactivesheet ()->getcell ("a". $j)->getvalue ();//Get the value of column A $b = $objPHPExcel->getactivesheet ()->getcell ("B". $j)->getvalue ();//Get the value of column B $sql = "INSERT into table VALUES (". $a. ",". $b. ")"; mysql_query ($sql); } |
Example 2
The code is as follows |
Copy Code |
<?php Set_time_limit (20000); Ini_set (' Memory_limit ', '-1 '); Require_once './phpexcel.php '; Require_once './phpexcel/iofactory.php '; Require_once './phpexcel/reader/excel5.php ';
Connecting to a database using PDO $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::P aram_str); $stmt->bindparam (": Student_no", $student _no,pdo::P aram_str); $stmt->bindparam (": Name", $name, PDO::P aram_str);
$objReader = new Phpexcel_reader_excel5 (); Use excel2007 $objPHPExcel = $objReader->load (' Bks.xls '); The specified file $sheet = $objPHPExcel->getsheet (0); $highestRow = $sheet->gethighestrow (); Get total number of rows $highestColumn = $sheet->gethighestcolumn (); Total number of columns obtained
For ($j =1 $j <=10; $j + +) {
$student _no = $objPHPExcel->getactivesheet ()->getcell ("A". $j)->getvalue ();//First row number $name = $objPHPExcel->getactivesheet ()->getcell ("B". $j)->getvalue ();//second column name $gid = $objPHPExcel->getactivesheet ()->getcell ("C". $j)->getvalue ();//third-column GID } Inserts the obtained Excel content into the database $stmt->execute (); ?> |
Example 3
The new database table is as follows:
The code is as follows |
Copy Code |
--Database: ' Alumni ' --The structure of the table ' alumni ' CREATE TABLE IF not EXISTS ' alumni ' ( ' ID ' bigint not NULL auto_increment, ' gid ' varchar DEFAULT NULL COMMENT ' file Number ', ' student_no ' varchar DEFAULT NULL COMMENT ' School Number ', ' Name ' varchar (#) DEFAULT NULL, PRIMARY KEY (' id '), KEY ' gid ' (' gid '), KEY ' name ' (' name ') ) Engine=myisam DEFAULT Charset=utf8; |
PHP Program
The code is as follows |
Copy Code |
<?php Header ("Content-type:text/html;charset=utf-8"); Require_once ' excel_reader2.php '; Set_time_limit (20000); Ini_set ("Memory_limit", "2000M"); Connecting to a database using PDO $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::P aram_str); $stmt->bindparam (": Student_no", $student _no,pdo::P aram_str); $stmt->bindparam (": Name", $name, PDO::P aram_str); Reading Excel content using Php-excel-reader $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]; } Inserts the obtained Excel content into the database $stmt->execute (); } echo "Successful execution"; echo "Last inserted ID:". $dbh->lastinsertid (); ?> |