web page best approach to implement excel save as

Question

What is considered the best way to code which will allow user to save table data by clicking "save as"

Here is the scenario

I've created a web page that list data from a mysql db (app created in php).

The page lists the data and provides a "Save as" button so the data can be save to the users local computer (csv, xml, etc.).

Technical

Is the following the best way to do this 1) save the data to a file from in the same php program that queried that mysql db? 2) when user clicks "save as" on web page, ajax streams the data from #1 (server) to the users local computer

I've read a few things on how to create xml data (opendocument, openxml) and php excel/xml libs but just don't completely understand the flow; should a file be created at time of query, how does the data get to the users local computer, etc. Or is there a different approach to do this?

Thanks

Answer 1

There are mostly two approaches:

• Generate a file on the server. You can do this before anything else, when the first user hits the download link or every time someone downloads the file. You can do this by providing a download link that is interpreted by your web server or web framework as a location of the file, e.g.

  http://example.org/uploads/file1.csv

  or you write a custom action/page for downloads, like:

  http://example.org/download.php?filename=file1.csv (how to make a download link in PHP)

• Generate an HTTP response which will be interpreted by the client (the browser) as a download. You won't need to write an extra file to disk, but it's most useful for filetypes, that you can easily generate in your program, e.g. rows of CSV files and the like. Here's an example for CSV files: http://code.stephenmorley.org/php/creating-downloadable-csv-files/

In any way the browser knows, that it deals with a file download and not with just another webpage is through the HTTP headers:

// specify the MIME-type 
// (e.g. if it's an image or PDF most browser can display the content directly)
header('Content-Type: text/csv; charset=utf-8');

// The Content-Disposition response-header field has been proposed 
// as a means for the origin server to suggest a default filename 
// if the user requests that the content is saved to a file.
header('Content-Disposition: attachment; filename=data.csv');

You can read more about that here: http://www.w3.org/Protocols/rfc2616/rfc2616-sec19.html

In the end, it's really up to you and your requirements. Is it a low traffic site and and the files are small? - Generate the files beforehand and let the web server deal with the download links. Is it a high traffic site with lot of small files? - maybe generate it on the fly (like in the CSV example). Not everyone should be able to download every file and authentication is managed by your php project? - it will be easier to use a custom download.php script to check permissions.