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_db");  
 include ("PHPExcel/IOFactory.php");  
 $html="<table border='1'>";  
 $objPHPExcel = PHPExcel_IOFactory::load('example.xls');  
 foreach ($objPHPExcel->getWorksheetIterator() as $worksheet)   
 {  
      $highestRow = $worksheet->getHighestRow();  
      for ($row=2; $row<=$highestRow; $row++)  
      {  
           $html.="<tr>";  
           $name = mysqli_real_escape_string($connect, $worksheet->getCellByColumnAndRow(0, $row)->getValue());  
           $email = mysqli_real_escape_string($connect, $worksheet->getCellByColumnAndRow(1, $row)->getValue());  
           $sql = "INSERT INTO tbl_excel(excel_name, excel_email) VALUES ('".$name."', '".$email."')";  
           mysqli_query($connect, $sql);  
           $html.= '<td>'.$name.'</td>';  
           $html .= '<td>'.$email.'</td>';  
           $html .= "</tr>";  
      }  
 }  
 $html .= '</table>';  
 echo $html;  
 echo '<br />Data Inserted';  
 ?>  

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