Upload and insert excel data to MySql in PHP
If you want to upload an excel file and then after insert it's date to Mysql table then use below code. I am using PHPExcel library to read Excel data.
Download PHPExcel library here:
https://github.com/PHPOffice/PHPExcel
Direct link of PHPExcel library is:
https://github.com/PHPOffice/PHPExcel/archive/1.8.zip
Create a folder name "exfile", where we will store excel file.
upload.php code here:
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
</head>
<body>
<?php
require_once 'PHPExcel/Classes/PHPExcel.php';
if(isset($_FILES['myexcel'])){
$errors= array();
$file_name = $_FILES['myexcel']['name'];
$file_size =$_FILES['myexcel']['size'];
$file_tmp =$_FILES['myexcel']['tmp_name'];
$file_type=$_FILES['myexcel']['type'];
$targetPath = 'exfile/'.$_FILES['myexcel']['name'];
move_uploaded_file($file_tmp,$targetPath);
$conn = mysqli_connect("localhost","root","","mytest");
$objReader = PHPExcel_IOFactory::createReader('Excel2007');
$objReader->setReadDataOnly(true);
$objPHPExcel = $objReader->load( $targetPath);
$worksheet=$objPHPExcel->getActiveSheet();
$lastRow = $worksheet->getHighestRow();
$lastCol = $worksheet->getHighestColumn();
for ($row = 2; $row <= $lastRow; $row++) {
$class_nm=$worksheet->getCell("A".$row)->getValue();
$class_nm = mysqli_real_escape_string($conn,$class_nm);
$subject=$worksheet->getCell("B".$row)->getValue();
$batch=$worksheet->getCell("C".$row)->getValue();
$query = "insert into cl_nm(class_nm,subject,batch) values('".$class_nm."','".$subject."','".$batch."')";
$result = mysqli_query($conn, $query);
}
echo "Success";
}
?>
<form action="" method="POST" enctype="multipart/form-data">
<input type="file" name="myexcel" />
<input type="submit"/>
</form>
</body>
</html>
Download PHPExcel library here:
https://github.com/PHPOffice/PHPExcel
Direct link of PHPExcel library is:
https://github.com/PHPOffice/PHPExcel/archive/1.8.zip
Create a folder name "exfile", where we will store excel file.
upload.php code here:
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
</head>
<body>
<?php
require_once 'PHPExcel/Classes/PHPExcel.php';
if(isset($_FILES['myexcel'])){
$errors= array();
$file_name = $_FILES['myexcel']['name'];
$file_size =$_FILES['myexcel']['size'];
$file_tmp =$_FILES['myexcel']['tmp_name'];
$file_type=$_FILES['myexcel']['type'];
$targetPath = 'exfile/'.$_FILES['myexcel']['name'];
move_uploaded_file($file_tmp,$targetPath);
$conn = mysqli_connect("localhost","root","","mytest");
$objReader = PHPExcel_IOFactory::createReader('Excel2007');
$objReader->setReadDataOnly(true);
$objPHPExcel = $objReader->load( $targetPath);
$worksheet=$objPHPExcel->getActiveSheet();
$lastRow = $worksheet->getHighestRow();
$lastCol = $worksheet->getHighestColumn();
for ($row = 2; $row <= $lastRow; $row++) {
$class_nm=$worksheet->getCell("A".$row)->getValue();
$class_nm = mysqli_real_escape_string($conn,$class_nm);
$subject=$worksheet->getCell("B".$row)->getValue();
$batch=$worksheet->getCell("C".$row)->getValue();
$query = "insert into cl_nm(class_nm,subject,batch) values('".$class_nm."','".$subject."','".$batch."')";
$result = mysqli_query($conn, $query);
}
echo "Success";
}
?>
<form action="" method="POST" enctype="multipart/form-data">
<input type="file" name="myexcel" />
<input type="submit"/>
</form>
</body>
</html>
Posts
0 comments:
Post a Comment