What is PHPSpreadSheet Library
PHPSpreadSheet library is purely writter in PHP and it main purpose of communicate your PHP application with Excel spreadsheet. Here communicate means data send and received or import or export from excel spreadsheet to PHP application and from PHP application to Excel file. It not only support excel file for reading and writing of data, but also read and write html file data and CSV file data. PHPSpreadSheet library is a import version of PHPExcel library. PHPExcel library is not maintained for many years and very less has used this library. Most of the user has been migrated to this PHPSpreadSheet library or any other alternate library.
If you want to use this PHPSpreadSheet Library, you have to PHP 7.1 version or latest PHP version. For this library you have to install composer in your computer.
How to install PHPSpreadSheet Library
If you are looking for install PHPSpreadsheet library, then you have to go command prompt and go to directory in which you want to download this library and write following command.
composer require phpoffice/phpspreadsheet
This command will download PHPSpreadSheet library by using composer. We will discuss following topic of PHPSpreadsheet library in this post.
- Load Excel File Data in Browser
- Convert HTML Table Data to Excel Sheet Data
- Import Data From Xls, Xlsx or CSV file to Mysql
- Export Mysql Table Data to .xlsx, .xls or .csv file
1. Load Excel File Data in Browser
This is first topic, which we have discuss in this post is that How to view Excel Spreadsheet data in Browser by using PHPSpreadSheet library. Here we will make PHP script with PHPSpreadsheet library for load Excel sheet content directly in browser without store in our computer. Because this library has capability to view excel data in browser. Below you can find source code of directly view Excel data in browser.
index.php
<!DOCTYPE html>
<html>
<head>
<title>Load Excel Sheet in Browser using PHPSpreadsheet</title>
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />
</head>
<body>
<div class="container">
<br />
<h3 align="center">Load Excel Sheet in Browser using PHPSpreadsheet</h3>
<br />
<div class="table-responsive">
<span id="message"></span>
<form method="post" id="load_excel_form" enctype="multipart/form-data">
<table class="table">
<tr>
<td width="25%" align="right">Select Excel File</td>
<td width="50%"><input type="file" name="select_excel" /></td>
<td width="25%"><input type="submit" name="load" class="btn btn-primary" /></td>
</tr>
</table>
</form>
<br />
<div id="excel_area"></div>
</div>
</div>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.2.0/jquery.min.js"></script>
</body>
</html>
<script>
$(document).ready(function(){
$('#load_excel_form').on('submit', function(event){
event.preventDefault();
$.ajax({
url:"upload.php",
method:"POST",
data:new FormData(this),
contentType:false,
cache:false,
processData:false,
success:function(data)
{
$('#excel_area').html(data);
$('table').css('width','100%');
}
})
});
});
</script>
upload.php
<?php
//upload.php
include 'vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
if($_FILES["select_excel"]["name"] != '')
{
$allowed_extension = array('xls', 'xlsx');
$file_array = explode(".", $_FILES['select_excel']['name']);
$file_extension = end($file_array);
if(in_array($file_extension, $allowed_extension))
{
$reader = IOFactory::createReader('Xlsx');
$spreadsheet = $reader->load($_FILES['select_excel']['tmp_name']);
$writer = IOFactory::createWriter($spreadsheet, 'Html');
$message = $writer->save('php://output');
}
else
{
$message = '<div class="alert alert-danger">Only .xls or .xlsx file allowed</div>';
}
}
else
{
$message = '<div class="alert alert-danger">Please Select File</div>';
}
echo $message;
?>
2. Convert HTML Table Data to Excel Sheet Data
There are lots of tutorial available on Internet regarding make excel sheet from HTML table data, but that tutorial use javascript for convert html table data to excel data. But it has not make original excel spreadsheet but it has only open html data in excel file format. So, when we have open that type of excel then it has display corrupted or invalid file format message has been appear in window. This is because that file is not correctly excel file but only open html file in excel file. For make original excel file you have to use PHPSpreadsheet library or PHPExcel library. By using both library you can make excel sheet.
Here we have use PHPSpreadsheet library for create excel file from HTML table data. Here we have use simple jquery code with PHP script and PHPSpreadsheet library for export html table data to excel. First it will store html table data in temporary html file, then after by using PHPSpreadsheet IOFactory class method, it will read HTML file by using createReader() method of IOFactory class. Next it will make excel file from that temporary html file by using createWriter() method of PHPSpreadSheet IOFactory class. Once HTML table data has been exported to Excel file then excel sheet will be downloaded in computer and temporary excel file and temporary html file will be removed from working folder.
html-to-excel.php
<?php
//html-to-excel.php
$connect = new PDO("mysql:host=localhost;dbname=testing", "root", "");
$query = "SELECT * FROM sample_datas";
$statement = $connect->prepare($query);
$statement->execute();
$result = $statement->fetchAll();
?>
<!DOCTYPE html>
<html>
<head>
<title>Convert HTML Table to Excel using PHPSpreadsheet</title>
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />
</head>
<body>
<div class="container">
<br />
<h3 align="center">Convert HTML Table to Excel using PHPSpreadsheet</h3>
<br />
<div class="table-responsive">
<form method="POST" id="convert_form" action="export.php">
<table class="table table-striped table-bordered" id="table_content">
<tr>
<th>First Name</th>
<th>Last Name</th>
<th>Created At</th>
<th>Updated At</th>
</tr>
<?php
foreach($result as $row)
{
echo '
<tr>
<td>'.$row["first_name"].'</td>
<td>'.$row["last_name"].'</td>
<td>'.$row["created_at"].'</td>
<td>'.$row["updated_at"].'</td>
</tr>
';
}
?>
</table>
<input type="hidden" name="file_content" id="file_content" />
<button type="button" name="convert" id="convert" class="btn btn-primary">Convert</button>
</form>
<br />
<br />
</div>
</div>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.2.0/jquery.min.js"></script>
</body>
</html>
<script>
$(document).ready(function(){
$('#convert').click(function(){
var table_content = '<table>';
table_content += $('#table_content').html();
table_content += '</table>';
$('#file_content').val(table_content);
$('#convert_form').submit();
});
});
</script>
export.php
<?php
//export.php
include 'vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\IOFactory;
if(isset($_POST["file_content"]))
{
$temporary_html_file = './tmp_html/' . time() . '.html';
file_put_contents($temporary_html_file, $_POST["file_content"]);
$reader = IOFactory::createReader('Html');
$spreadsheet = $reader->load($temporary_html_file);
$writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
$filename = time() . '.xlsx';
$writer->save($filename);
header('Content-Type: application/x-www-form-urlencoded');
header('Content-Transfer-Encoding: Binary');
header("Content-disposition: attachment; filename=\"".$filename."\"");
readfile($filename);
unlink($temporary_html_file);
unlink($filename);
exit;
}
?>
3. Import Data From Xls, Xlsx or CSV file to Mysql
PHPSpreadSheet library is able to read data from Excel file or CSV file. So, here we have use PHPSpreadSheet library has been use for Import data from Excel sheet or CSV file and Insert into Mysql table using PHP script. If we have developed any web based application, then data import is the basic feature for any PHP application. So, for this feature here we have use PHPSpreadsheet library for read data from Excel or CSV file, this is because by using this library, we can easily import data from Excel or CSV file and Insert into Mysql table using PHP script.
php_spreadsheet_import.php
<!DOCTYPE html>
<html>
<head>
<title>Import Data From Excel or CSV File to Mysql using PHPSpreadsheet</title>
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />
</head>
<body>
<div class="container">
<br />
<h3 align="center">Import Data From Excel or CSV File to Mysql using PHPSpreadsheet</h3>
<br />
<div class="panel panel-default">
<div class="panel-heading">Import Data From Excel or CSV File to Mysql using PHPSpreadsheet</div>
<div class="panel-body">
<div class="table-responsive">
<span id="message"></span>
<form method="post" id="import_excel_form" enctype="multipart/form-data">
<table class="table">
<tr>
<td width="25%" align="right">Select Excel File</td>
<td width="50%"><input type="file" name="import_excel" /></td>
<td width="25%"><input type="submit" name="import" id="import" class="btn btn-primary" value="Import" /></td>
</tr>
</table>
</form>
<br />
</div>
</div>
</div>
</div>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.2.0/jquery.min.js"></script>
</body>
</html>
<script>
$(document).ready(function(){
$('#import_excel_form').on('submit', function(event){
event.preventDefault();
$.ajax({
url:"import.php",
method:"POST",
data:new FormData(this),
contentType:false,
cache:false,
processData:false,
beforeSend:function(){
$('#import').attr('disabled', 'disabled');
$('#import').val('Importing...');
},
success:function(data)
{
$('#message').html(data);
$('#import_excel_form')[0].reset();
$('#import').attr('disabled', false);
$('#import').val('Import');
}
})
});
});
</script>
import.php
<?php
//import.php
include 'vendor/autoload.php';
$connect = new PDO("mysql:host=localhost;dbname=testing", "root", "");
if($_FILES["import_excel"]["name"] != '')
{
$allowed_extension = array('xls', 'csv', 'xlsx');
$file_array = explode(".", $_FILES["import_excel"]["name"]);
$file_extension = end($file_array);
if(in_array($file_extension, $allowed_extension))
{
$file_name = time() . '.' . $file_extension;
move_uploaded_file($_FILES['import_excel']['tmp_name'], $file_name);
$file_type = \PhpOffice\PhpSpreadsheet\IOFactory::identify($file_name);
$reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader($file_type);
$spreadsheet = $reader->load($file_name);
unlink($file_name);
$data = $spreadsheet->getActiveSheet()->toArray();
foreach($data as $row)
{
$insert_data = array(
':first_name' => $row[0],
':last_name' => $row[1],
':created_at' => $row[2],
':updated_at' => $row[3]
);
$query = "
INSERT INTO sample_datas
(first_name, last_name, created_at, updated_at)
VALUES (:first_name, :last_name, :created_at, :updated_at)
";
$statement = $connect->prepare($query);
$statement->execute($insert_data);
}
$message = '<div class="alert alert-success">Data Imported Successfully</div>';
}
else
{
$message = '<div class="alert alert-danger">Only .xls .csv or .xlsx file allowed</div>';
}
}
else
{
$message = '<div class="alert alert-danger">Please Select File</div>';
}
echo $message;
?>
4. Export Mysql Table Data to .xlsx, .xls or .csv File
PHPSpreadSheet library can write data in Excel or CSV file. So suppose you have build any web based application by using PHP script. Then you have to use PHPSpreadSheet library for export data from Mysql database to any spreadsheet file format like .xlsx, .xls or .csv file. Export data from web application is one of the feature of you system. If your system is able to give data to user in Excel or csv file, so it will increase your application usabilty and user can use that data for other purpose. So, below you can find source code for How to Export Mysql data to Excel or Csv file format by using PhpSpreadsheet library.
php_spreadsheet_export.php
php_spreadsheet_export.php
<?php
//php_spreadsheet_export.php
include 'vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Spreadsheet;
$connect = new PDO("mysql:host=localhost;dbname=testing", "root", "");
$query = "SELECT * FROM sample_datas ORDER BY id DESC";
$statement = $connect->prepare($query);
$statement->execute();
$result = $statement->fetchAll();
if(isset($_POST["export"]))
{
$file = new Spreadsheet();
$active_sheet = $file->getActiveSheet();
$active_sheet->setCellValue('A1', 'First Name');
$active_sheet->setCellValue('B1', 'Last Name');
$active_sheet->setCellValue('C1', 'Created At');
$active_sheet->setCellValue('D1', 'Updated At');
$count = 2;
foreach($result as $row)
{
$active_sheet->setCellValue('A' . $count, $row["first_name"]);
$active_sheet->setCellValue('B' . $count, $row["last_name"]);
$active_sheet->setCellValue('C' . $count, $row["created_at"]);
$active_sheet->setCellValue('D' . $count, $row["updated_at"]);
$count = $count + 1;
}
$writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($file, $_POST["file_type"]);
$file_name = time() . '.' . strtolower($_POST["file_type"]);
$writer->save($file_name);
header('Content-Type: application/x-www-form-urlencoded');
header('Content-Transfer-Encoding: Binary');
header("Content-disposition: attachment; filename=\"".$file_name."\"");
readfile($file_name);
unlink($file_name);
exit;
}
?>
<!DOCTYPE html>
<html>
<head>
<title>Export Data From Mysql to Excel using PHPSpreadsheet</title>
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />
</head>
<body>
<div class="container">
<br />
<h3 align="center">Export Data From Mysql to Excel using PHPSpreadsheet</h3>
<br />
<div class="panel panel-default">
<div class="panel-heading">
<form method="post">
<div class="row">
<div class="col-md-6">User Data</div>
<div class="col-md-4">
<select name="file_type" class="form-control input-sm">
<option value="Xlsx">Xlsx</option>
<option value="Xls">Xls</option>
<option value="Csv">Csv</option>
</select>
</div>
<div class="col-md-2">
<input type="submit" name="export" class="btn btn-primary btn-sm" value="Export" />
</div>
</div>
</form>
</div>
<div class="panel-body">
<div class="table-responsive">
<table class="table table-striped table-bordered">
<tr>
<th>First Name</th>
<th>Last Name</th>
<th>Created At</th>
<th>Updated At</th>
</tr>
<?php
foreach($result as $row)
{
echo '
<tr>
<td>'.$row["first_name"].'</td>
<td>'.$row["last_name"].'</td>
<td>'.$row["created_at"].'</td>
<td>'.$row["updated_at"].'</td>
</tr>
';
}
?>
</table>
</div>
</div>
</div>
</div>
<br />
<br />
<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.2.0/jquery.min.js"></script>
</body>
</html>
Sourece: https://www.webslesson.info/2020/03/phpspreadsheet-beginner-tutorial.html
