3 Steps To Export Data From MYSQL To Excel In PHP

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!

ⓘ I have included a zip file with all the sample code at the start of this tutorial, so you don’t have to copy-paste everything… Or if you just want to dive straight in.

 

 

QUICK SLIDES

 

TABLE OF CONTENTS

Download & Notes Database to Excel Useful Bits & Links
Tutorial Video The End

 

DOWNLOAD & NOTES

First, here is the download link to the example source code as promised.

 

QUICK NOTES

  • A copy of PHPSpreadsheet is not included in the zip file.
  • Create a dummy database and import 2-database.sql.
  • Change the database settings in 3-db-spreadsheet.php to your own, and launch in the browser (or command line).
If you spot a bug, feel free to comment below. I try to answer short questions too, but it is one person versus the entire world… If you need answers urgently, please check out my list of websites to get help with programming.

 

EXAMPLE CODE DOWNLOAD

Click here to download the source code, I have released it under the MIT license, so feel free to build on top of it or use it in your own project.

 

 

DATABASE TO EXCEL SPREADSHEET

All right, let us now get into the example of how to export MYSQL data to an Excel spreadsheet.

 

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

2-database.sql
CREATE TABLE `users` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

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, just has 3 fields – ID, name, and email.

 

STEP 3) EXPORT FROM DATABASE TO SPREADSHEET

3-db-spreadsheet.php
<?php
// (A) CONNECT TO DATABASE - CHANGE SETTINGS TO YOUR OWN!
$dbhost = "localhost";
$dbname = "test";
$dbchar = "utf8";
$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:

  1. Connect to the database, remember to change the database settings to your own.
  2. Captain Obvious. Include and use the PHPSpreadsheet library.
  3. Create a new spreadsheet, get the current worksheet.
  4. Fetch entries from the database and populate the cells.
  5. Save the spreadsheet onto the server (or force download if you want).

One small thing to take note though – You might want to avoid exporting an entire database and not generate massive spreadsheets… That will probably crash the server.

 

 

ALTERNATIVE) EXPORT MYSQL TO CSV FILE

4c-mysql-csv.php
<?php
// (A) CONNECT TO DATABASE - CHANGE SETTINGS TO YOUR OWN!
$dbhost = "localhost";
$dbname = "test";
$dbchar = "utf8";
$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
$stmt = $pdo->prepare("SELECT * FROM `users`");
$stmt->execute();
while ($row = $stmt->fetch()) {
  echo implode(",", $row) . PHP_EOL;
}

If you do not want to use any extra libraries, we can always export in CSV format instead. Just like the above, except that we are reading entries from the database and directly echoing them out.

 

 

USEFUL BITS & LINKS

Finally, here are some small extras and links that may be useful to you.

 

USEFUL LINKS & REFERENCES

 

TUTORIAL VIDEO

 

INFOGRAPHIC CHEAT SHEET

Export MySQL To Excel Spreadsheet (Click To Enlarge)

 

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!

2 thoughts on “3 Steps To Export Data From MYSQL To Excel In PHP”

Leave a Comment

Your email address will not be published.