PHP instance: Importing Excel2003 Documents and Excel2007 documents into the MySQL database using Phpexcel

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

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 Code copy content to clipboard

2. <form name="Form2" method="POST" enctype="Multipart/form-data" action=" upload_excel.php ">
4. <input type="hidden" name="Leadexcel" value="true">
6. <table align="center" width="90%" border="0">
8. <tr>
10. <TD>
12. <input type="file" name="Inputexcel"><input type="Submit " name=" import " value=" importing Data>
14. </td>
16. </tr>
18. </table>
20. </form>

(3), Form processing Handler section: upload_excel.php File:

PHP Code copy content to clipboard

2. Include ("conn.php");
4. Include ("function.php");
8. if ($_post [' Import ']=="importing Data") {
12. $leadExcel =$_post[' leadexcel ');
16. if ($leadExcel = = "true")
18. {
20. //echo "OK";d ie ();
22. //Get the uploaded file name
24. $filename = $HTTP _post_files['inputexcel '] [' name '];
26. //Upload a temporary file name on the server
28. $tmp _name = $_files [' Inputexcel '] [' tmp_name '];
32. $msg = UploadFile ($filename,$tmp _name);
34. echo $msg;
36. }
38. }

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

PHP Code copy content to clipboard

2. Import an Excel file
4. function UploadFile ($file,$filetempname)
6. {
8. //Upload file storage path of your own settings
10. $filePath = ' upfile/';
12. $str = "";
14. //The path below is modified according to the path you phpexcel
16. Set_include_path ('. '). Path_separator. ' E:\php\AppServ\www\91ctcStudy\PHPExcelImportSQl2 \phpexcel '.    Path_separator. Get_include_path ());
20. require_once ' phpexcel.php ';
22. require_once ' phpexcel\iofactory.php ';
24. //require_once ' phpexcel\reader\excel5.php ';//excel 2003
26. require_once ' phpexcel\reader\excel2007.php '; Excel
30. $filename =explode (".",$file); Put the uploaded file name as "."    Good to do an array.
32. $time =Date ("y-m-d-h-i-s"); To the current upload time
34. $filename [0]=$time; Take file name T replace
36. $name =implode (".",$filename); //The file name after uploading
38. $uploadfile =$filePath. $name; //The file name address after upload
44. The //move_uploaded_file () function moves the uploaded file to a new location.   Returns true if successful, otherwise false is returned.
46. $result =move_uploaded_file ($filetempname,$uploadfile); If uploaded to the current directory
48. if ($result) //If the upload file is successful, perform the import Excel operation
50. {
52. //$objReader = Phpexcel_iofactory::createreader (' Excel5 ');//use excel2003
54. $objReader = Phpexcel_iofactory::createreader (' Excel2007 '); Use excel2003 and format
56. //$objPHPExcel = $objReader->load ($uploadfile);//This easily causes httpd to collapse.
58. $objPHPExcel = phpexcel_iofactory::load ($uploadfile); Just change it.
62. $sheet = $objPHPExcel->getsheet (0);
64. $highestRow = $sheet->gethighestrow (); //Total number of rows obtained
66. $highestColumn = $sheet->gethighestcolumn (); //Get total number of columns
70. //Loop through the Excel file, read a line, insert a
72. For ($j =2; $j <=$highestRow; $j + +)
74. {
76. For ($k =' A '; $k <=$highestColumn; $k + +)
78. {
80. $str. = Iconv (' utf-8 ',' GBK ',$objPHPExcel->getactivesheet ()->getcell ("$k $j") GetValue ()). ' \ \ ';//Read cells
82. }
84. Explode: function splits a string into arrays.
86. $strs =& nbsp;explode ("\ \", $STR);
90. Var_dump ($strs);
92. Die ();
94. $sql =&n BSP; " INSERT into Z_test_importexcel (duty_date,name_am,name_pm) VALUES ('". $strs [0]." ', '". $strs [1]." ', '". $strs [2]." ')       ";
96. echo $ sql;
98. mysql_query ("Set names GBK");//This is the specified database character set, which is usually placed behind the connected database.
100. if (! mysql_query ($sql)) {
102. & nbsp; return false;
104. }
106. $str =&n BSP; "  ";
108. }
112. Unlink ($uploadfile); Delete an uploaded Excel file
114. $msg = "Import succeeded!  ";
116. }else{
118. $msg = "Import failed! ";
120. }
122. return $msg;
124. }

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