PHP import Excel data to MySQL using PHPExcel
In this article, you will learn how to import Excel data to the MySQL database using the PHPExcel library. PHPExcel is a library written in pure PHP and providing a set of classes that allow you to write to and read from different spreadsheet file formats.
The web applications may contain data in various formats or they may have to collect data from various sources. It is not necessary that all the gathered data are compatible to store on the server. The most large-scale websites use server-side code to dynamically display different data when needed. Database stores information more efficiently, it can handle volumes of information that would be unmanageable in a spreadsheet. Spreadsheets have record limitations, whereas the database does not. So, we need to store these data into a database for future use. Here, we are using the PHPExcel library to import a CSV file into a PHP database. You can easily implement this by following these steps.
These are the steps to import Excel data to MySQL using PHPExcel -
Install PHPExcel with Composer
First, we need to install the PHPExcel library to read excel data. You can either download this from Github or install it using Composer. If your system is not installed composer, then first download the latest composer version from its official website -
https://getcomposer.org/download/
You can check a successful installation using the following command on cmd -
Now, go to your project directory on the command prompt and run the following command to install the PHPExcel library.
E:\wamp\www\exceltomysql>composer require phpoffice/phpexcelAfter this, you have noticed that Composer has downloaded all libraries under the 'vendor' directory of your project root. Here is the file structure of this project -
Excel File
Suppose we have the following excel file containing school program participants data -
Database Script (db.php)
Here, we have written the database connection code, make sure to replace 'hostname', 'username', 'password' and 'database' with your database credentials and name.
<?php
$hostname = "localhost";
$db = "school";
$password = "";
$user = "root";
$mysqli = new mysqli($hostname, $user, $password, $db);
?>If we want to store or import excel data into the MySQL database, we need a database table. So, we have a MySQL database 'school' and in it we have to create an 'participants' table with the given columns.
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;Create Upload File Template
To create a file uploader, we have created a simple HTML file upload form 'index.php'. If a form contains any file type input field, then we have to add an attribute 'encrypt' with a value 'multipart/form-data'.
<!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>Parse Excel and Insert into Database (uploadexcel.php)
In this PHP code, we have validated the uploaded file type and then included the PHPExcel library files and database configuration file (db.php). At each logical code, we have validated the execution. It returns an error message on failure. Further, PHPExcel parses the excel file, reads the data and stores it in an array. We have looped over this array to insert data into the database and show the response to the web browser.
<?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>';
}
}
?>On successful execution, the data will be inserted in the database as in the given screenshot -
Related Articles
PHP capitalize first letterAge calculator in PHP
PHP Display PDF file from Database
Remove empty values from array PHP
Remove duplicates from array PHP
Floor function in PHP
Convert stdclass object to array PHP
How to read CSV file in PHP and store in MySQL
Generating word documents with PHP
PHP SplFileObject Examples
How to Upload a File in PHP
Simple PHP email form
Password reset system in PHP
HTTP authentication with PHP
PHP file cache library
PHP get current directory url
How to prevent CSRF attack in PHP
Forgot Password Script PHP mysqli database
PHP Contact Form with Google reCAPTCHA
HTML Form Validation in PHP
PHP sanitize input for MySQL
Simple pagination in PHP with MySQL
Store Emoji character in MySQL using PHP
