In this post we are going to learn how to export excel file and insert that excel file data into mysql data by using PHP with Ajax. In this example we will export excel file data into mysql database without page refresh this is because we have use ajax request with jquery for upload excel file in php script. For fetch data from excel file we have use PHPExcel library, by using this PHPExcel library we can fetch data from excel and we can use that data into php script. PHPExcel library is used for read, write or create excel file in php script. If you want to fetch data from any excel document to php then you can use this PHPExcel library. We have use ajax request to upload upload excel file and then after by using this PHPExcel library we have use that excel data in php script. So we have make simple data export system, in this system you can export excel data into mysql database without page refresh. This is because we have use ajax method for upload excel file to sever. For exporting excel data we have use PHPExcel library. By using this library we can smoothly export large amount of excel data to database. This system is very important if you are working on any enterprise level application. In that application you have large amount of data in excel file and you want to export that to mysql database server. Then at that time this type of system is very important. In this we have use ajax method so it will export data to server without page refresh. That means it will export data to server fast as compare to simple php script.
You can download PHPExcel class from this link - https://phpexcel.codeplex.com/releases/view/119187
Souce Code
index.php
<html>
<head>
<title>PHPExcel - Export Excel file into Mysql Database using Ajax</title>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.2.0/jquery.min.js"></script>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/js/bootstrap.min.js"></script>
<style>
body
{
margin:0;
padding:0;
background-color:#f1f1f1;
}
.box
{
width:900px;
padding:20px;
background-color:#fff;
border:1px solid #ccc;
border-radius:5px;
margin-top:100px;
}
</style>
</head>
<body>
<div class="container box">
<h3 align="center">PHPExcel - Export Excel file into Mysql Database using Ajax</h3>
<br /><br />
<br /><br />
<form mehtod="post" id="export_excel">
<label>Select Excel</label>
<input type="file" name="excel_file" id="excel_file" />
</form>
<br />
<br />
<div id="result">
</div>
</div>
</body>
</html>
<script>
$(document).ready(function(){
$('#excel_file').change(function(){
$('#export_excel').submit();
});
$('#export_excel').on('submit', function(event){
event.preventDefault();
$.ajax({
url:"export.php",
method:"POST",
data:new FormData(this),
contentType:false,
processData:false,
success:function(data){
$('#result').html(data);
$('#excel_file').val('');
}
});
});
});
</script>
export.php
<?php
//export.php
if(!empty($_FILES["excel_file"]))
{
$connect = mysqli_connect("localhost", "root", "", "testing");
$file_array = explode(".", $_FILES["excel_file"]["name"]);
if($file_array[1] == "xls")
{
include("PHPExcel/IOFactory.php");
$output = '';
$output .= "
<label class='text-success'>Data Inserted</label>
<table class='table table-bordered'>
<tr>
<th>Customer Name</th>
<th>Address</th>
<th>City</th>
<th>Postal Code</th>
<th>Country</th>
</tr>
";
$object = PHPExcel_IOFactory::load($_FILES["excel_file"]["tmp_name"]);
foreach($object->getWorksheetIterator() as $worksheet)
{
$highestRow = $worksheet->getHighestRow();
for($row=2; $row<=$highestRow; $row++)
{
$name = mysqli_real_escape_string($connect, $worksheet->getCellByColumnAndRow(1, $row)->getValue());
$address = mysqli_real_escape_string($connect, $worksheet->getCellByColumnAndRow(2, $row)->getValue());
$city = mysqli_real_escape_string($connect, $worksheet->getCellByColumnAndRow(3, $row)->getValue());
$postal_code = mysqli_real_escape_string($connect, $worksheet->getCellByColumnAndRow(4, $row)->getValue());
$country = mysqli_real_escape_string($connect, $worksheet->getCellByColumnAndRow(5, $row)->getValue());
$query = "
INSERT INTO tbl_customer
(CustomerName, Address, City, PostalCode, Country)
VALUES ('".$name."', '".$address."', '".$city."', '".$postal_code."', '".$country."')
";
mysqli_query($connect, $query);
$output .= '
<tr>
<td>'.$name.'</td>
<td>'.$address.'</td>
<td>'.$city.'</td>
<td>'.$postal_code.'</td>
<td>'.$country.'</td>
</tr>
';
}
}
$output .= '</table>';
echo $output;
}
else
{
echo '<label class="text-danger">Invalid File</label>';
}
}
?>
tbl_customer
--
-- Table structure for table `tbl_customer`
--
CREATE TABLE IF NOT EXISTS `tbl_customer` (
`CustomerID` int(11) NOT NULL AUTO_INCREMENT,
`CustomerName` varchar(250) NOT NULL,
`Address` text NOT NULL,
`City` varchar(250) NOT NULL,
`PostalCode` varchar(30) NOT NULL,
`Country` varchar(100) NOT NULL,
PRIMARY KEY (`CustomerID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
very good video tutorial! Thanks admin
ReplyDeleteThks it works 100%
ReplyDeleteVideo is removed
ReplyDeleteThanks, your videos are clear enough, and codes provided are working well.
ReplyDeleteThanks for the explanation
ReplyDeleteI have blank lines
Between each line there is a blank line
How can these empty lines not be lifted?
thanks!
ReplyDeleteFatal error: Uncaught Error: Class 'PHPExcel' not found in E:\xampp\htdocs\feb\uploadexcel\php\PHPExcel\Reader\Excel2007.php:342 Stack trace: #0 E:\xampp\htdocs\feb\uploadexcel\php\PHPExcel\IOFactory.php(192): PHPExcel_Reader_Excel2007->load('E:\\xampp\\tmp\\ph...') #1 E:\xampp\htdocs\feb\uploadexcel\php\export.php(14): PHPExcel_IOFactory::load('E:\\xampp\\tmp\\ph...') #2 {main} thrown in E:\xampp\htdocs\feb\uploadexcel\php\PHPExcel\Reader\Excel2007.php on line 342
ReplyDeletePHPExcel - Export Excel file into Mysql Database using Ajax change to
ReplyDeletePHPExcel - Import Excel file into Mysql Database using Ajax
helpful
ReplyDeletethanks alot
Thanks. This is very helpful. However, How may I insert data in MYSQL with commas
ReplyDeletethank you .. its very help full to me
ReplyDeleteExecellent Code
ReplyDeleteFatal error: Uncaught Error: Class 'PHPExcel' not found in C:\xampp\htdocs\stores\admin\PHPExcel\Reader\Excel5.php:625 Stack trace: #0 C:\xampp\htdocs\stores\admin\PHPExcel\IOFactory.php(192): PHPExcel_Reader_Excel5->load('C:\\xampp\\tmp\\ph...') #1 C:\xampp\htdocs\stores\admin\export.php(22): PHPExcel_IOFactory::load('C:\\xampp\\tmp\\ph...') #2 {main} thrown in C:\xampp\htdocs\stores\admin\PHPExcel\Reader\Excel5.php on line 625
ReplyDeleteauto_awesome
ReplyDeleteLangue source : Anglais
182 / 5000
Résultats de traduction
Here is the error displayed to me:
Warning: include (PHPExcel / IOFactory.php): failed to open stream: No such file or directory in C: \ wamp \ www \ Excel \ export.php on line 9.
help me please.
auto_awesome
ReplyDeleteLangue source : Anglais
182 / 5000
Résultats de traduction
Here is the error displayed to me:
Warning: include (PHPExcel / IOFactory.php): failed to open stream: No such file or directory in C: \ wamp \ www \ Excel \ export.php on line 9.
help me please
Warning: include (PHPExcel / IOFactory.php): failed to open stream: No such file or directory in C:
ReplyDelete