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