PHP 7 Script to Upload Excel File and Insert Data Into MySQL Database Using PHPExcel Library in Browser Using HTML5 Form

There is a lot of confusion PHP 7 Script to Upload Excel File and Insert Data Into MySQL Database Using PHPExcel Library in Browser Using HTML5 Form We are sure this article will give you a clear result. Let’s go into the article.

PHP 7 Script to Upload Excel File and Insert Data Into MySQL Database Using PHPExcel Library in Browser Using HTML5 Form

PHP 7 Script to Upload Excel File and Insert Data Into MySQL Database Using PHPExcel Library in Browser Using HTML5 Form

composer require phpoffice/phpexcel
<?php
$hostname = "localhost";
$db = "school";
$password = "";
$user = "root";
$mysqli = new mysqli($hostname, $user, $password, $db);
?>

CREATE TABLE IF NOT EXISTS `participants` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `rollno` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  `age` int(11) NOT NULL,
  `program` varchar(100) NOT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

<!DOCTYPE html>
<html>
<head>
<title>PHP import Excel data</title>
<link rel="stylesheet" type="text/css" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">
</head>
<body>
<div class="container">
<h1>Upload Excel File</h1>
<form method="POST" action="uploadexcel.php" enctype="multipart/form-data">
<div class="form-group">
<label>Choose File</label>
<input type="file" name="uploadFile" class="form-control" />
</div>
<div class="form-group">
<button type="submit" name="submit" class="btn btn-success">Upload</button>
</div>
</form>
</div>
</body>
</html>

<?php  
 
if(isset($_POST['submit'])) {
     if(isset($_FILES['uploadFile']['name']) && $_FILES['uploadFile']['name'] != "") {
        $allowedExtensions = array("xls","xlsx");
        $ext = pathinfo($_FILES['uploadFile']['name'], PATHINFO_EXTENSION);

        if(in_array($ext, $allowedExtensions)) {
// Uploaded file
               $file = "uploads/".$_FILES['uploadFile']['name'];
               $isUploaded = copy($_FILES['uploadFile']['tmp_name'], $file);
   // check uploaded file
               if($isUploaded) {
// Include PHPExcel files and database configuration file
                    include("db.php");
require_once __DIR__ . '/vendor/autoload.php';
                    include(__DIR__ .'/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());
                    }
                    
                    // Specify the excel sheet index
                    $sheet = $objPHPExcel->getSheet(0);
                    $total_rows = $sheet->getHighestRow();
$highestColumn      = $sheet->getHighestColumn();
$highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);

// loop over the rows
for ($row = 1; $row <= $total_rows; ++ $row) {
for ($col = 0; $col < $highestColumnIndex; ++ $col) {
$cell = $sheet->getCellByColumnAndRow($col, $row);
$val = $cell->getValue();
$records[$row][$col] = $val;
}
}
$html="<table border="1">";
$html.="<tr><th>Roll No</th>";
$html.="<th>Name</th><th>Age</th>";
$html.="<th>Program</th></tr>";
foreach($records as $row){
// HTML content to render on webpage
$html.="<tr>";
$rollno = isset($row[0]) ? $row[0] : '';
$name = isset($row[1]) ? $row[1] : '';
$age = isset($row[2]) ? $row[2] : '';
$program = isset($row[3]) ? $row[3] : '';
$html.="<td>".$rollno."</td>";
$html.="<td>".$name."</td>";
$html.="<td>".$age."</td>";
$html.="<td>".$program."</td>";
$html.="</tr>";
// Insert into database
$query = "INSERT INTO participants (rollno,name,age,program)
values('".$rollno."','".$name."','".$age."','".$program."')";
$mysqli->query($query);
}
$html.="</table>";
echo $html;
echo "<br/>Data inserted in Database";

                    unlink($file);
                } else {
                    echo '<span class="msg">File not uploaded!</span>';
                }
        } else {
            echo '<span class="msg">Please upload excel sheet.</span>';
        }
    } else {
        echo '<span class="msg">Please upload excel file.</span>';
    }
}
?>

Final Words

We hope the PHP 7 Script to Upload Excel File and Insert Data Into MySQL Database Using PHPExcel article has given good results for your confusion. See you in the next article.

Hi, I'm Selva a full-time Blogger, YouTuber, Affiliate Marketer, & founder of Coding Deekshi. Here, I post about programming to help developers.

Share on:

Leave a Comment