3 Steps to Create Excel Files With PHP & MYSQL

INTRODUCTION

GAME OF EXCEL

Welcome to a tutorial on how to create Excel spreadsheets with PHP and MYSQL. Need to export data from MySQL to Excel spreadsheets from PHP? Sadly, PHP is unable to generate Excel files natively. So in order to generate Excel files in PHP, we need to download and use a library called PHPSpreadsheet.

But just how do we do it? We will walk through the exact steps with working code examples in this guide – Read on to find out!

ⓘ 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.

 

 

 

PREAMBLE

DOWNLOAD & NOTES

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

 

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.

 

QUICK NOTES

  • A copy of PHP Spreadsheet is not included in the zip file.
    • Please download and install Composer first.
    • Then navigate to your project folder in the command prompt (or terminal), run composer require phpoffice/phpspreadsheet to fetch the latest version.
  • There are 3 sets of examples:
    • Run 2-create-xlsx.php for the simple spreadsheet example.
    • Database example – Create a dummy database, import 3a-dummy.sql, then change the database settings in 3b-mysql-xlsx.php to your own.
    • Lastly, 4a-4c are the CSV alternatives.

If you spot a bug, please feel free to comment below. I try to answer 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.

 

STEP 1

INSTALLING PHP SPREADSHEET

In order to create XLSX files, we are going to need a library called PhpSpreadsheet.

 

HOW TO INSTALL

One of the easier ways to get PHPSpreadsheet is to install an application called Composer. Then fire up the command line (or terminal) and run “composer require phpoffice/phpspreadsheet” in your project folder:

1-install.txt
D:\http>composer require phpoffice/phpspreadsheet
Using version ^1.14 for phpoffice/phpspreadsheet
./composer.json has been created
Loading composer repositories with package information
Updating dependencies (including require-dev)
Package operations: 10 installs, 0 updates, 0 removals
- Installing psr/http-message (1.0.1): Downloading (100%)
- Installing psr/http-factory (1.0.1): Downloading (100%)
- Installing psr/http-client (1.0.1): Downloading (100%)
- Installing psr/simple-cache (1.0.1): Loading from cache
- Installing markbaker/matrix (1.2.1): Downloading (100%)
- Installing markbaker/complex (1.5.0): Downloading (100%)
- Installing myclabs/php-enum (1.7.6): Downloading (100%)
- Installing symfony/polyfill-mbstring (v1.18.1): Downloading (100%)
- Installing maennchen/zipstream-php (2.1.0): Downloading (100%)
- Installing phpoffice/phpspreadsheet (1.14.1): Downloading (100%)
phpoffice/phpspreadsheet suggests installing mpdf/mpdf (Option for rendering PDF with PDF Writer)
phpoffice/phpspreadsheet suggests installing dompdf/dompdf (Option for rendering PDF with PDF Writer)
phpoffice/phpspreadsheet suggests installing tecnickcom/tcpdf (Option for rendering PDF with PDF Writer)
phpoffice/phpspreadsheet suggests installing jpgraph/jpgraph (Option for rendering charts, or including charts with PDF or HTML Writers)
Writing lock file
Generating autoload files
2 packages you are using are looking for funding.
Use the `composer fund` command to find out more!

That’s it. Just add a require "vendor/autoload.php" in your PHP scripts to include the PHPSpreadsheet library.

 

GITHUB ALTERNATIVE

Alternatively, you can also download from GitHub, check out their documentation.

 

 

STEP 2

CREATING XLSX SPREADSHEETS

After getting the PhpSpreadSheet package, you are ready to generate XLSX files. Simply load the library and create your spreadsheets.

 

SIMPLE SPREADSHEET GENERATOR

2-create-xlsx.php
<?php
// (A) CREATE PHPSPREADSHEET OBJECT
require "vendor/autoload.php";
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

// (B) CREATE A NEW SPREADSHEET
$spreadsheet = new Spreadsheet();
 
// (C) NEW WORKSHEET
$sheet = $spreadsheet->getActiveSheet();
$sheet->setTitle('Testing');
$sheet->setCellValue('A1', 'Hello World !');
$sheet->setCellValue('A2', 'Goodbye World !');
 
// (D) YOU CAN ALSO USE FORMULAS!
$sheet->setCellValue('B1', '5');
$sheet->setCellValue('B2', '6');
$sheet->setCellValue('B3', '=SUM(B1:B2)');
 
// (E) OUTPUT
$writer = new Xlsx($spreadsheet);

// (E1) THIS WILL SAVE TO A FILE ON THE SERVER
$writer->save('test.xlsx');

/* (E2) OR FORCE DOWNLOAD
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="demo.xlsx"');
header('Cache-Control: max-age=0');
header('Expires: Fri, 11 Nov 2011 11:11:11 GMT');
header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT');
header('Cache-Control: cache, must-revalidate');
header('Pragma: public');
$writer->save('php://output');
*/

Yep, the basic usage shouldn’t be too difficult to understand –

  • Load the PHPSpreadsheet library.
  • Create a new spreadsheet.
  • Add a new worksheet.
  • Populate the cells.
  • Save the spreadsheet.

 

 

STEP 3

FROM DATABASE TO EXCEL SPREADSHEET

Now that you have a good idea of how to generate a basic Excel file, this final section will walk you through on how to “export” data from the database to an Excel file.

 

DUMMY DATABASE

For you guys who need a dummy database and some dummy data to work with, here it is:

