Welcome to a quick tutorial on how to export data from MYSQL to an Excel Spreadsheet. Need to export some data from the database into Excel spreadsheets? Sadly, PHP is unable to generate Excel files natively.
To export data from MySQL to an Excel Spreadsheet:
- We need to use a third-party library to generate Excel files in PHP, a good recommendation is PHPSpreadsheet.
- Fetch entries from the database table.
- Write them to a Spreadsheet using PHPSpreadsheet.
But just how is this done exactly? Let us walk through a simple example in this guide – Read on!
TABLE OF CONTENTS
DOWNLOAD & NOTES
Here is the download link to the example code, so you don’t have to copy-paste everything.
EXAMPLE CODE DOWNLOAD
Just click on “download zip” or do a git clone. I have released it under the MIT license, so feel free to build on top of it or use it in your own project.
SORRY FOR THE ADS...
But someone has to pay the bills, and sponsors are paying for it. I insist on not turning Code Boxx into a "paid scripts" business, and I don't "block people with Adblock". Every little bit of support helps.
Buy Me A Coffee Code Boxx eBooks
DATABASE TO EXCEL SPREADSHEET
All right, let us now get into the example of how to export MYSQL data to an Excel spreadsheet.
TUTORIAL VIDEO
STEP 1) DOWNLOAD PHPSPREADSHEET
Yep, PHP does not have native functions and extensions to generate Excel files, we will use a library called PHPSpreadsheet.
- One of the lazy ways to get PHPSpreadsheet is to use a package manager called Composer. Just download and install that, quite a hassle, but a one-time effort nonetheless.
- After installing Composer – Open the command line, navigate to your project folder, and run “
composer require phpoffice/phpspreadsheet
“. - That’s all, Composer will automatically fetch the latest version into the
vendor/
project.
If you need more, the official manual for PhpSpreadsheet is available online here, and also check out their GitHub page.
STEP 2) CREATE A DUMMY DATABASE
CREATE TABLE `users` (
`id` bigint(20) NOT NULL,
`name` varchar(255) NOT NULL,
`email` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
ALTER TABLE `users`
ADD PRIMARY KEY (`id`),
ADD UNIQUE KEY `email` (`email`),
ADD KEY `name` (`name`);
INSERT INTO `users` (`id`, `name`, `email`) VALUES
(1, 'John Doe', 'john@doe.com'),
(2, 'Jane Doe', 'jane@doe.com'),
(3, 'Apple Doe', 'apple@doe.com'),
...
For this example, we will be using this dummy user table. Very simple, and just has 3 fields – ID, name, and email.
STEP 3) EXPORT FROM DATABASE TO SPREADSHEET
<?php
// (A) CONNECT TO DATABASE - CHANGE SETTINGS TO YOUR OWN!
$dbhost = "localhost";
$dbname = "test";
$dbchar = "utf8mb4";
$dbuser = "root";
$dbpass = "";
$pdo = new PDO(
"mysql:host=$dbhost;charset=$dbchar;dbname=$dbname",
$dbuser, $dbpass, [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_NAMED
]);
// (B) LOAD PHPSPREADSHEET
require "vendor/autoload.php";
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
// (C) CREATE A NEW SPREADSHEET + WORKSHEET
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->setTitle("Users");
// (D) FETCH USERS + WRITE TO SPREADSHEET
$stmt = $pdo->prepare("SELECT * FROM `users`");
$stmt->execute();
$i = 1;
while ($row = $stmt->fetch()) {
$sheet->setCellValue("A".$i, $row["id"]);
$sheet->setCellValue("B".$i, $row["name"]);
$sheet->setCellValue("C".$i, $row["email"]);
$i++;
}
// (E) SAVE FILE
$writer = new Xlsx($spreadsheet);
$writer->save("users.xlsx");
echo "OK";
This should be pretty straightforward and easy to understand:
- Connect to the database, and remember to change the database settings to your own.
- Captain Obvious. Include and use the PHPSpreadsheet library.
- Create a new spreadsheet, and get the current worksheet.
- Fetch entries from the database and populate the cells.
- Save the spreadsheet onto the server (or force download if you want).
One small thing to take note though – Avoid exporting an entire database and not generate massive spreadsheets… That will probably crash the server.
ALTERNATIVE) EXPORT MYSQL TO CSV FILE
<?php
// (A) CONNECT TO DATABASE - CHANGE SETTINGS TO YOUR OWN!
$dbhost = "localhost";
$dbname = "test";
$dbchar = "utf8mb4";
$dbuser = "root";
$dbpass = "";
$pdo = new PDO(
"mysql:host=$dbhost;charset=$dbchar;dbname=$dbname",
$dbuser, $dbpass, [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_NAMED
]);
// (B) CSV HTTP HEADERS
header("Content-Type: text/csv");
header("Content-Disposition: attachment; filename=\"users.csv\"");
// (C) GET ENTRIES + OUTPUT
$stream = fopen("php://output", "w");
$stmt = $pdo->prepare("SELECT * FROM `users`");
$stmt->execute();
while ($row = $stmt->fetch()) { fputcsv($stream, $row); }
fclose($stream);
If you do not want to use any extra libraries, we can always export them in CSV format instead. Just like the above, except that we are reading entries from the database and directly echoing them out.
EXTRAS
Finally, here are some small extras and links that may be useful to you.
LINKS & REFERENCES
- A massive list of commonly used Excel formula that you might find useful.
- Need to do the opposite of importing data from a spreadsheet instead? Here’s how – Import Excel into MySQL
- Create Excel File In Javascript – Code Boxx
THE END
Thank you for reading, and we have come to the end of this short tutorial. I hope this has helped you to create Excel files in PHP for your project, and if you have anything to share with this guide, please feel free to comment below. Good luck and happy coding!
Hello,
how to force the download ?
thanks
https://code-boxx.com/phpspreadsheet-beginner-tutorial/