From time to time developers may need to be able to export data to an Excel spreadsheet. And there are lots of tools that allow developers to do just that, in many different, robust ways.
But what if you just need to put something together quickly that allows your users to be able to take a data set – for example a database query result – and download that in the form of an Excel spreadsheet? It is easier to do than you might think.
Let’s say your boss just called you into his office and said that he would like you to put an Excel spreadsheet together that will let him see all of the employees in your company. And let’s say that you have an intranet page that shows all of your employees on the screen in a table. Since there is a good chance that your boss isn’t the only person that might find an Excel spreadsheet of the employee list useful, you decide you’re going to take the employee list from the intranet and allow your entire company to download a copy of it in Excel format.
Before we get started, we will need a data set to work with. For this example we are going to keep our employee list very simple:
<?php $employees = array( array('fname' => 'Hans', 'lname' => 'Auf', 'room' => '1D', 'ext' => '1145'), array('fname' => 'Yung', 'lname' => 'Ling', 'room' => '3C', 'ext' => '1220'), array('fname' => 'Mike', 'lname' => 'Rowe', 'room' => '4E', 'ext' => '1232'), array('fname' => 'Joe', 'lname' => 'Shmoe', 'room' => '2B', 'ext' => '1159'), array('fname' => 'Mary', 'lname' => 'Smith', 'room' => '1A', 'ext' => '1111'), ); ?>
Now, to add a little more complexity to our simple example (because we can 
) we are going to make a listing of column headers for our employee table. This will also serve as our spreadsheet header row as well:
<?php $columns = array( 'fname' => 'First Name', 'lname' => 'Last Name', 'room' => 'Office Address', 'ext' => 'Phone Number', ); ?>
If you look closely you can see that we mapped the columns array keys to the query result column names. The reason we are doing this is because as we present our HTML table of this data we can control the column order and column heading text with this array. Let’s have a look at that:
<?php /** * Build a mock data set as though it were a database query result */ $employees = array( array('fname' => 'Hans', 'lname' => 'Auf', 'room' => '1D', 'ext' => '1145'), array('fname' => 'Yung', 'lname' => 'Ling', 'room' => '3C', 'ext' => '1220'), array('fname' => 'Mike', 'lname' => 'Rowe', 'room' => '4E', 'ext' => '1232'), array('fname' => 'Joe', 'lname' => 'Shmoe', 'room' => '2B', 'ext' => '1159'), array('fname' => 'Mary', 'lname' => 'Smith', 'room' => '1A', 'ext' => '1111'), ); /** * Build a custom header array for column headings * * NOTE[0]: You could have done this in the query by aliasing column names, but * doing it this way gives us a little something more to learn. ;) * * NOTE[1]: If you miss it the first time around, come back and look at this to * see how you can control the column display order with this array. */ $columns = array( 'fname' => 'First Name', 'lname' => 'Last Name', 'room' => 'Office Address', 'ext' => 'Phone Number', ); ?> <html> <head> <title>Company employee list</title> </head> <body> <h1>Our company employee list</h1> <?php // If there are employees list them ?> <?php if (count($employees)): ?> <!-- Let's build our employee table --> <table border="1"> <?php // Let's build the header row first ?> <tr> <?php foreach ($columns as $column): ?> <th><?php echo $column ?></th> <?php endforeach; ?> </tr> <?php // Now let's build the body ?> <?php foreach ($employees as $employee): ?> <tr> <?php /* Let's make this a little dynamic... Yes, this is a lot of */ ?> <?php /* looping, but it is an exercise in what you can do */ ?> <?php foreach($columns as $alias => $column) : ?> <td><?php echo $employee[$alias] ?></td> <?php endforeach; ?> </tr> <?php endforeach; ?> </table> <?php else: ?> <div style=" background: #fcf; color: #900; border: solid 1px #900; font-weight: bold; padding: 10px 20px;" >There are no employees on the list</div> <?php endif; ?> </html> </body>
So now we need a mechanism to allow our users to download this data as an Excel spreadsheet. We could use pretty much anything that lets the page know we want to export (like a simple link) but for this exercise we are going to use a form. Why? No reason really, other than it is sometimes easier to tell a user to “push the button” instead of “click on the link that says …”.
As part of this we are also going to tie in a process that checks for the form button based export request, and if it is set, build the necessary data to allow the download. We do that like this:
<?php /** * Build a mock data set as though it were a database query result */ $employees = array( array('fname' => 'Hans', 'lname' => 'Auf', 'room' => '1D', 'ext' => '1145'), array('fname' => 'Yung', 'lname' => 'Ling', 'room' => '3C', 'ext' => '1220'), array('fname' => 'Mike', 'lname' => 'Rowe', 'room' => '4E', 'ext' => '1232'), array('fname' => 'Joe', 'lname' => 'Shmoe', 'room' => '2B', 'ext' => '1159'), array('fname' => 'Mary', 'lname' => 'Smith', 'room' => '1A', 'ext' => '1111'), ); /** * Build a custom header array for column headings * * NOTE[0]: You could have done this in the query by aliasing column names, but * doing it this way gives us a little something more to learn. ;) * * NOTE[1]: If you miss it the first time around, come back and look at this to * see how you can control the column display order with this array. */ $columns = array( 'fname' => 'First Name', 'lname' => 'Last Name', 'room' => 'Office Address', 'ext' => 'Phone Number', ); /** * If the export request was sent, handle that here */ if (!empty($_POST['export'])) { // Set the field delimiter as a tab for Excel $delimiter = "\t"; // Set the End Of Line character to force a new row $eol = "\n"; // Start the spreadsheet by creating the header row based on our columns $sheet = implode($delimiter, $columns) . $eol; /** * Create the body of the spreadsheet by way of a loop, looping the columns * array inside of each employee iteration so that column order matches. */ foreach ($employees as $employee) { // Create a row string for building each row $row = ''; // Loop through the columns to make columnar data that matches the header foreach ($columns as $name => $text) { $row .= $employee[$name] . $delimiter; } // Pluck of the last tab and append a new line to the row $sheet .= trim($row, $delimiter) . $eol; } // Create the download file name - adding the date to make it a little unique $filename = 'employee-list-' . date('Ymd') . '.xls'; // Send the appropriate response headers header('Content-type: application/x-msdownload'); header('Content-type: application/vnd.ms-excel'); header('Content-Disposition: attachment; filename="'.$filename.'"'); /** * Normally we would set our no-cache header here, but... * Internet Explorer cannot handle SSL download saves if no-cache is set * anywhere. For most this would not be an issue, but if you are allowing * exports to Excel over SSL then this will bite you in the butt when your * users are using IE */ //header("Pragma: no-cache"); header("Expires: 0"); // Output the spreadsheet as a download print $sheet; exit; } ?> <html> <head> <title>Company employee list</title> </head> <body> <h1>Our company employee list</h1> <?php // If there are employees list them ?> <?php if (count($employees)): ?> <form method="post" action="<?php echo basename(__FILE__) ?>" id="export-form"> <!-- This will just be a simple button to trigger the export action --> <p> <input type="submit" name="export" id="ef-export" value="Save this list as Excel" /> </p> </form> <!-- Let's build our employee table --> <table border="1"> <?php // Let's build the header row first ?> <tr> <?php foreach ($columns as $column): ?> <th><?php echo $column ?></th> <?php endforeach; ?> </tr> <?php // Now let's build the body ?> <?php foreach ($employees as $employee): ?> <tr> <?php /* Let's make this a little dynamic... Yes, this is a lot of */ ?> <?php /* looping, but it is an exercise in what you can do */ ?> <?php foreach($columns as $alias => $column) : ?> <td><?php echo $employee[$alias] ?></td> <?php endforeach; ?> </tr> <?php endforeach; ?> </table> <?php else: ?> <div style=" background: #fcf; color: #900; border: solid 1px #900; font-weight: bold; padding: 10px 20px;" >There are no employees on the list</div> <?php endif; ?> </html> </body>
And there you have it. A simple, dynamic Excel exporter that will take your current data and dump it into a downloadable spreadsheet for your users.