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 ;  

2 comments: