/**
* Store the DATAVALIDATIONS and DATAVALIDATION records.
*/
private function _writeDataValidity()
{
// Datavalidation collection
$dataValidationCollection = $this->_phpSheet->getDataValidationCollection();
// Write data validations?
if (count($dataValidationCollection) > 0) {
// DATAVALIDATIONS record
$record = 0x1b2;
// Record identifier
$length = 0x12;
// Bytes to follow
$grbit = 0x0;
// Prompt box at cell, no cached validity data at DV records
$horPos = 0x0;
// Horizontal position of prompt box, if fixed position
$verPos = 0x0;
// Vertical position of prompt box, if fixed position
$objId = 0xffffffff;
// Object identifier of drop down arrow object, or -1 if not visible
$header = pack('vv', $record, $length);
$data = pack('vVVVV', $grbit, $horPos, $verPos, $objId, count($dataValidationCollection));
$this->_append($header . $data);
// DATAVALIDATION records
$record = 0x1be;
// Record identifier
foreach ($dataValidationCollection as $cellCoordinate => $dataValidation) {
// initialize record data
$data = '';
// options
$options = 0x0;
// data type
$type = $dataValidation->getType();
switch ($type) {
case Cell_DataValidation::TYPE_NONE:
$type = 0x0;
break;
case Cell_DataValidation::TYPE_WHOLE:
$type = 0x1;
break;
case Cell_DataValidation::TYPE_DECIMAL:
$type = 0x2;
break;
case Cell_DataValidation::TYPE_LIST:
$type = 0x3;
break;
case Cell_DataValidation::TYPE_DATE:
$type = 0x4;
break;
case Cell_DataValidation::TYPE_TIME:
$type = 0x5;
break;
case Cell_DataValidation::TYPE_TEXTLENGTH:
$type = 0x6;
break;
case Cell_DataValidation::TYPE_CUSTOM:
$type = 0x7;
break;
}
$options |= $type << 0;
// error style
$errorStyle = $dataValidation->getType();
switch ($errorStyle) {
case Cell_DataValidation::STYLE_STOP:
$errorStyle = 0x0;
break;
case Cell_DataValidation::STYLE_WARNING:
$errorStyle = 0x1;
break;
case Cell_DataValidation::STYLE_INFORMATION:
$errorStyle = 0x2;
break;
}
$options |= $errorStyle << 4;
// explicit formula?
if ($type == 0x3 && preg_match('/^\\".*\\"$/', $dataValidation->getFormula1())) {
$options |= 0x1 << 7;
}
// empty cells allowed
$options |= $dataValidation->getAllowBlank() << 8;
// show drop down
$options |= !$dataValidation->getShowDropDown() << 9;
// show input message
$options |= $dataValidation->getShowInputMessage() << 18;
// show error message
$options |= $dataValidation->getShowErrorMessage() << 19;
// condition operator
$operator = $dataValidation->getOperator();
switch ($operator) {
case Cell_DataValidation::OPERATOR_BETWEEN:
$operator = 0x0;
break;
case Cell_DataValidation::OPERATOR_NOTBETWEEN:
$operator = 0x1;
break;
case Cell_DataValidation::OPERATOR_EQUAL:
$operator = 0x2;
break;
case Cell_DataValidation::OPERATOR_NOTEQUAL:
$operator = 0x3;
break;
case Cell_DataValidation::OPERATOR_GREATERTHAN:
$operator = 0x4;
break;
case Cell_DataValidation::OPERATOR_LESSTHAN:
$operator = 0x5;
break;
case Cell_DataValidation::OPERATOR_GREATERTHANOREQUAL:
$operator = 0x6;
break;
case Cell_DataValidation::OPERATOR_LESSTHANOREQUAL:
$operator = 0x7;
break;
}
$options |= $operator << 20;
$data = pack('V', $options);
// prompt title
$promptTitle = $dataValidation->getPromptTitle() !== '' ? $dataValidation->getPromptTitle() : chr(0);
$data .= Shared_String::UTF8toBIFF8UnicodeLong($promptTitle);
// error title
$errorTitle = $dataValidation->getErrorTitle() !== '' ? $dataValidation->getErrorTitle() : chr(0);
$data .= Shared_String::UTF8toBIFF8UnicodeLong($errorTitle);
// prompt text
$prompt = $dataValidation->getPrompt() !== '' ? $dataValidation->getPrompt() : chr(0);
$data .= Shared_String::UTF8toBIFF8UnicodeLong($prompt);
// error text
$error = $dataValidation->getError() !== '' ? $dataValidation->getError() : chr(0);
$data .= Shared_String::UTF8toBIFF8UnicodeLong($error);
// formula 1
try {
$formula1 = $dataValidation->getFormula1();
if ($type == 0x3) {
// list type
$formula1 = str_replace(',', chr(0), $formula1);
}
$this->_parser->parse($formula1);
$formula1 = $this->_parser->toReversePolish();
$sz1 = strlen($formula1);
} catch (Exception $e) {
$sz1 = 0;
$formula1 = '';
}
$data .= pack('vv', $sz1, 0x0);
$data .= $formula1;
// formula 2
try {
$formula2 = $dataValidation->getFormula2();
if ($formula2 === '') {
throw new Exception('No formula2');
}
$this->_parser->parse($formula2);
$formula2 = $this->_parser->toReversePolish();
$sz2 = strlen($formula2);
} catch (Exception $e) {
$sz2 = 0;
$formula2 = '';
}
$data .= pack('vv', $sz2, 0x0);
$data .= $formula2;
// cell range address list
$data .= pack('v', 0x1);
$data .= $this->_writeBIFF8CellRangeAddressFixed($cellCoordinate);
$length = strlen($data);
$header = pack("vv", $record, $length);
$this->_append($header . $data);
}
}
}
/**
* Writes Excel FORMAT record for non "built-in" numerical formats.
*
* @param string $format Custom format string
* @param integer $ifmt Format index code
*/
private function _writeNumFormat($format, $ifmt)
{
$record = 0x41e;
// Record identifier
if ($this->_BIFF_version == 0x600) {
$numberFormatString = Shared_String::UTF8toBIFF8UnicodeLong($format);
$length = 2 + strlen($numberFormatString);
// Number of bytes to follow
} elseif ($this->_BIFF_version == 0x500) {
$length = 3 + strlen($format);
// Number of bytes to follow
}
$header = pack("vv", $record, $length);
if ($this->_BIFF_version == 0x600) {
$data = pack("v", $ifmt) . $numberFormatString;
$this->_append($header . $data);
} elseif ($this->_BIFF_version == 0x500) {
$cch = strlen($format);
// Length of format string
$data = pack("vC", $ifmt, $cch);
$this->_append($header . $data . $format);
}
}
