DEV Community

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

Posted on

Laravel Excel Export with Dropdown Columns

#excel #dropdown #laravel #export

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!

Read next

dev_michael profile image

Snag the Current URL in SvelteKit (2024 Edition)

Michael Amachree -

devxbr profile image

Golang - Singleton Design pattern

Bruno Souza -

_bhupeshk_ profile image

Git and Github Guide

Bhupesh Kumar -

bad-logic profile image

Custom Memory Pool: Creating memory pool for custom class

Roshan Bhattarai -