Export knowledge to an excel file is principally used for taking a backup. When taking database backup, excel format is a handy one to learn and handle simply. For some purposes exporting knowledge is necessary to take a backup or an offline copy of the server database.
This text exhibits learn how to export knowledge to excel utilizing PHP. There are various methods to implement this performance. We now have already seen an instance of information export from MySQL.
This text makes use of the PHPSpreadSheet library for implementing PHP excel export.
It’s a widespread library that helps studying, and writing excel recordsdata. It would smoothen the excel import-export operations by means of its built-in capabilities.
The entire instance on this article will let create your personal export software or your utility.
About this Instance
It would present a minimal interface with the checklist of database data and an “Export to Excel” button. By clicking this button, it is going to name the customized ExportService created for this instance.
This service instantiates the PHPSpreadsheet library class and units the column header and values. Then it creates a author object by setting the PHPSpreadsheet occasion to output the info to excel.
Comply with the under steps to let this instance run in your setting.
- Create and arrange the database with knowledge exported to excel.
- Obtain the code on the finish of this text and configure the database.
- Add PHPSpreadSheet library and different dependencies into the appliance.
We now have already used the PHPSpreadsheet library to retailer extracted picture URLs.
1) Create and arrange the database with knowledge exported to excel
Create a database named “db_excel_export” and import the under SQL script into it.
construction.sql
--
-- Desk construction for desk `tbl_products`
--
CREATE TABLE `tbl_products` (
`id` int(8) NOT NULL,
`identify` varchar(255) NOT NULL,
`value` double(10,2) NOT NULL,
`class` varchar(255) NOT NULL,
`product_image` textual content NOT NULL,
`average_rating` float(3,1) NOT NULL
);
--
-- Dumping knowledge for desk `tbl_products`
--
INSERT INTO `tbl_products` (`id`, `identify`, `value`, `class`, `product_image`, `average_rating`) VALUES
(1, 'Tiny Purses', 100.00, 'Trend', 'gallery/purse.jpeg', 5.0),
(2, 'Males's Watch', 300.00, 'Generic', 'gallery/watch.jpeg', 4.0),
(3, 'Fashionable Watch', 550.00, 'Generic', 'gallery/trendy-watch.jpeg', 4.0),
(4, 'Journey Bag', 820.00, 'Journey', 'gallery/travel-bag.jpeg', 5.0),
(5, 'Plastic Ducklings', 200.00, 'Toys', 'gallery/ducklings.jpeg', 4.0),
(6, 'Wood Dolls', 290.00, 'Toys', 'gallery/wooden-dolls.jpeg', 5.0),
(7, 'Superior Digicam', 600.00, 'Gadget', 'gallery/digital camera.jpeg', 4.0),
(8, 'Jewel Field', 180.00, 'Trend', 'gallery/jewel-box.jpeg', 5.0),
(9, 'Perl Jewelry', 940.00, 'Trend', 'gallery/perls.jpeg', 5.0);
--
-- Indexes for dumped tables
--
--
-- Indexes for desk `tbl_products`
--
ALTER TABLE `tbl_products`
ADD PRIMARY KEY (`id`);
--
-- AUTO_INCREMENT for dumped tables
--
--
-- AUTO_INCREMENT for desk `tbl_products`
--
ALTER TABLE `tbl_products`
MODIFY `id` int(8) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=10;
2) Obtain the code and configure the database
The supply code incorporates the next recordsdata. This part explains the database configuration.
When you obtain the excel export code from this web page, you could find DataSource.php file within the lib folder. Open it and configure the database particulars in it as under.
<?php
class DataSource
{
const HOST = 'localhost';
const USERNAME = 'root';
const PASSWORD = '';
const DATABASENAME = 'db_excel_export';
...
...
?>
3) Add PHPSpreadSheet library and different dependencies into the appliance
If you see the PHPSpreadsheet documentation, it offers a straightforward to observe set up steps.
It provides the composer command so as to add the PHPSpreadsheet and associated dependencies into the appliance.
composer require phpoffice/phpspreadsheet
For PHP model 7
Add the under specification to the composer.json file.
{
"require": {
"phpoffice/phpspreadsheet": "^1.23"
},
"config": {
"platform": {
"php": "7.3"
}
}
}
then run
composer replace
Be aware: PHPSpreadsheet requires no less than PHP 7.3 model.
The way it works
Easy interface with export possibility
This web page fetches the info from the MySQL database and shows it in a grid type. Under the info grid, this web page exhibits an “Excel Export” button.
By clicking this button the motion parameter is distributed to the URL to name the excel export service in PHP.
index.php
<?php
require_once __DIR__ . '/lib/Put up.php';
$publish = new publish();
$postResult = $post->getAllPost();
$columnResult = $post->getColumnName();
if (! empty($_GET["action"])) {
require_once __DIR__ . '/lib/ExportService.php';
$exportService = new ExportService();
$end result = $exportService->exportExcel($postResult, $columnResult);
}
?>
<html>
<head>
<meta identify="viewport" content material="width=device-width, initial-scale=1">
<hyperlink href="https://phppot.com/php/php-excel-export/./type.css" kind="textual content/css" rel="stylesheet" />
</head>
<physique>
<div id="table-container">
<desk id="tab">
<thead>
<tr>
<th width="5%">Id</th>
<th width="35%">Identify</th>
<th width="20%">Value</th>
<th width="25%">Class</th>
<th width="25%">product Picture</th>
<th width="20%">Common Score</th>
</tr>
</thead>
<tbody>
<?php
if (! empty($postResult)) {
foreach ($postResult as $key => $worth) {
?>
<tr>
<td><?php echo $postResult[$key]["id"]; ?></td>
<td><?php echo $postResult[$key]["name"]; ?></td>
<td><?php echo $postResult[$key]["price"]; ?></td>
<td><?php echo $postResult[$key]["category"]; ?></td>
<td><?php echo $postResult[$key]["product_image"]; ?></td>
<td><?php echo $postResult[$key]["average_rating"]; ?></td>
</tr>
<?php
}
}
?>
</tbody>
</desk>
<div class="btn">
<type motion="" technique="POST">
<a
href="<?php echo strtok($_SERVER["REQUEST_URI"]);?><?php echo $_SERVER["QUERY_STRING"];?>?motion=export"><button
kind="button" id="btnExport" identify="Export"
worth="Export to Excel" class="btn btn-info">Export
to Excel</button></a>
</type>
</div>
</div>
</physique>
</html>
PHP mannequin calls put together queries to fetch knowledge to export
This can be a PHP mannequin class that is named to learn knowledge from the database. The info array will likely be despatched to the export service to construct the excel sheet object.
The getColumnName() reads the database desk column identify array. This array will provide knowledge to type the primary row in excel to create a column header.
The getAllPost() reads the info rows that will likely be iterated and set the info cells with the values.
lib/Put up.php
<?php
class Put up
{
personal $ds;
public operate __construct()
{
require_once __DIR__ . '/DataSource.php';
$this->ds = new DataSource();
}
public operate getAllPost()
{
$question = "choose * from tbl_products";
$end result = $this->ds->choose($question);
return $end result;
}
public operate getColumnName()
{
$question = "choose * from INFORMATION_SCHEMA.COLUMNS the place TABLE_NAME=N'tbl_products'";
$end result = $this->ds->choose($question);
return $end result;
}
}
?>
PHP excel export service
This service helps to export knowledge to the excel sheet. The resultant file will likely be downloaded to the browser by setting the PHP header() properties.
The $postResult has the row knowledge and the $columnResult has the column knowledge.
This instance instantiates the PHPSpreadSheet library class and units the column header and values. Then it creates a author object by setting the spreadsheet occasion to output the info to excel.
lib/ExportService.php
<?php
use PhpOfficePhpSpreadsheetIOFactory;
use PhpOfficePhpSpreadsheetSpreadsheet;
use PhpOfficePhpSpreadsheetWriterXlsx;
use PhpOfficePhpSpreadsheetCalculationTextDataReplace;
require_once __DIR__ . '/../vendor/autoload.php';
class ExportService
{
public operate exportExcel($postResult, $columnResult)
{
$spreadsheet = new Spreadsheet();
$spreadsheet->getProperties()->setTitle("excelsheet");
$spreadsheet->setActiveSheetIndex(0);
$spreadsheet->getActiveSheet()->SetCellValue('A1', ucwords($columnResult[0]["COLUMN_NAME"]));
$spreadsheet->getActiveSheet()->SetCellValue('B1', ucwords($columnResult[1]["COLUMN_NAME"]));
$spreadsheet->getActiveSheet()->SetCellValue('C1', ucwords($columnResult[2]["COLUMN_NAME"]));
$spreadsheet->getActiveSheet()->SetCellValue('D1', ucwords($columnResult[3]["COLUMN_NAME"]));
$spreadsheet->getActiveSheet()->SetCellValue('E1', str_replace('_', ' ', ucwords($columnResult[4]["COLUMN_NAME"], '_')));
$spreadsheet->getActiveSheet()->SetCellValue('F1', str_replace('_', ' ', ucwords($columnResult[5]["COLUMN_NAME"], '_')));
$spreadsheet->getActiveSheet()
->getStyle("A1:F1")
->getFont()
->setBold(true);
$rowCount = 2;
if (! empty($postResult)) {
foreach ($postResult as $okay => $v) {
$spreadsheet->getActiveSheet()->setCellValue("A" . $rowCount, $postResult[$k]["id"]);
$spreadsheet->getActiveSheet()->setCellValue("B" . $rowCount, $postResult[$k]["name"]);
$spreadsheet->getActiveSheet()->setCellValue("C" . $rowCount, $postResult[$k]["price"]);
$spreadsheet->getActiveSheet()->setCellValue("D" . $rowCount, $postResult[$k]["category"]);
$spreadsheet->getActiveSheet()->setCellValue("E" . $rowCount, $postResult[$k]["product_image"]);
$spreadsheet->getActiveSheet()->setCellValue("F" . $rowCount, $postResult[$k]["average_rating"]);
$rowCount ++;
}
$spreadsheet->getActiveSheet()
->getStyle('A:F')
->getAlignment()
->setWrapText(true);
$spreadsheet->getActiveSheet()
->getRowDimension($rowCount)
->setRowHeight(- 1);
}
$author = IOFactory::createWriter($spreadsheet, 'Xls');
header('Content material-Sort: textual content/xls');
$fileName="exported_excel_" . time() . '.xls';
$headerContent="Content material-Disposition: attachment;filename="" . $fileName . '"';
header($headerContent);
$writer->save('php://output');
}
}
?>
