Friday 2 December 2016

PHPExcel - Export Excel file into Mysql Database using Ajax



In this post we are going to learn how to export excel file and insert that excel file data into mysql data by using PHP with Ajax. In this example we will export excel file data into mysql database without page refresh this is because we have use ajax request with jquery for upload excel file in php script. For fetch data from excel file we have use PHPExcel library, by using this PHPExcel library we can fetch data from excel and we can use that data into php script. PHPExcel library is used for read, write or create excel file in php script. If you want to fetch data from any excel document to php then you can use this PHPExcel library. We have use ajax request to upload upload excel file and then after by using this PHPExcel library we have use that excel data in php script. So we have make simple data export system, in this system you can export excel data into mysql database without page refresh. This is because we have use ajax method for upload excel file to sever. For exporting excel data we have use PHPExcel library. By using this library we can smoothly export large amount of excel data to database. This system is very important if you are working on any enterprise level application. In that application you have large amount of data in excel file and you want to export that to mysql database server. Then at that time this type of system is very important. In this we have use ajax method so it will export data to server without page refresh. That means it will export data to server fast as compare to simple php script.

You can download PHPExcel class from this link - https://phpexcel.codeplex.com/releases/view/119187




Souce Code


index.php


 <html>  
      <head>  
           <title>PHPExcel - Export Excel file into Mysql Database using Ajax</title>  
           <script src="https://ajax.googleapis.com/ajax/libs/jquery/2.2.0/jquery.min.js"></script>  
           <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />  
           <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/js/bootstrap.min.js"></script>  
           <style>  
                body  
                {  
                     margin:0;  
                     padding:0;  
                     background-color:#f1f1f1;  
                }  
                .box  
                {  
                     width:900px;  
                     padding:20px;  
                     background-color:#fff;  
                     border:1px solid #ccc;  
                     border-radius:5px;  
                     margin-top:100px;  
                }  
           </style>  
      </head>  
      <body>  
           <div class="container box">  
                <h3 align="center">PHPExcel - Export Excel file into Mysql Database using Ajax</h3>  
                <br /><br />  
                <br /><br />  
                <form mehtod="post" id="export_excel">  
                     <label>Select Excel</label>  
                     <input type="file" name="excel_file" id="excel_file" />  
                </form>  
                <br />  
                <br />  
                <div id="result">  
                </div>  
           </div>  
      </body>  
 </html>  
 <script>  
 $(document).ready(function(){  
      $('#excel_file').change(function(){  
           $('#export_excel').submit();  
      });  
      $('#export_excel').on('submit', function(event){  
           event.preventDefault();  
           $.ajax({  
                url:"export.php",  
                method:"POST",  
                data:new FormData(this),  
                contentType:false,  
                processData:false,  
                success:function(data){  
                     $('#result').html(data);  
                     $('#excel_file').val('');  
                }  
           });  
      });  
 });  
 </script>  

export.php


 <?php  
 //export.php  
 if(!empty($_FILES["excel_file"]))  
 {  
      $connect = mysqli_connect("localhost", "root", "", "testing");  
      $file_array = explode(".", $_FILES["excel_file"]["name"]);  
      if($file_array[1] == "xls")  
      {  
           include("PHPExcel/IOFactory.php");  
           $output = '';  
           $output .= "  
           <label class='text-success'>Data Inserted</label>  
                <table class='table table-bordered'>  
                     <tr>  
                          <th>Customer Name</th>  
                          <th>Address</th>  
                          <th>City</th>  
                          <th>Postal Code</th>  
                          <th>Country</th>  
                     </tr>  
                     ";  
           $object = PHPExcel_IOFactory::load($_FILES["excel_file"]["tmp_name"]);  
           foreach($object->getWorksheetIterator() as $worksheet)  
           {  
                $highestRow = $worksheet->getHighestRow();  
                for($row=2; $row<=$highestRow; $row++)  
                {  
                     $name = mysqli_real_escape_string($connect, $worksheet->getCellByColumnAndRow(1, $row)->getValue());  
                     $address = mysqli_real_escape_string($connect, $worksheet->getCellByColumnAndRow(2, $row)->getValue());  
                     $city = mysqli_real_escape_string($connect, $worksheet->getCellByColumnAndRow(3, $row)->getValue());  
                     $postal_code = mysqli_real_escape_string($connect, $worksheet->getCellByColumnAndRow(4, $row)->getValue());  
                     $country = mysqli_real_escape_string($connect, $worksheet->getCellByColumnAndRow(5, $row)->getValue());  
                     $query = "  
                     INSERT INTO tbl_customer  
                     (CustomerName, Address, City, PostalCode, Country)   
                     VALUES ('".$name."', '".$address."', '".$city."', '".$postal_code."', '".$country."')  
                     ";  
                     mysqli_query($connect, $query);  
                     $output .= '  
                     <tr>  
                          <td>'.$name.'</td>  
                          <td>'.$address.'</td>  
                          <td>'.$city.'</td>  
                          <td>'.$postal_code.'</td>  
                          <td>'.$country.'</td>  
                     </tr>  
                     ';  
                }  
           }  
           $output .= '</table>';  
           echo $output;  
      }  
      else  
      {  
           echo '<label class="text-danger">Invalid File</label>';  
      }  
 }  
 ?>  

