/** * Check a string that it satisfies Excel requirements * * @param mixed Value to sanitize to an Excel string * @return mixed Sanitized value */ public static function checkString($pValue = null) { if ($pValue instanceof PHPExcel_RichText) { return $pValue; } // string must never be longer than 32,767 characters, truncate if necessary $pValue = PHPExcel_Shared_String::Substring($pValue, 0, 32767); // we require that newline is represented as "\n" in core, not as "\r\n" or "\r" $pValue = str_replace(array("\r\n", "\r"), "\n", $pValue); return $pValue; }
/** * Define the code name of the sheet * * @param null|string Same rule as Title minus space not allowed (but, like Excel, change silently space to underscore) * @return objWorksheet * @throws PHPExcel_Exception */ public function setCodeName($pValue = null) { // Is this a 'rename' or not? if ($this->getCodeName() == $pValue) { return $this; } $pValue = str_replace(' ', '_', $pValue); //Excel does this automatically without flinching, we are doing the same // Syntax check // throw an exception if not valid self::_checkSheetCodeName($pValue); // We use the same code that setTitle to find a valid codeName else not using a space (Excel don't like) but a '_' if ($this->getParent()) { // Is there already such sheet name? if ($this->getParent()->sheetCodeNameExists($pValue)) { // Use name, but append with lowest possible integer if (PHPExcel_Shared_String::CountCharacters($pValue) > 29) { $pValue = PHPExcel_Shared_String::Substring($pValue, 0, 29); } $i = 1; while ($this->getParent()->sheetCodeNameExists($pValue . '_' . $i)) { ++$i; if ($i == 10) { if (PHPExcel_Shared_String::CountCharacters($pValue) > 28) { $pValue = PHPExcel_Shared_String::Substring($pValue, 0, 28); } } elseif ($i == 100) { if (PHPExcel_Shared_String::CountCharacters($pValue) > 27) { $pValue = PHPExcel_Shared_String::Substring($pValue, 0, 27); } } } $pValue = $pValue . '_' . $i; // ok, we have a valid name //codeName is'nt used in formula : no need to call for an update //return $this->setTitle($altTitle,$updateFormulaCellReferences); } } $this->_codeName = $pValue; return $this; }
} /** * Read VERTICALPAGEBREAKS record */ private function _readVerticalPageBreaks() { $length = self::_GetInt2d($this->_data, $this->_pos + 2); $recordData = substr($this->_data, $this->_pos + 4, $length); // move stream pointer to next record $this->_pos += 4 + $length; if ($this->_version == self::XLS_BIFF8 && !$this->_readDataOnly) { // offset: 0; size: 2; number of the following column index structures $nm = self::_GetInt2d($recordData, 0); // offset: 2; size: 6 * $nm; list of $nm row index structures for ($i = 0; $i < $nm; ++$i) { $c = self::_GetInt2d($recordData, 2 + 6 * $i); $rf = self::_GetInt2d($recordData, 2 + 6 * $i + 2); $rl = self::_GetInt2d($recordData, 2 + 6 * $i + 4); // not sure why two row indexes are necessary? $this->_phpSheet->setBreakByColumnAndRow($c, $rf, PHPExcel_Worksheet::BREAK_COLUMN); } } } /** * Read HEADER record */ private function _readHeader() { $length = self::_GetInt2d($this->_data, $this->_pos + 2); $recordData = substr($this->_data, $this->_pos + 4, $length); // move stream pointer to next record $this->_pos += 4 + $length; if (!$this->_readDataOnly) { // offset: 0; size: var // realized that $recordData can be empty even when record exists if ($recordData) { if ($this->_version == self::XLS_BIFF8) { $string = self::_readUnicodeStringLong($recordData); } else { $string = $this->_readByteStringShort($recordData); } $this->_phpSheet->getHeaderFooter()->setOddHeader($string['value']); $this->_phpSheet->getHeaderFooter()->setEvenHeader($string['value']); } } } /** * Read FOOTER record */ private function _readFooter() { $length = self::_GetInt2d($this->_data, $this->_pos + 2); $recordData = substr($this->_data, $this->_pos + 4, $length); // move stream pointer to next record $this->_pos += 4 + $length; if (!$this->_readDataOnly) { // offset: 0; size: var // realized that $recordData can be empty even when record exists if ($recordData) { if ($this->_version == self::XLS_BIFF8) { $string = self::_readUnicodeStringLong($recordData); } else {
/** * Set title * * @param string $pValue String containing the dimension of this worksheet * @param string $updateFormulaCellReferences boolean Flag indicating whether cell references in formulae should * be updated to reflect the new sheet name. * This should be left as the default true, unless you are * certain that no formula cells on any worksheet contain * references to this worksheet * * @return PHPExcel_Worksheet */ public function setTitle($pValue = 'Worksheet', $updateFormulaCellReferences = true) { // Is this a 'rename' or not? if ($this->getTitle() == $pValue) { return $this; } // Syntax check self::_checkSheetTitle($pValue); // Old title $oldTitle = $this->getTitle(); if ($this->_parent) { // Is there already such sheet name? if ($this->_parent->sheetNameExists($pValue)) { // Use name, but append with lowest possible integer if (PHPExcel_Shared_String::CountCharacters($pValue) > 29) { $pValue = PHPExcel_Shared_String::Substring($pValue, 0, 29); } $i = 1; while ($this->_parent->sheetNameExists($pValue . ' ' . $i)) { ++$i; if ($i == 10) { if (PHPExcel_Shared_String::CountCharacters($pValue) > 28) { $pValue = PHPExcel_Shared_String::Substring($pValue, 0, 28); } } elseif ($i == 100) { if (PHPExcel_Shared_String::CountCharacters($pValue) > 27) { $pValue = PHPExcel_Shared_String::Substring($pValue, 0, 27); } } } $altTitle = $pValue . ' ' . $i; return $this->setTitle($altTitle, $updateFormulaCellReferences); } } // Set title $this->_title = $pValue; $this->_dirty = true; if ($this->_parent) { // New title $newTitle = $this->getTitle(); PHPExcel_Calculation::getInstance($this->_parent)->renameCalculationCacheForWorksheet($oldTitle, $newTitle); if ($updateFormulaCellReferences) { PHPExcel_ReferenceHelper::getInstance()->updateNamedFormulas($this->_parent, $oldTitle, $newTitle); } } return $this; }
/** * Set cell value (with explicit data type given) * * @param mixed $pValue Value * @param string $pDataType Explicit data type * @return PHPExcel_Cell */ public function setValueExplicit($pValue = null, $pDataType = PHPExcel_Cell_DataType::TYPE_STRING) { // check strings that they are ok // TODO: fix also for RichText if ($pDataType == PHPExcel_Cell_DataType::TYPE_STRING && !$pValue instanceof PHPExcel_RichText) { // string must never be longer than 32,767 characters, truncate if necessary $pValue = PHPExcel_Shared_String::Substring($pValue, 0, 32767); // we require that newline is represented as "\n" in core, not as "\r\n" or "\r" $pValue = str_replace(array("\r\n", "\r"), "\n", $pValue); } $this->_value = $pValue; $this->_dataType = $pDataType; return $this; }
/** * Define the code name of the sheet * * @param null|string Same rule as Title minus space not allowed (but, like Excel, change silently space to underscore) * @return objWorksheet * @throws PHPExcel_Exception */ public function setCodeName($pValue = null) { // Is this a 'rename' or not? if ($this->getCodeName() == $pValue) { return $this; } $pValue = str_replace(' ', '_', $pValue); // Excel does this automatically without flinching, we are doing the same Syntax check, throw an exception if not valid self::_checkSheetCodeName($pValue); if ($this->getParent()) { // Is there already such sheet name? if ($this->getParent()->sheetCodeNameExists($pValue)) { // Use name, but append with lowest possible integer if (PHPExcel_Shared_String::CountCharacters($pValue) > 29) { $pValue = PHPExcel_Shared_String::Substring($pValue, 0, 29); } $i = 1; while ($this->getParent()->sheetCodeNameExists($pValue . '_' . $i)) { ++$i; if ($i == 10) { if (PHPExcel_Shared_String::CountCharacters($pValue) > 28) { $pValue = PHPExcel_Shared_String::Substring($pValue, 0, 28); } } elseif ($i == 100) { if (PHPExcel_Shared_String::CountCharacters($pValue) > 27) { $pValue = PHPExcel_Shared_String::Substring($pValue, 0, 27); } } } $pValue = $pValue . '_' . $i; } } $this->_codeName = $pValue; return $this; }