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