In this tutorial, we are going to learn how to see how to do export to excel in php. Lately, I was using data tables export but it kind of didn’t suit my needs. So, I thought if I could create my down library that can control custom column headers and metadata that I would like to display on the top of my data in excel.
It took a little effort to make one but it was worth it. Of course, it is not as sophisticated as other libraries that you will find around the internet like PHPExcel but you can customize the way you want using my codes. I am also going to make a full tutorial on how to give export mysql data to excel in php example.
let’s get started.
File Structure:-
What are we going to do?
- Load dummy data into the database.
- Create the files as the structure and setting up a database connection.
- Prepare Custom Headers for Excel.
- Prepare Custom MetaData.
- Export to Excel.
Load Dummy Data Into Database for Excel Export
Table structure:-
|
1 2 3 4 5 6 7 8 |
CREATE TABLE `users` ( `id` bigint(20) UNSIGNED NOT NULL, `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `email` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `email_verified_at` timestamp NULL DEFAULT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; |
Dummy data:-
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
INSERT INTO `users` (`id`, `name`, `email`, `email_verified_at`, `created_at`, `updated_at`) VALUES (1, 'Erich Leannon', 'francisca66@example.org', '2020-09-20 20:56:49', '2020-09-20 20:56:49', '2020-09-20 20:56:49'), (2, 'Janie Hermann', 'frederick09@example.org', '2020-09-20 20:56:49', '2020-09-20 20:56:49', '2020-09-20 20:56:49'), (3, 'Monroe Maggio', 'aprohaska@example.org', '2020-09-20 20:56:49', '2020-09-20 20:56:49', '2020-09-20 20:56:49'), (4, 'Cassidy Monahan', 'ihartmann@example.com', '2020-09-20 20:56:49', '2020-09-20 20:56:49', '2020-09-20 20:56:49'), (5, 'Elisabeth Oberbrunner', 'jgutmann@example.com', '2020-09-20 20:56:49', '2020-09-20 20:56:49', '2020-09-20 20:56:49'), (6, 'Brain Howell', 'hester.kozey@example.com', '2020-09-20 20:56:49', '2020-09-20 20:56:49', '2020-09-20 20:56:49'), (7, 'Julia Baumbach', 'conroy.oma@example.com', '2020-09-20 20:56:49', '2020-09-20 20:56:49', '2020-09-20 20:56:49'), (8, 'Mr. Cornell Ferry', 'bruce.wintheiser@example.net', '2020-09-20 20:56:49', '2020-09-20 20:56:49', '2020-09-20 20:56:49'), (9, 'Lamont Hodkiewicz', 'aparisian@example.net', '2020-09-20 20:56:49', '2020-09-20 20:56:49', '2020-09-20 20:56:49'), (10, 'Zackery Sanford', 'fking@example.org', '2020-09-20 20:56:49', '2020-09-20 20:56:49', '2020-09-20 20:56:49'), (11, 'Zita Hermiston', 'wade.wehner@example.com', '2020-09-20 20:56:49', '2020-09-20 20:56:49', '2020-09-20 20:56:49'), (12, 'Melvina Schamberger', 'bradley.fisher@example.com', '2020-09-20 20:56:49', '2020-09-20 20:56:49', '2020-09-20 20:56:49'), (13, 'Dr. Kaden Ritchie', 'granville24@example.net', '2020-09-20 20:56:49', '2020-09-20 20:56:49', '2020-09-20 20:56:49'), (14, 'Ms. Cydney Baumbach', 'jrogahn@example.com', '2020-09-20 20:56:49', '2020-09-20 20:56:49', '2020-09-20 20:56:49'), (15, 'Ms. Gilda Sporer I', 'jules.larkin@example.org', '2020-09-20 20:56:49', '2020-09-20 20:56:49', '2020-09-20 20:56:49'), (16, 'Trevion Balistreri', 'yschulist@example.com', '2020-09-20 20:56:49', '2020-09-20 20:56:49', '2020-09-20 20:56:49'), (17, 'Geoffrey Barton MD', 'bradtke.carissa@example.com', '2020-09-20 20:56:49', '2020-09-20 20:56:49', '2020-09-20 20:56:49'), (18, 'Cade D\'Amore', 'angelina35@example.net', '2020-09-20 20:56:49', '2020-09-20 20:56:49', '2020-09-20 20:56:49'), (19, 'Michelle Cruickshank', 'weimann.ross@example.org', '2020-09-20 20:56:49', '2020-09-20 20:56:49', '2020-09-20 20:56:49'), (20, 'Miss Twila Brakus', 'enrico.bruen@example.com', '2020-09-20 20:56:49', '2020-09-20 20:56:49', '2020-09-20 20:56:49'); |
Creating a file File Structure and Setting up Database Connection
Create the necessary files that you need like the image above. I will just write codes to paste in each file
config.php
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
<?php define('HOST', 'localhost'); define('USERNAME', 'root'); define("PASSWORD", ""); define('DB', 'export2csv'); try { $dbcon = new PDO("mysql:host=" . HOST . ";dbname=" . DB . ";", USERNAME, PASSWORD); $dbcon->query("SET NAMES 'utf8'"); $dbcon->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); } catch (PDOException $e) { // If database connection occurs log an error error_log($e->getMessage()); die("Cannot make connetion to database"); } |
Let’s create some HTML markup and fetch about 10 records from database.
index.php
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 |
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css" /> <body> <div class="container mt-5 mb-5"> <div class="row"> <div class="col-md-12"> <div class="card"> <div class="card-header bg-success text-white text-bold">List of Users</div> <div class="card-body"> <form action="process.php" method="POST" class="mb-5"> <button name="submit" class="btn btn-warning text-white">Export Excel</button> </form> <?php $sql = "SELECT * FROM USERS LIMIT 10"; $st = $dbcon->prepare($sql); $st->execute(); $users = $st->fetchAll(PDO::FETCH_CLASS); ?> <table class="table table-bordered"> <th>Name</th> <th>Email</th> <th>Created Date</th> <th>Updated Date</th> <tbody> <?php foreach ($users as $val) : ?> <tr> <td><?php echo $val->name; ?></td> <td><?php echo $val->email; ?></td> <td><?php echo $val->created_at; ?></td> <td><?php echo $val->updated_at; ?></td> </tr> <?php endforeach; ?> </tbody> </table> </div> </div> </div> </div> </div> </body> |
Prepare Custom Headers
As you can see we have left process.php and export.class.php as blank for now. It is because we are going to go in detail about it and know-how can we create column headers in our excel.
As you can see in our index.php there is a form with a button named Excel Export. When it is clicked your process.php will be triggered. So head over to process.php and copy these codes below:-
|
1 2 3 4 5 |
<?php if (isset($_POST['submit'])) { // Now we are going to write some codes here... } |
Custom headers are those which you can customize for columns to view in your excel sheet.
For e.g:-
If your database fields have a name, created_at, and updated_at field. We don’t want our column headers in excel to populate the database fields instead of it give unique names.
So we will create an array of the object of column headers. inside if (isset($_POST['submit'])) condition. Create new column headers associating field name and label.
|
1 2 3 4 5 6 |
$columns = [ 'name' => 'Name', 'email' => 'Email', 'created_at' => 'Created Date', 'updated_at' => 'Updated Date' ]; |
Preparing Custom Meta Data
Metadata is the extra information that you may require if you want to show some information above the table. To create metadata make one array like the code below:-
|
1 2 3 4 |
$metaData = [ 'Report' => 'Users Table Report', 'Filter' => 'Filter from 10 to 100 rows' ]; |
Finally, we are going to need the SQL query which will fetch the data and write into excel. For our use case.
|
1 |
$sql = "SELECT * FROM USERS"; |
Finally, your full code for process.php will something like this.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
<?php include("class.export.php"); if (isset($_POST['submit'])) { $columns = [ 'name' => 'Name', 'email' => 'Email', 'created_at' => 'Created Date', 'updated_at' => 'Updated Date' ]; $metaData = [ 'Report' => 'Users Table Report', 'Filter' => 'Filter from 10 to 100 rows' ]; $sql = "SELECT * FROM USERS"; $obj->exportExcel($sql, $columns, $metaData, true); } |
As you have seen we have also included class.export.php in our process.php so, here is the full code for class.export.php. Make sure to update your database connection settings inside this file.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 |
<?php /** * @Author Shashank Bhattarai * @Descriptin: Simplest MySQL Export to Excel Using PHP * @AuthorURI: https://shashankbhattarai.com.np * @Tutoriallink: https://blog.shashankbhattarai.com.np/export-mysql-data-to-excel-in-php-with-example-source-code * * * Using Export Class * I tried to make it as easy as possible to use it and easily configurable. * Make connection to database in your own way else you can configure inside our constructor inside here too. * * Excel Export Features * Inside this class exportExcel() requires 4 parameters. Each of them one described below. * 1. $sql = SELECT query that you want to execute and retrive the data, [Required parameter] * 2. $columns = You can pass custom names to columns in order to your data to give it custom names. * Array pattern $columns['fieldName' => 'Custom Name'] // e.g $columns['name' = 'Person Name'] * If you pass isShowHeader as true and pass NULL to columns, Database field names will be used as column headers in excel, default = NULL * 3. Meta Data = you can add any thing avobe the table e.g filters, pagename or TableName [Optional], defualt = null, * Array Pattern = [ * 'Page Title' => 'User Table Data', * 'Filters' => 'From Jan to Sept' * ] * 4. isShowHeader = If you want just data and not the column header in excel Just Pass false. Columns Header will not be shown. Defualt = false */ class Export { public $dbcon = null; public $db = 'export2csv'; public $host = 'localhost'; public $username = 'root'; public $password = null; public function __construct() { /** * Database configuration can be overridden by your custom database configurations. */ try { $this->dbcon = new PDO("mysql:host=" . $this->host . ";dbname=" . $this->db . ";", $this->username, $this->password); $this->dbcon->query("SET NAMES 'utf8'"); $this->dbcon->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); } catch (PDOException $e) { // If database connection occurs log and error error_log($e->getMessage()); die("Cannot make connetion to database"); } } public function exportExcel($sql, $columns = NULL, $metaData = NULL, $isShowHeader = false) { // fetch Record Count $stmt = $this->dbcon->prepare($sql); $stmt->execute(); $data = $stmt->fetchAll(PDO::FETCH_CLASS); /** * Pass Record Count inside the query in order to execute the function * e.g. SELECT *, (SELECT count(*) FROM USERS) as recordCount FROM USERS; * Alias must be "recordCount" else you have to change line below */ if (isset($data[0]->recordCount)) { $metaData['Record Count'] = $data[0]->recordCount; } ob_start(); $filename = 'download_' . time() . '.xls'; header("Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); header("Content-Disposition: attachment; filename=$filename"); header('Cache-Control: must-revalidate'); // Print Meta Data if ($metaData != null) { foreach ($metaData as $key => $meta) { echo $key . "\t" . $meta . "\n"; } echo "\n"; } foreach ($data as $row) { $row = (array) $row; $rowData = []; if ($columns != null) { foreach ($columns as $key => $head) $rowData[$key] = $row[$key]; } else $rowData = $row; if ($isShowHeader) { echo implode("\t", $columns == null ? array_keys($row) : $columns) . "\n"; $isShowHeader = false; } echo implode("\t", array_values($rowData)) . "\n"; } ob_end_flush(); exit(); } } $obj = new Export(); |
Finally Export MySQL Data to Excel
Finally, it is done. If you load the page and click the button, it will communicate to the server and create the excel file based on your query you have passed inside your process.php tables.
But there is something to take note of.
There might be cases you don’t want column headers. In that case pass $column to NULL and isShowHeader to false inside your class.query.php‘s exportExcel() function.
If you pass $isShowHeader to true and $columns to NULL. The field names of the database will be used as a column header in excel.
Also, the similar case for metadata. Pass metadata to NULL if you do not want to display metadata above your table data in excel. If you just want data to pass only $sql query to exportExcel() function e.g. $obj->exportExcel($sql)as others are null by default.
As you can see I have added an external database config settings inside the class.query.php. It is to make class.query.php file pluggable across all other projects such that you can just copy the class.query.php and implement export to excel in PHP all the other projects.
There is still room for improvement. You can get my full source code using my Link. I am also planning to extend this class to add colors to column headers and formattings. Feel free to contribute.
Leave a Reply