The PHP Excel class reads the Excel file and imports the database

This code is used in thinkphp.

1. Introducing the Class

The code is as follows

Copy Code

Vendor (' Phpexcel.phpexcel ');//The extension class is introduced. Yes/         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 (); Gets the total number of rows $highestColumn = $sheet->gethighestcolumn ();/////////$arr _result = Array (), $strs =array () $ Strs_datas=array (); $SUCC _result=0; $error _result=0;

It looks a bit messy, I'll write a complete class below

The code is as follows

Copy Code

<?php Set_time_limit (20000); Ini_set (' Memory_limit ', '-1 '); Require_once './phpexcel.php '; Require_once './phpexcel/iofactory.php '; Require_once './phpexcel/reader/excel5.php '; Connecting 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 (); Get total number of rows $highestColumn = $sheet->gethighestcolumn (); Total number of columns obtained For ($j =1 $j <=10; $j + +) { $student _no = $objPHPExcel->getactivesheet ()->getcell ("A". $j)->getvalue ();//First row number $name = $objPHPExcel->getactivesheet ()->getcell ("B". $j)->getvalue ();//second column name $gid = $objPHPExcel->getactivesheet ()->getcell ("C". $j)->getvalue ();//third-column GID } Inserts the obtained Excel content into the database $stmt->execute (); ?>

Php-excel-reader to manipulate two important methods in Excel:

1.dump (), which can output Excel content in HTML format:

echo $data->dump (true,true);

2. Put the Excel data into the array, using $data->sheets, print the following:

The code is as follows

Copy Code

Array ( [0] => Array ( [MaxRow] => 0 [Maxcol] => 0 [NumRows] => 5 [Numcols] => 4 [Cells] => Array ( [1] => Array ( [1] => number [2] => name [3] => age [4] => ) [2] => Array ( [1] => 1 [2] => Xiao Hong [3] => 22 [4] => a1000 ) [3] => Array ( [1] => 2 [2] => Xiao Wang [3] => 33 [4] => a1001 ) [4] => Array ( [1] => 3 [2] => small black [3] => 44 [4] => a1002 ) [5] => Array ( [2] => by [3] => www.phpddt.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.111cn.net [link] => http://www.phpddt.co ) ) ) ) ) [1] => Array ( [MaxRow] => 0 [Maxcol] => 0 [NumRows] => 0 [Numcols] => 0 ) [2] => Array ( [MaxRow] => 0 [Maxcol] => 0 [NumRows] => 0 [Numcols] => 0 ) )