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. Databases store information more efficiently, databases can handle volumes of information that would be unmanageable in a spreadsheet. Spreadsheets have record limitations whereas databases do not. So, we need to store data in any format to a database.
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);
?>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 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>';
}
}
?>
Related Articles
How to display PDF file in PHP from databaseHow to read CSV file in PHP and store in MySQL
Create And Download Word Document in PHP
PHP SplFileObject Standard Library
Simple File Upload Script in PHP
Sending form data to an email using PHP
Recover forgot password using PHP and MySQL
Php file based authentication
Simple PHP File Cache
How to get current directory, filename and code line number in PHP
