PHP Excel class read Excel file and Import Database _php tutorial

In general, if we're going to import data from an Excel database into a MySQL database, we have nothing to do with it, but it's easy to get out of control with PHP Excel.

This code has been used in thinkphp.

1. Introduction of Classes

The code is as follows

Copy Code

Vendor (' Phpexcel.phpexcel ');//introduce an extension class. 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 $objPHPExcel = $objReader->load ($excel _file);//$uploadfile $sheet = $objPHPExcel->getsheet (0); $highestRow = $sheet->gethighestrow (); Total number of rows obtained $highestColumn = $sheet->gethighestcolumn (); Total number of columns obtained $arr _result = Array (); $strs =array (); $strs _datas=array (); $SUCC _result=0; $error _result=0;

It looks a little messy, let me write a complete class.

The code is as follows

Copy Code

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

Php-excel-reader two important ways to manipulate Excel:

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

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

2. Save the Excel data in an array, using $data->sheets, print as follows:

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] = = Study No. ) [2] = = Array ( [1] = 1 [2] = red [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.bKjia.c0m [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 ) )

http://www.bkjia.com/PHPjc/630697.html www.bkjia.com true http://www.bkjia.com/PHPjc/630697.html techarticle in general, if we're going to import data from an Excel database into a MySQL database, we have nothing to do with it, but it's easy to get out of control with PHP Excel. This generation ...

