trivial "excel download" in php

Personally I've never liked Excel, but it's a critical tool in business, and lots of people I collaborate with find in useful. (I think from an early age I was doing my data wrangling in Perl, so my Excel use was always clumsy)

Anyway, it's obviously usually easy to generate tab-delimited files, but it's even easier to then wrap that as a download-able "Excel file". (If you try to do the noble thing and just offer a ".tab" download, the Excel import wizard asks lots of questions that might distract a newbie, even though the defaults all work fine - it's also a little fiddly to tell a Mac that yes, please try opening this .tab in Excel even though you don't know Excel speaks "tab" kthxbye.)

So, as this page helpfully points out in PHP you can add these lines near the top:

header("Content-Disposition: attachment; filename=\"FILENAME.xls\""); 

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

and the page will trigger a download of a file, rather than blast stuff to the screen. (And obviously any CGI-like platform can do similar)

There are a couple things to watch out for if you're doing this in PHP, like making sure you don't have any extraneous white space outside your <?php ?> tags, and you should run each chunk of data through something like this (stolen from the previously linked page)

function cleanData(&$str)

  {

    $str = preg_replace("/\t/", "\\t", $str);

    $str = preg_replace("/\r?\n/", "\\n", $str);

    if(strstr($str, '"')) $str = '"' . str_replace('"', '""', $str) . '"';

  }

to ensure the data is cleaned and ready to be join()'d with tabs.

Anyway, having a download that Just Works is likely a big and empowering win for your users, especially if they are Excel-savvy but not super technical.

UPDATE: In the comments Nathan Tolbert writes some additional steps that might help things play better with IE:
Just FYI, using this exact same technique, we've had issues with a few oddball versions/configurations of IE failing to properly download and open the attachment if you don't let it cache the attachment. Assuming a filename that's autogenerated based on a timestamp, we add something like the following, which helps IE not be stupid:

$expire = date("D, m M Y H:i:s", $pickAnExpirationDate);
header("Expires: $expire GMT");
header("Cache-Control: ");
header("Pragma: ");