In this project we are uploading an Excel file with multiple sheets to MySQL database.
We are reading the PHP file with the help of PHPExcel class.
1. Create the MySQL table inside be DB.
As we are saving the sheet1 and sheet2 to different tables we need to create 2 tables inside DB.
Below are the create tables queries for the table shown in the video.
CREATE TABLE `sheet1` (
`sl_no` INT(10) NOT NULL AUTO_INCREMENT,
`employee_id` VARCHAR(1000),
`pay_slip_name` VARCHAR(1000),
`net_salary` VARCHAR(1000),
PRIMARY KEY (`sl_no`)
)
CREATE TABLE `sheet2` (
`sl_no` INT(10) NOT NULL AUTO_INCREMENT,
`serialno` VARCHAR(100),
`fcamount` VARCHAR(100),
`costrate` VARCHAR(100),
`rate` VARCHAR(100),
`charges` VARCHAR(100),
`fxprofit` VARCHAR(100),
`chargeprofit` VARCHAR(100),
`netprofit` VARCHAR(100),
`lcamount` VARCHAR(100),
PRIMARY KEY (`sl_no`)
)
2. The database connectivity should be established between PHP and
MySQL. The database credentials are stored in db.php inside the root
folder. This db.php is accessed when ever required.
db.php
<?php
$hostname='localhost';
$username='root';
$password='root';
$database='test';
?>
3. Download the classes folder and keep it inside the root folder as shown in the project structure. Its been accessed from the read_file.php
4. The index.php page has the upload form that uploads the excel file into the upload folder.
index.php
<html>
<head>
</head>
<body>
<center>
<h2>PHP Upload Excel file with multiple Sheets and save the data in MySQL table</h2>
<form action="upload_form.php" method="post" enctype="multipart/form-data">
Select file <input type="file" name="file_upload"/>
<input type="submit" value="Upload"/>
</form>
</center>
</body>
</html>
5. The upload_form.php page will save the uploaded file to the
uploads folder. Then it calls the read_file.php to read the excel file
and upload the data.
upload_form.php
<?php
error_reporting(E_ALL & ~E_NOTICE & ~E_DEPRECATED);
if (isset($_FILES["file_upload"])) {
$dir = "uploads/";
$file_name = $_FILES["file_upload"]["name"];
$size = $_FILES["file_upload"]["size"];
$tmp_file_name = $_FILES["file_upload"]["tmp_name"];
echo $tmp_file_name . "<br>";
echo $file_name . "<br>";
echo $size;
move_uploaded_file($tmp_file_name, $dir . $file_name);
echo "success<br>";
include "read_file.php";
} else {
echo "File not selected";
}
?>
6. Once the read_file.php is called from upload_form.php, It reads the sheet 1 and sheet 2 respectively and creates
the query and execute the query to insert the data.
read_file.php
<html>
<body>
<center>
<?php
include 'db.php';
$conn = mysqli_connect($hostname, $username, $password, $database);
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
require_once "Classes/PHPExcel.php";
$path="uploads/Records.xlsx";
//$path="uploads/".$file_name;
$reader= PHPExcel_IOFactory::createReaderForFile($path);
$excel_Obj = $reader->load($path);
//Read Sheet 0
$worksheet=$excel_Obj->getSheet('0');
$colomncount = $worksheet->getHighestDataColumn();
$rowcount = $worksheet->getHighestRow();
$colomncount_number=PHPExcel_Cell::columnIndexFromString($colomncount);
$insertquery='INSERT INTO `sheet1` (`employee_id`, `pay_slip_name`, `net_salary`) VALUES ';
$subquery='';
for($row=1;$row<=100;$row++){
$subquery=$subquery.' (';
for($col=0;$col<$colomncount_number;$col++){
$subquery=$subquery.'\''.$worksheet->getCell(PHPExcel_Cell::stringFromColumnIndex($col).$row)->getValue().'\',';
}
$subquery = substr($subquery, 0, strlen($subquery) - 1);
$subquery=$subquery.')'.' , ';
}
$insertquery=$insertquery.$subquery;
$insertquery= substr($insertquery,0,strlen($insertquery)-2);
if (mysqli_query($conn, $insertquery)) {
echo "Sheet 1 Uploaded <br>";
} else {
echo "Error: " . $insertquery . "<br>" . mysqli_error($conn);
}
//Read Sheet 1
$worksheet=$excel_Obj->getSheet('1');
$colomncount = $worksheet->getHighestDataColumn();
$rowcount = $worksheet->getHighestRow();
$colomncount_number=PHPExcel_Cell::columnIndexFromString($colomncount);
$insertquery='INSERT INTO `sheet2` (`serialno`, `fcamount`, `costrate`, `rate`, `charges`, `fxprofit`, `chargeprofit`, `netprofit`, `lcamount`) VALUES ';
$subquery='';
for($row=1;$row<=100;$row++){
$subquery=$subquery.' (';
for($col=0;$col<$colomncount_number;$col++){
$subquery=$subquery.'\''.$worksheet->getCell(PHPExcel_Cell::stringFromColumnIndex($col).$row)->getValue().'\',';
}
$subquery = substr($subquery, 0, strlen($subquery) - 1);
$subquery=$subquery.')'.' , ';
}
$insertquery=$insertquery.$subquery;
$insertquery= substr($insertquery,0,strlen($insertquery)-2);
if (mysqli_query($conn, $insertquery)) {
echo "Sheet 2 Uploaded <br>";
} else {
echo "Error: " . $insertquery . "<br>" . mysqli_error($conn);
}
mysqli_close($conn);
?>
</center>
</body>
</html>
No comments:
Post a Comment