Exporting Laravel Data to an Excel Spreadsheet

  • April 19, 2016

Welcome to the incredibly popular Easy Laravel 5 companion blog. To celebrate the new edition's release (updated for Laravel 5.5!) use the discount code easteregg to receive 20% off the book or book/video package! » Buy the book

I've lately been working on an administrative console for a client's Laravel-driven SaaS, and wanted to create an easy solution for exporting invoice-related data to a spreadsheet which can then be periodically provided to the company accountant. In this tutorial I'll show you just how easy it is to integrate such a feature into your Laravel application.

The excellent PHPExcel library has become the de facto PHP solution for these sorts of features, and so I wondered whether an enterprising fellow programmer had made the library easily accessible via a Laravel application. Sure enough, a quick search turned up MaatWebsite's Laravel Excel package, and I wasn't disappointed.

To install Laravel Excel run the following command from your Laravel application's root directory:

$ composer require maatwebsite/excel

Once installed, open config/app.php and add the following line to the bottom of the providers array:

'providers' => [
    ...
    Maatwebsite\Excel\ExcelServiceProvider::class,
],

Finally, add the Excel facade to the bottom of the aliases array (also in the config/app.php file):

'aliases' => [
    ...
    'Excel'     => Maatwebsite\Excel\Facades\Excel::class,
],

Save these changes and you're ready to begin exporting data to an Excel spreadsheet! Let's work through a simple example. Like many developers lacking design acumen I tend to use the fantastic Bootstrap framework for my administrative consoles, and so the below example will create a stylized hyperlink titled "Export to Excel" and pointing to the URL represented by the route alias admin.payments.excel:

{!! link_to_route('admin.payments.excel', 
      'Export to Excel', null, 
      ['class' => 'btn btn-info']) 
!!}

When rendered to the browser, the button will look like this:

In case you're wondering, the route alias is defined in the routes.php file, and will look something like this:

Route::get('/payments/excel', 
[
  'as' => 'admin.invoices.excel',
  'uses' => 'PaymentsController@excel'
]);

So in this example the Payments controller's excel() action should contain the logic used to generate and return the Excel file. A commented example follows:

public function excel() {

    // Execute the query used to retrieve the data. In this example
    // we're joining hypothetical users and payments tables, retrieving
    // the payments table's primary key, the user's first and last name, 
    // the user's e-mail address, the amount paid, and the payment
    // timestamp.

    $payments = Payment::join('users', 'users.id', '=', 'payments.id')
        ->select(
          'payments.id', 
          \DB::raw("concat(users.first_name, ' ', users.last_name) as `name`"), 
          'users.email', 
          'payments.total', 
          'payments.created_at')
        ->get();

    // Initialize the array which will be passed into the Excel
    // generator.
    $paymentsArray = []; 

    // Define the Excel spreadsheet headers
    $paymentsArray[] = ['id', 'customer','email','total','created_at'];

    // Convert each member of the returned collection into an array,
    // and append it to the payments array.
    foreach ($payments as $payment) {
        $paymentsArray[] = $payment->toArray();
    }

    // Generate and return the spreadsheet
    Excel::create('payments', function($excel) use ($invoicesArray) {

        // Set the spreadsheet title, creator, and description
        $excel->setTitle('Payments');
        $excel->setCreator('Laravel')->setCompany('WJ Gilmore, LLC');
        $excel->setDescription('payments file');

        // Build the spreadsheet, passing in the payments array
        $excel->sheet('sheet1', function($sheet) use ($paymentsArray) {
            $sheet->fromArray($paymentsArray, null, 'A1', false, false);
        });

    })->download('xlsx');
}

Once these changes are in place, upon pressing the Export to Excel button the user will be prompted to download the newly generated spreadsheet!

This really is just a taste of what Laravel Excel can do. Be sure to check out the documentation for more details!