3a-dummy.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'),
(4, 'Beck Doe', 'beck@doe.com'),
(5, 'Charlie Doe', 'charlie@doe.com'),
(6, 'Charles Doe', 'charles@doe.com'),
(7, 'Dion Doe', 'dion@doe.com'),
(8, 'Dee Doe', 'dee@doe.com'),
(9, 'Emily Doe', 'emily@doe.com'),
(10, 'Ethan Doe', 'ethan@doe.com'),
(11, 'Frank Doe', 'frank@doe.com'),
(12, 'Gina Doe', 'gina@doe.com'),
(13, 'Hela Doe', 'hela@doe.com'),
(14, 'Hubert Doe', 'hubert@doe.com'),
(15, 'Ivy Doe', 'ivy@doe.com'),
(16, 'Ingrid Doe', 'ingrid@doe.com'),
(17, 'James Doe', 'james@doe.com'),
(18, 'Jace Doe', 'jace@doe.com'),
(19, 'Kate Doe', 'kate@doe.com'),
(20, 'Luke Doe', 'luke@doe.com');

 

DATABASE EXPORT SCRIPT

3b-mysql-xlsx.php
<?php
// (A) CONNECT TO DATABASE
// ! CHANGE THESE SETTINGS TO YOUR OWN !
$dbhost = 'localhost';
$dbname = 'test';
$dbchar = 'utf8';
$dbuser = 'root';
$dbpass = '';
try {
  $pdo = new PDO(
    "mysql:host=$dbhost;charset=$dbchar;dbname=$dbname",
    $dbuser, $dbpass, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]
  );
} catch (Exception $ex) {
  die($ex->getMessage());
}

// (B) PHPSPREADSHEET
require "vendor/autoload.php";
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

// (C) CREATE A NEW SPREADSHEET + POPULATE DATA
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->setTitle('Users');
$stmt = $pdo->prepare("SELECT * FROM `users`");
$stmt->execute();
$i = 1;
while ($row = $stmt->fetch(PDO::FETCH_NAMED)) {
  $sheet->setCellValue('A'.$i, $row['id']);
  $sheet->setCellValue('B'.$i, $row['name']);
  $i++;
}

// (D) SAVE
$writer = new Xlsx($spreadsheet);
$writer->save('test.xlsx');
echo "OK";

This should be pretty straightforward and easy to understand again:

  • Part A – Connect to the database, remember to change the database settings to your own.
  • Part B – Captain Obvious. Include the PhpSpreadsheet library and create a new PhpSpreadsheet object.
  • Part C – Create a new spreadsheet, add a new worksheet. Then fetch entries from the database and populate the cells.
  • Part D – 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 choke the server.

 

 

ALTERNATIVE

GENERATING CSV FILES

If you don’t like PhpSpreadsheet for some reason, or just want to create simple CSV files – Here is how to do it with “raw PHP”, without the need to install any extra packages.

 

CREATING CSV FILES ON THE SERVER

4a-csv-write.php
<?php
// (A) DATA TO WRITE
$data = [
  ["Apple", "Orange", "Pear"],
  ["Grape", "Durian", "Papaya"]
];
 
// (B) WRITE TO CSV FILE
$file = fopen("fruits.csv","w");
foreach ($data as $line) { fputcsv($file, $line); }
fclose($file);
echo "OK";

CSV is heck a lot easier, just 3 “main functions” to know:

  • fopen(FILE, MODE) to create a new CSV file.
  • fputcsv(FILE, LINE) to put a line into the CSV file. Take note of that LINE is an array.
  • fclose(FILE) to properly close and write the CSV file.

But of course, CSV is pure data – No formulas, cannot set cell colors, no column dimensions, and all the funky stuff.

 

FORCE DOWNLOAD CSV FILES

4b-csv-download.php
<?php
// (A) SERVE HTTP HEADERS
header('Content-Type: text/csv');
header('Content-Disposition: attachment; filename="fruits.csv"');
 
// (B) JUST OUTPUT DIRECTLY AS A STRING
$data = [
  ["Apple", "Orange", "Pear"],
  ["Grape", "Durian", "Papaya"]
];
foreach ($data as $row) {
  foreach ($row as $column) { echo "$column,"; }
  echo PHP_EOL;
}

Not a mystery… CSV files are literally just text files in the format of column, column, column. We just have to output the proper HTTP headers and data format.

 

FROM MYSQL TO CSV FILE

4c-mysql-csv.php
<?php
// (A) CONNECT TO DATABASE
// ! CHANGE THESE SETTINGS TO YOUR OWN !
$dbhost = 'localhost';
$dbname = 'test';
$dbchar = 'utf8';
$dbuser = 'root';
$dbpass = '';
try {
  $pdo = new PDO(
    "mysql:host=$dbhost;charset=$dbchar;dbname=$dbname",
    $dbuser, $dbpass, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]
  );
} catch (Exception $ex) {
  die($ex->getMessage());
}
 
// (B) 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(PDO::FETCH_NAMED)) {
  echo $row['id'] . "," . $row['name'] . PHP_EOL;
}

Just like the above, except that we are reading entries from the database and directly echoing them out.

 

 

EXTRA

USEFUL BITS & LINKS

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

 

USEFUL LINKS & REFERENCES

 

CHEAT SHEET

Create Excel Spreadsheet With PHP MYSQL (Click To Enlarge)

 

CLOSING

WHAT NEXT?

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!

6 thoughts on “3 Steps to Create Excel Files With PHP & MYSQL”

  1. Hi, I encountered this problem while trying to run the codes,
    Warning: require(vendor/autoload.php): failed to open stream: No such file or directory in C:\wamp64\www\php\2-generate-xlsx.php on line 3
    ( ! ) Fatal error: require(): Failed opening required ‘vendor/autoload.php’ (include_path=’.;C:\php\pear’) in C:\wamp64\www\php\2-generate-xlsx.php on line 3

    Where can I obtain the vendor/autoload.php file? Thanks in advanced!

Leave a Comment

Your email address will not be published. Required fields are marked *