Background
Previously we have seen how to make a native Excel spreadsheet through the use of the PEAR extension. This article is about generating comma (.csv.) and tab delimited spreadsheets that are readable in many popular programs, and useful in a multitude of websites and applications. You need no external packages or extensions to generate these files – PHP does this natively.
Differences
Comma separated value files known by their .csv extension are the most popular alternative to the native Excel .xls spreadsheet file. One problem is that .csv files are actually text files with column values separated with commas, and this can cause problems when your data also has commas in it. The additional code necessary to solve this problem is often not worth it, so many people generate tab delimited files with PHP instead. When creating tab delimited files, you can use both the native Excel .xls extension or the .tsv (tab separated values) extension. However, a user’s OS may not be setup to recognize the .tsv extension and may try to open it in a regular text editor. The better option is to use the Excel .xls extension for your tab delimited files to guarantee that the user will be able to open it successfully with a myriad of browser, OS, and software combinations – I have found that many other non Microsoft spreadsheet programs support the .xls extension. In the below script, I have opted below for an .xls tab delimited file, but I have also included the necessary changes in the comments to generate .csv files as well.
PHP Code
- Execute the below script and generate an .xls tab delimited file.
- Execute the below script and generate a .tsv tab separated values file.
<?php /* Data to be inserted into excel in an array of arrays * Each array is the avg monthly temp of a different city */ $data = array ( array ( "City" => "Seattle", "Jan" => 46, "Feb" => 50, "Mar" => 53, "Apr" => 58, "May" => 64, "Jun" => 70, "Jul" => 75, "Aug" => 76, "Sep" => 70, "Oct" => 60, "Nov" => 51, "Dec" => 46 ), array ( "City" => "New York", "Jan" => 39, "Feb" => 42, "Mar" => 50, "Apr" => 60, "May" => 71, "Jun" => 79, "Jul" => 85, "Aug" => 83, "Sep" => 76, "Oct" => 64, "Nov" => 54, "Dec" => 44 ), array ( "City" => "San Francisco", "Jan" => 56, "Feb" => 59, "Mar" => 61, "Apr" => 64, "May" => 67, "Jun" => 70, "Jul" => 71, "Aug" => 72, "Sep" => 73, "Oct" => 70, "Nov" => 62, "Dec" => 56 ) ); $csvTitle = "Monthly Average High Temperature By City"; /* We know the keys of each sub-array are the same, so * extract them from the first sub-array and set them * to be our column titles */ $titleArray = array_keys($data[0]); /* Set your desired delimiter. You can make this a true * .csv and set $delimiter = ","; but I find that tabs * work better as commas can also be present in your data. * Note that you must use the .tsv or .xls file extension for Excel * to correctly interpret tabs. Otherwise if you are using commas * for your delimiter, use .csv for your file extension. */ $delimiter = "\t"; //Set target filename - see above comment on file extension. $filename="Search_Export.xls"; //Send headers header("Content-type: application/octet-stream"); header("Content-Disposition: attachment; filename=$filename"); header("Pragma: no-cache"); header("Expires: 0"); //print the title to the first cell print $csvTitle . "\r\n"; //Separate each column title name with the delimiter $titleString = implode($delimiter, $titleArray); print $titleString . "\r\n"; //Loop through each subarray, which are our data sets foreach ($data as $subArrayKey => $subArray) { //Separate each datapoint in the row with the delimiter $dataRowString = implode($delimiter, $subArray); print $dataRowString . "\r\n"; } ?>
Nice script, thank you.
Nice post. Its very helpful to my code.
Nice script.
Thank you