maatwebsite laravel excel export columns with drop down list

Question

I have been using Laravel Excel to export data in csv format and it has been great so far. Now I need to export in xlsx format so that I can include dropdown lists in some of the columns. I have looked at this question but it looks like that is for an older version of Laravel Excel. I also looked at the page in the docs that explains extending the package, but I can't seem to figure out how to add a dropdown list to a column while exporting data.

This is a simplified version of my export class:

namespace App\Exports;

use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithHeadings;
use Maatwebsite\Excel\Concerns\WithStrictNullComparison;

class ActionItemExport implements FromCollection, WithHeadings, WithStrictNullComparison
{

    public function collection()
    {
        return $this->getActionItems();
    }

    public function headings(): array
    {
        $columns = [
            'Column 1',
            'Column 2',
            'Column 3',
            'Column 4',
            'Column 5',
            'Column 6',
            'Column 7'
        ];
        return $columns;
    }

    private function getActionItems()
    {
        $select = 'column1, column2, column3, column4, column5, column6, column7';

        $query = \DB::table('action_items')->select(\DB::raw($select));
        $query->whereNull('action_items.deleted_at');

        $ai = $query->orderBy('column1')->get();
        return $ai;
    }
}

What I would like to do is query a lookup table that has the options for column1 and use those values for a drop down list in the column so that when a user wants to change the excel sheet, they are limited to only the drop down values.

In the docs it mentions to use \Maatwebsite\Excel\Sheet or \Maatwebsite\Excel\Writer, but I'm not even sure where to use those at, or which one to use.

Throughout my searches I just can't seem to piece together a solution so any help would be appreciated.

I'm using:

maatwebsite/excel 3.1, php 7.2, laravel 5.8

matticustard · Accepted Answer

The implementation of sheet events can be rather confusing and hard to find examples for, so I try to lend a hand when I see a post like this. First, I'll say you should really be looking at the PHPSpreadsheet documentation for these additional features. It's where you're going to find the important information you need. Then you can translate what you find for use in Laravel Excel.

PHPSpreadsheet: Setting data validation on a cell https://phpspreadsheet.readthedocs.io/en/latest/topics/recipes/#setting-data-validation-on-a-cell

Here is an example building upon your existing file. I also threw in some bonus formatting to autosize the column widths — a must in my opinion.

namespace App\Exports;

use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithHeadings;
use Maatwebsite\Excel\Concerns\WithStrictNullComparison;
use Maatwebsite\Excel\Concerns\WithEvents;
use Maatwebsite\Excel\Events\AfterSheet;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\Cell\DataValidation;

class ActionItemExport implements FromCollection, WithHeadings, WithEvents, WithStrictNullComparison
{
    protected $results;

    public function collection()
    {
        // store the results for later use
        $this->results = $this->getActionItems();

        return $this->results;
    }

    // ...

    public function registerEvents(): array
    {
        return [
            // handle by a closure.
            AfterSheet::class => function(AfterSheet $event) {

                // get layout counts (add 1 to rows for heading row)
                $row_count = $this->results->count() + 1;
                $column_count = count($this->results[0]->toArray());

                // set dropdown column
                $drop_column = 'A';

                // set dropdown options
                $options = [
                    'option 1',
                    'option 2',
                    'option 3',
                ];

                // set dropdown list for first data row
                $validation = $event->sheet->getCell("{$drop_column}2")->getDataValidation();
                $validation->setType(DataValidation::TYPE_LIST );
                $validation->setErrorStyle(DataValidation::STYLE_INFORMATION );
                $validation->setAllowBlank(false);
                $validation->setShowInputMessage(true);
                $validation->setShowErrorMessage(true);
                $validation->setShowDropDown(true);
                $validation->setErrorTitle('Input error');
                $validation->setError('Value is not in list.');
                $validation->setPromptTitle('Pick from list');
                $validation->setPrompt('Please pick a value from the drop-down list.');
                $validation->setFormula1(sprintf('"%s"',implode(',',$options)));

                // clone validation to remaining rows
                for ($i = 3; $i <= $row_count; $i++) {
                    $event->sheet->getCell("{$drop_column}{$i}")->setDataValidation(clone $validation);
                }

                // set columns to autosize
                for ($i = 1; $i <= $column_count; $i++) {
                    $column = Coordinate::stringFromColumnIndex($i);
                    $event->sheet->getColumnDimension($column)->setAutoSize(true);
                }
            },
        ];
    }
}

maatwebsite laravel excel export columns with drop down list

Answers (2)

Related Questions