How to batch upload data to a database using PHPExcel-php Tutorial

The following small series will introduce a PHP method for batch uploading to the database using PHPExcel. I think this is quite good. now I will share it with you and give you a reference. Let's take a look at the following small series for you to use PHPExcel PHP to batch upload to the database. I think this is quite good. now I will share it with you and give you a reference. Let's take a look at it with Xiaobian.

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!

Function description: only xls files of the Excel2003 type can be uploaded. The size cannot exceed 5 MB. You can download the sample template and add data before uploading!

Test. php page at the front end

Running result:

Background Process. php page

 5*1024*1024) {echo "script" alert ('upload failed, the size of the uploaded table cannot exceed 5 MB '); 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. you can only upload the xls format of excel2003! '); 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 = $ obj Reader-> 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 (); // obtain the total number of columns // read the excel table cyclically, read one, and insert one // j indicates the row from which the reading starts from the second row, because the first row is a title that does not save // $ a indicates the column number for ($ j = 2; $ j <= $ highestRow; $ j ++) {$ a = $ objPHPExcel-> getActiveSheet ()-> getCell ("". $ j)-> getValue (); // obtain the value of column A (owner name) $ B = $ objPHPExcel-> getAc TiveSheet ()-> getCell ("B ". $ j)-> getValue (); // obtain the value of the B (password) column $ c = $ objPHPExcel-> getActiveSheet ()-> getCell ("C ". $ j)-> getValue (); // obtain the value of the C (mobile phone number) column $ d = $ objPHPExcel-> getActiveSheet ()-> getCell ("D ". $ j)-> getValue (); // obtain the value of the D (address) column // null is the primary key id, auto-increment null indicates that $ SQL = "insert into house VALUES (null, '$ A',' $ B ',' $ C', '$ d')" is automatically added ')"; // echo "$ SQL"; // exit (); $ res = mysql_query ($ SQL); if ($ res) {echo "script alert ('added successfully! '); Window. location. href ='./test. php'; script "";} else {echo "script" alert ('failed to add! '); Window. location. href ='./test. php'; script "; exit () ;}}// call upExecel ();?>

Effect:If the file to be uploaded is not selected, the system prompts "no table selected". if the file size exceeds 5 MB, the system prompts. if the file type to be uploaded is not xls, the system prompts you!

The above is the details about how to use PHPExcel to batch upload data to the database. For more information, see other related articles in the first PHP community!