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.
Environment Setup:
First, lets create a Laravel application to house our drop-down example:
composer create-project laravel/laravel exampleLaravelExcelDropdowns
Check if it is up and running
cd exampleLaravelExcelDropdowns
php artisan serve
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 .)
composer require maatwebsite/excel
friendsofphp/php-cs-fixer
Register the Laravel Excel config
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:
Controller
Now, Lets add a DogController in App/Http/controllers/DogController.php and add an endpoint so the Excel Spreadsheet can be downloaded:
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
Route::get('/dogs/downloadImportTemplate',
[DogController::class,'downloadImportTemplate']
);
The Spreadsheet
The Spreadsheet will contain two sheets:
The Dog Sheet
On this sheet, we will populate the collection with some example dogs
<?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
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
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!
Top comments (1)
Your support has been immensely valuable to me <3