Import Excel File into MySQL Database in PHP using Spreadsheet
How to import excel file or data into Mysqli database in PHP using Spreedsheet?
In this tutorial I will show you how to create an application that can be used to import CSV/Excel files using PHP Mysql with Spreadsheet. We will create a simple PHP web application that contains a form for our import file and a table to display the data we import in the database.
- How to Import and Export CSV Files Using PHP and MySQL
- How to Export excel data from Database in PHP using Spreadsheet
Install PHP Spreadsheet
- The easiest way is to use an application manager called Composer Something like Git, quite a useful one for pulling libraries automatically. A small hassle to download and install, but a one-time effort nonetheless.
- Run composer require phpoffice/phpspreadsheet
Note: That’s all. Composer will automatically pull the latest version into the vendor/ folder.
Created Database Configuration File
In this step, we require to create database configuration file, here we will set database name, username and password. So let’s create config.php file on your root directory and put bellow code:
config.php
<?php
// Database configuration
$hostname = “localhost”;
$username = “root”;
$password = “”;
$dbname = “webscodex”;
// Create database connection
$con = new mysqli($hostname, $username, $password, $dbname);
// Check connection
if ($con->connect_error) {
die(“Connection failed: “ . $con->connect_error);
}
?>
Creating UI to upload Import Template
Then create another PHP file called index.php and this PHP file will be the first page to be loaded in our browser when we access view the Project folder of our web directory. And this file will load the employee table list if the employee table is not empty, because this page allows users to import CSV/Excel files and upload data to MySQL database.
index.php
<?php session_start(); ?>
<!DOCTYPE html>
<html>
<head>
<meta charset=”utf-8">
<meta name=”viewport” content=”width=device-width, initial-scale=1">
<title>How to Import Excel data in Database using PHP with Spreadsheet</title>
<link href=”https://cdn.jsdelivr.net/npm/bootstrap@5.2.3/dist/css/bootstrap.min.css" rel=”stylesheet”>
</head>
<body>
<div class=”container text-center” style=”margin-top:40px; margin-bottom: 40px;”>
<h2>How to Import Excel data in Database using PHP with Spreadsheet</h2><br>
<hr><br>
<?php
if (isset($_SESSION[“status”])) {
echo “<div class=’alert alert-success’ role=’alert’>” . $_SESSION[‘status’] . “</div>”;
unset($_SESSION[“status”]);
}
?>
<form action=”import_data.php” method=”POST” enctype=”multipart/form-data”>
<div class=”row”>
<div class=”fomr-group col-md-5 offset-2">
<input type=”file” name=”file_data” class=”form-control” />
</div>
<div class=”fomr-group col-md-5">
<button type=”submit” name=”import_btn” class=”btn btn-success”>Upload / Import</button>
</div>
</div>
</form>
</div>
<?php
// Include database
include “config.php”;
$query = “SELECT * FROM employee”;
$result = $con->query($query);
if ($result->num_rows > 0) {
?>
<div class=”container”>
<table class=”table table-striped table-hover table-bordered”>
<thead>
<tr>
<th>Emp Id</th>
<th>Name</th>
<th>Job Title</th>
<th>Department</th>
<th>Gender</th>
<th>Age</th>
<th>Hire date</th>
<th>Annual Salary</th>
<th>Bonus</th>
<th>Country</th>
<th>City</th>
</tr>
</thead>
<tbody>
<?php foreach ($result as $row) { ?>
<tr>
<td><?php echo $row[‘emp_id’]; ?></td>
<td><?php echo $row[‘full_name’]; ?></td>
<td><?php echo $row[‘job_title’]; ?></td>
<td><?php echo $row[‘department’]; ?></td>
<td><?php echo $row[‘gender’]; ?></td>
<td><?php echo $row[‘age’]; ?></td>
<td><?php echo $row[‘hire_date’]; ?></td>
<td><?php echo $row[‘annual_salary’]; ?></td>
<td><?php echo $row[‘bonus’]; ?></td>
<td><?php echo $row[‘country’]; ?></td>
<td><?php echo $row[‘city’]; ?></td>
</tr>
<?php } ?>
</tbody>
</table>
</div>
<?php } ?>
<script src=”https://cdn.jsdelivr.net/npm/bootstrap@5.2.3/dist/js/bootstrap.bundle.min.js”></script>
</body>
</html>
import_data.php
<?php
session_start();
// include database configuration
include “config.php”;
require ‘vendor/autoload.php’;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
if (isset($_POST[‘import_btn’])) {
$fileData = $_FILES[‘file_data’][‘name’];
$allowed_ext = [‘xls’, ‘csv’, ‘xlsx’];
$checking = explode(“.”, $fileData);
$file_ext = end($checking);
if (in_array($file_ext, $allowed_ext)) {
$targetPath = $_FILES[‘file_data’][‘tmp_name’];
/** Load $inputFileName to a Spreadsheet object **/
$spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($targetPath);
$excelData = $spreadsheet->getActiveSheet()->toArray();
$message = false;
$count = 0;
foreach ($excelData as $key => $row) {
if ($count > 0) {
$emp_id = $row[‘0’];
$full_name = $row[‘1’];
$job_title = $row[‘2’];
$department = $row[‘3’];
$gender = $row[‘4’];
$age = $row[‘5’];
$hire_date = date(‘Y-m-d’, strtotime($row[‘6’]));
$annual_salary = $row[‘7’];
$bonus = $row[‘8’];
$country = $row[‘9’];
$city = $row[‘10’];
// Already exist data the update record
$sql = “SELECT * FROM employee WHERE emp_id = ‘$emp_id’”;
$result = $con->query($sql);
if ($result->num_rows > 0) {
$query_up = “UPDATE employee SET full_name =’$full_name’, job_title =’$job_title’, department =’$department’, gender =’$gender’, age =’$age’, hire_date =’$hire_date’, annual_salary =’$annual_salary’, bonus =’$bonus’, country =’$country’, city =’$city’ WHERE emp_id = ‘$emp_id’”;
$result_up = $con->query($query_up);
$message = true;
}else{
// If New record than insert into database
$query =”INSERT INTO employee (emp_id, full_name, job_title, department, gender, age, hire_date, annual_salary, bonus, country, city)
VALUES(‘$emp_id’, ‘$full_name’, ‘$job_title’, ‘$department’, ‘$gender’, ‘$age’, ‘$hire_date’, ‘$annual_salary’, ‘$bonus’, ‘$country’, ‘$city’)”;
$result_in = $con->query($query);
$message = true;
}
}
$count++;
}
if ($message) {
$_SESSION[‘status’] = “File Imported Successfully”;
header(“Location: index.php”);
} else {
$_SESSION[‘status’] = “File is not imported please try again”;
header(“Location: index.php”);
}
}else{
$_SESSION[‘status’] = “Invalid File Extension please try again”;
header(“Location: index.php”);
}
}
?>
If you want to more tutorials please visit webscodex. and so many articles
Import Excel File into MySQL Database in PHP using Spreadsheet