If you want to import Excel data into the MySQL database using Phpexcelreader, please click this article to view it.
Use Phpexcelreader to import Excel data into the MySQL database.
Here we introduce another way to implement importing Excel into MySQL database.
1, to the official website http://phpexcel.codeplex.com/download Phpexcel class library, I currently use is 1.7.9 version.
2, directly on the code.
(1), conn.php file (this does not introduce, you know):
PHP Code copy content to clipboard
- $mysql =mysql_connect ("localhost","root", "root");
- mysql_select_db ("test",$mysql);
- mysql_query ("Set names GBK");
(2), HTML page part: index.php File (Form submission page):
xml/html Code copy content to clipboard
- <form name="Form2" method="POST" enctype="Multipart/form-data" action=" upload_excel.php ">
- <input type="hidden" name="Leadexcel" value="true">
- <table align="center" width="90%" border="0">
- <tr>
- <TD>
- <input type="file" name="Inputexcel"><input type="Submit " name=" import " value=" importing Data>
- </td>
- </tr>
- </table>
- </form>
(3), Form processing Handler section: upload_excel.php File:
PHP Code copy content to clipboard
- Include ("conn.php");
- Include ("function.php");
- if ($_post [' Import ']=="importing Data") {
- $leadExcel =$_post[' leadexcel ');
- if ($leadExcel = = "true")
- {
- //echo "OK";d ie ();
- //Get the uploaded file name
- $filename = $HTTP _post_files['inputexcel '] [' name '];
- //Upload a temporary file name on the server
- $tmp _name = $_files [' Inputexcel '] [' tmp_name '];
- $msg = UploadFile ($filename,$tmp _name);
- echo $msg;
- }
- }
(4), function section: function.php file:
PHP Code copy content to clipboard
- Import an Excel file
- function UploadFile ($file,$filetempname)
- {
- //Upload file storage path of your own settings
- $filePath = ' upfile/';
- $str = "";
- //The path below is modified according to the path you phpexcel
- Set_include_path ('. '). Path_separator. ' E:\php\AppServ\www\91ctcStudy\PHPExcelImportSQl2 \phpexcel '. Path_separator. Get_include_path ());
- require_once ' phpexcel.php ';
- require_once ' phpexcel\iofactory.php ';
- //require_once ' phpexcel\reader\excel5.php ';//excel 2003
- require_once ' phpexcel\reader\excel2007.php '; Excel
- $filename =explode (".",$file); Put the uploaded file name as "." Good to do an array.
- $time =Date ("y-m-d-h-i-s"); To the current upload time
- $filename [0]=$time; Take file name T replace
- $name =implode (".",$filename); //The file name after uploading
- $uploadfile =$filePath. $name; //The file name address after upload
- The //move_uploaded_file () function moves the uploaded file to a new location. Returns true if successful, otherwise false is returned.
- $result =move_uploaded_file ($filetempname,$uploadfile); If uploaded to the current directory
- if ($result) //If the upload file is successful, perform the import Excel operation
- {
- //$objReader = Phpexcel_iofactory::createreader (' Excel5 ');//use excel2003
- $objReader = Phpexcel_iofactory::createreader (' Excel2007 '); Use excel2003 and format
- //$objPHPExcel = $objReader->load ($uploadfile);//This easily causes httpd to collapse.
- $objPHPExcel = phpexcel_iofactory::load ($uploadfile); Just change it.
- $sheet = $objPHPExcel->getsheet (0);
- $highestRow = $sheet->gethighestrow (); //Total number of rows obtained
- $highestColumn = $sheet->gethighestcolumn (); //Get total number of columns
- //Loop through the Excel file, read a line, insert 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 cells
- }
- Explode: function splits a string into arrays.
- $strs =& nbsp;explode ("\ \", $STR);
- Var_dump ($strs);
- Die ();
- $sql =&n BSP; " INSERT into Z_test_importexcel (duty_date,name_am,name_pm) VALUES ('". $strs [0]." ', '". $strs [1]." ', '". $strs [2]." ') ";
- echo $ sql;
- mysql_query ("Set names GBK");//This is the specified database character set, which is usually placed behind the connected database.
- if (! mysql_query ($sql)) {
- & nbsp; return false;
- }
- $str =&n BSP; " ";
- }
- Unlink ($uploadfile); Delete an uploaded Excel file
- $msg = "Import succeeded! ";
- }else{
- $msg = "Import failed! ";
- }
- return $msg;
- }
About this function, refer to HTTP://BLOG.CSDN.NET/GRASSROOTS20 11/article/details/8104604 the blog friend's article, but, the blogger's writing, I think there is a problem , at least, I use the
$objPHPExcel = $objReader->load ($uploadfile);
This sentence, when running, will appear:
or the Phpexcel class library official later upgrade, call method need to correct it, the specific author did not look.
3, through the above steps, the reader will prepare a XLS and xlsx documents separately, the system operation effect:
Attachment Download: Full demo download (Phpexcel class library included) attachment download: Full demo download (Phpexcel class library included)
PHP instance: Importing Excel2003 Documents and Excel2007 documents into the MySQL database using Phpexcel