CODEWITHSUNDEEP
laravellaravel-excel
Code Embassy
Code Embassy

Reputation: 247

how to apply dropdown list to given column range or entire column Laravel excel

Hi There I am using Maatwebsite/Laravel-Excel package to create my excel sheet. i have many dropdown list and i am able to add it for particular cell. i want to add drop down in entire column or in the give column range. can you please guide me how to apply drop down list for entire column please ?

see this is the peace of code

$objValidation2 = $sheet->getCell('E1')->getDataValidation();

above code currently puts drop down in cell E1 only. how can i specify particular cell range to put drop down in the given range

Upvotes: 1

Views: 1067

Answers (2)

Ashish Chauhan
Ashish Chauhan

Reputation: 51

 public function registerEvents(): array
{
    return [
            // set dropdown column
            $drop_column = 'E';

            // set dropdown options
            $options = ["Option1", "Option2", "Option3"];

            // 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)));


             $row_count = count($this->results) + 1;
            $column_count = count($this->results);

             // // 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);
            }
        },
    ];
}

Upvotes: 0

vimuth
vimuth

Reputation: 5672

You need to get the row count you need to export. then try,

for($i=1; $i<=$this->rowCount; $i++){
   $objValidation2 = $sheet->getCell('E'.$i)->getDataValidation();
}

For getting row count try using public variable and set it when take the dataset. For example.

namespace App\Exports;

use App\Invoice;
use Maatwebsite\Excel\Concerns\FromCollection;

class InvoicesExport implements FromCollection
{
    public $rowCount = 0;
    public function collection()
    {   
        $invoices = Invoice::all()
        $this->rowCount = invoices->count(); 
        return $invoices;
    }
}

Upvotes: 1

Related Questions