Example #1
0
 /**
  * Get Data validation
  *
  * @return PHPExcel_Cell_DataValidation
  */
 public function getDataValidation()
 {
     if (!isset($this->_parent)) {
         throw new Exception('Cannot get data validation for cell that is not bound to a worksheet');
     }
     return $this->_parent->getDataValidation($this->getCoordinate());
 }
Example #2
0
 /**
  * Set excel validation for cell range
  * @param string $range In format A2:E30
  * @param string $sourceDataFormula In format Sites!$A$2:$A$4233
  * @param string $errorMessage Optional
  * @return Formatter
  * @throws PhpExcelException
  */
 public function setDataValidationRange($range, $sourceDataFormula, $errorMessage = 'Invalid data')
 {
     $regCoor = '([a-zA-Z]+)(\\d+)';
     if (!preg_match("/{$regCoor}:{$regCoor}/", $range)) {
         throw new PhpExcelException('Range is not in valid format');
     }
     list($fromCoordinates, $toCoordinates) = explode(':', $range);
     $matches = [];
     preg_match("/{$regCoor}/", $fromCoordinates, $matches);
     list(, $fromColName, $fromRowNum) = $matches;
     // intentional skip
     preg_match("/{$regCoor}/", $toCoordinates, $matches);
     list(, $toColName, $toRowNum) = $matches;
     // intentional skip
     $fromColNum = PhpOffice_PHPExcel_Cell::columnIndexFromString($fromColName);
     // A = 1
     $toColNum = PhpOffice_PHPExcel_Cell::columnIndexFromString($toColName);
     if ($fromColNum > $toColNum || $fromRowNum > $toRowNum) {
         throw new PhpExcelException("Range '{$range}' is not in valid format");
     }
     for ($colNum = $fromColNum; $colNum <= $toColNum; $colNum++) {
         $colName = PhpOffice_PHPExcel_Cell::stringFromColumnIndex($colNum - 1);
         // A = 0
         for ($rowNum = $fromRowNum; $rowNum <= $toRowNum; $rowNum++) {
             $objValidation = $this->sheet->getDataValidation($colName . $rowNum);
             $objValidation->setType(PhpOffice_PHPExcel_Cell_DataValidation::TYPE_LIST);
             $objValidation->setErrorStyle(PhpOffice_PHPExcel_Cell_DataValidation::STYLE_INFORMATION);
             $objValidation->setAllowBlank(false);
             $objValidation->setShowInputMessage(true);
             $objValidation->setShowErrorMessage(true);
             $objValidation->setShowDropDown(true);
             $objValidation->setErrorTitle('Input error');
             $objValidation->setError($errorMessage);
             $objValidation->setFormula1($sourceDataFormula);
         }
     }
     return $this;
 }