← Posts

Select dropdown cells with Laravel Excel

September 22nd, 2023 - 2 min read

An application I work on automatically generates & sends an spreadsheet export, with a few fields pre-filled. The user can then populate the rest of the data and upload it back into the application.

However, one of the fields that's part of that spreadsheet is an enum field. This means that whatever value that's used for the field in the spreadsheet needs to match exactly with the backing value of the enum.

After some digging, I found that I could add a 'select dropdown' to the relevant cells in the spreadsheet. The user could then select what they want from that dropdown and it'll match exactly... woo hoo! 🎉

To save you some digging, here's how you add 'select dropdown' fields to exports generated with Laravel Excel.

Note: For this to work, I believe you'll need to be exporting as a .xlsx file, rather than a .csv

As a basic example, I'm going to use the names of Laravel Core Team members, and make a dropdown for the countries.

First of all, here's my collection of rows:

 1public function collection()
 2{
 3    $countries = ['United Kingdom', 'Belgium', 'United Kingdom', 'Portugal', 'Malaysia', 'Australia', 'Ireland'];
 4 
 5    return collect([
 6        ['Taylor Otwell', $countries],
 7        ['Dries Vints', $countries],
 8        ['James Brooks', $countries],
 9        ['Nuno Maduro', $countries],
10        ['Mior Muhammad Zaki Mior Khairuddin', $countries],
11        ['Jess Archer', $countries],
12        ['Guus Leeuw', $countries],
13        ['Tim MacDonald', $countries],
14        ['Joe Dixon', $countries],
15    ]);
16}

Then, in order to be able to change the "type" of the country cells, we need to create a "custom value binder".

To do this, you'll first need to extend the DefaultValueBinder class and implement the WithCustomValueBinder interface:

1use Maatwebsite\Excel\Concerns\FromCollection;
2use Maatwebsite\Excel\Concerns\WithCustomValueBinder;
3use PhpOffice\PhpSpreadsheet\Cell\Cell;
4use PhpOffice\PhpSpreadsheet\Cell\DataValidation;
5use PhpOffice\PhpSpreadsheet\Cell\DefaultValueBinder;
6 
7class LaravelTeamExport extends DefaultValueBinder implements FromCollection, WithCustomValueBinder

Then, you'll want to add a bindValue method to your export.

 1public function bindValue(Cell $cell, $value)
 2{
 3    if (is_array($value)) {
 4        $validation = $cell->getDataValidation();
 5        $validation->setType(DataValidation::TYPE_LIST);
 6        $validation->setAllowBlank(true);
 7        $validation->setShowDropDown(true);
 8        $validation->setFormula1('"'.collect($value)->join(',').'"');
 9 
10        $value = '';
11    }
12 
13    return parent::bindValue($cell, $value);
14}

The above code will check if the $value of the current cell is an array. If it is, we'll set the type of data validation that should take place to TYPE_LIST, set some other settings and provide the formula (the options) that should be available in the cell dropdown.

If it's not an array, then Laravel Excel will bind everything just like it normally would.

So, as a finished product, you'll have something along the lines of this:

 1<?php
 2 
 3namespace App\Exports;
 4 
 5use Maatwebsite\Excel\Concerns\FromCollection;
 6use Maatwebsite\Excel\Concerns\WithCustomValueBinder;
 7use PhpOffice\PhpSpreadsheet\Cell\Cell;
 8use PhpOffice\PhpSpreadsheet\Cell\DataValidation;
 9use PhpOffice\PhpSpreadsheet\Cell\DefaultValueBinder;
10 
11class LaravelTeamExport extends DefaultValueBinder implements FromCollection, WithCustomValueBinder
12{
13    /**
14     * @return \Illuminate\Support\Collection
15     */
16    public function collection()
17    {
18        $countries = ['United Kingdom', 'Belgium', 'United Kingdom', 'Portugal', 'Malaysia', 'Australia', 'Ireland'];
19 
20        return collect([
21            ['Taylor Otwell', $countries],
22            ['Dries Vints', $countries],
23            ['James Brooks', $countries],
24            ['Nuno Maduro', $countries],
25            ['Mior Muhammad Zaki Mior Khairuddin', $countries],
26            ['Jess Archer', $countries],
27            ['Guus Leeuw', $countries],
28            ['Tim MacDonald', $countries],
29            ['Joe Dixon', $countries],
30        ]);
31    }
32 
33    public function bindValue(Cell $cell, $value)
34    {
35        if (is_array($value)) {
36            $validation = $cell->getDataValidation();
37            $validation->setType(DataValidation::TYPE_LIST);
38            $validation->setAllowBlank(true);
39            $validation->setShowDropDown(true);
40            $validation->setFormula1('"'.collect($value)->join(',').'"');
41 
42            $value = '';
43        }
44 
45        return parent::bindValue($cell, $value);
46    }
47}