PHP uses Phpexcel to upload batches to the database

The following small series for everyone to bring a PHP use Phpexcel to achieve batch upload to the database method. Small series feel very good, now share to everyone, also for everyone to make a reference. Let's take a look at it with a little knitting.

This example uses only the execel2003. xls document, and if you are using a different version, you can save the format as "Execel 97-2003 Workbook (*.xls)", which is the. xls file type!

Function Description: Can only upload Excel2003 type of xls file, size not more than 5M. Download the sample template to add data and upload it!

Front desk test.php Page

<! DOCTYPE html>
Operation Result:


Background process.php page

<?phpheader ("Content-type:text/html;charset=utf-8");//link Database $link = @mysql_connect (' localhost ', ' root ', ') or die (' Connection database failed '); mysql_select_db (' Test ', $link); mysql_query (' Set names UTF8 '); function Upexecel () {//determines if the table to be uploaded is selected if ( Empty ($_post[' myfile ')) {echo "<script>alert (you have not selected a table); History.go ( -1);</script>";} Get the size of the table, limit the size of the upload table 5m$file_size = $_files[' myfile ' [' Size '];if ($file _size>5*1024*1024) {echo ' <script>  Alert (' Upload failed, upload form cannot exceed 5M size '); History.go ( -1);</script> "; Exit ();} Limit upload table Type $file_type = $_files[' myfile ' [' type '];//application/vnd.ms-excel for XLS file type if ($file _type!= ' application/ Vnd.ms-excel ') {echo ' <script>alert (' upload failed, only upload excel2003 xls format! '); History.go ( -1) </script> "; Exit ();}  Determine if the form was successfully uploaded if (is_uploaded_file ($_files[' myfile ' [' tmp_name '])) {require_once ' phpexcel.php ';  Require_once ' phpexcel/iofactory.php ';  Require_once ' phpexcel/reader/excel5.php '; The above three-step load phpexcel class $objReader = Phpexcel_iofactory::createreader (' Excel5 ');//use excel20For format//Receive Excel table in cache $filename = $_files[' myfile ' [' tmp_name ']; $objPHPExcel = $objReader->load ($filename);   $filename can be an uploaded form, or a specified table $sheet = $objPHPExcel->getsheet (0); $highestRow = $sheet->gethighestrow (); Total number of rows obtained//$highestColumn = $sheet->gethighestcolumn ();    Get total columns//Loop through the Excel table, read one, insert a//j to start reading from the second line, since the first row is the title does not save//$a represents the column number for ($j =2; $j <= $highestRow; $j + +) { $a = $objPHPExcel->getactivesheet ()->getcell ("a". $j)->getvalue ();//Gets the value of a (owner name) column $b = $objPHPExcel Getactivesheet ()->getcell ("B". $j)->getvalue ();//Gets the value of the B (password) column $c = $objPHPExcel->getactivesheet () Getcell ("C". $j)->getvalue ();//Gets the value of the C (cell phone number) column $d = $objPHPExcel->getactivesheet ()->getcell ("D". $j)    GetValue ();//Gets the value of the D (address) column//null The primary key ID, and the self-increment available null means auto-add $sql = "INSERT into house values (null, ' $a ', ' $b ', ' $c ', ' $d ')";    echo "$sql";    Exit ();    $res = mysql_query ($sql); if ($res) {echo "<script>alert (' added successfully! '); window.location.href= './test.php ';</script> "; }else{echo "<script>alert (' Add failed!      '); window.location.href= './test.php ';</script> ";    Exit (); }}}}//call Upexecel ();? >
The effect is: If you do not select the file to upload, will prompt "No table selected", if the table file is more than 5M, prompt, if the upload file type is not XLS, will prompt!

The above PHP use Phpexcel to achieve batch upload to the database is a small part of the whole content to share to everyone, I hope to give you a reference, but also hope that we support the script home.