DEV Community

Cover image for Laravel Excel Export with Dropdown Columns
Timothy Soladoye
Timothy Soladoye

Posted on

Laravel Excel Export with Dropdown Columns

You can generate an excel with column dropdown in Laravel. It will look like this

Laravel Excel Dropdown Select Options

It will come with a prompt
Pick from list
Please pick a value from the drop-down list

Pick from list prompt

You can modify the prompt. Continue to see how



$validation->setError('Value is not in list.');
$validation->setPromptTitle('Pick from list');
$validation->setPrompt('Please pick a value from the drop-down list.');


Enter fullscreen mode Exit fullscreen mode

To do this with Laravel, you need to have this package Laravel Excel. It used to be called Maatwebsite Laravel Excel

First create a new Excel Export using
php artisan make:export UserExport

Implement the following interfaces



<?php

namespace App\Exports;

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

class UserExport implements FromCollection,WithHeadings,WithEvents
{


Enter fullscreen mode Exit fullscreen mode

Next define the variables



protected  $selects;
protected  $row_count;
protected  $column_count;


Enter fullscreen mode Exit fullscreen mode

In the Constructor



public function __construct()
    {
        $status=['active','pending','disabled'];
        $departments=['Account','Admin','Ict','Sales'];
        //$departments=ModelName::pluck('name')->toArray(); You can get values from a model or DB Facade
        $selects=[  //selects should have column_name and options
            ['columns_name'=>'D','options'=>$departments], //Column D has heading departments. See headings() method below
            ['columns_name'=>'E','options'=>$status],
        ];

        $this->selects=$selects;
        $this->row_count=50;//number of rows that will have the dropdown
        $this->column_count=5;//number of columns to be auto sized
    }


Enter fullscreen mode Exit fullscreen mode

Other needed methods collection and headings



 public function collection()
    {
        return collect([]);
    }


    public function headings(): array
    {
        return [
            'name', //column A
            'email', //column B
            'phone', //column C
            'department', //column D
            'status', //column E
            'role', //column F
        ];
    }



Enter fullscreen mode Exit fullscreen mode

Copy this method registerEvents and place at the end of the class (You don't need to dive deep into this though 😊)



public function registerEvents(): array
    {
        return [
            // handle by a closure.
            AfterSheet::class => function(AfterSheet $event) {
                $row_count = $this->row_count;
                $column_count = $this->column_count;
                foreach ($this->selects as $select){
                    $drop_column = $select['columns_name'];
                    $options = $select['options'];
                    // 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);
                    }
                }

            },
        ];
    }


Enter fullscreen mode Exit fullscreen mode

Putting all together, This is a snippet of a working class for Laravel Export with Dropdown options



<?php

namespace App\Exports;

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

class UserExport implements FromCollection,WithHeadings,WithEvents
{
    protected  $users;
    protected  $selects;
    protected  $row_count;
    protected  $column_count;

    public function __construct()
    {
        $status=['active','pending','disabled'];
        $departments=['Account','Admin','Ict','Sales'];
        //$departments=\ModelName::pluck('name')->toArray();
        $selects=[  //selects should have column_name and options
            ['columns_name'=>'D','options'=>$departments],
            ['columns_name'=>'E','options'=>$status],
        ];
        $this->selects=$selects;
        $this->row_count=50;//number of rows that will have the dropdown
        $this->column_count=5;//number of columns to be auto sized
    }

    public function collection()
    {
        return collect([]);
    }


    public function headings(): array
    {
        return [
            'name',
            'email',
            'phone',
            'department',
            'status',
            'role',
        ];
    }


    /**
     * @return array
     */
    public function registerEvents(): array
    {
        return [
            // handle by a closure.
            AfterSheet::class => function(AfterSheet $event) {
                $row_count = $this->row_count;
                $column_count = $this->column_count;
                foreach ($this->selects as $select){
                    $drop_column = $select['columns_name'];
                    $options = $select['options'];
                    // 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);
                    }
                }

            },
        ];
    }
}



Enter fullscreen mode Exit fullscreen mode

Top comments (3)

Collapse
 
paulpreibisch profile image
Paul Preibisch • Edited

Nice article!

Did you know that if you implement the withStyles concern?
Just place the code, (or your dropdown method) in the styles method:
Lastly, since Excel has a 255 character limit, you may consider pulling all the options from another sheet:

public class scratch_65 implements  WithStyles, FromView
{
    public function styles(Worksheet $sheet) : void
    {
        parent::styles($sheet);
        $listFromSheet = '\'' . SomeSheet::SHEET_TITLE . '\'!$A$2:$A$10000';
        $this->setDropDown(
            column: 'A',
            sheet: $sheet,
            startRow: 2,
            list: $listFromSheet,
            endRow: 10000
        );

    }
    public function setDropDown(
        string                                        $column,
        \PhpOffice\PhpSpreadsheet\Worksheet\Worksheet $sheet,
        int                                           $startRow,
        string                                        $list,
        ?int                                          $endRow = null,
    ): static {
        if (strlen($list) > 253) {
            throw new \Exception('Excel only allows 255 total characters for static validation lists');
        }

        $startCell = $column . $startRow;
        $endRow = $endRow ?? $sheet->getHighestRow();
        $endCell = $column . $endRow;

        $validation = $sheet->getCell($startCell)->getDataValidation();
        $validation->setType( DataValidation::TYPE_LIST );
        $validation->setErrorStyle( DataValidation::STYLE_INFORMATION );
        $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($list);

        $validation->setSqref("{$startCell}:{$endCell}");

        return $this;
    }
}
Enter fullscreen mode Exit fullscreen mode
Collapse
 
timoye profile image
Timothy Soladoye

oh this is nice!
I like the idea of loading the dropdown from another sheet.
Thanks for the comment @paulpreibisch

Collapse
 
paulpreibisch profile image
Paul Preibisch

You are very welcome Timothy!