tbl_customer


 --  
 -- Table structure for table `tbl_customer`  
 --  
 CREATE TABLE IF NOT EXISTS `tbl_customer` (  
  `CustomerID` int(11) NOT NULL AUTO_INCREMENT,  
  `CustomerName` varchar(250) NOT NULL,  
  `Address` text NOT NULL,  
  `City` varchar(250) NOT NULL,  
  `PostalCode` varchar(30) NOT NULL,  
  `Country` varchar(100) NOT NULL,  
  PRIMARY KEY (`CustomerID`)  
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;  

16 comments:

  1. very good video tutorial! Thanks admin

    ReplyDelete
  2. Thanks, your videos are clear enough, and codes provided are working well.

    ReplyDelete
  3. Thanks for the explanation
    I have blank lines
    Between each line there is a blank line
    How can these empty lines not be lifted?

    ReplyDelete
  4. Fatal error: Uncaught Error: Class 'PHPExcel' not found in E:\xampp\htdocs\feb\uploadexcel\php\PHPExcel\Reader\Excel2007.php:342 Stack trace: #0 E:\xampp\htdocs\feb\uploadexcel\php\PHPExcel\IOFactory.php(192): PHPExcel_Reader_Excel2007->load('E:\\xampp\\tmp\\ph...') #1 E:\xampp\htdocs\feb\uploadexcel\php\export.php(14): PHPExcel_IOFactory::load('E:\\xampp\\tmp\\ph...') #2 {main} thrown in E:\xampp\htdocs\feb\uploadexcel\php\PHPExcel\Reader\Excel2007.php on line 342

    ReplyDelete
  5. PHPExcel - Export Excel file into Mysql Database using Ajax change to

    PHPExcel - Import Excel file into Mysql Database using Ajax

    ReplyDelete
  6. Thanks. This is very helpful. However, How may I insert data in MYSQL with commas

    ReplyDelete
  7. thank you .. its very help full to me

    ReplyDelete
  8. Execellent Code

    ReplyDelete
  9. Fatal error: Uncaught Error: Class 'PHPExcel' not found in C:\xampp\htdocs\stores\admin\PHPExcel\Reader\Excel5.php:625 Stack trace: #0 C:\xampp\htdocs\stores\admin\PHPExcel\IOFactory.php(192): PHPExcel_Reader_Excel5->load('C:\\xampp\\tmp\\ph...') #1 C:\xampp\htdocs\stores\admin\export.php(22): PHPExcel_IOFactory::load('C:\\xampp\\tmp\\ph...') #2 {main} thrown in C:\xampp\htdocs\stores\admin\PHPExcel\Reader\Excel5.php on line 625

    ReplyDelete
  10. auto_awesome
    Langue source : Anglais
    182 / 5000
    Résultats de traduction
    Here is the error displayed to me:
    Warning: include (PHPExcel / IOFactory.php): failed to open stream: No such file or directory in C: \ wamp \ www \ Excel \ export.php on line 9.
    help me please.

    ReplyDelete
  11. auto_awesome
    Langue source : Anglais
    182 / 5000
    Résultats de traduction
    Here is the error displayed to me:
    Warning: include (PHPExcel / IOFactory.php): failed to open stream: No such file or directory in C: \ wamp \ www \ Excel \ export.php on line 9.
    help me please

    ReplyDelete
  12. Warning: include (PHPExcel / IOFactory.php): failed to open stream: No such file or directory in C:

    ReplyDelete