PHP Excel Export Code (Data to File)
by Vincy. Last modified on September 23rd, 2022.
Export data to an excel file is mainly used for taking a backup. When taking database backup, excel format is a convenient one to read and manage easily. For some applications exporting data is important to take a backup or an offline copy of the server database.
This article shows how to export data to excel using PHP. There are many ways to implement this functionality. We have already seen an example of data export from MySQL.
This article uses the PHPSpreadSheet library for implementing PHP excel export.
It is a popular library that supports reading, and writing excel files. It will smoothen the excel import-export operations through its built-in functions.
The complete example in this article will let create your own export tool or your application.
About this Example
It will show a minimal interface with the list of database records and an “Export to Excel” button. By clicking this button, it will call the custom ExportService created for this example.
This service instantiates the PHPSpreadsheet library class and sets the column header and values. Then it creates a writer object by setting the PHPSpreadsheet instance to output the data to excel.
Follow the below steps to let this example run in your environment.
- Create and set up the database with data exported to excel.
- Download the code at the end of this article and configure the database.
- Add PHPSpreadSheet library and other dependencies into the application.
We have already used the PHPSpreadsheet library to store extracted image URLs.
1) Create and set up the database with data exported to excel
Create a database named “db_excel_export” and import the below SQL script into it.
structure.sql
--
-- Table structure for table `tbl_products`
--
CREATE TABLE `tbl_products` (
`id` int(8) NOT NULL,
`name` varchar(255) NOT NULL,
`price` double(10,2) NOT NULL,
`category` varchar(255) NOT NULL,
`product_image` text NOT NULL,
`average_rating` float(3,1) NOT NULL
);
--
-- Dumping data for table `tbl_products`
--
INSERT INTO `tbl_products` (`id`, `name`, `price`, `category`, `product_image`, `average_rating`) VALUES
(1, 'Tiny Handbags', 100.00, 'Fashion', 'gallery/handbag.jpeg', 5.0),
(2, 'Men\'s Watch', 300.00, 'Generic', 'gallery/watch.jpeg', 4.0),
(3, 'Trendy Watch', 550.00, 'Generic', 'gallery/trendy-watch.jpeg', 4.0),
(4, 'Travel Bag', 820.00, 'Travel', 'gallery/travel-bag.jpeg', 5.0),
(5, 'Plastic Ducklings', 200.00, 'Toys', 'gallery/ducklings.jpeg', 4.0),
(6, 'Wooden Dolls', 290.00, 'Toys', 'gallery/wooden-dolls.jpeg', 5.0),
(7, 'Advanced Camera', 600.00, 'Gadget', 'gallery/camera.jpeg', 4.0),
(8, 'Jewel Box', 180.00, 'Fashion', 'gallery/jewel-box.jpeg', 5.0),
(9, 'Perl Jewellery', 940.00, 'Fashion', 'gallery/perls.jpeg', 5.0);
--
-- Indexes for dumped tables
--
--
-- Indexes for table `tbl_products`
--
ALTER TABLE `tbl_products`
ADD PRIMARY KEY (`id`);
--
-- AUTO_INCREMENT for dumped tables
--
--
-- AUTO_INCREMENT for table `tbl_products`
--
ALTER TABLE `tbl_products`
MODIFY `id` int(8) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=10;
2) Download the code and configure the database
The source code contains the following files. This section explains the database configuration.
Once you download the excel export code from this page, you can find DataSource.php file in the lib folder. Open it and configure the database details in it as below.
<?php
class DataSource
{
const HOST = 'localhost';
const USERNAME = 'root';
const PASSWORD = '';
const DATABASENAME = 'db_excel_export';
...
...
?>
3) Add PHPSpreadSheet library and other dependencies into the application
When you see the PHPSpreadsheet documentation, it provides an easy to follow installation steps.
It gives the composer command to add the PHPSpreadsheet and related dependencies into the application.
composer require phpoffice/phpspreadsheet
For PHP version 7
Add the below specification to the composer.json file.
{
"require": {
"phpoffice/phpspreadsheet": "^1.23"
},
"config": {
"platform": {
"php": "7.3"
}
}
}
then run
composer update
Note: PHPSpreadsheet requires at least PHP 7.3 version.
How it works
Simple interface with export option
This page fetches the data from the MySQL database and displays it in a grid form. Below the data grid, this page shows an “Excel Export” button.
By clicking this button the action parameter is sent to the URL to call the excel export service in PHP.
index.php
<?php
require_once __DIR__ . '/lib/Post.php';
$post = new post();
$postResult = $post->getAllPost();
$columnResult = $post->getColumnName();
if (! empty($_GET["action"])) {
require_once __DIR__ . '/lib/ExportService.php';
$exportService = new ExportService();
$result = $exportService->exportExcel($postResult, $columnResult);
}
?>
<html>
<head>
<meta name="viewport" content="width=device-width, initial-scale=1">
<link href="./style.css" type="text/css" rel="stylesheet" />
</head>
<body>
<div id="table-container">
<table id="tab">
<thead>
<tr>
<th width="5%">Id</th>
<th width="35%">Name</th>
<th width="20%">Price</th>
<th width="25%">Category</th>
<th width="25%">product Image</th>
<th width="20%">Average Rating</th>
</tr>
</thead>
<tbody>
<?php
if (! empty($postResult)) {
foreach ($postResult as $key => $value) {
?>
<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>
</table>
<div class="btn">
<form action="" method="POST">
<a
href="<?php echo strtok($_SERVER["REQUEST_URI"]);?><?php echo $_SERVER["QUERY_STRING"];?>?action=export"><button
type="button" id="btnExport" name="Export"
value="Export to Excel" class="btn btn-info">Export
to Excel</button></a>
</form>
</div>
</div>
</body>
</html>
PHP model calls prepare queries to fetch data to export
This is a PHP model class that is called to read data from the database. The data array will be sent to the export service to build the excel sheet object.
The getColumnName() reads the database table column name array. This array will supply data to form the first row in excel to create a column header.
The getAllPost() reads the data rows that will be iterated and set the data cells with the values.
lib/Post.php
<?php
class Post
{
private $ds;
public function __construct()
{
require_once __DIR__ . '/DataSource.php';
$this->ds = new DataSource();
}
public function getAllPost()
{
$query = "select * from tbl_products";
$result = $this->ds->select($query);
return $result;
}
public function getColumnName()
{
$query = "select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME=N'tbl_products'";
$result = $this->ds->select($query);
return $result;
}
}
?>
PHP excel export service
This service helps to export data to the excel sheet. The resultant file will be downloaded to the browser by setting the PHP header() properties.
The $postResult has the row data and the $columnResult has the column data.
This example instantiates the PHPSpreadSheet library class and sets the column header and values. Then it creates a writer object by setting the spreadsheet instance to output the data to excel.
lib/ExportService.php
<?php
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Calculation\TextData\Replace;
require_once __DIR__ . '/../vendor/autoload.php';
class ExportService
{
public function 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 $k => $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);
}
$writer = IOFactory::createWriter($spreadsheet, 'Xls');
header('Content-Type: text/xls');
$fileName = 'exported_excel_' . time() . '.xls';
$headerContent = 'Content-Disposition: attachment;filename="' . $fileName . '"';
header($headerContent);
$writer->save('php://output');
}
}
?>
Written by Vincy, a web developer with 15+ years of experience and a Masters degree in Computer Science. She specializes in building modern, lightweight websites using PHP, JavaScript, React, and related technologies. Phppot helps you in mastering web development through over a decade of publishing quality tutorials.
Hi, I'm Vincy. I help build websites, grow businesses, big and small. If that is what you want, contact me.
I'm currently available for freelance work.