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:

/**
 * Connect to MySQL using PDO.
 */
$user = 'root';
$password = '';
$server = 'localhost';
$database = 'test';
$pdo = new PDO("mysql:host=$server;dbname=$database", $user, $password);

//Query our MySQL table
$sql = "SELECT * FROM members";
$stmt = $pdo->query($sql);

//Retrieve the data from our table.
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

//The name of the Excel file that we want to force the
//browser to download.
$filename = 'members.xls';

//Send the correct headers to the browser so that it knows
//it is downloading an Excel file.
header("Content-Type: application/xls");    
header("Content-Disposition: attachment; filename=$filename");  
header("Pragma: no-cache"); 
header("Expires: 0");

//Define the separator line
$separator = "\t";

//If our query returned rows
if(!empty($rows)){
    
    //Dynamically print out the column names as the first row in the document.
    //This means that each Excel column will have a header.
    echo implode($separator, array_keys($rows[0])) . "\n";
    
    //Loop through the rows
    foreach($rows as $row){
        
        //Clean the data and remove any special characters that might conflict
        foreach($row as $k => $v){
            $row[$k] = str_replace($separator . "$", "", $row[$k]);
            $row[$k] = preg_replace("/\r\n|\n\r|\n|\r/", " ", $row[$k]);
            $row[$k] = trim($row[$k]);
        }
        
        //Implode and print the columns out using the 
        //$separator as the glue parameter
        echo implode($separator, $row) . "\n";
    }
}

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.