/** * 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; }
/** * Used to write internal reference hyperlinks such as "Sheet1!A1". * * @access private * @see _writeUrl() * @param integer $row1 Start row * @param integer $col1 Start column * @param integer $row2 End row * @param integer $col2 End column * @param string $url URL string * @return integer */ function _writeUrlInternal($row1, $col1, $row2, $col2, $url) { $record = 0x1b8; // Record identifier $length = 0x0; // Bytes to follow // Strip URL type $url = preg_replace('/^internal:/', '', $url); // Pack the undocumented parts of the hyperlink stream $unknown1 = pack("H*", "D0C9EA79F9BACE118C8200AA004BA90B02000000"); // Pack the option flags $options = pack("V", 0x8); // Convert the URL type and to a null terminated wchar string $url .= ""; // character count $url_len = PHPExcel_Shared_String::CountCharacters($url); $url_len = pack('V', $url_len); $url = PHPExcel_Shared_String::ConvertEncoding($url, 'UTF-16LE', 'UTF-8'); // Calculate the data length $length = 0x24 + strlen($url); // Pack the header data $header = pack("vv", $record, $length); $data = pack("vvvv", $row1, $row2, $col1, $col2); // Write the packed data $this->_append($header . $data . $unknown1 . $options . $url_len . $url); return 0; }
/** * Write a DEFINEDNAME record for BIFF8 using explicit binary formula data * * @param string $name The name in UTF-8 * @param string $formulaData The binary formula data * @param string $sheetIndex 1-based sheet index the defined name applies to. 0 = global * @param boolean $isBuiltIn Built-in name? * @return string Complete binary record data */ private function writeDefinedNameBiff8($name, $formulaData, $sheetIndex = 0, $isBuiltIn = false) { $record = 0x18; // option flags $options = $isBuiltIn ? 0x20 : 0x0; // length of the name, character count $nlen = PHPExcel_Shared_String::CountCharacters($name); // name with stripped length field $name = substr(PHPExcel_Shared_String::UTF8toBIFF8UnicodeLong($name), 2); // size of the formula (in bytes) $sz = strlen($formulaData); // combine the parts $data = pack('vCCvvvCCCC', $options, 0, $nlen, $sz, 0, $sheetIndex, 0, 0, 0, 0) . $name . $formulaData; $length = strlen($data); $header = pack('vv', $record, $length); return $header . $data; }
} /** * 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 {
/** * SEARCHINSENSITIVE * * @param string $needle The string to look for * @param string $haystack The string in which to look * @param int $offset Offset within $haystack * @return string */ public static function SEARCHINSENSITIVE($needle, $haystack, $offset = 1) { $needle = PHPExcel_Calculation_Functions::flattenSingleValue($needle); $haystack = PHPExcel_Calculation_Functions::flattenSingleValue($haystack); $offset = PHPExcel_Calculation_Functions::flattenSingleValue($offset); if (!is_bool($needle)) { if (is_bool($haystack)) { $haystack = $haystack ? PHPExcel_Calculation::getTRUE() : PHPExcel_Calculation::getFALSE(); } if ($offset > 0 && PHPExcel_Shared_String::CountCharacters($haystack) > $offset) { if (PHPExcel_Shared_String::CountCharacters($needle) == 0) { return $offset; } if (function_exists('mb_stripos')) { $pos = mb_stripos($haystack, $needle, --$offset, 'UTF-8'); } else { $pos = stripos($haystack, $needle, --$offset); } if ($pos !== false) { return ++$pos; } } } return PHPExcel_Calculation_Functions::VALUE(); }
/** * 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; }
/** * Check sheet title for valid Excel syntax * * @param string $pValue The string to check * @return string The valid string * @throws Exception */ private static function _checkSheetTitle($pValue) { // Some of the printable ASCII characters are invalid: * : / \ ? [ ] if (str_replace(self::$_invalidCharacters, '', $pValue) !== $pValue) { throw new Exception('Invalid character found in sheet title'); } // Maximum 31 characters allowed for sheet title if (PHPExcel_Shared_String::CountCharacters($pValue) > 31) { throw new Exception('Maximum 31 characters allowed in sheet title.'); } return $pValue; }
/** * Set title * * @param string $pValue String containing the dimension of this worksheet * @throws Exception * @return PHPExcel_Worksheet */ public function setTitle($pValue = 'Worksheet') { // Is this a 'rename' or not? if ($this->getTitle() == $pValue) { return; } // Maximum 31 characters allowed for sheet title if (PHPExcel_Shared_String::CountCharacters($pValue) > 31) { throw new Exception('Maximum 31 characters allowed in sheet title.'); } // Old title $oldTitle = $this->getTitle(); // Is there already such sheet name? if ($this->getParent()->getSheetByName($pValue)) { // Use name, but append with lowest possible integer $i = 1; while ($this->getParent()->getSheetByName($pValue . ' ' . $i)) { ++$i; } $altTitle = $pValue . ' ' . $i; $this->setTitle($altTitle); return; } // Set title $this->_title = $pValue; // New title $newTitle = $this->getTitle(); PHPExcel_ReferenceHelper::getInstance()->updateNamedFormulas($this->getParent(), $oldTitle, $newTitle); return $this; }
/** * Set title * * @param string $pValue String containing the dimension of this worksheet * @throws Exception */ public function setTitle($pValue = 'Worksheet') { // Is this a 'rename' or not? if ($this->getTitle() == $pValue) { return; } // Maximum 31 characters allowed for sheet title if (PHPExcel_Shared_String::CountCharacters($pValue) > 31) { throw new Exception('Maximum 31 characters allowed in sheet title.'); } // Old title $oldTitle = $this->getTitle(); // Loop trough all sheets in parent PHPExcel and verify unique names $titleCount = 0; $aNames = $this->getParent()->getSheetNames(); foreach ($aNames as $strName) { if ($strName == $pValue || substr($strName, 0, strrpos($strName, ' ')) == $pValue) { ++$titleCount; } } // Eventually, add a number to the sheet name if ($titleCount > 0) { $this->setTitle($pValue . ' ' . $titleCount); return; } // Set title $this->_title = $pValue; // New title $newTitle = $this->getTitle(); PHPExcel_ReferenceHelper::getInstance()->updateNamedFormulas($this->getParent(), $oldTitle, $newTitle); }
/** * Get approximate width in pixels for a string of text in a certain font at a certain rotation angle * * @param string $columnText * @param PHPExcel_Style_Font $font * @param int $rotation * @return int Text width in pixels (no padding added) */ public static function getTextWidthPixelsApprox($columnText, PHPExcel_Style_Font $font = null, $rotation = 0) { $fontName = $font->getName(); $fontSize = $font->getSize(); // Calculate column width in pixels. We assume fixed glyph width. Result varies with font name and size. switch ($fontName) { case 'Calibri': // value 8.26 was found via interpolation by inspecting real Excel files with Calibri 11 font. $columnWidth = (int) (8.26 * PHPExcel_Shared_String::CountCharacters($columnText)); $columnWidth = $columnWidth * $fontSize / 11; // extrapolate from font size break; case 'Arial': // value 7 was found via interpolation by inspecting real Excel files with Arial 10 font. $columnWidth = (int) (7 * PHPExcel_Shared_String::CountCharacters($columnText)); $columnWidth = $columnWidth * $fontSize / 10; // extrapolate from font size break; case 'Verdana': // value 8 was found via interpolation by inspecting real Excel files with Verdana 10 font. $columnWidth = (int) (8 * PHPExcel_Shared_String::CountCharacters($columnText)); $columnWidth = $columnWidth * $fontSize / 10; // extrapolate from font size break; default: // just assume Calibri $columnWidth = (int) (8.26 * PHPExcel_Shared_String::CountCharacters($columnText)); $columnWidth = $columnWidth * $fontSize / 11; // extrapolate from font size break; } // Calculate approximate rotated column width if ($rotation !== 0) { if ($rotation == -165) { // stacked text $columnWidth = 4; // approximation } else { // rotated text $columnWidth = $columnWidth * cos(deg2rad($rotation)) + $fontSize * abs(sin(deg2rad($rotation))) / 5; // approximation } } // pixel width is an integer $columnWidth = (int) $columnWidth; return $columnWidth; }
/** * Write a string to the specified row and column (zero indexed). * This is the BIFF8 version (no 255 chars limit). * $format is optional. * Returns 0 : normal termination * -2 : row or column out of range * -3 : long string truncated to 255 chars * * @access public * @param integer $row Zero indexed row * @param integer $col Zero indexed column * @param string $str The string to write * @param mixed $format The XF format for the cell * @return integer */ private function _writeLabelSst($row, $col, $str, $xfIndex) { $strlen = PHPExcel_Shared_String::CountCharacters($str, 'UTF-8'); $str = PHPExcel_Shared_String::ConvertEncoding($str, 'UTF-16LE', 'UTF-8'); $encoding = 0x1; $record = 0xfd; // Record identifier $length = 0xa; // Bytes to follow $str = pack('vC', $strlen, $encoding) . $str; /* check if string is already present */ if (!isset($this->_str_table[$str])) { $this->_str_table[$str] = $this->_str_unique++; } $this->_str_total++; $header = pack('vv', $record, $length); $data = pack('vvvV', $row, $col, $xfIndex, $this->_str_table[$str]); $this->_append($header . $data); }
/** * Check sheet title for valid Excel syntax * * @param string $pValue The string to check * @return string The valid string * @throws Exception */ private static function _checkSheetTitle($pValue) { // Some of the printable ASCII characters are invalid: * : / \ ? [ ] if (preg_match('/(\\*|\\:|\\/|\\\\|\\?|\\[|\\])/', $pValue)) { throw new Exception('Invalid character found in sheet title'); } // Maximum 31 characters allowed for sheet title if (PHPExcel_Shared_String::CountCharacters($pValue) > 31) { throw new Exception('Maximum 31 characters allowed in sheet title.'); } 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); 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; }