How to Export Excel with Dropdowns in Laravel

In this tutorial, we'll explore how to generate an Excel file in Laravel with dropdown options in specific columns using the Maatwebsite Excel package. This approach is useful when you need predefined choices for users filling out Excel sheets.

Steps to Implement Dropdowns in Excel Export

1. Install Maatwebsite Excel Package

First, ensure the maatwebsite/excel package is installed in your Laravel project. You can do this by running:

composer require maatwebsite/excel

2. Create the Export Class

We'll create an export class called SampleExport. This class implements the FromCollection and WithEvents interfaces. Here's the code:

<?php

namespace App\Exports;

use App\Models\TDA\Addon\TdaAddon;
use Illuminate\Support\Facades\DB;
use Maatwebsite\Excel\Events\AfterSheet;
use Maatwebsite\Excel\Concerns\WithEvents;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use Maatwebsite\Excel\Concerns\FromCollection;
use PhpOffice\PhpSpreadsheet\Cell\DataValidation;

class SampleExport implements FromCollection, WithEvents
{
    protected $results;

    public function collection()
    {
        $this->results = $this->getActionItems();
        return $this->results;
    }

    private function getActionItems()
    {
        $query = DB::table('tda_accommodations')->select(DB::raw('name'));
        return $query->orderBy('name')->get()->map(function ($item) {
            return (array) $item;
        });
    }

    public function registerEvents(): array
    {
        return [
            AfterSheet::class => function (AfterSheet $event) {
                $sheet = $event->sheet->getDelegate();

                // Dropdown data
                $options_hotel = TdaAddon::where('tda_batch_id', 3)
                    ->where('feature', 'tda')
                    ->where('type', 'hotel')
                    ->pluck('name')
                    ->toArray();

                // Write dropdown options to a hidden column
                $rowIndex = 1;
                foreach ($options_hotel as $option) {
                    $sheet->setCellValue("Y{$rowIndex}", $option);
                    $rowIndex++;
                }

                // Set dropdown on column B
                $drop_column = 'B';
                $row_count = $this->results->count() + 1;

                $validation = $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('Y1:Y' . count($options_hotel));

                // Clone validation for all rows
                for ($i = 2; $i <= $row_count; $i++) {
                    $sheet->getCell("{$drop_column}{$i}")->setDataValidation(clone $validation);
                }

                // Autosize columns
                $column_count = $this->results->isNotEmpty() ? count($this->results->first()) : 0;
                for ($i = 1; $i <= $column_count; $i++) {
                    $column = Coordinate::stringFromColumnIndex($i);
                    $sheet->getColumnDimension($column)->setAutoSize(true);
                }
            },
        ];
    }
}

3. Explanation of Key Code Sections

• Dropdown Data: We fetch the dropdown options from the database and write them to a hidden column (e.g., column Y).
• Dropdown Formula: The dropdown list uses a range formula (e.g., Y1:Y10) to reference the hidden column.
• Data Validation: We create a DataValidation object to define the dropdown behavior and apply it to the desired column.
• Autosize Columns: The column widths are automatically adjusted based on content.

4. Handle Long Dropdown Lists

If the dropdown options exceed the Excel limit (255 characters), writing them to a hidden column and referencing that range ensures compatibility.

5. Generate the Export

Use the export class in your controller to generate the Excel file:

<?php
use App\Exports\SampleExport;
use Maatwebsite\Excel\Facades\Excel;

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

Conclusion

By following these steps, you can easily add dropdown lists to your exported Excel files in Laravel. This feature is especially useful for predefined input fields, ensuring data consistency and validation.

If you have any questions or run into issues, feel free to leave a comment below!