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.
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 PhoneJohn Doe 555-5555Step 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!
