How to import data into MySQL database by PHP Excel

This article mainly introduces the PHP Excel import data to MySQL database method, has a certain reference value, now share to everyone, the need for friends can refer to

Data export already have, how can no data import, also use TP5 frame, first need to download Phpexcel.zip, put in third party class library directory vendor directory, then have a page lets you choose to import the Excel file, and then click the Import button Call interface:

The data in my template is as follows:

The back-end interface code is as follows:

    Public Function Uplexcel (Request $request) {if (!empty ($_files[' Excel ' [' name '])) {$fileName =    $_files[' Excel ' [' name '];    Get the full name of the file $dotArray = Explode ('. ', $fileName);            Divide the file name into an array $type = end ($dotArray);                if ($type! = "xls" && $type! = "xlsx") {$ret [' res '] = "0";                $ret [' msg '] = "Not an Excel file, please re-upload!";            Return Json_encode ($ret); }//Take the last element of the array and get the file type $uploaddir = "... /uploads2/". Date ("y-m-d").            '/';//Set File Save directory Note contains if (!file_exists ($uploaddir)) {mkdir ($uploaddir, 0777, true); } $path = $uploaddir. MD5 (Uniqid (rand ())). '.' . $type; Generate random filename//$path = "images/". $fileName; The file name uploaded by the client;//The following must be tmp_name because it is moved from the temporary folder Move_uploaded_file ($_files[' Excel ' [' Tmp_name '], $path);            Copy the temporary files from the server to the appropriate folder $file _path = $path;  if (!file_exists ($path)) {              $ret [' res '] = "0"; $ret [' msg '] = "Upload file Lost!".                $_files[' Excel ' [' Error '];            Return Json_encode ($ret);            }//File extension $ext = Strtolower (PathInfo ($path, pathinfo_extension));                if ($ext = = ' xlsx ') {$objReader = \phpexcel_iofactory::createreader (' Excel2007 ');            $objPHPExcel = $objReader->load ($file _path, ' utf-8 ');                } elseif ($ext = = ' xls ') {$objReader = \phpexcel_iofactory::createreader (' Excel5 ');            $objPHPExcel = $objReader->load ($file _path, ' utf-8 ');            } $sheet = $objPHPExcel->getsheet (0); $highestRow = $sheet->gethighestrow (); Total number of rows obtained $highestColumn = $sheet->gethighestcolumn ();            Gets the total number of columns $ar = Array ();            $i = 0;            $importRows = 0;                for ($j = 2; $j <= $highestRow; $j + +) {$importRows + +; $realName = (string) $objPHPExCel->getactivesheet ()->getcell ("A$j")->getvalue ();//realname $phone to import = (string) $objPHPExcel-&G   T;getactivesheet ()->getcell ("B$j")->getvalue (); Phone $company = (string) to be imported $objPHPExcel->getactivesheet ()->getcell ("C$j")->getvalue ();     Company $job to import (string) $objPHPExcel->getactivesheet ()->getcell ("D$j")->getvalue ();   Job to import $email = (string) $objPHPExcel->getactivesheet ()->getcell ("E$j")->getvalue (); Email to import $ret [' mdata '] = $this->addmemb ($phone, $realName, $company, $job, $email);//This is my database. Add Operation definition A way to correspond to replace with own if ($ret [' Mdata '] &&!is_bool ($ret [' Mdata ')) {$ar [$i] = $ret ['                    Mdata '];                $i + +;                }} if ($i > 0) {$ret [' res '] = "0";                $ret [' errnum '] = $i;                $ret [' allnum '] = $importRows; $ret [' SuCNum '] = $importRows-$i;                $ret [' mdata '] = $ar;                $ret [' msg '] = "import complete!";            Return Json_encode ($ret);            } $ret [' res '] = "1";            $ret [' allnum '] = $importRows;            $ret [' errnum '] = 0;            $ret [' sucnum '] = $importRows;            $ret [' mdata '] = "import succeeded!";        Return Json_encode ($ret);            } else {$ret [' res '] = "0";            $ret [' msg '] = "failed to upload file!";        Return Json_encode ($ret); }    }

The above is the whole content of this article, I hope that everyone's learning has helped, more relevant content please pay attention to topic.alibabacloud.com!