PHPExcel validates data for a range of cells

Question

I am trying to validate data for a range of cells in PHPExcel . Checking the operation of one cell.

$objValidation = $objPHPExcel->getActiveSheet()->getCell('A1')->getDataValidation(); $objValidation->setType( PHPExcel_Cell_DataValidation::TYPE_WHOLE ); $objValidation->setErrorStyle( PHPExcel_Cell_DataValidation::STYLE_STOP ); $objValidation->setAllowBlank(true); $objValidation->setShowInputMessage(true); $objValidation->setShowErrorMessage(true); $objValidation->setErrorTitle('Input error'); $objValidation->setError('Number is not allowed!'); $objValidation->setPromptTitle('Allowed input'); $objValidation->setPrompt('Only 1 and 0 are allowed.'); $objValidation->setFormula1(0); $objValidation->setFormula2(1); 

I can check other cells by creating such a clone.

 $objPHPExcel->getActiveSheet()->getCell("A2")->setDataValidation(clone $objValidation); 

But if I try to check the data through a loop, it freezes.

 $j = 2; while($j <= 10) { $objPHPExcel->getActiveSheet()->getCell("A$j")->setDataValidation(clone $objValidation); } 

What am I doing wrong here?

PS Looping works with other functions like getStyle() , etc.

Answer 1

In your example, you never increment the $j variable.

Try instead of foreach:

 foreach($j=2; $j<=10; $j++) { //do your stuff } 

Or change it to do-while http://php.net/manual/en/control-structures.do.while.php

Answer 2

You can create a data validation object and assign it to a range - easier than using a loop

 //-- Duration: 1 to 12 on range H2:H100 $xl = new PHPExcel(); $sht = $xl->getActiveSheet(); $oVal = $sht->getDataValidation(); $oVal->setType( PHPExcel_Cell_DataValidation::TYPE_WHOLE ); $oVal->setErrorStyle( PHPExcel_Cell_DataValidation::STYLE_STOP ); $oVal->setAllowBlank(false); $oVal->setShowInputMessage(false); $oVal->setShowErrorMessage(true); $oVal->setErrorTitle("Erreur d'encodage"); $oVal->setError("La durée doit être entre 1 et 12 heures !"); $oVal->setPromptTitle('Validation de données'); $oVal->setPrompt('Uniquement de 1 à 12 heures sont autorisées.'); $oVal->setFormula1(1); $oVal->setFormula2(12); $sht->setDataValidation("H2:H100", $oVal);