Default Deployment Options for PHPExcel by Column

Question

I use PHPExcel to create Excel template documents for users to download so that they can load bulk data.

As part of this, I want certain fields to be a selection from the drop-down list.

The DataValidation example shows how to do this for a single 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 a single cell, but I want all the cells in column B to be dropdowns when adding new rows of data. How can I achieve this?

thanks

Answer 1

you can try this code: your starting cell is no $ i = 3 you can change this value and the cycle area $ i <= 250.

 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"'); } 

Answer 2

I know this is a very late answer. 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++; } 

it will do what you want.

Answer 3

From the official doc:

If you need to validate data across multiple cells, you can clone a rule set:

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

But, at the end of 2015, there is no 'per-area' method for this.