/** * Read DATAVALIDATION record */ private function _readDataValidation() { $length = $this->_GetInt2d($this->_data, $this->_pos + 2); $recordData = substr($this->_data, $this->_pos + 4, $length); // move stream pointer forward to next record $this->_pos += 4 + $length; if ($this->_readDataOnly) { return; } // offset: 0; size: 4; Options $options = $this->_GetInt4d($recordData, 0); // bit: 0-3; mask: 0x0000000F; type $type = (0xf & $options) >> 0; switch ($type) { case 0x0: $type = Cell_DataValidation::TYPE_NONE; break; case 0x1: $type = Cell_DataValidation::TYPE_WHOLE; break; case 0x2: $type = Cell_DataValidation::TYPE_DECIMAL; break; case 0x3: $type = Cell_DataValidation::TYPE_LIST; break; case 0x4: $type = Cell_DataValidation::TYPE_DATE; break; case 0x5: $type = Cell_DataValidation::TYPE_TIME; break; case 0x6: $type = Cell_DataValidation::TYPE_TEXTLENGTH; break; case 0x7: $type = Cell_DataValidation::TYPE_CUSTOM; break; } // bit: 4-6; mask: 0x00000070; error type $errorStyle = (0x70 & $options) >> 4; switch ($errorStyle) { case 0x0: $errorStyle = Cell_DataValidation::STYLE_STOP; break; case 0x1: $errorStyle = Cell_DataValidation::STYLE_WARNING; break; case 0x2: $errorStyle = Cell_DataValidation::STYLE_INFORMATION; break; } // bit: 7; mask: 0x00000080; 1= formula is explicit (only applies to list) // I have only seen cases where this is 1 $explicitFormula = (0x80 & $options) >> 7; // bit: 8; mask: 0x00000100; 1= empty cells allowed $allowBlank = (0x100 & $options) >> 8; // bit: 9; mask: 0x00000200; 1= suppress drop down arrow in list type validity $suppressDropDown = (0x200 & $options) >> 9; // bit: 18; mask: 0x00040000; 1= show prompt box if cell selected $showInputMessage = (0x40000 & $options) >> 18; // bit: 19; mask: 0x00080000; 1= show error box if invalid values entered $showErrorMessage = (0x80000 & $options) >> 19; // bit: 20-23; mask: 0x00F00000; condition operator $operator = (0xf00000 & $options) >> 20; switch ($operator) { case 0x0: $operator = Cell_DataValidation::OPERATOR_BETWEEN; break; case 0x1: $operator = Cell_DataValidation::OPERATOR_NOTBETWEEN; break; case 0x2: $operator = Cell_DataValidation::OPERATOR_EQUAL; break; case 0x3: $operator = Cell_DataValidation::OPERATOR_NOTEQUAL; break; case 0x4: $operator = Cell_DataValidation::OPERATOR_GREATERTHAN; break; case 0x5: $operator = Cell_DataValidation::OPERATOR_LESSTHAN; break; case 0x6: $operator = Cell_DataValidation::OPERATOR_GREATERTHANOREQUAL; break; case 0x7: $operator = Cell_DataValidation::OPERATOR_LESSTHANOREQUAL; break; } // offset: 4; size: var; title of the prompt box $offset = 4; $string = $this->_readUnicodeStringLong(substr($recordData, $offset)); $promptTitle = $string['value'] !== chr(0) ? $string['value'] : ''; $offset += $string['size']; // offset: var; size: var; title of the error box $string = $this->_readUnicodeStringLong(substr($recordData, $offset)); $errorTitle = $string['value'] !== chr(0) ? $string['value'] : ''; $offset += $string['size']; // offset: var; size: var; text of the prompt box $string = $this->_readUnicodeStringLong(substr($recordData, $offset)); $prompt = $string['value'] !== chr(0) ? $string['value'] : ''; $offset += $string['size']; // offset: var; size: var; text of the error box $string = $this->_readUnicodeStringLong(substr($recordData, $offset)); $error = $string['value'] !== chr(0) ? $string['value'] : ''; $offset += $string['size']; // offset: var; size: 2; size of the formula data for the first condition $sz1 = $this->_GetInt2d($recordData, $offset); $offset += 2; // offset: var; size: 2; not used $offset += 2; // offset: var; size: $sz1; formula data for first condition (without size field) $formula1 = substr($recordData, $offset, $sz1); $formula1 = pack('v', $sz1) . $formula1; // prepend the length try { $formula1 = $this->_getFormulaFromStructure($formula1); // in list type validity, null characters are used as item separators if ($type == Cell_DataValidation::TYPE_LIST) { $formula1 = str_replace(chr(0), ',', $formula1); } } catch (Exception $e) { return; } $offset += $sz1; // offset: var; size: 2; size of the formula data for the first condition $sz2 = $this->_GetInt2d($recordData, $offset); $offset += 2; // offset: var; size: 2; not used $offset += 2; // offset: var; size: $sz2; formula data for second condition (without size field) $formula2 = substr($recordData, $offset, $sz2); $formula2 = pack('v', $sz2) . $formula2; // prepend the length try { $formula2 = $this->_getFormulaFromStructure($formula2); } catch (Exception $e) { return; } $offset += $sz2; // offset: var; size: var; cell range address list with $cellRangeAddressList = $this->_readBIFF8CellRangeAddressList(substr($recordData, $offset)); $cellRangeAddresses = $cellRangeAddressList['cellRangeAddresses']; foreach ($cellRangeAddresses as $cellRange) { $stRange = $this->_phpSheet->shrinkRangeToFit($cellRange); $stRange = Cell::extractAllCellReferencesInRange($stRange); foreach ($stRange as $coordinate) { $objValidation = $this->_phpSheet->getCell($coordinate)->getDataValidation(); $objValidation->setType($type); $objValidation->setErrorStyle($errorStyle); $objValidation->setAllowBlank((bool) $allowBlank); $objValidation->setShowInputMessage((bool) $showInputMessage); $objValidation->setShowErrorMessage((bool) $showErrorMessage); $objValidation->setShowDropDown(!$suppressDropDown); $objValidation->setOperator($operator); $objValidation->setErrorTitle($errorTitle); $objValidation->setError($error); $objValidation->setPromptTitle($promptTitle); $objValidation->setPrompt($prompt); $objValidation->setFormula1($formula1); $objValidation->setFormula2($formula2); } } }