Import Excel to MySQL using Phpexcel

1 . connection.php file that connects to the database

<?php//Modify the following code to join the database//mysql_connect open a connection to the MySQL server, if successful, return a MySQL connection identity, failure returns FALSE. $mysql =mysql_connect ("localhost", "root", "root");  Integer mysql_connect (host, user name, password), mysql_select_db ("test", $mysql);  Boolean mysql_select_db (database name, connection number); mysql_query ("Set names GBK");  This is the specified database character set, which is typically placed behind a connected database?>

　　

2

<?phprequire ("connection.php"); Require ("upload_excel.php");　　if ($leadExcel = = "true") {//Gets the uploaded file name $filename = $HTTP _post_files[' inputexcel ' [' name '];　　Temporary file name uploaded to the server $tmp _name = $_files[' Inputexcel ' [' tmp_name ']; $msg = UploadFile ($filename, $tmp _name);}? ><form method= "POST" enctype= "Multipart/form-data" > <input type= "hidden" name= "Leadexcel" value= "true" > <table align= "center" width= "90%" border= "0" > <tr> <td> <input type= "file" name= "input Excel "><input type=" Submit "value=" Import data "> </td> </tr> </table></form>

　　

3 . Imported upload_excel.php File

<?php

Require ("connection.php");//import Excel File function UploadFile ($file, $filetempname) {//Set the upload file for your own path $filePath = ' upfile/　　‘; 　　$str = ""; The following path modifies Set_include_path ('. ') according to the path you phpexcel. Path_separator. ' D:\xampp\htdocs\XM1\PHPExcel '.　　Path_separator. Get_include_path ());　　Require_once ' phpexcel.php ';　　Require_once ' phpexcel\iofactory.php ';　　Require_once ' phpexcel\reader\excel5.php '; $filename =explode (".", $file);//upload the file name with "."　　Good to do an array. $time =date ("y-m-d-h-i-s");//go to the current upload time $filename [0]= $time;//Take the filename t to replace the $name =implode (".", $filename); The file name after uploading $uploadfile = $filePath. $name;//The file name after uploading the address//move_uploaded_file () function to move the uploaded files to a new location.　　Returns true if successful, otherwise false is returned. $result =move_uploaded_file ($filetempname, $uploadfile);//If uploading to the current directory if ($result)//If the upload file succeeds, do import Excel action {$ objreader = Phpexcel_iofactory::createreader (' Excel5 ');//use excel2007 for format $objPHPExcel = $objReader->lo　　　　AD ($uploadfile);　　　　$sheet = $objPHPExcel->getsheet (0); $highestRow = $sheet->gethighestrow ();　　　　Total number of rows obtained$highestColumn = $sheet->gethighestcolumn ();　　　　　　Gets the total number of columns//loops to read an Excel file, reads a bar, inserts a for ($j =2; $j <= $highestRow; $j + +) {for ($k = ' A '; $k <= $highestColumn; $k + +) {$str. = Iconv (' utf-8 ', ' GBK ', $objPHPExcel->getactivesheet ()->getcell ("$k $j")->getvalue ()). '　　　　　　\ \ ';//Read Cell}//explode: The function splits the string into arrays.　　　　　　$strs = explode ("\ \", $STR); $sql = "INSERT into Tab_zgjx_award (jx_name,jx_degree,jx_item_name,jx_unit,dy_originator,de_originator,xm_intro,hj_　　　　　　　　item_id) VALUES (' ". $strs [0]. "', '". Prize name $STRS [1]. "', '". Awards session $strs [2]. "', '". The award-winning project name $STRS [3]. "', '". Award-winning Unit $strs [4]. "', '". The first inventor $strs [5]. "', '". The second inventor $strs [6]. "', '". Project brief $strs [7]. "')"; The winning item number mysql_query ("Set names GBK");//This is the specified database character set, which is typically placed behind the connected database and tied if (!mysql_query ($sql)) {return F　　　　　　Alse;　　　　} $str = ""; } unlink ($uploadfile); Delete the uploaded excel file $msg = "Import successfully!　　"; } else {$msg = "Import failed!　　"; }return $msg;}? >

　　

Import Excel to MySQL using Phpexcel