maatwebsite laravel excel export columns with drop down list

Question

I have been using Laravel Excel to export data in csv format and it has been great so far. Now I need to export in xlsx format so that I can include dropdown lists in some of the columns. I have looked at this question but it looks like that is for an older version of Laravel Excel. I also looked at the page in the docs that explains extending the package, but I can't seem to figure out how to add a dropdown list to a column while exporting data.

This is a simplified version of my export class:

namespace App\Exports;

use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithHeadings;
use Maatwebsite\Excel\Concerns\WithStrictNullComparison;

class ActionItemExport implements FromCollection, WithHeadings, WithStrictNullComparison
{

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

    public function headings(): array
    {
        $columns = [
            'Column 1',
            'Column 2',
            'Column 3',
            'Column 4',
            'Column 5',
            'Column 6',
            'Column 7'
        ];
        return $columns;
    }

    private function getActionItems()
    {
        $select = 'column1, column2, column3, column4, column5, column6, column7';

        $query = \DB::table('action_items')->select(\DB::raw($select));
        $query->whereNull('action_items.deleted_at');

        $ai = $query->orderBy('column1')->get();
        return $ai;
    }
}

What I would like to do is query a lookup table that has the options for column1 and use those values for a drop down list in the column so that when a user wants to change the excel sheet, they are limited to only the drop down values.

In the docs it mentions to use \Maatwebsite\Excel\Sheet or \Maatwebsite\Excel\Writer, but I'm not even sure where to use those at, or which one to use.

Throughout my searches I just can't seem to piece together a solution so any help would be appreciated.

I'm using:

maatwebsite/excel 3.1, php 7.2, laravel 5.8

You want something like this? https://imgur.com/a/lwnSa02 – IGP Nov 27 '19 at 01:52 — IGP, Nov 27 '19 at 01:52

score 10 · Accepted Answer · answered Nov 27 '19 at 01:55

The implementation of sheet events can be rather confusing and hard to find examples for, so I try to lend a hand when I see a post like this. First, I'll say you should really be looking at the PHPSpreadsheet documentation for these additional features. It's where you're going to find the important information you need. Then you can translate what you find for use in Laravel Excel.

PHPSpreadsheet: Setting data validation on a cell https://phpspreadsheet.readthedocs.io/en/latest/topics/recipes/#setting-data-validation-on-a-cell

Here is an example building upon your existing file. I also threw in some bonus formatting to autosize the column widths — a must in my opinion.

namespace App\Exports;

use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithHeadings;
use Maatwebsite\Excel\Concerns\WithStrictNullComparison;
use Maatwebsite\Excel\Concerns\WithEvents;
use Maatwebsite\Excel\Events\AfterSheet;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\Cell\DataValidation;

class ActionItemExport implements FromCollection, WithHeadings, WithEvents, WithStrictNullComparison
{
    protected $results;

    public function collection()
    {
        // store the results for later use
        $this->results = $this->getActionItems();

        return $this->results;
    }

    // ...

