Currently Browsing: Home » 3 Easy Steps to Create Excel Spreadsheets in PHP

3 Easy Steps to Create Excel Spreadsheets in PHP

Spreadsheets are simple files that help you manage and organize data. They are used everywhere and have become quite popular. You can find them by using Microsoft Excel, Open Office, or even an online alternative such as Google Docs.

Spreadsheet

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.

Download the Files

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!

Comments? Questions? Concerns?

It’s your time now. If you have any comments, questions, or concerns, please post them below!

This entry was posted on Wednesday, February 17th, 2010 at 19:42:04. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

3 Responses to “3 Easy Steps to Create Excel Spreadsheets in PHP”

  1. Aymen BEN says:

    WoooooW This is awesome… how simple it is :)

    I wonder what the libraries are there for :D

  2. @Aymen BEN: Thanks! When it’s this simple, I don’t understand why there are libraries either. :)

  3. thulasi says:

    It is so simple and so efficient.very nice.

    Using this code we are able to create an excel sheet and write data to an excel sheet. I want to import(read) the excel sheet data into php. Can you help me how to do that?

Leave a Reply

Want to be notified when someone replies? Subscribe to this post's comment RSS feed.
Any field marked with a * is required.