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).

// App\Exports\ItemExport.php

namespace App\Exports;

use App\Models\Supplier;
use Maatwebsite\Excel\Concerns\Exportable;
// ... (other necessary imports)

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

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

    public function __construct()
    {
        // Retrieve predefined options for dropdowns
        $suppliers = Supplier::orderBy('name')->pluck('name')->toArray();

        // Define columns and their respective dropdown options
        $selects = [
            ['columns_name' => 'A', 'options' => $suppliers], // Example: Column A with supplier names (unique)
        ];

        $this->selects = $selects;
        $this->rowCount = count($suppliers) + 1;
        $this->columnCount = 11; // Number of columns in the Excel sheet
    }
}

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'];

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

                // Set data validation formula to refer to hidden sheet cells
                $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));

                // 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 = \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;

// Inside a controller or where you need to trigger the export
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.