You can generate an excel with column dropdown in Laravel. It will look like this
It will come with a prompt
Pick from list
Please pick a value from the drop-down list
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.');
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
{
Next define the variables
protected $selects;
protected $row_count;
protected $column_count;
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
}
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
];
}
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);
}
}
},
];
}
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);
}
}
},
];
}
}
Top comments (3)
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:
oh this is nice!
I like the idea of loading the dropdown from another sheet.
Thanks for the comment @paulpreibisch
You are very welcome Timothy!