PHP uses PHPExcel to batch upload data to the database, phpphpexcel

PHP uses PHPExcel to batch upload data to the database, phpphpexcel

 

This example only uses the. xls document of execel2003. if you are using another version, you can save it in the format of "Execel 97-2003 Workbook (* .xls)" .xls file type!

Test. php page at the front end

<! DOCTYPE html> <Tr> <td> select the file you want to upload </td> <input type = "file" name = "myfile"> </td> </tr> <tr> <td> <input type = "submit" value = "Upload File"/> </td> </tr> </table> </form> </ body> 
Running result:

 
Background Process. php page
<? Phpheader ("Content-type: text/html; charset = UTF-8"); // link to the database $ link = @ mysql_connect ('localhost', 'root ','') or die ('database connection failed'); mysql_select_db ('test', $ link); mysql_query ('set names utf8'); function upExecel () {// determine whether the table to be uploaded is selected (if (empty ($ _ POST ['myfile']) {echo "<script> alert (you have not selected a table ); history. go (-1); </script> ";}// obtain the table size, restrict the size of the uploaded table by 5 M $ file_size = $ _ FILES ['myfile'] ['SIZE']; if ($ file_size> 5*1024*1024) {echo "<scr EPT> alert ('upload failed, the size of the uploaded table cannot exceed 5 Ms'); history. go (-1); </script> "; exit () ;}// restrict the upload table type $ file_type = $ _ FILES ['myfile'] ['type']; // application/vnd. ms-excel is an xls file type if ($ file_type! = 'Application/vnd. ms-excel ') {echo "<script> alert ('upload failed, only excel2003 xls format can be uploaded! '); History. go (-1) </script> "; exit ();} // determine whether the table is 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'; // class of PHPExcel loaded in the preceding three steps $ objReader = PHPExcel_IOFactory: createReader ('excel5 '); // use excel2007 for 2007 format // receives an excel table in the cache $ filename = $ _ FILES ['myfile'] ['tmp _ name']; $ objPHPExcel = $ ob JReader-> load ($ filename); // $ filename can be an uploaded table or a specified table $ sheet = $ objPHPExcel-> getSheet (0 ); $ highestRow = $ sheet-> getHighestRow (); // get the total number of rows $ highestColumn = $ sheet-> getHighestColumn (); // get the total number of columns // read the excel table cyclically, read one and insert one. // j indicates the row from which to start reading. // $ a indicates that the column number/null is the id. Auto-increment can be automatically added with null for ($ j = 1; $ j <= $ highestRow; $ j ++) {$ a = $ objPHPExcel-> getActiveSheet ()-> getCell ("". $ j)-> getValue (); // obtain the value of column A $ B = $ objPHPExcel-> getActiveS Heet ()-> getCell ("B ". $ j)-> getValue (); // obtain the value of Column B $ c = $ objPHPExcel-> getActiveSheet ()-> getCell ("C ". $ j)-> getValue (); // obtain the value of column C $ d = $ objPHPExcel-> getActiveSheet ()-> getCell ("D ". $ j)-> getValue (); // obtain the value of column D $ SQL = "insert into house VALUES (null, '$ A',' $ B ', '$ C',' $ D') "; $ res = mysql_query ($ SQL); if ($ res) {echo" <script> alert ('added successfully! '); Window. location. href = '. /test. php'; </script> "; exit ();} else {echo" <script> alert ('addition failed! '); Window. location. href ='./test. php'; </script> "; exit () ;}}// call upExecel ();?>
Function: only the xls file type of excel2003 can be uploaded. The size cannot exceed 5 MB.
Effect: if you do not select the file to be uploaded, the system will prompt "no table selected". If the table file exceeds 5 MB, the system will prompt. If the file type to be uploaded is not xls, the system will prompt you!