CODEWITHSUNDEEP
phpexcel
Pandy Legend
Pandy Legend

Reputation: 1111

PHPExcel Setting Default Dropdown Options on a Column

I am using PHPExcel to generate template Excel documents for users to download in order for them to upload bulk data.

As part of this, I want certain fields to be choices from a dropdown.

The DataValidation example shows how to do this for an individual cell as follows:

$objValidation = $objPHPExcel->getActiveSheet()->getCell('B5')->getDataValidation();
$objValidation->setType( PHPExcel_Cell_DataValidation::TYPE_LIST );
$objValidation->setErrorStyle( PHPExcel_Cell_DataValidation::STYLE_INFORMATION );
$objValidation->setAllowBlank(false);
$objValidation->setShowInputMessage(true);
$objValidation->setShowErrorMessage(true);
$objValidation->setShowDropDown(true);
$objValidation->setErrorTitle('Input error');
$objValidation->setError('Value is not in list.');
$objValidation->setPromptTitle('Pick from list');
$objValidation->setPrompt('Please pick a value from the drop-down list.');
$objValidation->setFormula1('"Item A,Item B,Item C"');  // Make sure to put the list items between " and "  !!!

This is fine for an individual cell, but I want all cells in Column B to be dropdowns when new data rows are added. How can I achieve this?

Thanks

Upvotes: 4

Views: 7411

Answers (3)

realtebo
realtebo

Reputation: 25691

From official doc:

If you need data validation on multiple cells, one can clone the ruleset:

$objPHPExcel->getActiveSheet()->getCell('B8')->setDataValidation(clone
$objValidation);

But, still at end of 2015 there is no 'per-area' way to set this.

Upvotes: 1

user3127118
user3127118

Reputation: 21

you can try this code: your start cell no $i=3 you can change this value and $i<=250 looping area.

for ($i = 3; $i <= 250; $i++)
{
    $objValidation2 = $objPHPExcel->getActiveSheet()->getCell('N' . $i)->getDataValidation();
    $objValidation2->setType(PHPExcel_Cell_DataValidation::TYPE_LIST);
    $objValidation2->setErrorStyle(PHPExcel_Cell_DataValidation::STYLE_INFORMATION);
    $objValidation2->setAllowBlank(false);
    $objValidation2->setShowInputMessage(true);
    $objValidation2->setShowDropDown(true);
    $objValidation2->setPromptTitle('Pick from list');
    $objValidation2->setPrompt('Please pick a value from the drop-down list.');
    $objValidation2->setErrorTitle('Input error');
    $objValidation2->setError('Value is not in list');
    $objValidation2->setFormula1('"male,female"');
}

Upvotes: 2

Shathish
Shathish

Reputation: 296

I know this is a very late reply. But, just in case.

use a loop (foreach / for)

inside the loop

$i = 2;
foreach(...){
   $objValidation = $objPHPExcel->getActiveSheet()->getCell('B'.$i)->getDataValidation();
   $objValidation->setType( PHPExcel_Cell_DataValidation::TYPE_LIST );
   $objValidation->setErrorStyle( PHPExcel_Cell_DataValidation::STYLE_INFORMATION );
   $objValidation->setAllowBlank(false);
   $objValidation->setShowInputMessage(true);
   $objValidation->setShowErrorMessage(true);
   $objValidation->setShowDropDown(true);
   $objValidation->setErrorTitle('Input error');
   $objValidation->setError('Value is not in list.');
   $objValidation->setPromptTitle('Pick from list');
   $objValidation->setPrompt('Please pick a value from the drop-down list.');
   $objValidation->setFormula1('"Item A,Item B,Item C"');

   $i++;
}

this will do what you want.

Upvotes: 1

Related Questions