PHP Import execl table to database
/** * Upload file */function Uploadfileforexcel () {//allow upload of image suffix//$allowedExts = Array ("GIF", "jpeg "," JPG "," png "," xls "); $allowedExts = Array ("XLS", "xlsx"); $temp = Explode (".", $_files["file" ["name"]); echo $_files["File" ["Size"]; $extension = End ($temp); Get file suffix if ($_files["file" ["Size"] < 204800 && In_array ($extension, $allowedExts)) {//Less than KB if ($_files["file" ["error"] > 0) {echo "Error::". $_files["File" ["Error"]. "
"; Return ""; } else {///To determine if the upload directory in the current directory exists for the file//if there is no upload directory, you need to create it, upload directory permission is 777 if (File_exists (DirName (DirName (dirname (dirname))). Directory_separator. "UploadFile". Directory_separator. $_files["File" ["Name"])) {echo $_files["file" ["Name"]. "The file already exists. "; } else {//If the upload directory does not exist, upload the file to the upload directory Move_uploaded_file ($_files["file"] [ "Tmp_name"], DirName (DirName (dirname (dirname))). Directory_separator. "UploadFile". Directory_separator. $_files["File" ["name"]); Return DirName (DirName (DirName (dirname (__file__))). Directory_separator. "UploadFile". Directory_separator. $_files["File" ["Name"]; }}} else {echo "illegal file format"; Return "";//illegal file format}/** * get execl table data */function Getexecldata () { First, import the Phpexcel require_once (dirname (DirName (dirname (dirname))). '/api/phpexcel/classes/phpexcel.php '); $filePath = $this->uploadfileforexcel (); if ($filePath = = NULL | | $filePath = = ') {return; }//Create Reader Object $PHPReader = new phpexcel_reader_excel2007 (); if (! $PHPReader->canread ($filePath)) {$PHPReader = new Phpexcel_reader_excel5 (); if (! $PHPReader->canread ($filePath)) {echo ' no Excel '; Return }}//Create an Excel object, at which point you can read the file through an Excel object, or write it to a file $PHPExcel = $PHPReader->load ($filePath); /** read the first worksheet in an Excel file */$currentSheet = $PHPExcel->getsheet (0); /** gets the largest column number */$allColumn = $currentSheet->gethighestcolumn (); /** get total number of lines */$allRow = $currentSheet->gethighestrow (); Echo $allColumn. " -- " . $allRow. "
"; Iterates through the contents of each cell. Note that the row starts at 1 and the column starts with a for ($rowIndex = 2; $rowIndex <= $allRow; $rowIndex + +) {$data = array (); for ($colIndex = ' A '; $colIndex <= ' N '; $colIndex + +) {$addr = $colIndex. $rowIndex; $cell = $currentSheet->getcell ($addr)->getvalue (); Array_push ($data, $cell); } var_dump ($data); $this->updataforexcel ($data); } unlink ($filePath); /** * Update database based on EXECL data * @param array $data * $data [0]-Name Customer name * $data [1]--sex gender * $data [2]-Cellphone contact * $data [3]-Knowchannel cognitive pathway * $data [4]-intent_size required Area * $data [5]-intent_huxing Demand type * $data [6]-prices_reflect price Reflect * $data [7]--INTENT_DESC Home Purpose * $data [8]-Focus_desc focus * $data [9]-Nofocus_desc not recognized points * $data [ten]--buytime Number of home purchase * $data [11]--> Locdesc Living Area * $data [+]-Intent_level Intent level * $data [+]-Note NOTE */fun Ction Updataforexcel ($data = Array ()) {if (count ($data) = = 0) {return; } $cellphone = $data [2]; if (Isset ($cellphone)) {$info = $this->useinfo_tag_db->get_one ("cellphone = $cellphone"); $settime = time (); if (null! = $info) {//original data exists, modified $sql = "Update useinfo_tag set name= ' $data [0] ', sex= ' $data [1] ', knowchannel= ' $ Data[3] ', '; $sql. = "Intent_size= ' $data [4] ', intent_huxing= ' $data [5] ', prices_reflect= ' $data [6] ',"; $sql. = "Intent_desc= ' $data [7] ', focus_desc= ' $data [8] ', nofocus_desc= ' $data [9] ',"; $sql. = "Buytime= ' $data [ten] ', locdesc= ' $data [one] ', intent_level= ' $data [12] ',"; $sql. = "Note= ' $data [] ', settime= $settime"; $sql. = "where cellphone = ' $cellphone '"; $result = $this->useinfo_tag_dB->query ($sql); if ($result) {echo "modified successfully"; } else {echo "modification failed"; }} else {//no current data, insert new data $sql = "INSERT INTO Useinfo_tag (name,sex,cellphone,knowchannel,intent_s Ize,intent_huxing, "; $sql. = "Prices_reflect,intent_desc,focus_desc,nofocus_desc,buytime,locdesc,intent_level,note,settime)"; $sql. = "VALUES (' $data [0] ', ' $data [1] ', ' $data [2] ', ' $data [3] ', ' $data [4] ', ' $data [5] ', ' $data [6] ', '; $sql. = "' $data [7] ', ' $data [8] ', ' $data [9] ', ' $data [] ', ' $data [one] ', ' $data [] ', ' $data [] ', $settime)"; $result = $this->useinfo_tag_db->query ($sql); if ($result) {echo "Insert succeeded"; } else {echo "Insert failed"; }} echo "
"; } }
HTML Part code:
$ ("#upfileSubmit"). Click (() { options = { beforesend() { //console.log (" start "); $ (' #container '). CSS ("Display""block") ;} , Success (data) { //console.log (" end "); $ (' #container '). CSS ("Display""None") ; window. location. Reload ();} } $ ("#form_file"). Ajaxsubmit (options) ;}) ;
The above on the introduction of the sword of the data how to import PHP import execl table to the database, including the Spirit pinch people data How to import content, I hope that the PHP tutorial interested in a friend helpful.