Downloading data for use in Excel (from PHP / MySQL)
Archive - Originally posted on "The Horse's Mouth" - 2008-01-25 07:24:03 - Graham Ellis
Requirement: To allow a visitor to your web site to click on a link that downloads data from the server into a Microsoft Excel spreadhseet.
Solutions - I'm going to offer you two:
a) To send the output as a text file - delimited by tabs between columns, and with new lines for each new line. Add appropriate content type headers (such as octet-stream) to save the file to local disc rather than displaying it, and pass back a file name with a .xls extension to provide direction to the operating system as to how to handle the file. In most environments, Excel will do the import for you.
or b) To send the output as an HTML table, in which case Excel will interpret the rows and columns of the tables as its data cells. I've written an example of this too - using an alternative content type to show the flexibility that's available. And I have also put a formula into one of the cells so that you can get excel to fill in the extra boxes for you.
This example just populates a spread sheet with some numbers as a "proof of concept" but it's still a series of useful pointers, I hope. You can Run the example or see the source code
You'll notice in the source code of both examples that I've added in extra headers in my response to ensure that the browser doesn't cache the results - that's done to make sure that you get a fresh download each time you run the scripts, rather than being given stored old data.