I am trying to upload my excel file to MySQL database and using Spout to do that. Here is my code for HTML and PHP.
<!DOCTYPE html>
<html>
<head>
<title>Excel Uploading PHP</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>Excel Upload</h1>
<form method="POST" action="upload_excel.php" enctype="multipart/form-data">
<div class="form-group">
<label>Upload Excel File</label>
<input type="file" name="file" class="form-control">
</div>
<div class="form-group">
<button type="submit" name="Submit" class="btn btn-success">Upload</button>
</div>
</form>
</div>
</body>
</html>
And here is PHP Code
<?php
use Box\Spout\Reader\ReaderFactory;
use Box\Spout\Common\Type;
echo 'File Used';
// Include Spout library
require_once 'Spout/Autoloader/autoload.php';
require 'SpreadsheetReader.php';
echo 'Require complete';
require('db_connection.php');
// check file name is not empty
if (!empty($_FILES['file']['name'])) {
// Get File extension eg. 'xlsx' to check file is excel sheet
$pathinfo = pathinfo($_FILES["file"]["name"]);
print_r($pathinfo);
// check file has extension xlsx, xls and also check
// file is not empty
if (($pathinfo['extension'] == 'xlsx' || $pathinfo['extension'] == 'xls')
&& $_FILES['file']['size'] > 0 ) {
echo 'File validated';
$inputFileName = 'uploads/'.$_FILES['file']['name'];
move_uploaded_file($_FILES['file']['tmp_name'], $inputFileName);
$Reader = new SpreadsheetReader($inputFileName);
echo $inputFileName;
try
{
$reader = ReaderFactory::create(Type::XLSX);
echo 'Reader successful';
}
catch(Exception $e)
{
echo $e->getMessage();
}
// Read excel file by using ReadFactory object.
echo 'opening file';
// Open file
$reader->open($inputFileName);
$count = 1;
// Number of sheet in excel file
foreach ($reader->getSheetIterator() as $sheet) {
// Number of Rows in Excel sheet
foreach ($sheet->getRowIterator() as $row) {
// It reads data after header. In the my excel sheet,
// header is in the first row.
if ($count > 1) {
// Data of excel sheet
$data['<column_name>'] = $row[0];
$data['<column_name>'] = $row[1];
$data['<column_name>'] = $row[2];
$data['<column_name>'] = $row[3];
$data['<column_name>'] = $row[3];
$data['<column_name>'] = $row[3];
$data['<column_name>'] = $row[3];
$data['<column_name>'] = $row[3];
$data['<column_name>'] = $row[3];
$data['<column_name>'] = $row[3];
$query = "<SQL QUERY GOES HERE>";
$result = mysqli_query($conn, $query);
print_r(data);
}
$count++;
}
}
// Close excel file
$reader->close();
} else {
echo "Please Select Valid Excel File";
}
} else {
echo "Please Select Excel File";
}
?>
I have written some PHP echo' to check the flow of the program. The file works perfectly till the following line: move_uploaded_file($_FILES['file']['tmp_name'], $inputFileName); and stop executing after that.
It copies the file in a given folder and stops when trying to create SpreadsheetReader object. I tried to put Try-Catch block around it to capture any exception error but not catching any exception either.
Let me give you the status of setup :
All of the above is hosted on VPS and running on Ubuntu 18.10. The spout isn't installed using composer, I have copied files from the preinstalled source.
Am I missing any package that must be installed on OS to work out this? Or is there anything missing in the program itself? I have searched for references but as per articles, this code seems to work. I am confused, where is the problem here?
Kindly someone give me direction to look for or at least give me a reference for working Excel-MySQL file upload.
Thanks
SpreadsheetReaderis not a class that's part of Spout. Trying to instantiate this class won't work, since you did not 'require' it. That's why your program crashes there. You can just remove this line, as I don't see it used.Also I see this:
if (($pathinfo['extension'] == 'xlsx' || $pathinfo['extension'] == 'xls')Spout only supports XLSX documents. So if you pass it a XLS document, it won't work.