[Tut] PHP Excel Export Code (Data to File)

[Tut] PHP Excel Export Code (Data to File) - Printable Version

+- Sick Gaming (https://www.sickgaming.net)
+-- Forum: Programming (https://www.sickgaming.net/forum-76.html)
+--- Forum: PHP Development (https://www.sickgaming.net/forum-82.html)
+--- Thread: [Tut] PHP Excel Export Code (Data to File) (/thread-99981.html)

[Tut] PHP Excel Export Code (Data to File) - xSicKxBot - 09-24-2022

PHP Excel Export Code (Data to File)

<div style="margin: 5px 5% 10px 5%;"><img src="https://www.sickgaming.net/blog/wp-content/uploads/2022/09/php-excel-export-code-data-to-file.jpg" width="550" height="471" title="" alt="" /></div><div><div class="modified-on" readability="7.1666666666667"> by <a href="https://phppot.com/about/">Vincy</a>. Last modified on September 23rd, 2022.</div>
<p>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.</p>
<p>This article shows how to export data to excel using PHP. There are many ways to implement this functionality. We have already seen an <a href="https://phppot.com/php/database-data-export-to-excel-file-using-php/">example of data export from MySQL</a>.</p>
<p><strong>This article uses the PHPSpreadSheet library for implementing PHP excel export.</strong></p>
<p>It is a popular library that supports reading, and writing excel files. It will smoothen the excel <a href="https://phppot.com/php/how-to-handle-csv-with-php-read-write-import-export-with-database/">import-export operations</a> through its built-in functions.</p>
<p>The complete example in this article will let create your own export tool or your application.<br /><img loading="lazy" class="alignnone size-large wp-image-19505" src="https://phppot.com/wp-content/uploads/2022/08/php-excel-export-550x471.jpg" alt="php excel export" width="550" height="471" srcset="https://phppot.com/wp-content/uploads/2022/08/php-excel-export-550x471.jpg 550w, https://phppot.com/wp-content/uploads/2022/08/php-excel-export-300x257.jpg 300w, https://phppot.com/wp-content/uploads/2022/08/php-excel-export-768x658.jpg 768w, https://phppot.com/wp-content/uploads/2022/08/php-excel-export.jpg 916w" sizes="(max-width: 550px) 100vw, 550px"></p>
<h2>About this Example</h2>
<p>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.</p>
<p>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.</p>
<p>Follow the below steps to let this example run in your environment.</p>
<ol>
<li>Create and set up the database with data exported to excel.</li>
<li>Download the code at the end of this article and configure the database.</li>
<li>Add PHPSpreadSheet library and other dependencies into the application.</li>
</ol>
<p>We have already used the <a href="https://phppot.com/php/extract-images-from-url-in-excel-with-php-using-phpspreadsheet/">PHPSpreadsheet library to store extracted image URLs</a>.</p>
<h2>1) Create and set up the database with data exported to excel</h2>
<p>Create a database named “db_excel_export” and import the below SQL script into it.</p>
<p class="code-heading">structure.sql</p>
<pre class="prettyprint"><code class="language-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;
</code></pre>
<h2>2) Download the code and configure the database</h2>
<p>The source code contains the following files. This section explains the database configuration.</p>
<p><img loading="lazy" class="alignnone size-full wp-image-19496" src="https://phppot.com/wp-content/uploads/2022/08/excel-export-file-structure.jpg" alt="excel export file structure" width="199" height="205"></p>
<p>Once you download the excel export code from this page, you can find <em>DataSource.php</em> file in the <em>lib</em> folder. Open it and configure the database details in it as below.</p>
<pre class="prettyprint"><code class="language-php"><?php class DataSource
{ const HOST = 'localhost'; const USERNAME = 'root'; const PASSWORD = ''; const DATABASENAME = 'db_excel_export'; ... ...
?>
</code></pre>
<h2>3) Add PHPSpreadSheet library and other dependencies into the application</h2>
<p>When you <a href="https://phpspreadsheet.readthedocs.io/en/latest/" target="_blank" rel="noopener">see the PHPSpreadsheet documentation</a>, it provides an easy to follow installation steps.</p>
<p>It gives the composer command to add the PHPSpreadsheet and related dependencies into the application.</p>
<pre class="prettyprint"><code>composer require phpoffice/phpspreadsheet
</code></pre>
<h3>For PHP version 7</h3>
<p>Add the below specification to the composer.json file.</p>
<pre class="prettyprint"><code>{ "require": { "phpoffice/phpspreadsheet": "^1.23" }, "config": { "platform": { "php": "7.3" } }
}
</code></pre>
<p>then run</p>
<pre class="prettyprint"><code>composer update
</code></pre>
<p><strong>Note:</strong> PHPSpreadsheet requires at least PHP 7.3 version.</p>
<h2>How it works</h2>
<h3>Simple interface with export option</h3>
<p>This page fetches the data from the MySQL database and <a href="https://phppot.com/jquery/using-jqgrid-control-with-php/">displays it in a grid form</a>. Below the data grid, this page shows an “Excel Export” button.</p>
<p>By clicking this button the action parameter is sent to the URL to call the excel export service in PHP.</p>
<p class="code-heading">index.php</p>
<pre class="prettyprint"><code class="language-php-template"><?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>
</code></pre>
<h3>PHP model calls prepare queries to fetch data to export</h3>
<p>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.</p>
<p>The <em>getColumnName()</em> reads the database table column name array. This array will supply data to form the first row in excel to create a column header.</p>
<p>The <em>getAllPost()</em> reads the data rows that will be iterated and set the data cells with the values.</p>
<p class="code-heading">lib/Post.php</p>
<pre class="prettyprint"><code class="language-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; }
}
?>
</code></pre>
<h3>PHP excel export service</h3>
<p>This service helps to export data to the excel sheet. The resultant file will be downloaded to the browser by setting the <a href="https://phppot.com/php/php-header/">PHP header() properties</a>.</p>
<p>The $postResult has the row data and the $columnResult has the column data.</p>
<p>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.</p>
<p class="code-heading">lib/ExportService.php</p>
<pre class="prettyprint"><code class="language-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'); }
}
?>
</code></pre>
<p><a class="download" href="https://phppot.com/downloads/php/php-excel-export.zip">Download</a></p>
<p>  </p>
<div class="related-articles">
<h2>Popular Articles</h2>
</p></div>
<p> <a href="https://phppot.com/php/php-excel-export/#top" class="top">↑ Back to Top</a> </p>
</div>

https://www.sickgaming.net/blog/2022/09/23/php-excel-export-code-data-to-file/