Friday, 4 March 2016

Read Excel Data and Insert into Mysql Database using PHP




In previous post I had show you how import mysql data into excel sheet but In this post we are going to learn how to import excel data into MySql Database using php. For import from excel to MySql Database I have use PHPExcel Class. PHPExcel library is best library for Export and Import from excel to database and from database to Excel sheet. By using this functionality user can easily transfer excel data to Mysql database table using PHP programming language. In post user can easily read excel sheet data and insert into mysql database table and show on webpage. This functionality is mainly used in enterprise level application and even small application we can use this type of functionality. I hope you will enjoy this post.

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

Source Code

Database Table

 CREATE TABLE IF NOT EXISTS `tbl_excel` (  
  `excel_id` int(11) NOT NULL AUTO_INCREMENT,  
  `excel_name` varchar(250) NOT NULL,  
  `excel_email` varchar(300) NOT NULL,  
  PRIMARY KEY (`excel_id`)  
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;  

index.php



<?php
$connect = mysqli_connect("localhost", "root", "", "test");
$output = '';
if(isset($_POST["import"]))
{
 $extension = end(explode(".", $_FILES["excel"]["name"])); // For getting Extension of selected file
 $allowed_extension = array("xls", "xlsx", "csv"); //allowed extension
 if(in_array($extension, $allowed_extension)) //check selected file extension is present in allowed extension array
 {
  $file = $_FILES["excel"]["tmp_name"]; // getting temporary source of excel file
  include("PHPExcel/IOFactory.php"); // Add PHPExcel Library in this code
  $objPHPExcel = PHPExcel_IOFactory::load($file); // create object of PHPExcel library by using load() method and in load method define path of selected file

  $output .= "<label class='text-success'>Data Inserted</label><br /><table class='table table-bordered'>";
  foreach ($objPHPExcel->getWorksheetIterator() as $worksheet)
  {
   $highestRow = $worksheet->getHighestRow();
   for($row=2; $row<=$highestRow; $row++)
   {
    $output .= "<tr>";
    $name = mysqli_real_escape_string($connect, $worksheet->getCellByColumnAndRow(0, $row)->getValue());
    $email = mysqli_real_escape_string($connect, $worksheet->getCellByColumnAndRow(1, $row)->getValue());
    $query = "INSERT INTO tbl_excel(excel_name, excel_email) VALUES ('".$name."', '".$email."')";
    mysqli_query($connect, $query);
    $output .= '<td>'.$name.'</td>';
    $output .= '<td>'.$email.'</td>';
    $output .= '</tr>';
   }
  } 
  $output .= '</table>';

 }
 else
 {
  $output = '<label class="text-danger">Invalid File</label>'; //if non excel file then
 }
}
?>

<html>
 <head>
  <title>Import Excel to Mysql using PHPExcel in PHP</title>
  <script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.3/jquery.min.js"></script>
  <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/js/bootstrap.min.js"></script>
  <link href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" rel="stylesheet" />
  <style>
  body
  {
   margin:0;
   padding:0;
   background-color:#f1f1f1;
  }
  .box
  {
   width:700px;
   border:1px solid #ccc;
   background-color:#fff;
   border-radius:5px;
   margin-top:100px;
  }
  
  </style>
 </head>
 <body>
  <div class="container box">
   <h3 align="center">Import Excel to Mysql using PHPExcel in PHP</h3><br />
   <form method="post" enctype="multipart/form-data">
    <label>Select Excel File</label>
    <input type="file" name="excel" />
    <br />
    <input type="submit" name="import" class="btn btn-info" value="Import" />
   </form>
   <br />
   <br />
   <?php
   echo $output;
   ?>
  </div>
 </body>
</html>

6 comments:

  1. gets this error pls help me

    Fatal error: Uncaught exception 'PHPExcel_Reader_Exception' with message 'Could not open example.xls for reading! File does not exist.' in C:\xampp\htdocs\task3\PHPExcel\Reader\Excel5.php:443 Stack trace: #0 C:\xampp\htdocs\task3\PHPExcel\IOFactory.php(268): PHPExcel_Reader_Excel5->canRead('example.xls') #1 C:\xampp\htdocs\task3\PHPExcel\IOFactory.php(191): PHPExcel_IOFactory::createReaderForFile('example.xls') #2 C:\xampp\htdocs\task3\index.php(5): PHPExcel_IOFactory::load('example.xls') #3 {main} thrown in C:\xampp\htdocs\task3\PHPExcel\Reader\Excel5.php on line 443

    ReplyDelete
  2. Muito boa dica para resolver seu problema esta na hora de salvar o arquivo em excel pois deve salvar o arquivo como Planilha Microsoft Excel 97-2003 depois verifica na pasta onde voce salvou se o arquivo ficou com a extensão xls e pronto

    ReplyDelete
  3. thanks man for your efforts keep it up

    ReplyDelete