Laravel excel library(Maatwebsite) : How to create a drop down list in exports
public function index() { \Excel::create('file', function($excel) { require_once("/apppath//vendor/phpoffice/phpexcel/Classes/PHPExcel/NamedRange.php"); require_once("/apppath/vendor/phpoffice/phpexcel/Classes/PHPExcel/Cell/DataValidation.php"); $excel->sheet('New sheet', function($sheet) { $sheet->SetCellValue("A1", "UK"); $sheet->SetCellValue("A2", "USA"); $sheet->_parent->addNamedRange( new \PHPExcel_NamedRange( 'countries', $sheet, 'A1:A2' ) ); $sheet->SetCellValue("B1", "London"); $sheet->SetCellValue("B2", "Birmingham"); $sheet->SetCellValue("B3", "Leeds"); $sheet->_parent->addNamedRange( new \PHPExcel_NamedRange( 'UK', $sheet, 'B1:B3' ) ); $sheet->SetCellValue("C1", "Atlanta"); $sheet->SetCellValue("C2", "New York"); $sheet->SetCellValue("C3", "Los Angeles"); $sheet->_parent->addNamedRange( new \PHPExcel_NamedRange( 'USA', $sheet, 'C1:C3' ) ); $objValidation = $sheet->getCell('D1')->getDataValidation(); $objValidation->setType(\PHPExcel_Cell_DataValidation::TYPE_LIST); $objValidation->setErrorStyle(\PHPExcel_Cell_DataValidation::STYLE_INFORMATION); $objValidation->setAllowBlank(false); $objValidation->setShowInputMessage(true); $objValidation->setShowErrorMessage(true); $objValidation->setShowDropDown(true); $objValidation->setErrorTitle('Input error'); $objValidation->setError('Value is not in list.'); $objValidation->setPromptTitle('Pick from list'); $objValidation->setPrompt('Please pick a value from the drop-down list.'); $objValidation->setFormula1('countries'); //note this! }); })->download("xlsx"); return view('home'); }
for maatwebite version V.3.1 use below code
<?phpnamespace App\Exports;use App\Models\Category;use Illuminate\Contracts\View\View;use Maatwebsite\Excel\Concerns\FromView;use Maatwebsite\Excel\Concerns\WithEvents;use Maatwebsite\Excel\Concerns\WithTitle;use Maatwebsite\Excel\Events\AfterSheet;use PhpOffice\PhpSpreadsheet\Cell\DataValidation;class BulkUploadProductExport implements FromView, WithEvents , WithTitle{ public function view(): View { return view('exports', [ 'categories' => Category::all() ]); } public function title(): string { return 'bulkupload'; } public function registerEvents(): array { //$event = $this->getEvent(); return [ AfterSheet::class => function (AfterSheet $event) { /** @var Sheet $sheet */ $sheet = $event->sheet; /** * validation for bulkuploadsheet */ $sheet->setCellValue('B5', "SELECT ITEM"); $configs = "DUS800, DUG900+3xRRUS, DUW2100, 2xMU, SIU, DUS800+3xRRUS, DUG900+3xRRUS, DUW2100"; $objValidation = $sheet->getCell('B5')->getDataValidation(); $objValidation->setType(DataValidation::TYPE_LIST); $objValidation->setErrorStyle(DataValidation::STYLE_INFORMATION); $objValidation->setAllowBlank(false); $objValidation->setShowInputMessage(true); $objValidation->setShowErrorMessage(true); $objValidation->setShowDropDown(true); $objValidation->setErrorTitle('Input error'); $objValidation->setError('Value is not in list.'); $objValidation->setPromptTitle('Pick from list'); $objValidation->setPrompt('Please pick a value from the drop-down list.'); $objValidation->setFormula1('"' . $configs . '"'); } ]; }}