building menu...

PHP: Exporting Data to Excel

After you've put all that effort into importing your data into and SQL database and connecting it to your website, how do you get it back out and into Excel in order to keep your off-line and on-line systems synchronised?

The following article presents a simple method for downloading any data from PHP into an Excel spreadsheet - or at least something that looks like one.

Preparing the data

Using the data from the article on Sorting Arrays of Arrays which is defined as follows:

  $data = array(
    array("firstname" => "Mary", "lastname" => "Johnson", "age" => 25),
    array("firstname" => "Amanda", "lastname" => "Miller", "age" => 18),
    array("firstname" => "James", "lastname" => "Brown", "age" => 31),
    array("firstname" => "Patricia", "lastname" => "Williams", "age" => 7),
    array("firstname" => "Michael", "lastname" => "Davis", "age" => 43),
    array("firstname" => "Sarah", "lastname" => "Miller", "age" => 24),
    array("firstname" => "Patrick", "lastname" => "Miller", "age" => 27)
  );

Note: Further down this page you can find an example on creating an export from an SQL query.

The first step is to output the data in a tab-delimited format (CSV can also be used but is slightly more complicated). To achieve this we use the following code:

  header("Content-Type: text/plain");

  $flag = false;
  foreach($data as $row) {
    if(!$flag) {
      # display field/column names as first row
      echo implode("\t", array_keys($row)) . "\n";
      $flag = true;
    }
    echo implode("\t", array_values($row)) . "\n";
  }

We set the content type to text/plain so that the output can more easily be viewed in the browser. Otherwise, because there is no HTML formatting, the output would appear as a single line of text.

The first line of output will be the column headings (in this case the field names are used). Values are separated with a tab \t and rows with a line break \n. The output should look something like the following:

firstname	lastname	age
Mary	Johnson	25
Amanda	Miller	18
James	Brown	31
Patricia	Williams	7
Michael	Davis	43
Sarah	Miller	24
Patrick	Miller	27

There's already a weakness in this code that may not be immediately obvious. What if one of the fields to be ouput already contains one or more tab characters, or worse, a newline? That's going to throw the whole process out as we rely on those characters to indicate column- and line-breaks.

The solution is to 'escape' the tab characters. In this case we're going to replace tabs with a literal \t and line breaks with a literal \n so they don't affect the formatting:

  function cleanData(&$str)
  {
    $str = preg_replace("/\t/", "\\t", $str);
    $str = preg_replace("/\r?\n/", "\\n", $str);
  }

  header("Content-Type: text/plain");

  $flag = false;
  foreach($data as $row) {
    if(!$flag) {
      # display field/column names as first row
      echo implode("\t", array_keys($row)) . "\n";
      $flag = true;
    }
    array_walk($row, 'cleanData');
    echo implode("\t", array_values($row)) . "\n";
  }

Note: This represents the simplest case. You might also want to consider different line break characters and other special characters that may need encoding.

Now, before each row is echoed any tab characters are replaced "\t" so that our columns aren't broken up. Also any line breaks within the data are replaced with "\n". Now, how to set this up as a download...

Triggering a download

What many programmers don't realise is that you don't have to create a file, even a temporary one, in order for one to be downloaded. It's sufficient to 'mimic' a download by passing the equivalent HTTP headers followed by the data.

If we create a PHP file with the following code then when it's called a file will be downloaded that can be opened directly using Excel.

  function cleanData(&$str)
  {
    $str = preg_replace("/\t/", "\\t", $str);
    $str = preg_replace("/\r?\n/", "\\n", $str);
  }

  # filename for download
  $filename = "website_data_" . date('Ymd') . ".xls";

  header("Content-Disposition: attachment; filename=\"$filename\"");
  header("Content-Type: application/vnd.ms-excel");

  $flag = false;
  foreach($data as $row) {
    if(!$flag) {
      # display field/column names as first row
      echo implode("\t", array_keys($row)) . "\n";
      $flag = true;
    }
    array_walk($row, 'cleanData');
    echo implode("\t", array_values($row)) . "\n";
  }
  exit;

