Exporting Data to a Downloadable CSV File with CakePHP

Generating a downloadable CSV file in CakePHP seems to be a bit of a stumbling block for many relatively new to using Cake. It doesn’t need to be challenging. There’s just a few basic steps you need to put in place.

As an example we’ll consider a User model that contains contact details of people who have register to our app. We want to create a CSV export of all the User data.

The Controller

Let’s start with the controller. We need to create an action in the User’s Controller for generating the CSV export:-

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
<?php
// app/Controllers/UsersController.php

class UsersController extends AppController {

    public function export() {

        $this->response->download("export.csv");

        $data = $this->User->find('all');
        $this->set(compact('data'));

        $this->layout = 'ajax';

        return;

    }

}

We’re calling

1
$this->response->download("export.csv")

to set the relevant headers. When you trigger the controller’s action from the browser it will start to download a file named ‘export.csv’ rather than attempt to display the view in the browser.

We then grab the data from the database and set it ready for the View.

Finally we set the View layout to use the AJAX layout, $this->layout = ‘ajax’. This might seem quirky, but it will ensure the View isn’t wrapped in any HTML markup. We just want to output the content of the View we are shortly about to put together.

The Route

As it currently stands the URL for our CSV export will be ‘users/export’; it would be nice to append that with the CSV extension. To do that we’re going to tell Cake to correctly parse CSV file requests. In our routes.php file (in app/Config/) add the following:-

1
Router::parseExtensions('csv');

This will now allow us to access our CSV export from the URL ‘users/export.csv’.

The View

Now we need to put together our View, making sure we correctly escape values being output:-

1
2
3
4
5
6
7
8
9
10
11
<?php

// app/Views/Users/export.ctp

foreach ($data as $row):
    foreach ($row['User'] as &$cell):
        // Escape double quotation marks
        $cell = '"' . preg_replace('/"/','""',$cell) . '"';
    endforeach;
    echo implode(',', $row['User']) . "\n";
endforeach;

This should be pretty self explanatory. We’re looping over all the rows in our

1
$data

array; escaping any double quotation marks in the values and then wrapping the value in quotation marks; and finally outputting the row delimiting values with commas and adding a new line break at the end.

Finally

Everything should now be setup, you’ll just want to be able to link to your CSV export from another View. If you’re parsing the file extension then you need to include this in the URL; so using the HtmlHelper we need to include the ‘ext’ parameter for the file extension:-

1
2
3
4
5
echo $this->Html->link('export', array(
    'controller' => 'users',
    'action' => 'export',
    'ext' => 'csv'
));

That should be it! Clicking on the ‘export’ link should start the browser downloading ourexport.csv file.

Leave a Reply