PHPExcel reads excel files and imports them to the database

Under normal circumstances, if we want to import data from the excel database to the mysql database, we have no good way to achieve it, but with PHPExcel out of control, everything becomes simple. this code is used in thinkphp. 1. introduction class... under normal circumstances, if we want to import data from the excel database to the mysql database, we have no good way to achieve this, but with PHP Excel out of control, everything becomes simple.

This code is used in thinkphp.

1. introduce the class with the following code:

Vendor ('phpexcel. PHPExcel '); // introduce the extension class. that is/Vendor ('phpexcel. PHPExcel. IOFactory '); Vendor ('phpexcel. PHPExcel. reader. excel5 '); $ excel_file = ROOT_PATH. "/public/Uploads /". $ publicity_bankdata_mod-> where ("id = ". $ data ['id'])-> getField ('Excel _ file'); // dump ($ excel_file); exit; $ objReader = PHPExcel_IOFactory :: createReader ('excel5'); // use excel2007 for 2007 format $ objPHPExcel = $ objReader-> load ($ excel_file ); // $ uploadfile $ sheet = $ objPHPExcel-> getSheet (0); $ highestRow = $ sheet-> getHighestRow (); // get the total number of rows $ highestColumn = $ sheet-> getHighestColumn (); // get the total number of columns $ arr_result = array (); $ strs = array (); $ strs_datas = array (); $ succ_result = 0; $ error_result = 0; the above seems a bit messy. here I will write a complete class, the code is as follows:
 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: PARAM_STR); $ stmt-> bindParam (": student_no ", $ student_no, PDO: PARAM_STR); $ stmt-> bindParam (": name", $ name, PDO: PARAM_STR); $ objReader = new PHPExcel_Reader_Excel5 (); // use excel2007 $ objPHPExcel = $ objReader-> load('bks.xls '); // specify the file $ sheet = $ objPHPExcel-> getSheet (0 ); $ highestRow = $ sheet-> getHighestRow (); // get the total number of rows $ highestColumn = $ sheet-> getHighestColumn (); // get the total number of columns for ($ j = 1; $ j <= 10; $ j ++) {$ student_no = $ objPHPExcel-> getActiveSheet ()-> getCell ("". $ j)-> getValue (); // The first student ID $ name = $ objPHPExcel-> getActiveSheet ()-> getCell ("B ". $ j)-> getValue (); // name of the second column $ gid = $ objPHPExcel-> getActiveSheet ()-> getCell ("C ". $ j)-> getValue (); // The third gid} // Insert the obtained excel content to the database $ stmt-> execute ();

Php-excel-reader:

1. dump (), which can output excel content in html format: echo $ data-> dump (true, true );

2. Save the excel data to the array and use $ data-> sheet to print the following:

Array ([0] => Array ([maxrow] => 0 [maxcol] => 0 [numRows] => 5 [numCols] => 4 [cells] => Array ([1] => Array ([1] => No. [2] => name [3] => age [4] => Student ID) [2] => Array ([1] => 1 [2] => small red [3] => 22 [4] => a1000) [3] => Array ([1] => 2 [2] => John [3] => 33 [4] => a1001) [4] => Array ([1] => 3 [2] => small black [3] => 44 [4] => a1002) [5] => Array ([2] => by [3] => www.phprm.com )) [cellsInfo] => Array ([1] => Array ([1] => Array ([xfIndex] => 15) [2] => Array ([xfIndex] => 15) [3] => Array ([xfIndex] => 15) [4] => Array ([xfIndex] => 15 )) [2] => Array ([1] => Array ([string] => 1 [raw] => 1 [rectype] => unknown [format] => % s [formatIndex] => 0 [fontIndex] => 0 [formatColor] => [xfIndex] => 15) [2] => Array ([xfIndex] => 15) [3] => Array ([string] => 22 [raw] => 22 [rectype] => unknown [format] => % s [formatIndex] => 0 [fontIndex] => 0 [formatColor] => [xfIndex] => 15) [4] => Array ([xfIndex] => 15 )) [3] => Array ([1] => Array ([string] => 2 [raw] => 2 [rectype] => unknown [format] => % s [formatIndex] => 0 [fontIndex] => 6 [formatColor] => [xfIndex] => 23) [2] => Array ([xfIndex] => 23) [3] => Array ([string] => 33 [raw] => 33 [rectype] => unknown [format] => % s [formatIndex] => 0 [fontIndex] => 6 [formatColor] => [xfIndex] => 23) [4] => Array ([xfIndex] => 23 )) [4] => Array ([1] => Array ([string] => 3 [raw] => 3 [rectype] => unknown [format] => % s [formatIndex] => 0 [fontIndex] => 0 [formatColor] => [xfIndex] => 15) [2] => Array ([xfIndex] => 15) [3] => Array ([string] => 44 [raw] => 44 [rectype] => unknown [format] => % s [formatIndex] => 0 [fontIndex] => 0 [formatColor] => [xfIndex] => 15) [4] => Array ([xfIndex] => 15) [5] => Array ([2] => Array ([xfIndex] => 15) [3] => Array ([xfIndex] => 24 [hyperlink] => Array ([flags] => 23 [desc] => www.phprm.com [link] => http://www.phprm.com ) [1] => Array ([maxrow] => 0 [maxcol] => 0 [numRows] => 0 [numCols] => 0) [2] => Array ([maxrow] => 0 [maxcol] => 0 [numRows] => 0 [numCols] => 0 ))

Address:

Reprinted at will, but please attach the article address :-)