Creating a Laravel Excel Export with Dropdowns

Exporting data to an Excel file is a common requirement in many applications. Laravel Excel provides a straightforward way to generate Excel files with data from your application. In this guide, we’ll walk through the process of creating an Excel export file using Laravel Excel and including dropdowns with predefined options.

In this blog I’ll create an Excel file export, because I want to let the user define the relations already. The Excel export which can later be imported back in the Laravel app will contain the dropdown containing the name of the relations.

Setting Up Laravel Excel and the Export Class

To get started, make sure you have Laravel installed along with the Laravel Excel package. Next, create an export class (ItemExport in this case) that implements the necessary interfaces (FromCollection, WithHeadings, and WithEvents).

namespace App\Exports;

use App\Models\Supplier;
use Maatwebsite\Excel\Concerns\Exportable;


class ItemExport implements FromCollection, WithHeadings, WithEvents
{
    use Exportable;

    protected $selects;
    protected $rowCount;
    protected $columnCount;

    public function __construct()
    {
        
        $suppliers = Supplier::orderBy('name')->pluck('name')->toArray();

        
        $selects = [
            ['columns_name' => 'A', 'options' => $suppliers], 
        ];

        $this->selects = $selects;
        $this->rowCount = count($suppliers) + 1;
        $this->columnCount = 11; 
    }
}

Adding Dropdowns to Excel File

In the ItemExport class, the registerEvents() method is used to define the behavior after the Excel sheet is created. Inside this method, we’ll set up dropdowns for specified columns.

public function registerEvents(): array
{
    return [
        AfterSheet::class => function (AfterSheet $event) {
            $row_count = $this->rowCount;
            $column_count = $this->columnCount;

            $hiddenSheet = $event->sheet->getDelegate()->getParent()->createSheet();
            $hiddenSheet->setTitle('Hidden');
            $hiddenSheet->setSheetState(\PhpOffice\PhpSpreadsheet\Worksheet\Worksheet::SHEETSTATE_HIDDEN);

            foreach ($this->selects as $select) {
                $drop_column = $select['columns_name'];
                $options = $select['options'];

                
                foreach ($options as $index => $option) {
                    $cellCoordinate = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex(1) . ($index + 1);
                    $hiddenSheet->setCellValue($cellCoordinate, $option);
                }

                
                $validation = $event->sheet->getCell("{$drop_column}2")->getDataValidation();
                $validation->setType(\PhpOffice\PhpSpreadsheet\Cell\DataValidation::TYPE_LIST);
                $validation->setShowDropDown(true);
                $validation->setFormula1('Hidden!$A$1:$A$' . count($options));

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

                
                for ($i = 1; $i <= $column_count; $i++) {
                    $column = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($i);
                    $event->sheet->getColumnDimension($column)->setAutoSize(true);
                }
            }
        },
    ];
}

Usage and Output

To use the ItemExport class, instantiate it and export the data.

use App\Exports\ItemExport;
use Maatwebsite\Excel\Facades\Excel;


public function export()
{
    return Excel::download(new ItemExport(), 'exported_data.xlsx');
}

When executed, this will generate an Excel file (exported_data.xlsx) with the specified columns and dropdowns in the designated columns (A in this example), containing the predefined options.

Feel free to adjust the column names, options, and other configurations according to your specific use case.