Select dropdown cells with Laravel Excel

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:

public function collection()
{
    $countries = ['United Kingdom', 'Belgium', 'United Kingdom', 'Portugal', 'Malaysia', 'Australia', 'Ireland'];

    return collect([
        ['Taylor Otwell', $countries],
        ['Dries Vints', $countries],
        ['James Brooks', $countries],
        ['Nuno Maduro', $countries],
        ['Mior Muhammad Zaki Mior Khairuddin', $countries],
        ['Jess Archer', $countries],
        ['Guus Leeuw', $countries],
        ['Tim MacDonald', $countries],
        ['Joe Dixon', $countries],
    ]);
}

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:

use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithCustomValueBinder;
use PhpOffice\PhpSpreadsheet\Cell\Cell;
use PhpOffice\PhpSpreadsheet\Cell\DataValidation;
use PhpOffice\PhpSpreadsheet\Cell\DefaultValueBinder;

class LaravelTeamExport extends DefaultValueBinder implements FromCollection, WithCustomValueBinder

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

public function bindValue(Cell $cell, $value)
{
    if (is_array($value)) {
        $validation = $cell->getDataValidation();
        $validation->setType(DataValidation::TYPE_LIST);
        $validation->setAllowBlank(true);
        $validation->setShowDropDown(true);
        $validation->setFormula1('"'.collect($value)->join(',').'"');

        $value = '';
    }

    return parent::bindValue($cell, $value);
}

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:

<?php

namespace App\Exports;

use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithCustomValueBinder;
use PhpOffice\PhpSpreadsheet\Cell\Cell;
use PhpOffice\PhpSpreadsheet\Cell\DataValidation;
use PhpOffice\PhpSpreadsheet\Cell\DefaultValueBinder;

class LaravelTeamExport extends DefaultValueBinder implements FromCollection, WithCustomValueBinder
{
    /**
     * @return \Illuminate\Support\Collection
     */
    public function collection()
    {
		$countries = ['United Kingdom', 'Belgium', 'United Kingdom', 'Portugal', 'Malaysia', 'Australia', 'Ireland'];

        return collect([
            ['Taylor Otwell', $countries],
            ['Dries Vints', $countries],
            ['James Brooks', $countries],
            ['Nuno Maduro', $countries],
            ['Mior Muhammad Zaki Mior Khairuddin', $countries],
            ['Jess Archer', $countries],
            ['Guus Leeuw', $countries],
            ['Tim MacDonald', $countries],
            ['Joe Dixon', $countries],
        ]);
    }

    public function bindValue(Cell $cell, $value)
    {
        if (is_array($value)) {
            $validation = $cell->getDataValidation();
            $validation->setType(DataValidation::TYPE_LIST);
            $validation->setAllowBlank(true);
            $validation->setShowDropDown(true);
            $validation->setFormula1('"'.collect($value)->join(',').'"');

            $value = '';
        }

        return parent::bindValue($cell, $value);
    }
}