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