PHP example: Use PHPExcel to import Excel2003 documents and Excel2007 documents into the MySQL database, _PHP tutorial

PHP Example: Use PHPExcel to import Excel2003 documents and Excel2007 documents into the MySQL database.

If you want to use phpExcelReader to import Excel data into the mysql database, please click on this article to view it.

Use phpExcelReader to import Excel data into mysql database.

Below we introduce another method to import Excel to MySQL database.

1. Go to the official website http://phpexcel.codeplex.com/ to download the PHPExcel class library. I am currently using version 1.7.9. As shown in the picture:

2. Upload the code directly.

(1), conn.php file (no need to introduce this, you know it):

PHP CodeCopy content to clipboard

1. $mysql=mysql_connect("localhost","root","root");
2. mysql_select_db("test",$mysql);
3. mysql_query("set names GBK");

(2), HTML page part: index.php file (form submission page):

XML/HTML CodeCopy content to clipboard

1. form name="form2" method="post" enctype="multipart/form-data" action="upload_excel.php"> input type="hidden" name="leadExcel" value="true">
2. table align="center" width="90% " border="0"> ;
3. tr> td
4. >  input
5. type="file" name="inputExcel">input
type=
6. "submit" name="import" value="Import data"> td>
tr
7. >
table
8. >
form
9. > (3), form processing handler part: upload_excel.php file:
PHP CodeCopy content to clipboard
1. include("conn.php");
2. include("function.php");
4. if($_POST ['import']=="Import data"){
6. $leadExcel=$_POST['leadExcel'];
8. if($leadExcel == "true")
9. {
10. //echo "OK";die();
11. //Get the uploaded file name
12.  $filename = $HTTP_POST_FILES['inputExcel'] ['name']; 
13. //The name of the temporary file uploaded to the server
14.  $tmp_name = $_FILES ['inputExcel']['tmp_name']; 
15.       
16.  $msg = uploadFile($filename,$tmp_name); 
17. echo $msg;
18. }
19. }

(4), function part: function.php file:

PHP CodeCopy content to clipboard
1. //Import Excel file
2. function uploadFile($file,$filetempname)
3. {
4. //The upload file storage path set by yourself
5. $filePath = 'upFile/';
6. $str = "";
7. //The path below should be modified according to your PHPExcel path
8. set_include_path('.'.PATH_SEPARATOR .'E:phpAppServwww91ctcStudyPHPExcelImportSQl2 PHPExcel' .PATH_SEPARATOR .get_include_path());
9.  
10. require_once 'PHPExcel.php';
11. require_once 'PHPExcelIOFactory.php';
12. //require_once 'PHPExcelReaderExcel5.php';//excel 2003
13.  require_once 'PHPExcelReaderExcel2007.php';//excel 2007 
15.  $filename=explode(".",$file);//Make the uploaded file name into an array based on ".". 
16. $time=date("y-m-d-H-i- s");//Go to the current upload time
17. $filename [0]=$time;//Replace with file name t
18. $name=implode (".",$filename); //Uploaded file name
19.  $uploadfile=$filePath.$name;//Uploaded file name address 
22. //move_uploaded_file() function moves the uploaded file to a new location. If successful, return true, otherwise return false.
23. $result=move_uploaded_file($filetempname,$uploadfile);//If uploaded to the current directory
24. if($result) //If the file upload is successful, execute the import excel operation
25. {
26. // $objReader = PHPExcel_IOFactory::createReader('Excel5');//use excel2003
27.  $objReader = PHPExcel_IOFactory::createReader('Excel2007');//use excel2003 and 2007 format 
28. // $objPHPExcel = $objReader->load($uploadfile); //This can easily cause httpd to crash
29. $objPHPExcel = PHPExcel_IOFactory::load($uploadfile);//Just change it to this way of writing
31. $sheet = $objPHPExcel- >getSheet(0);
32. $highestRow = $sheet- >getHighestRow(); // Get the total number of rows
33. $highestColumn = $sheet- >getHighestColumn(); // Get the total number of columns
35. //Loop through the excel file, read one item, insert one item
36.  for($j=2;$j$highestRow;$j) 
10.                                                    
 
11. for($k='A';$k$highestColumn;$k ) 
                                                                    
12.            
$str .= iconv(
13. 'utf-8','gbk',$objPHPExcel->getActiveSheet()->getCell("$k $j")->getValue()).'\';//Read cell
                                                               
14. //explode: Function splits a string into an array.
15. $strs =& ​​nbsp;explode("\",$str);
16.                                                   
//var_dump ($strs);
//die();
               $sql =&n bsp;"INSERT INTO z_test_importexcel(duty_date,name_am,name_pm) VALUES ('
19. ".$strs[0]."','".$ strs[1]."','".$strs[2]."')"; 🎜> //echo $ sql; mysql_query ("set names GBK");//This is the specified database character set, usually placed after connecting to the database
20. if(! mysql_query($sql)){
21. & nbsp; return false;
22.                                                                       $str =&n bsp;"
";
24. }  
25.   unlink ($uploadfile); //Delete the uploaded excel file
26. $msg = "Import successful!
";
28. }else{
29. $msg = "Import failed!"; }
30. return $msg;
31. }
32. Regarding this function, I refer to the blogger’s article at http://blog.csdn.net/grassroots20 11/article/details/8104604. However,
I think there is a problem with the blogger’s writing method
33. , at least, what I use $objPHPExcel = $objReader->load ($uploadfile);
34. This sentence will appear when running:

Perhaps the PHPExcel class library has been officially upgraded later, and the calling method needs to be corrected. The author has not studied the details.

3. Through the above steps, readers can prepare an xls and xlsx document respectively. The system operation effect is:

Attachment download: Complete DEMO download (with PHPExcel class library) Attachment download: Complete DEMO download (with PHPExcel class library)

http://www.bkjia.com/PHPjc/1048745.html

www.bkjia.com

true

http: //www.bkjia.com/PHPjc/1048745.html

TechArticle

PHP Example: Use PHPExcel to import Excel2003 documents and Excel2007 documents into the MySQL database. If you want to use phpExcelReader to import Excel data To the mysql database, please click on this article...