Export Data from Your Website as an Excel Spreadsheet
https://www.htmlgoodies.com/beyond/export-data-from-your-website-as-an-excel-spreadsheet.html (Back to article)
Excel is one of the most popular formats for viewing and working with tabular data. For that reason, it only makes sense to give your visitors the option of downloading website data as an Excel spreadsheet. Many sites have opted for the simpler Comma Separated Values (CSV) text format, fearing that Excel files could only be generated using Microsoft technologies. Not true; the PHPExcel PHP library makes it easy to read and write Excel content. In today's tutorial, we'll get a taste of its capabilities by dumping some web domain statistics in a spreadsheet and serving it to the client - all at the push of a button!
PHPExcel is part of the larger PHPOffice collection. It officially came together as a GitHub project on June 10, 2012. It incorporates three projects: PHPExcel, PowerPoint, and Word.
Developer Maarten Balliauw actually began working on PHPExcel back in 2007 because he wanted "to check how hard it could be to create Excel 2007 files using PHP5." To his surprise, it was doable! Encouraged by his progress, Balliauw continued with the open-source project that he hosted on Microsoft CodePlex. This eventually led to a version 1.0.0 release of PHPExcel on February 23, 2007. Of the tree PHPOffice projects, PHPExcel remains the most active one.
The Set Up
You'll need a web server that can run PHP files. There are a lot of good free development servers to choose from. I personally use WampServer because it's so easy to use and configure.
The next step is to fetch PHPExcel. You can download PHPExcel right from Github. I chose to download it as a .zip archive. To use the PHPExcel library, all you need to do is extract its contents to a directory under your server's web root. I created a subdirectory called "phpExcel" because I like to keep each project separate from one another. The directory structure should look like this:
The main library interface file is PHPExcel.php. It's located in the top-level "Classes" folder, so, if you create PHP file in your project root (/phpExcel), then you can bring the PHPExcel.php into your project by including the following line at the top of your PHP file:
<?php require_once 'Classes/PHPExcel.php';
Our spreadsheet will contain some stats for three web domains. In fact, it's the exact same data that will be displayed on our webstats.html landing page.
In a real production scenario, the data would be coming from a database, feed, or a combination of both. For today's demo, we'll keep things simple by storing everything in an array, where each element contains an associative array that uses the column headers as the key:
$webstats = array( array( "Domain"=>"robgravelle.com", "Status"=>"200 OK", "Speed"=>0.57, "Last Backup"=>"2017-10-27", "SSL Certificate?"=>"No" ), array( "Domain"=>"buysci-fi.com", "Status"=>"301 redirect detected", "Speed"=>1.08, "Last Backup"=>"2017-10-27", "SSL Certificate?"=>"Yes" ), array( "Domain"=>"captains-blog.com", "Status"=>"500 Server Error!", "Speed"=>0.52, "Last Backup"=>"2017-09-27", "SSL Certificate?"=>"Yes" ) );
Serving up a Spreadsheet
Since there are a number of very specific steps to follow in order to create, modify, and serve a spreadsheet to the client, let's break them down.
Instantiating the Spreadsheet Object
Before we can work with a spreadsheet, we have to instantiate the PHPExcel library using the new keyword:
$objPHPExcel = new PHPExcel();
It returns an empty Excel object for us to populate.
Writing to the Active Sheet
Referencing the active sheet is as easy as calling our Excel object's getActiveSheet() method. The Sheet object has many of its own methods for working with both the sheet's content and styles. For instance, you can set a sheet's title, individual cell values, and font sizes. Even more interesting is the fromArray() method. It allows us to dump an entire array's contents onto the sheet:
$activeSheet = $objPHPExcel->getActiveSheet(); $activeSheet->setTitle('Website Stats Page'); $activeSheet->setCellValue('A1', 'Website Stats Page'); $activeSheet->getStyle("A1")->getFont()->setSize(16); //output headers $activeSheet->fromArray(array_keys($webstats), NULL, 'A3'); //output values $activeSheet->fromArray($webstats, NULL, 'A4');
Setting the HTTP Headers
Although you can save a spreadsheet to disk, we don't want to do that. Instead, we're going to return it to the client. To do that, we have to set some HTTP headers:
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); header('Content-Disposition: attachment;filename="webstats.xlsx"'); header('Cache-Control: max-age=0');
The Content-Type header tells the client (browser in our case) to expect a spreadsheet.
The Content-Disposition header sets the file name.
The Cache-Control header tells the client that the response is stale from the get-go and so they SHOULD revalidate the response (e.g. with the If-Not-Modified header) before using a cached copy.
Writing the Response
The last step is to write the Excel file to the output stream. This is done by:
- creating a writer object using the PHPExcel_IOFactory. The second parameter determines the output format.
- Saving the Excel object to the PHP output stream.
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007'); $objWriter->save('php://output');
Triggering the File Download
<table border=1 cellspacing=0 cellpadding=3> <tbody> <tr style="background-color:#E5E4E2;"> <th>Domain</th> <th>Status</th> <th>Speed</th> <th>Last Backup</th> <th>SSL Certificate?</th> </tr> <tr> <td><a href="http://www.robgravelle.com">robgravelle.com</a></td> <td style="background-color:green;color:white;">200 OK</td> <td>0.57 seconds</td> <td>2017-10-27</td> <td>No</td> </tr> <tr> <td><a href="http://www.buysci-fi.com">buysci-fi.com</a></td> <td style="background-color:orange;color:white;">301 redirect detected</td> <td>1.08 seconds</td> <td>2017-10-27</td> <td>Yes</td> </tr> <tr> <td><a href="http://www.captains-blog.com">captains-blog.com</a></td> <td style="background-color:darkred;color:white;">500 Server Error!</td> <td>0.52 seconds</td> <td>2017-09-27</td> <td>Yes</td> </tr> </tbody> </table> <form action="download.php"> <p><button type="submit">download as Excel Spreadsheet</button></p> </form>
Running the Demo
To run the demo, bring up the webstats.html page via your web server. The URL will be something like
http://localhost/phpExcel/webstats.html. Here's what it looks like:
Once you click the download button, the browser should ask you what you'd like to do with the file - i.e. Open, Save, or Save As.
Here is the webstats.xlsx file with our data (Note that column widths were increased to show the full cell contents):
Download this .zip archive to explore the two files that we worked on here today.
In this tutorial, we got a taste of PHPExcel's capabilities by dumping some web domain statistics. In future articles, we'll delve deeper into PHPExcel's formatting capabilities by dynamically replicating the webstats HTML table in a spreadsheet as well as address how to handle errors.
Rob's alter-ego, "Blackjacques", is an accomplished guitar player, who has released several CDs and cover songs. His band, Ivory Knight, was rated as one of Canada's top hard rock and metal groups by Brave Words magazine (issue #92).