Click to trigger the download »

This should result in a file being downloaded and saved to your computer. If all goes well then the filename will be "website_data_20100310.xls".

How does it work? Setting the headers tells the browser to expect a file with a given name and type. The data is then echoed, but instead of appearing on the page it becomes the downloaded file.

Because of the .xls extension and the ms-excel file type, most computers will associate it with Excel and double-clicking will cause that program to open. You could also modify the file name and mime type to indicate a different spreadsheet package or database application.

If for some reason you want to create 'real' Excel files, follow the link under References below.

A similar technique can be used to allow users to download files that have been uploaded previously using PHP and stored with different names. More on that later...

Exporting from an SQL database

If your goal is to allow data to be exported from a query result then the changes are relatively simple:

  # Original PHP code by Chirp Internet: www.chirp.com.au
  # Please acknowledge use of this code by including this header.

  function cleanData(&$str)
  {
    $str = preg_replace("/\t/", "\\t", $str);
    $str = preg_replace("/\r?\n/", "\\n", $str);
  }

  # file name for download
  $filename = "website_data_" . date('Ymd') . ".xls";

  header("Content-Disposition: attachment; filename=\"$filename\"");
  header("Content-Type: application/vnd.ms-excel");

  $flag = false;
  $result = pg_query("SELECT * FROM table ORDER BY field") or die('Query failed!');
  while(false !== ($row = pg_fetch_assoc($result))) {
    if(!$flag) {
      # display field/column names as first row
      echo implode("\t", array_keys($row)) . "\n";
      $flag = true;
    }
    array_walk($row, 'cleanData');
    echo implode("\t", array_values($row)) . "\n";
  }

The database functions need to match the database you're using. MySQL users for example would use mysql_query and mysql_fetch_assoc in place of the PostgreSQL functions.

If you're having trouble at this stage, remove the Content-Disposition header and change the Content-Type back to text/plain. This makes debugging a lot easier as you can see the output in your browser rather than having to download and open the generated file every time you edit the script.

References

Easy way to create XLS file from PHP

< Back to PHP

Feedback and Questions

5 January 2007: keith says:

Excellent tutorial - thanks alot, it helped me a hell of a lot. Keep up the excellent work.

29 August 2007: Open Source says:

Very useful to know this. Appreciate it.

10 October 2007: kathy says:

Its really useful and have implemented. But my client wants it to be true excel and not text (tab limited).

Most versions of Excel use a proprietary file format called BIFF which is difficult to reproduce. Newer versions use Office Open XML which has so far failed to get approval as an open standard so also a problem to reproduce. You should tell your client that TAB and CSV formats are many times more efficient and can easily be converted to true Excel using a macro.

4 July 2008: Dragan says:

Excel can "read" simple HTML just fine, so you might as well create an HTML-table, with the required xls headers of course.

Sure, if you think HTML is a sensible format for transmitting data then go for it. I'll be sticking with tab-delimited text (or CSV or XML) myself as those formats are compatible with more programs

11 June 2009: ammupon says:

Thank you soooooo much for your wonderful Post!!!

14 June 2009: James (Home) says:

Why do i get a blank spreadsheet after exporting the data?

If you get a blank spreadsheet then you haven't exported any data. Try first with a text/plain header to see what's happening and then when it's working change the headers to trigger the Excel download.

7 December 2009: swapnil (SLSMS) says:

i find Amazing and Excellent information, especially tht u dont need to create file for a download. Thanks for the awesome code.

11 December 2009: aSeptik says:

Use this header both in CVS and HTML format:

header( "Content-type: application/vnd.ms-excel; charset=UTF-16LE" );

23 February 2010: Halfling Rogue says:

Excellent article; easy to understand and very useful.

Only problem is that the cleanData function doesn't seem to actually get rid of newlines. I found that was easily fixed by using the custom nl2br function on the php.net site: ca2.php.net/manual/en/function.nl2br.php#91828

Any numbers longer than 15 digits get converted by the spreadsheet program into scientific notation, but that can be fixed by throwing quotes around the number in the while loop so that it's parsed as text.