PHP Import Excel File Data Into MySql Database Using PHPExcel Library

In this post, we will show you how you can import Excel file data to MySql Database using PHPExcel Library.

Nowadays, in web development data import is an important functionality and most of the website user wants that his spreadsheet data stores in database with more manageable form. For achieve this result we are using PHPExcel library to import a excel sheet into a MySql database with PHP code.

Before starts code, here we need some initial steps

First, we need to install PHPExcel library to read excel data. We can install it using Composer. If your system is not installed composer, then go to official website https://getcomposer.org/

After installed composer open cmd and write command on cmd-

				
					C:\>Users>composer

If Above screen show in cmd than composer successfully installed.

Now in command prompt go to the your project directory where you want to install PHPExcel Libaray and run command

				
					D:\XAMPP\htdocs\example\excel_to_mysql>composer require phpoffice/phpexcel

After this, you can see that Composer has downloaded all libraries under the ‘vendor‘ directory of your project root. Now create a folder under root folder for upload files “Folder name is files_upload“. Here we show you folder structure.

CREATE EXCEL FILE

we create a sample Excel file.

Now, we will create a table in MySql Database.

				
					CREATE TABLE IF NOT EXISTS `emp_records` ( `Id` int(11) NOT NULL AUTO_INCREMENT, `emp_id` int(11) NOT NULL, `emp_name` varchar(100) NOT NULL, `salary` varchar(11) NOT NULL, `department` varchar(100) NOT NULL, PRIMARY KEY (`Id`) ) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=latin1

Now, we will create connection.php for stable connection with Database.

				
					<?php
  error_reporting(1);
  $mysql_hostname = "localhost"; # Enter Host name
  $mysql_user     = "root"; # Enter user name
  $mysql_password = ""; # Enter Password
  $mysql_database = "DATABASE_NAME"; # Enter Database name
  $bd = ($GLOBALS["___mysqli_ston"] = mysqli_connect($mysql_hostname,  $mysql_user,  $mysql_password)) or die("Opps some thing went wrong");
  mysqli_select_db( $bd, $mysql_database) or die("wrong");
?>

Now, we will create index.php file. where we create html and php code for upload and insert excel data into MySql table.

Below is code:.

				
					<?php
  // Include database connection file
  include("connection.php");
  if(isset($_POST['submit'])) 
  {
    if(isset($_FILES['File']['name']) && $_FILES['File']['name'] != "") 
    {
      $allowedExtensions = array("xls","xlsx");
      $ext = pathinfo($_FILES['File']['name'], PATHINFO_EXTENSION);
        if(in_array($ext, $allowedExtensions)) 
        {
        
          $file = "files_upload/".$_FILES['File']['name'];
          $isUploaded = copy($_FILES['File']['tmp_name'], $file);
        
          if($isUploaded) 
          {
            // Include PHPExcel files 
            
            require_once ('./vendor/autoload.php');
            require_once('./vendor/phpoffice/phpexcel/Classes/PHPExcel/IOFactory.php');
            try {
                  // load uploaded file
                  $objPHPExcel = PHPExcel_IOFactory::load($file);
                } catch (Exception $e) {
                  die('Error loading file "' . pathinfo($file, PATHINFO_BASENAME). '": ' . $e->getMessage());
                }
                    
              
                $sheet      = $objPHPExcel->getSheet(0);
                $total_rows = $sheet->getHighestRow();
                $highestColumn      = $sheet->getHighestColumn(); 
                $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);   
          
              
                for ($row = 2; $row <= $total_rows; ++ $row) {
                  for ($col = 0; $col < $highestColumnIndex; ++ $col) {
                    $cell = $sheet->getCellByColumnAndRow($col, $row);
                    $val = $cell->getValue();
                    $records[$row][$col] = $val;
                  }
                }
                
                foreach($records as $row)
                {
                  $rollno = isset($row[0]) ? $row[0] : '';
                  $name = isset($row[1]) ? $row[1] : '';
                  $age = isset($row[2]) ? $row[2] : '';
                  $program = isset($row[3]) ? $row[3] : '';
                  
                  // Insert into database
                  $query = "INSERT INTO emp_records (emp_id,emp_name,salary,department) 
                      values('".$rollno."','".$name."','".$age."','".$program."')";
                  mysqli_query($GLOBALS["___mysqli_ston"], $query);   
                }
                
                echo "Data inserted in Database";
                unlink($file);
           } else {
                    echo "File not uploaded!";
                }
        } else {
            echo "Please upload excel sheet.";
        }
    } else {
        echo "Please upload excel file.";
    }
  }
?>
  <!DOCTYPE html>
  <html>
  <head>
    <title>PHP Import Excel File Data Into MySql Database Using PHPExcel Library</title>
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/css/bootstrap.min.css">
  </head>
  <body>
  <div class="container">
    <h1>Upload Excel File</h1>
    <form method="POST" action="" enctype="multipart/form-data">
      <div class="form-group">
        <label>Upload File</label>
        <input type="file" name="File" id="File" class="form-control" accept="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" />
      </div>
      <div class="form-group">
        <button type="submit" name="submit" class="btn btn-success">Upload</button>
      </div>
    </form>
  </div>
  </body>
  </html>

After successfully run the code. Excel data inserted into Mysql Table.

I hope this article helps you.
Thanks for visiting Inflay.com