    public function registerEvents(): array
    {
        return [
            // handle by a closure.
            AfterSheet::class => function(AfterSheet $event) {

                // get layout counts (add 1 to rows for heading row)
                $row_count = $this->results->count() + 1;
                $column_count = count($this->results[0]->toArray());

                // set dropdown column
                $drop_column = 'A';

                // set dropdown options
                $options = [
                    'option 1',
                    'option 2',
                    'option 3',
                ];

                // set dropdown list for first data row
                $validation = $event->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(sprintf('"%s"',implode(',',$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 = Coordinate::stringFromColumnIndex($i);
                    $event->sheet->getColumnDimension($column)->setAutoSize(true);
                }
            },
        ];
    }
}

Thank you, this is exactly what I needed. I think the Laravel Excel docs needs more examples like this. — Sehael, Nov 27 '19 at 17:40
is there a limit of numbers you can add to dropdown options? — Franz, Oct 27 '20 at 07:03
According to online sources, Excel has (or had at one time) [limits of 32,767 items and 256 characters per item](https://answers.microsoft.com/en-us/msoffice/forum/msoffice_excel-mso_win10-mso_365hp/what-is-limit-of-the-number-of-entries-in-excel/9ce4a909-8b03-428f-94a4-1b13433aa399) in a dropdown list. I don't know whether Laravel Excel or PHPSpreadsheet impose their own limits. — matticustard, Oct 29 '20 at 13:08

Prajakta Kate · Answer 2 · 2023-08-03T05:59:32.800

Here is the example of export multiple columns with dropdown list from datalist taken from table and header having styling like bold and background color.

 <?php

namespace App\Exports;

use App\Category;
use App\Product;
use App\SKU;
use App\Tra_Tax;
use Illuminate\Support\Facades\Auth;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithHeadings;
use Maatwebsite\Excel\Concerns\WithStrictNullComparison;
use Maatwebsite\Excel\Concerns\WithEvents;
use Maatwebsite\Excel\Events\AfterSheet;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\Cell\DataValidation;
use Maatwebsite\Excel\Concerns\WithStyles;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;


class ProductExport implements FromCollection, WithHeadings, WithEvents, WithStrictNullComparison, WithStyles
{
    /**
    * @return \Illuminate\Support\Collection
    */
    protected $results;
    public function collection()
    {
        $user = Auth::user();
        if($user->type=="staff"){
            $this->id = $user->created_by;
        }else{
            $this->id = $user->id;
        }
        $this->results = $this->getProducts();
        return $this->results;
    }
    public function getProducts(){
        $product = Product::select('product_name','description','category','price','sale_price','quantity','sku',
        'sku_quantity','bar_code','tax','sold_by')
        ->where('created_by',$this->id)->where('status',1)->take(1)->get();
        $product[0]->category = Category::where('id',$product[0]->category)->pluck('name')->first();
        return $product;
    }
    public function headings(): array
    {
        return [
          'Product Name','Description','Category','Price','Sale Price','Quantity','SKU','SKU Quantity',
          'Bar Code','Tax','Sold By'
        ];
    }
    public function styles(Worksheet $sheet)
    {
        return [
             1    => ['font' => ['bold' => true]],
        ];
    }
    public function registerEvents(): array
    {
        return [
            AfterSheet::class => function(AfterSheet $event) {
               
                //sheet object & no. rows
                $row_count = 50;
                $column_count = count($this->results[0]->toArray());
                $sheet = $event->sheet;

                //header color
                $sheet->getDelegate()->getStyle('A1:L1')
                ->getFill()
                ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
                ->getStartColor()
                ->setARGB('ADD8E6');

                //category dropdown
                $category = Category::where('created_by',$this->id)->pluck('name')->toArray();
                $validation2 = $sheet->getCell('C2')->getDataValidation();
                $validation2->setType(DataValidation::TYPE_LIST );
                $validation2->setErrorStyle(DataValidation::STYLE_INFORMATION );
                $validation2->setAllowBlank(false);
                $validation2->setShowInputMessage(true);
                $validation2->setShowErrorMessage(true);
                $validation2->setShowDropDown(true);
                $validation2->setErrorTitle('Input error');
                $validation2->setError('Value is not in list.');
                $validation2->setPromptTitle('Pick from category list');
                $validation2->setPrompt('Please pick a value from the drop-down list.');
                $validation2->setFormula1('"' . implode(',',$category). '"');
                for ($i = 3; $i <= $row_count; $i++) {
                    $event->sheet->getCell("C{$i}")->setDataValidation(clone $validation2);
                }

                //sku dropdown
                $sku = SKU::where('created_by',$this->id)->pluck('unit')->toArray();
                $validation = $sheet->getCell('G2')->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 sku list');
                $validation->setPrompt('Please pick a value from the drop-down list.');
                $validation->setFormula1('"' . implode(',',$sku). '"');
                for ($i = 3; $i <= $row_count; $i++) {
                    $event->sheet->getCell("G{$i}")->setDataValidation(clone $validation);
                }

                //tax dropdown
                $tax = 'A,B,C,D,E';
                $validation1 = $sheet->getCell('J2')->getDataValidation();
                $validation1->setType(DataValidation::TYPE_LIST );
                $validation1->setErrorStyle(DataValidation::STYLE_INFORMATION );
                $validation1->setAllowBlank(false);
                $validation1->setShowInputMessage(true);
                $validation1->setShowErrorMessage(true);
                $validation1->setShowDropDown(true);
                $validation1->setErrorTitle('Input error');
                $validation1->setError('Value is not in list.');
                $validation1->setPromptTitle('Pick from tax list');
                $validation1->setPrompt('Please pick a value from the drop-down list.');
                $validation1->setFormula1('"' . $tax. '"');
                for ($i = 3; $i <= $row_count; $i++) {
                    $event->sheet->getCell("J{$i}")->setDataValidation(clone $validation1);
                }

                //sold by dropdown
                $sold_by ="each,weight";
                $validation2 = $sheet->getCell('K2')->getDataValidation();
                $validation2->setType(DataValidation::TYPE_LIST );
                $validation2->setErrorStyle(DataValidation::STYLE_INFORMATION );
                $validation2->setAllowBlank(false);
                $validation2->setShowInputMessage(true);
                $validation2->setShowErrorMessage(true);
                $validation2->setShowDropDown(true);
                $validation2->setErrorTitle('Input error');
                $validation2->setError('Value is not in list.');
                $validation2->setPromptTitle('Pick from sold by list');
                $validation2->setPrompt('Please pick a value from the drop-down list.');
                $validation2->setFormula1('"' . $sold_by. '"');
                for ($i = 3; $i <= $row_count; $i++) {
                    $event->sheet->getCell("K{$i}")->setDataValidation(clone $validation2);
                }

                //set size of column as per the length
                for ($i = 1; $i <= $column_count; $i++) {
                    $column = Coordinate::stringFromColumnIndex($i);
                    $event->sheet->getColumnDimension($column)->setAutoSize(true);
                }
            },
        ];
    }
}

maatwebsite laravel excel export columns with drop down list

2 Answers2