/**
* Store the DATAVALIDATIONS and DATAVALIDATION records.
*/
private function writeDataValidity()
{
// Datavalidation collection
$dataValidationCollection = $this->phpSheet->getDataValidationCollection();
// Write data validations?
if (!empty($dataValidationCollection)) {
// 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 \PHPExcel\Cell\DataValidation::TYPE_NONE:
$type = 0x0;
break;
case \PHPExcel\Cell\DataValidation::TYPE_WHOLE:
$type = 0x1;
break;
case \PHPExcel\Cell\DataValidation::TYPE_DECIMAL:
$type = 0x2;
break;
case \PHPExcel\Cell\DataValidation::TYPE_LIST:
$type = 0x3;
break;
case \PHPExcel\Cell\DataValidation::TYPE_DATE:
$type = 0x4;
break;
case \PHPExcel\Cell\DataValidation::TYPE_TIME:
$type = 0x5;
break;
case \PHPExcel\Cell\DataValidation::TYPE_TEXTLENGTH:
$type = 0x6;
break;
case \PHPExcel\Cell\DataValidation::TYPE_CUSTOM:
$type = 0x7;
break;
}
$options |= $type << 0;
// error style
$errorStyle = $dataValidation->getType();
switch ($errorStyle) {
case \PHPExcel\Cell\DataValidation::STYLE_STOP:
$errorStyle = 0x0;
break;
case \PHPExcel\Cell\DataValidation::STYLE_WARNING:
$errorStyle = 0x1;
break;
case \PHPExcel\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 \PHPExcel\Cell\DataValidation::OPERATOR_BETWEEN:
$operator = 0x0;
break;
case \PHPExcel\Cell\DataValidation::OPERATOR_NOTBETWEEN:
$operator = 0x1;
break;
case \PHPExcel\Cell\DataValidation::OPERATOR_EQUAL:
$operator = 0x2;
break;
case \PHPExcel\Cell\DataValidation::OPERATOR_NOTEQUAL:
$operator = 0x3;
break;
case \PHPExcel\Cell\DataValidation::OPERATOR_GREATERTHAN:
$operator = 0x4;
break;
case \PHPExcel\Cell\DataValidation::OPERATOR_LESSTHAN:
$operator = 0x5;
break;
case \PHPExcel\Cell\DataValidation::OPERATOR_GREATERTHANOREQUAL:
$operator = 0x6;
break;
case \PHPExcel\Cell\DataValidation::OPERATOR_LESSTHANOREQUAL:
$operator = 0x7;
break;
}
$options |= $operator << 20;
$data = pack('V', $options);
// prompt title
$promptTitle = $dataValidation->getPromptTitle() !== '' ? $dataValidation->getPromptTitle() : chr(0);
$data .= \PHPExcel\Shared\StringHelper::UTF8toBIFF8UnicodeLong($promptTitle);
// error title
$errorTitle = $dataValidation->getErrorTitle() !== '' ? $dataValidation->getErrorTitle() : chr(0);
$data .= \PHPExcel\Shared\StringHelper::UTF8toBIFF8UnicodeLong($errorTitle);
// prompt text
$prompt = $dataValidation->getPrompt() !== '' ? $dataValidation->getPrompt() : chr(0);
$data .= \PHPExcel\Shared\StringHelper::UTF8toBIFF8UnicodeLong($prompt);
// error text
$error = $dataValidation->getError() !== '' ? $dataValidation->getError() : chr(0);
$data .= \PHPExcel\Shared\StringHelper::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 (\PHPExcel\Exception $e) {
$sz1 = 0;
$formula1 = '';
}
$data .= pack('vv', $sz1, 0x0);
$data .= $formula1;
// formula 2
try {
$formula2 = $dataValidation->getFormula2();
if ($formula2 === '') {
throw new \PHPExcel\Writer\Exception('No formula2');
}
$this->parser->parse($formula2);
$formula2 = $this->parser->toReversePolish();
$sz2 = strlen($formula2);
} catch (\PHPExcel\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);
}
}
}
/**
* Write DataValidations
*
* @param \PHPExcel\Shared\XMLWriter $objWriter XML Writer
* @param \PHPExcel\Worksheet $pSheet Worksheet
* @throws \PHPExcel\Writer\Exception
*/
private function writeDataValidations(\PHPExcel\Shared\XMLWriter $objWriter = null, \PHPExcel\Worksheet $pSheet = null)
{
// Datavalidation collection
$dataValidationCollection = $pSheet->getDataValidationCollection();
// Write data validations?
if (!empty($dataValidationCollection)) {
$objWriter->startElement('dataValidations');
$objWriter->writeAttribute('count', count($dataValidationCollection));
foreach ($dataValidationCollection as $coordinate => $dv) {
$objWriter->startElement('dataValidation');
if ($dv->getType() != '') {
$objWriter->writeAttribute('type', $dv->getType());
}
if ($dv->getErrorStyle() != '') {
$objWriter->writeAttribute('errorStyle', $dv->getErrorStyle());
}
if ($dv->getOperator() != '') {
$objWriter->writeAttribute('operator', $dv->getOperator());
}
$objWriter->writeAttribute('allowBlank', $dv->getAllowBlank() ? '1' : '0');
$objWriter->writeAttribute('showDropDown', !$dv->getShowDropDown() ? '1' : '0');
$objWriter->writeAttribute('showInputMessage', $dv->getShowInputMessage() ? '1' : '0');
$objWriter->writeAttribute('showErrorMessage', $dv->getShowErrorMessage() ? '1' : '0');
if ($dv->getErrorTitle() !== '') {
$objWriter->writeAttribute('errorTitle', $dv->getErrorTitle());
}
if ($dv->getError() !== '') {
$objWriter->writeAttribute('error', $dv->getError());
}
if ($dv->getPromptTitle() !== '') {
$objWriter->writeAttribute('promptTitle', $dv->getPromptTitle());
}
if ($dv->getPrompt() !== '') {
$objWriter->writeAttribute('prompt', $dv->getPrompt());
}
$objWriter->writeAttribute('sqref', $coordinate);
if ($dv->getFormula1() !== '') {
$objWriter->writeElement('formula1', $dv->getFormula1());
}
if ($dv->getFormula2() !== '') {
$objWriter->writeElement('formula2', $dv->getFormula2());
}
$objWriter->endElement();
}
$objWriter->endElement();
}
}
