Phpexcel is a PHP for Excel data table read and write a very good plug-in, let me introduce you to use Phpexcel read Excel and Import MySQL database method.
Example 1
code example
| The code is as follows |
Copy Code |
< P>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 format
$objPHPExcel = $ Objreader->load ($filename); $filename can be an uploaded file, or the specified file
$sheet = $objPHPExcel->getsheet (0);
$highestRow = $sheet->gethighestrow (); Gets the total number of rows
$highestColumn = $sheet->gethighestcolumn ();///Gets the number of columns
$k = 0; //Iterate through the Excel file, read a line, insert a
for ($j =2; $j <= $highestRow; $j + +)
{ $a = $objPHPExcel->getactivesheet ( )->getcell ("A". $j)->getvalue ();//Gets the value of column a
$b = $objPHPExcel->getactivesheet ()->getcell ("B". $j) GetValue ();//Gets the value of column B
$sql = "INSERT into table values (". $a. ",". $b. ");
mysql_query ($sql); } |
Example 2
| The code is as follows |
Copy Code |
Set_time_limit (20000);
Ini_set (' Memory_limit ', '-1 ');
Require_once './phpexcel.php ';
Require_once './phpexcel/iofactory.php ';
Require_once './phpexcel/reader/excel5.php ';
Connect 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 (); Total number of rows obtained
$highestColumn = $sheet->gethighestcolumn (); Total number of columns obtained
for ($j =1; $j <=10; $j + +)
{
$student _no = $objPHPExcel->getactivesheet ()->getcell ("A". $j)->getvalue ();//First column number
$name = $objPHPExcel->getactivesheet ()->getcell ("B". $j)->getvalue ();//second column name
$gid = $objPHPExcel->getactivesheet ()->getcell ("C". $j)->getvalue ();//third-row GID
}
Insert the acquired 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 (a) not NULL auto_increment, ' GID ' varchar (DEFAULT NULL COMMENT ' archive number '), ' student_no ' varchar DEFAULT NULL COMMENT ' study 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 |
Header ("Content-type:text/html;charset=utf-8");
Require_once ' excel_reader2.php ';
Set_time_limit (20000);
Ini_set ("Memory_limit", "2000M");
Connect 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];
}
Insert the acquired Excel content into the database
$stmt->execute ();
}
echo "Successful execution";
echo "Last inserted ID:". $dbh->lastinsertid ();
?>
|
http://www.bkjia.com/PHPjc/630725.html www.bkjia.com true http://www.bkjia.com/PHPjc/630725.html techarticle Phpexcel is a PHP for Excel data table read and write a very good plug-in, let me introduce you to use Phpexcel read Excel and Import MySQL database method. Example 1 code example ...
