Create a Laravel App to export a Spreadsheet with a Dropdown
Published: 2023-08-27 20:22:34

So cool! Due to an example I posted earlier on dev.to,
a community member reached out to me on LinkedIn, and asked if I could provide an example.  Here is a Start To Finish example on how to programmatically create downloadable spreadsheets using Laravel excel WITH drop downs.

In this article I will explain how to create am Excel Spreadsheet using Laravel with Drop-down menus on a column.

You may view the code here:
https://github.com/paulpreibisch/laravelExcelDropdownExample

This example will pull drop-down data from a separate sheet within the Workbook so we can get around Excels 256 character data limit for drop-downs.

My example spreadsheet lists two sheets: Dogs, Dog Type
The type cell in each row of the Dog sheet, will provide a drop-down of dog types, which is pulled from the second sheet in the workbook.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/q5v61y4cy2tt9lnw2vy7.png)


## Environment Setup:
First, lets create a Laravel application to house our drop-down example:

```bash
composer create-project laravel/laravel exampleLaravelExcelDropdowns
```

Check if it is up and running
```bash
cd exampleLaravelExcelDropdowns
php artisan serve
```
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ypxj35pwtkcn2owzrb62.png)

### Composer Requirements
Lets add our requirements: 
* Laravel Excel
* friendsofphp/php-cs-fixer (so we can clean our code as we go using `./vendor/bin/php-cs-fixer fix .`)

```bash
composer require maatwebsite/excel
friendsofphp/php-cs-fixer  

```
Register the Laravel Excel config
```bash
php artisan vendor:publish --provider="Maatwebsite\Excel\ExcelServiceProvider" --tag=config
```

There are some nice default settings in excel.php so check it out. You can change things such as the document properties of each Excel File generated etc:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/897zmzn4mtbmqri7fe18.png)

### Controller

Now, Lets add a DogController in `App/Http/controllers/DogController.php` and add an endpoint so the Excel Spreadsheet can be downloaded:

```php

namespace App\Http\Controllers;

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

class DogController extends Controller
{
    public function downloadImportTemplate()
    {
       $dogWorkbook = App::make(DogWorkbook::class);
        return Excel::download($dogWorkbook, 'DogImportTemplate.xlsx');
}
```

### Routing
Now, let's add a route to our Controller function in `web.php`

```php

Route::get('/dogs/downloadImportTemplate',
    [DogController::class,'downloadImportTemplate']
);
```
# The Spreadsheet
The Spreadsheet will contain two sheets:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/6j7qtyz5w1kzg3ikao1k.png)


### The Dog Sheet 
On this sheet, we will populate the collection with some example dogs

```php
<?php

namespace App\Exports\Excel;

use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithHeadingRow;
use Maatwebsite\Excel\Concerns\WithStyles;
use Maatwebsite\Excel\Concerns\WithTitle;
use PhpOffice\PhpSpreadsheet\Cell\DataValidation;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;

class DogSheet implements FromCollection, WithHeadingRow, WithStyles, WithTitle
{
    const SHEET_TITLE = 'Dogs';

    public function collection()
    {
        return collect(
            [
                [
                    'id','name','type'
                ],
                [
                    'id'   => 1,
                    'name' => 'Midnight',
                    'dog_type' => 'Black Fell Terrier',
                ],
                [
                    'id'   => 2,
                    'name' => 'Celeste',
                    'dog_type' => 'Husky Mix',
                ],
                [
                    'id'   => 3,
                    'name' => 'Renata',
                    'dog_type' => 'Shepard Mix',
                ],
            ]
        );
    }
    public function styles(Worksheet $sheet)
    {
        $validation = $sheet->getCell('C1')->getDataValidation();
        $validation->setType(DataValidation::TYPE_LIST);
        $validation->setErrorStyle(DataValidation::STYLE_INFORMATION);
        $validation->setAllowBlank(true);
        $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( '\'' . DogTypesSheet::SHEET_TITLE . '\'!$A$2:$A$10000');

        $validation->setSqref("C1:C10000");
    }

    public function title(): string
    {
        return self::SHEET_TITLE;
    }
}
```

### Dog Types Sheet
Now Lets make the other sheet which will be used to populate the dropdown

```php
<?php

namespace App\Exports\Excel;

use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithHeadingRow;
use Maatwebsite\Excel\Concerns\WithStyles;
use Maatwebsite\Excel\Concerns\WithTitle;
use PhpOffice\PhpSpreadsheet\Cell\DataValidation;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;

class DogTypesSheet implements FromCollection, WithHeadingRow, WithTitle
{
    const SHEET_TITLE = "Dog Types";

    public function collection()
    {
        return collect(
            [
                [
                    'Type'
                ],
                [

                    'Black Fell Terrier',
                ],
                [
                    'Husky Mix',
                ],
                [
                   'Shepard Mix',
                ],
            ]
        );
    }

    public function title(): string
    {
        return self::SHEET_TITLE;
    }
}

```
### The Workbook
Finally, Ill create the Workbook that contains the two sheets, and is downloaded via our controller endpoint

```php
<?php

namespace App\Exports\Excel;

use Maatwebsite\Excel\Concerns\WithMultipleSheets;

class DogWorkbook implements WithMultipleSheets
{
    private DogSheet $dogSheet;
    private DogTypesSheet $dogTypesSheet;
    public function __construct(DogSheet $dogSheet, DogTypesSheet $dogTypesSheet)
    {
        $this->dogSheet = $dogSheet;
        $this->dogTypesSheet = $dogTypesSheet;
    }


    public function sheets(): array
    {
        return [
            "Dogs" => $this->dogSheet,
            "DogTypes" => $this->dogTypesSheet
        ];
    }
}
```
Now, point your browser to:

http://127.0.0.1:8001/dogs/downloadImportTemplate

And you should see the template in action!

I hope this helps.

P.S.

I just got asked to create a new Workbook, with the Columns:
Name, Type, Breed

I am to add a dropdown for the Type column where the user can choose the values "Dog" or "Cat".  If they choose "Dog" then another dropdown in Column "Breed", should list all the dogs breeds.  But if the user chooses "Cat", then the dropdown should show all the Cats Breeds.

I've Updated the git repo now, and once downloaded, you can go to
http://127.0.0.1:8001/animals/downloadTemplate
to see the desired behavior.
I will create another blog post this week to explain how I did it!

Cheers!