PHP import Excel data to MySQL using PHPExcel
In this article, you will learn how to import Excel data to MySQL database using PHPExcel library.
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. 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 database does not. So, we need to store these data into a database for future use. Here, i am using PHPExcel library to import CSV file into PHP database. You can easily implement it by following few step.
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 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 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 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 execute. It returns an error message on failure. Further, PHPExcel parses the excel file, read the data and stores in an array. We have looped over this array to insert data into 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 insert in the database as in the given screenshot -
Related Articles
PHP Display PDF file from DatabaseHow 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
