/** * 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()); }
/** * 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; }