Create Excel Spreadsheets in PHP

3 Easy Steps to Create Excel Spreadsheets in PHP

Generating Spreadsheets

Since computers and automation have become second nature, spreadsheet generation isn’t out of the ordinary. With MySQL databases, it becomes even more imperative, as spreadsheets are perfect to represent the data located in these structures.

Upon searching for how to create excel spreadsheets using PHP, most of the results came back with libraries that do the job. This seemed to be too much of a hassle. That’s why I’ve created 3 easy steps to create excel spreadsheets without any libraries.

Step 1: The Content-Type

The first thing you have to do is add an application/vnd.ms-excel content-type to your PHP file:

header("Content-Type: application/vnd.ms-excel");

Easy enough? Let’s move on to the actual data.

Step 2: Adding the Data

Data is just as simple. Separate cells/columns with tabs (“\t” in PHP) and move to the next row with a newline (“\n” in PHP).

Here are a few PHP echo statements that will do the trick:

echo 'First Name' . "\t" . 'Last Name' . "\t" . 'Phone' . "\n";
echo 'John' . "\t" . 'Doe' . "\t" . '555-5555' . "\n";

I’ve separated the tabs (\t) and the new lines (\n) so you can easily see the structure. The above would create a spreadsheet that looks like this:

First Name     Last Name     Phone
John           Doe           555-5555

Step 3: Downloading the Spreadsheet

Now that you’ve set the content-type and created the data, just open up the PHP file in a browser. You’ll be asked to download the spreadsheet. If you would like to give a name to the spreadsheet, just add the following:

header("Content-disposition: attachment; filename=spreadsheet.xls");

All you need to do is change spreadsheet.xls to the name of your spreadsheet. Note that the above header also ensures that Internet Explorer will ask you to download the file rather than trying to display it.

Conclusion

That’s really all there is to create a spreadsheet. For those of you who are using Microsoft Excel 2007, you might see the following message when opening the spreadsheet:

The file you are trying to open, ‘filename.xls’, is in a different format than specified by the file extension… [message condensed]

If so, just hit the “Yes” button and you’re good to go!