/**
* Read LABELSST record
* This record represents a cell that contains a string. It
* replaces the LABEL record and RSTRING record used in
* BIFF2-BIFF5.
*
* -- "OpenOffice.org's Documentation of the Microsoft
* Excel File Format"
*/
private function _readLabelSst()
{
$length = $this->_GetInt2d($this->_data, $this->_pos + 2);
$recordData = substr($this->_data, $this->_pos + 4, $length);
// move stream pointer to next record
$this->_pos += 4 + $length;
// offset: 0; size: 2; index to row
$row = $this->_GetInt2d($recordData, 0);
// offset: 2; size: 2; index to column
$column = $this->_GetInt2d($recordData, 2);
$columnString = Cell::stringFromColumnIndex($column);
// Read cell?
if (!is_null($this->getReadFilter()) && $this->getReadFilter()->readCell($columnString, $row + 1, $this->_phpSheet->getTitle())) {
// offset: 4; size: 2; index to XF record
$xfIndex = $this->_GetInt2d($recordData, 4);
// offset: 6; size: 4; index to SST record
$index = $this->_GetInt4d($recordData, 6);
// add cell
if (($fmtRuns = $this->_sst[$index]['fmtRuns']) && !$this->_readDataOnly) {
// then we should treat as rich text
$richText = new RichText();
$charPos = 0;
for ($i = 0; $i <= count($this->_sst[$index]['fmtRuns']); ++$i) {
if (isset($fmtRuns[$i])) {
$text = Shared_String::Substring($this->_sst[$index]['value'], $charPos, $fmtRuns[$i]['charPos'] - $charPos);
$charPos = $fmtRuns[$i]['charPos'];
} else {
$text = Shared_String::Substring($this->_sst[$index]['value'], $charPos, Shared_String::CountCharacters($this->_sst[$index]['value']));
}
if (Shared_String::CountCharacters($text) > 0) {
if ($i == 0) {
// first text run, no style
$richText->createText($text);
} else {
$textRun = $richText->createTextRun($text);
if (isset($fmtRuns[$i - 1])) {
if ($fmtRuns[$i - 1]['fontIndex'] < 4) {
$fontIndex = $fmtRuns[$i - 1]['fontIndex'];
} else {
// this has to do with that index 4 is omitted in all BIFF versions for some strange reason
// check the OpenOffice documentation of the FONT record
$fontIndex = $fmtRuns[$i - 1]['fontIndex'] - 1;
}
$textRun->setFont(clone $this->_objFonts[$fontIndex]);
}
}
}
}
$cell = $this->_phpSheet->getCell($columnString . ($row + 1));
$cell->setValueExplicit($richText, Cell_DataType::TYPE_STRING);
} else {
$cell = $this->_phpSheet->getCell($columnString . ($row + 1));
$cell->setValueExplicit($this->_sst[$index]['value'], Cell_DataType::TYPE_STRING);
}
if (!$this->_readDataOnly) {
// add style information
$cell->setXfIndex($this->_mapCellXfIndex[$xfIndex]);
}
}
}
/**
* 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 = Shared_String::CountCharacters($url);
$url_len = pack('V', $url_len);
$url = 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;
}
/**
* 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 (Shared_String::CountCharacters($pValue) > 31) {
throw new Exception('Maximum 31 characters allowed in sheet title.');
}
return $pValue;
}
/**
* Get approximate width in pixels for a string of text in a certain font at a certain rotation angle
*
* @param string $columnText
* @param Style_Font $font
* @param int $rotation
* @return int Text width in pixels (no padding added)
*/
public static function getTextWidthPixelsApprox($columnText, 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 * 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 * 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 * Shared_String::CountCharacters($columnText));
$columnWidth = $columnWidth * $fontSize / 10;
// extrapolate from font size
break;
default:
// just assume Calibri
$columnWidth = (int) (8.26 * 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 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 = Shared_String::CountCharacters($name);
// name with stripped length field
$name = substr(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;
}
