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