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

Source: Internet
Author: User
Tags explode vars

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
  1. <form name="Form2" method="POST" enctype="Multipart/form-data" action=" upload_excel.php ">
  2. <input type="hidden" name="Leadexcel" value="true">
  3. <table align="center" width="90%" border="0">
  4. <tr>
  5. <TD>
  6. <input type="file" name="Inputexcel"><input type="Submit " name=" import " value=" importing Data>
  7. </td>
  8. </tr>
  9. </table>
  10. </form>

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

PHP Code copy content to clipboard
  1. Include ("conn.php");
  2. Include ("function.php");
  3. if ($_post [' Import ']=="importing Data") {
  4. $leadExcel =$_post[' leadexcel ');
  5. if ($leadExcel = = "true")
  6. {
  7. //echo "OK";d ie ();
  8. //Get the uploaded file name
  9. $filename = $HTTP _post_files['inputexcel '] [' name '];
  10. //Upload a temporary file name on the server
  11. $tmp _name = $_files [' Inputexcel '] [' tmp_name '];
  12. $msg = UploadFile ($filename,$tmp _name);
  13. echo $msg;
  14. }
  15. }

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

PHP Code copy content to clipboard
  1. Import an Excel file
  2. function UploadFile ($file,$filetempname)
  3. {
  4. //Upload file storage path of your own settings
  5. $filePath = ' upfile/';
  6. $str = "";
  7. //The path below is modified according to the path you phpexcel
  8. Set_include_path ('. '). Path_separator. ' E:\php\AppServ\www\91ctcStudy\PHPExcelImportSQl2 \phpexcel '.    Path_separator. Get_include_path ());
  9. require_once ' phpexcel.php ';
  10. require_once ' phpexcel\iofactory.php ';
  11. //require_once ' phpexcel\reader\excel5.php ';//excel 2003
  12. require_once ' phpexcel\reader\excel2007.php '; Excel
  13. $filename =explode (".",$file); Put the uploaded file name as "."    Good to do an array.
  14. $time =Date ("y-m-d-h-i-s"); To the current upload time
  15. $filename [0]=$time; Take file name T replace
  16. $name =implode (".",$filename); //The file name after uploading
  17. $uploadfile =$filePath. $name; //The file name address after upload
  18. The //move_uploaded_file () function moves the uploaded file to a new location.   Returns true if successful, otherwise false is returned.
  19. $result =move_uploaded_file ($filetempname,$uploadfile); If uploaded to the current directory
  20. if ($result) //If the upload file is successful, perform the import Excel operation
  21. {
  22. //$objReader = Phpexcel_iofactory::createreader (' Excel5 ');//use excel2003
  23. $objReader = Phpexcel_iofactory::createreader (' Excel2007 '); Use excel2003 and format
  24. //$objPHPExcel = $objReader->load ($uploadfile);//This easily causes httpd to collapse.
  25. $objPHPExcel = phpexcel_iofactory::load ($uploadfile); Just change it.
  26. $sheet = $objPHPExcel->getsheet (0);
  27. $highestRow = $sheet->gethighestrow (); //Total number of rows obtained
  28. $highestColumn = $sheet->gethighestcolumn (); //Get total number of columns
  29. //Loop through the Excel file, read a line, insert a
  30. For ($j =2; $j <=$highestRow; $j + +)
  31. {
  32. For ($k =' A '; $k <=$highestColumn; $k + +)
  33. {
  34. $str. = Iconv (' utf-8 ',' GBK ',$objPHPExcel->getactivesheet ()->getcell ("$k $j") GetValue ()). ' \ \ ';//Read cells
  35. }
  36. Explode: function splits a string into arrays.
  37. $strs =& nbsp;explode ("\ \", $STR);
  38. Var_dump ($strs);
  39. Die ();
  40. $sql =&n BSP; " INSERT into Z_test_importexcel (duty_date,name_am,name_pm) VALUES ('". $strs [0]." ', '". $strs [1]." ', '". $strs [2]." ')       ";
  41. echo $ sql;
  42. mysql_query ("Set names GBK");//This is the specified database character set, which is usually placed behind the connected database.
  43. if (! mysql_query ($sql)) {
  44. & nbsp; return false;
  45. }
  46. $str =&n BSP; "  ";
  47. }
  48. Unlink ($uploadfile); Delete an uploaded Excel file
  49. $msg = "Import succeeded!  ";
  50. }else{
  51. $msg = "Import failed! ";
  52. }
  53. return $msg;
  54. }

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.