PHP: Export MySQL data to an Excel file.

This is a tutorial on how to export MySQL data to an Excel file using PHP. In this guide, we will query MySQL using the PDO object and then force the browser to download the data in an Excel file.

Take a look at the following screenshot of a MySQL table called members:

MySQL table

As you can see, it is just a basic MySQL table with three rows.

Now, let’s see if we can retrieve these rows and export them into an Excel file:

Here is a screenshot of the Excel file that this code generated:

As you can see, our PHP code correctly set the header value for each column.

An explanation of our Excel export code.

In the code above:

  1. We connected to MySQL using the PDO object.
  2. We selected all of the rows from our members table. Note that I used the PDO::query function in the example above because there is no external data being used in this query. If your query is using external data from a HTML form or a GET parameter, then I suggest that you use prepared statements instead.
  3. We named our file as “members.xls“. This is what the browser will name the file when it downloads it to the user’s file system. Note that older XLS files will open in the newer versions of Microsoft Office, so don’t rename the extension to xlsx.
  4. We set our Content-Type header to application/xls. This tells the browser that it will be downloading an Excel file.
  5. We set the Content-Disposition header to attachment. As a result, the browser now knows that the content should be downloaded and not displayed inline. If we left this header out, our data would be printed out directly into the browser.
  6. We used the Pragma and Expires headers in a way that should prevent the content from being cached by the browser.
  7. We set our $separator variable to \t, which represents the tab character. This separator character tells Excel where each column begins and ends.
  8. Using PHP’s implode and array_keys functions, we printed out the header values. These header values are obviously the names of our columns.
  9. Finally, we looped through each row in our table, removed any conflicting characters and then printed it out.

Note that this code will never be perfect, as creating Excel files with PHP will always fraught with danger. This leads me onto my next point.

Should I export to Excel or CSV?

Exporting data to a CSV file is a much cleaner solution than creating Excel files with PHP. The good thing about choosing the CSV format over Excel is that PHP has native support for creating CSV files. That means that you will never have to rely on an external library.

In my experience, many of the PHP Excel libraries end up being deprecated over time. That, or the owner stops updating the library and you end up running into issues when you need to upgrade your PHP version.

The CSV file format can be opened by Microsoft Excel and other spreadsheet programs, so unless you are dynamically adding your own formulas, there is no real need to export to Excel.

Facebook Comments