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