/**
* TEXTFORMAT
*
* @param mixed $value Value to check
* @return boolean
*/
public static function TEXTFORMAT($value, $format)
{
$value = self::flattenSingleValue($value);
$format = self::flattenSingleValue($format);
if (is_string($value) && !is_numeric($value) && PHPExcel_Shared_Date::isDateTimeFormatCode($format)) {
$value = self::DATEVALUE($value);
}
return (string) PHPExcel_Style_NumberFormat::toFormattedString($value, $format);
}
/**
* Loads PHPExcel from file into PHPExcel instance
*
* @param string $pFilename
* @param PHPExcel $objPHPExcel
* @return PHPExcel
* @throws PHPExcel_Reader_Exception
*/
public function loadIntoExisting($pFilename, PHPExcel $objPHPExcel)
{
// Check if file exists
if (!file_exists($pFilename)) {
throw new PHPExcel_Reader_Exception("Could not open " . $pFilename . " for reading! File does not exist.");
}
$timezoneObj = new DateTimeZone('Europe/London');
$GMT = new DateTimeZone('UTC');
$gFileData = $this->_gzfileGetContents($pFilename);
// echo '<pre>';
// echo htmlentities($gFileData,ENT_QUOTES,'UTF-8');
// echo '</pre><hr />';
//
$xml = simplexml_load_string($gFileData);
$namespacesMeta = $xml->getNamespaces(true);
// var_dump($namespacesMeta);
//
$gnmXML = $xml->children($namespacesMeta['gnm']);
$docProps = $objPHPExcel->getProperties();
// Document Properties are held differently, depending on the version of Gnumeric
if (isset($namespacesMeta['office'])) {
$officeXML = $xml->children($namespacesMeta['office']);
$officeDocXML = $officeXML->{'document-meta'};
$officeDocMetaXML = $officeDocXML->meta;
foreach ($officeDocMetaXML as $officePropertyData) {
$officePropertyDC = array();
if (isset($namespacesMeta['dc'])) {
$officePropertyDC = $officePropertyData->children($namespacesMeta['dc']);
}
foreach ($officePropertyDC as $propertyName => $propertyValue) {
$propertyValue = (string) $propertyValue;
switch ($propertyName) {
case 'title':
$docProps->setTitle(trim($propertyValue));
break;
case 'subject':
$docProps->setSubject(trim($propertyValue));
break;
case 'creator':
$docProps->setCreator(trim($propertyValue));
$docProps->setLastModifiedBy(trim($propertyValue));
break;
case 'date':
$creationDate = strtotime(trim($propertyValue));
$docProps->setCreated($creationDate);
$docProps->setModified($creationDate);
break;
case 'description':
$docProps->setDescription(trim($propertyValue));
break;
}
}
$officePropertyMeta = array();
if (isset($namespacesMeta['meta'])) {
$officePropertyMeta = $officePropertyData->children($namespacesMeta['meta']);
}
foreach ($officePropertyMeta as $propertyName => $propertyValue) {
$attributes = $propertyValue->attributes($namespacesMeta['meta']);
$propertyValue = (string) $propertyValue;
switch ($propertyName) {
case 'keyword':
$docProps->setKeywords(trim($propertyValue));
break;
case 'initial-creator':
$docProps->setCreator(trim($propertyValue));
$docProps->setLastModifiedBy(trim($propertyValue));
break;
case 'creation-date':
$creationDate = strtotime(trim($propertyValue));
$docProps->setCreated($creationDate);
$docProps->setModified($creationDate);
break;
case 'user-defined':
list(, $attrName) = explode(':', $attributes['name']);
switch ($attrName) {
case 'publisher':
$docProps->setCompany(trim($propertyValue));
break;
case 'category':
$docProps->setCategory(trim($propertyValue));
break;
case 'manager':
$docProps->setManager(trim($propertyValue));
break;
}
break;
}
}
}
} elseif (isset($gnmXML->Summary)) {
foreach ($gnmXML->Summary->Item as $summaryItem) {
$propertyName = $summaryItem->name;
$propertyValue = $summaryItem->{'val-string'};
switch ($propertyName) {
case 'title':
$docProps->setTitle(trim($propertyValue));
break;
case 'comments':
$docProps->setDescription(trim($propertyValue));
break;
case 'keywords':
$docProps->setKeywords(trim($propertyValue));
break;
case 'category':
$docProps->setCategory(trim($propertyValue));
break;
case 'manager':
$docProps->setManager(trim($propertyValue));
break;
case 'author':
$docProps->setCreator(trim($propertyValue));
$docProps->setLastModifiedBy(trim($propertyValue));
break;
case 'company':
$docProps->setCompany(trim($propertyValue));
break;
}
}
}
$worksheetID = 0;
foreach ($gnmXML->Sheets->Sheet as $sheet) {
$worksheetName = (string) $sheet->Name;
// echo '<b>Worksheet: ',$worksheetName,'</b><br />';
if (isset($this->_loadSheetsOnly) && !in_array($worksheetName, $this->_loadSheetsOnly)) {
continue;
}
$maxRow = $maxCol = 0;
// Create new Worksheet
$objPHPExcel->createSheet();
$objPHPExcel->setActiveSheetIndex($worksheetID);
// Use false for $updateFormulaCellReferences to prevent adjustment of worksheet references in formula
// cells... during the load, all formulae should be correct, and we're simply bringing the worksheet
// name in line with the formula, not the reverse
$objPHPExcel->getActiveSheet()->setTitle($worksheetName, false);
if (!$this->_readDataOnly && isset($sheet->PrintInformation)) {
if (isset($sheet->PrintInformation->Margins)) {
foreach ($sheet->PrintInformation->Margins->children('gnm', TRUE) as $key => $margin) {
$marginAttributes = $margin->attributes();
$marginSize = 72 / 100;
// Default
switch ($marginAttributes['PrefUnit']) {
case 'mm':
$marginSize = intval($marginAttributes['Points']) / 100;
break;
}
switch ($key) {
case 'top':
$objPHPExcel->getActiveSheet()->getPageMargins()->setTop($marginSize);
break;
case 'bottom':
$objPHPExcel->getActiveSheet()->getPageMargins()->setBottom($marginSize);
break;
case 'left':
$objPHPExcel->getActiveSheet()->getPageMargins()->setLeft($marginSize);
break;
case 'right':
$objPHPExcel->getActiveSheet()->getPageMargins()->setRight($marginSize);
break;
case 'header':
$objPHPExcel->getActiveSheet()->getPageMargins()->setHeader($marginSize);
break;
case 'footer':
$objPHPExcel->getActiveSheet()->getPageMargins()->setFooter($marginSize);
break;
}
}
}
}
foreach ($sheet->Cells->Cell as $cell) {
$cellAttributes = $cell->attributes();
$row = (int) $cellAttributes->Row + 1;
$column = (int) $cellAttributes->Col;
if ($row > $maxRow) {
$maxRow = $row;
}
if ($column > $maxCol) {
$maxCol = $column;
}
$column = PHPExcel_Cell::stringFromColumnIndex($column);
// Read cell?
if ($this->getReadFilter() !== NULL) {
if (!$this->getReadFilter()->readCell($column, $row, $worksheetName)) {
continue;
}
}
$ValueType = $cellAttributes->ValueType;
$ExprID = (string) $cellAttributes->ExprID;
// echo 'Cell ',$column,$row,'<br />';
// echo 'Type is ',$ValueType,'<br />';
// echo 'Value is ',$cell,'<br />';
$type = PHPExcel_Cell_DataType::TYPE_FORMULA;
if ($ExprID > '') {
if ((string) $cell > '') {
$this->_expressions[$ExprID] = array('column' => $cellAttributes->Col, 'row' => $cellAttributes->Row, 'formula' => (string) $cell);
// echo 'NEW EXPRESSION ',$ExprID,'<br />';
} else {
$expression = $this->_expressions[$ExprID];
$cell = $this->_referenceHelper->updateFormulaReferences($expression['formula'], 'A1', $cellAttributes->Col - $expression['column'], $cellAttributes->Row - $expression['row'], $worksheetName);
// echo 'SHARED EXPRESSION ',$ExprID,'<br />';
// echo 'New Value is ',$cell,'<br />';
}
$type = PHPExcel_Cell_DataType::TYPE_FORMULA;
} else {
switch ($ValueType) {
case '10':
// NULL
$type = PHPExcel_Cell_DataType::TYPE_NULL;
break;
case '20':
// Boolean
$type = PHPExcel_Cell_DataType::TYPE_BOOL;
$cell = $cell == 'TRUE' ? True : False;
break;
case '30':
// Integer
$cell = intval($cell);
case '40':
// Float
$type = PHPExcel_Cell_DataType::TYPE_NUMERIC;
break;
case '50':
// Error
$type = PHPExcel_Cell_DataType::TYPE_ERROR;
break;
case '60':
// String
$type = PHPExcel_Cell_DataType::TYPE_STRING;
break;
case '70':
// Cell Range
// Cell Range
case '80':
// Array
}
}
$objPHPExcel->getActiveSheet()->getCell($column . $row)->setValueExplicit($cell, $type);
}
if (!$this->_readDataOnly && isset($sheet->Objects)) {
foreach ($sheet->Objects->children('gnm', TRUE) as $key => $comment) {
$commentAttributes = $comment->attributes();
// Only comment objects are handled at the moment
if ($commentAttributes->Text) {
$objPHPExcel->getActiveSheet()->getComment((string) $commentAttributes->ObjectBound)->setAuthor((string) $commentAttributes->Author)->setText($this->_parseRichText((string) $commentAttributes->Text));
}
}
}
// echo '$maxCol=',$maxCol,'; $maxRow=',$maxRow,'<br />';
//
foreach ($sheet->Styles->StyleRegion as $styleRegion) {
$styleAttributes = $styleRegion->attributes();
if ($styleAttributes['startRow'] <= $maxRow && $styleAttributes['startCol'] <= $maxCol) {
$startColumn = PHPExcel_Cell::stringFromColumnIndex((int) $styleAttributes['startCol']);
$startRow = $styleAttributes['startRow'] + 1;
$endColumn = $styleAttributes['endCol'] > $maxCol ? $maxCol : (int) $styleAttributes['endCol'];
$endColumn = PHPExcel_Cell::stringFromColumnIndex($endColumn);
$endRow = $styleAttributes['endRow'] > $maxRow ? $maxRow : $styleAttributes['endRow'];
$endRow += 1;
$cellRange = $startColumn . $startRow . ':' . $endColumn . $endRow;
// echo $cellRange,'<br />';
$styleAttributes = $styleRegion->Style->attributes();
// var_dump($styleAttributes);
// echo '<br />';
// We still set the number format mask for date/time values, even if _readDataOnly is true
if (!$this->_readDataOnly || PHPExcel_Shared_Date::isDateTimeFormatCode((string) $styleAttributes['Format'])) {
$styleArray = array();
$styleArray['numberformat']['code'] = (string) $styleAttributes['Format'];
// If _readDataOnly is false, we set all formatting information
if (!$this->_readDataOnly) {
switch ($styleAttributes['HAlign']) {
case '1':
$styleArray['alignment']['horizontal'] = PHPExcel_Style_Alignment::HORIZONTAL_GENERAL;
break;
case '2':
$styleArray['alignment']['horizontal'] = PHPExcel_Style_Alignment::HORIZONTAL_LEFT;
break;
case '4':
$styleArray['alignment']['horizontal'] = PHPExcel_Style_Alignment::HORIZONTAL_RIGHT;
break;
case '8':
$styleArray['alignment']['horizontal'] = PHPExcel_Style_Alignment::HORIZONTAL_CENTER;
break;
case '16':
case '64':
$styleArray['alignment']['horizontal'] = PHPExcel_Style_Alignment::HORIZONTAL_CENTER_CONTINUOUS;
break;
case '32':
$styleArray['alignment']['horizontal'] = PHPExcel_Style_Alignment::HORIZONTAL_JUSTIFY;
break;
}
switch ($styleAttributes['VAlign']) {
case '1':
$styleArray['alignment']['vertical'] = PHPExcel_Style_Alignment::VERTICAL_TOP;
break;
case '2':
$styleArray['alignment']['vertical'] = PHPExcel_Style_Alignment::VERTICAL_BOTTOM;
break;
case '4':
$styleArray['alignment']['vertical'] = PHPExcel_Style_Alignment::VERTICAL_CENTER;
break;
case '8':
$styleArray['alignment']['vertical'] = PHPExcel_Style_Alignment::VERTICAL_JUSTIFY;
break;
}
$styleArray['alignment']['wrap'] = $styleAttributes['WrapText'] == '1' ? True : False;
$styleArray['alignment']['shrinkToFit'] = $styleAttributes['ShrinkToFit'] == '1' ? True : False;
$styleArray['alignment']['indent'] = intval($styleAttributes["Indent"]) > 0 ? $styleAttributes["indent"] : 0;
$RGB = self::_parseGnumericColour($styleAttributes["Fore"]);
$styleArray['font']['color']['rgb'] = $RGB;
$RGB = self::_parseGnumericColour($styleAttributes["Back"]);
$shade = $styleAttributes["Shade"];
if ($RGB != '000000' || $shade != '0') {
$styleArray['fill']['color']['rgb'] = $styleArray['fill']['startcolor']['rgb'] = $RGB;
$RGB2 = self::_parseGnumericColour($styleAttributes["PatternColor"]);
$styleArray['fill']['endcolor']['rgb'] = $RGB2;
switch ($shade) {
case '1':
$styleArray['fill']['type'] = PHPExcel_Style_Fill::FILL_SOLID;
break;
case '2':
$styleArray['fill']['type'] = PHPExcel_Style_Fill::FILL_GRADIENT_LINEAR;
break;
case '3':
$styleArray['fill']['type'] = PHPExcel_Style_Fill::FILL_GRADIENT_PATH;
break;
case '4':
$styleArray['fill']['type'] = PHPExcel_Style_Fill::FILL_PATTERN_DARKDOWN;
break;
case '5':
$styleArray['fill']['type'] = PHPExcel_Style_Fill::FILL_PATTERN_DARKGRAY;
break;
case '6':
$styleArray['fill']['type'] = PHPExcel_Style_Fill::FILL_PATTERN_DARKGRID;
break;
case '7':
$styleArray['fill']['type'] = PHPExcel_Style_Fill::FILL_PATTERN_DARKHORIZONTAL;
break;
case '8':
$styleArray['fill']['type'] = PHPExcel_Style_Fill::FILL_PATTERN_DARKTRELLIS;
break;
case '9':
$styleArray['fill']['type'] = PHPExcel_Style_Fill::FILL_PATTERN_DARKUP;
break;
case '10':
$styleArray['fill']['type'] = PHPExcel_Style_Fill::FILL_PATTERN_DARKVERTICAL;
break;
case '11':
$styleArray['fill']['type'] = PHPExcel_Style_Fill::FILL_PATTERN_GRAY0625;
break;
case '12':
$styleArray['fill']['type'] = PHPExcel_Style_Fill::FILL_PATTERN_GRAY125;
break;
case '13':
$styleArray['fill']['type'] = PHPExcel_Style_Fill::FILL_PATTERN_LIGHTDOWN;
break;
case '14':
$styleArray['fill']['type'] = PHPExcel_Style_Fill::FILL_PATTERN_LIGHTGRAY;
break;
case '15':
$styleArray['fill']['type'] = PHPExcel_Style_Fill::FILL_PATTERN_LIGHTGRID;
break;
case '16':
$styleArray['fill']['type'] = PHPExcel_Style_Fill::FILL_PATTERN_LIGHTHORIZONTAL;
break;
case '17':
$styleArray['fill']['type'] = PHPExcel_Style_Fill::FILL_PATTERN_LIGHTTRELLIS;
break;
case '18':
$styleArray['fill']['type'] = PHPExcel_Style_Fill::FILL_PATTERN_LIGHTUP;
break;
case '19':
$styleArray['fill']['type'] = PHPExcel_Style_Fill::FILL_PATTERN_LIGHTVERTICAL;
break;
case '20':
$styleArray['fill']['type'] = PHPExcel_Style_Fill::FILL_PATTERN_MEDIUMGRAY;
break;
}
}
$fontAttributes = $styleRegion->Style->Font->attributes();
// var_dump($fontAttributes);
// echo '<br />';
$styleArray['font']['name'] = (string) $styleRegion->Style->Font;
$styleArray['font']['size'] = intval($fontAttributes['Unit']);
$styleArray['font']['bold'] = $fontAttributes['Bold'] == '1' ? True : False;
$styleArray['font']['italic'] = $fontAttributes['Italic'] == '1' ? True : False;
$styleArray['font']['strike'] = $fontAttributes['StrikeThrough'] == '1' ? True : False;
switch ($fontAttributes['Underline']) {
case '1':
$styleArray['font']['underline'] = PHPExcel_Style_Font::UNDERLINE_SINGLE;
break;
case '2':
$styleArray['font']['underline'] = PHPExcel_Style_Font::UNDERLINE_DOUBLE;
break;
case '3':
$styleArray['font']['underline'] = PHPExcel_Style_Font::UNDERLINE_SINGLEACCOUNTING;
break;
case '4':
$styleArray['font']['underline'] = PHPExcel_Style_Font::UNDERLINE_DOUBLEACCOUNTING;
break;
default:
$styleArray['font']['underline'] = PHPExcel_Style_Font::UNDERLINE_NONE;
break;
}
switch ($fontAttributes['Script']) {
case '1':
$styleArray['font']['superScript'] = True;
break;
case '-1':
$styleArray['font']['subScript'] = True;
break;
}
if (isset($styleRegion->Style->StyleBorder)) {
if (isset($styleRegion->Style->StyleBorder->Top)) {
$styleArray['borders']['top'] = self::_parseBorderAttributes($styleRegion->Style->StyleBorder->Top->attributes());
}
if (isset($styleRegion->Style->StyleBorder->Bottom)) {
$styleArray['borders']['bottom'] = self::_parseBorderAttributes($styleRegion->Style->StyleBorder->Bottom->attributes());
}
if (isset($styleRegion->Style->StyleBorder->Left)) {
$styleArray['borders']['left'] = self::_parseBorderAttributes($styleRegion->Style->StyleBorder->Left->attributes());
}
if (isset($styleRegion->Style->StyleBorder->Right)) {
$styleArray['borders']['right'] = self::_parseBorderAttributes($styleRegion->Style->StyleBorder->Right->attributes());
}
if (isset($styleRegion->Style->StyleBorder->Diagonal) && isset($styleRegion->Style->StyleBorder->{'Rev-Diagonal'})) {
$styleArray['borders']['diagonal'] = self::_parseBorderAttributes($styleRegion->Style->StyleBorder->Diagonal->attributes());
$styleArray['borders']['diagonaldirection'] = PHPExcel_Style_Borders::DIAGONAL_BOTH;
} elseif (isset($styleRegion->Style->StyleBorder->Diagonal)) {
$styleArray['borders']['diagonal'] = self::_parseBorderAttributes($styleRegion->Style->StyleBorder->Diagonal->attributes());
$styleArray['borders']['diagonaldirection'] = PHPExcel_Style_Borders::DIAGONAL_UP;
} elseif (isset($styleRegion->Style->StyleBorder->{'Rev-Diagonal'})) {
$styleArray['borders']['diagonal'] = self::_parseBorderAttributes($styleRegion->Style->StyleBorder->{'Rev-Diagonal'}->attributes());
$styleArray['borders']['diagonaldirection'] = PHPExcel_Style_Borders::DIAGONAL_DOWN;
}
}
if (isset($styleRegion->Style->HyperLink)) {
// TO DO
$hyperlink = $styleRegion->Style->HyperLink->attributes();
}
}
// var_dump($styleArray);
// echo '<br />';
$objPHPExcel->getActiveSheet()->getStyle($cellRange)->applyFromArray($styleArray);
}
}
}
if (!$this->_readDataOnly && isset($sheet->Cols)) {
// Column Widths
$columnAttributes = $sheet->Cols->attributes();
$defaultWidth = $columnAttributes['DefaultSizePts'] / 5.4;
$c = 0;
foreach ($sheet->Cols->ColInfo as $columnOverride) {
$columnAttributes = $columnOverride->attributes();
$column = $columnAttributes['No'];
$columnWidth = $columnAttributes['Unit'] / 5.4;
$hidden = isset($columnAttributes['Hidden']) && $columnAttributes['Hidden'] == '1' ? true : false;
$columnCount = isset($columnAttributes['Count']) ? $columnAttributes['Count'] : 1;
while ($c < $column) {
$objPHPExcel->getActiveSheet()->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($c))->setWidth($defaultWidth);
++$c;
}
while ($c < $column + $columnCount && $c <= $maxCol) {
$objPHPExcel->getActiveSheet()->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($c))->setWidth($columnWidth);
if ($hidden) {
$objPHPExcel->getActiveSheet()->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($c))->setVisible(false);
}
++$c;
}
}
while ($c <= $maxCol) {
$objPHPExcel->getActiveSheet()->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($c))->setWidth($defaultWidth);
++$c;
}
}
if (!$this->_readDataOnly && isset($sheet->Rows)) {
// Row Heights
$rowAttributes = $sheet->Rows->attributes();
$defaultHeight = $rowAttributes['DefaultSizePts'];
$r = 0;
foreach ($sheet->Rows->RowInfo as $rowOverride) {
$rowAttributes = $rowOverride->attributes();
$row = $rowAttributes['No'];
$rowHeight = $rowAttributes['Unit'];
$hidden = isset($rowAttributes['Hidden']) && $rowAttributes['Hidden'] == '1' ? true : false;
$rowCount = isset($rowAttributes['Count']) ? $rowAttributes['Count'] : 1;
while ($r < $row) {
++$r;
$objPHPExcel->getActiveSheet()->getRowDimension($r)->setRowHeight($defaultHeight);
}
while ($r < $row + $rowCount && $r < $maxRow) {
++$r;
$objPHPExcel->getActiveSheet()->getRowDimension($r)->setRowHeight($rowHeight);
if ($hidden) {
$objPHPExcel->getActiveSheet()->getRowDimension($r)->setVisible(false);
}
}
}
while ($r < $maxRow) {
++$r;
$objPHPExcel->getActiveSheet()->getRowDimension($r)->setRowHeight($defaultHeight);
}
}
// Handle Merged Cells in this worksheet
if (isset($sheet->MergedRegions)) {
foreach ($sheet->MergedRegions->Merge as $mergeCells) {
if (strpos($mergeCells, ':') !== FALSE) {
$objPHPExcel->getActiveSheet()->mergeCells($mergeCells);
}
}
}
$worksheetID++;
}
// Loop through definedNames (global named ranges)
if (isset($gnmXML->Names)) {
foreach ($gnmXML->Names->Name as $namedRange) {
$name = (string) $namedRange->name;
$range = (string) $namedRange->value;
if (stripos($range, '#REF!') !== false) {
continue;
}
$range = explode('!', $range);
$range[0] = trim($range[0], "'");
if ($worksheet = $objPHPExcel->getSheetByName($range[0])) {
$extractedRange = str_replace('$', '', $range[1]);
$objPHPExcel->addNamedRange(new PHPExcel_NamedRange($name, $worksheet, $extractedRange));
}
}
}
// Return
return $objPHPExcel;
}
/**
* Map to the appropriate write method acording to the token recieved.
*
* @access public
* @param integer $row The row of the cell we are writing to
* @param integer $col The column of the cell we are writing to
* @param mixed $token What we are writing
* @param mixed $format The optional format to apply to the cell
*/
function write($row, $col, $token, $format = null, $numberFormat = null)
{
if ($numberFormat != 'General' && PHPExcel_Shared_Date::isDateTimeFormatCode($numberFormat)) {
if (is_string($token)) {
// Error string
return $this->writeString($row, $col, $token, $format);
} elseif (!is_float($token)) {
// PHP serialized date/time or date/time object
return $this->writeNumber($row, $col, PHPExcel_Shared_Date::PHPToExcel($token), $format);
} else {
// Excel serialized date/time
return $this->writeNumber($row, $col, $token, $format);
}
} elseif (preg_match("/^([+-]?)(?=\\d|\\.\\d)\\d*(\\.\\d*)?([Ee]([+-]?\\d+))?\$/", $token)) {
// Match number
return $this->writeNumber($row, $col, $token, $format);
} elseif ($token == '') {
// Match blank
return $this->writeBlank($row, $col, $format);
} else {
// Default: match string
return $this->writeString($row, $col, $token, $format);
}
}
/**
* Loads PHPExcel from file
*
* @param string $pFilename
* @throws Exception
*/
public function load($pFilename)
{
// Check if file exists
if (!file_exists($pFilename)) {
throw new Exception("Could not open " . $pFilename . " for reading! File does not exist.");
}
// Initialisations
$excel = new PHPExcel();
$excel->removeSheetByIndex(0);
// Use ParseXL for the hard work.
$this->_ole = new PHPExcel_Shared_OLERead();
$this->_rowoffset = $this->_coloffset = 0;
$this->_defaultEncoding = 'ISO-8859-1';
$this->_encoderFunction = function_exists('mb_convert_encoding') ? 'mb_convert_encoding' : 'iconv';
// get excel data
$res = $this->_ole->read($pFilename);
// oops, something goes wrong (Darko Miljanovic)
if ($res === false) {
// check error code
if ($this->_ole->error == 1) {
// bad file
throw new Exception('The filename ' . $pFilename . ' is not readable');
} elseif ($this->_ole->error == 2) {
throw new Exception('The filename ' . $pFilename . ' is not recognised as an Excel file');
}
// check other error codes here (eg bad fileformat, etc...)
}
$this->_data = $this->_ole->getWorkBook();
$this->_pos = 0;
/**
* PARSE WORKBOOK
*
**/
$pos = 0;
$code = ord($this->_data[$pos]) | ord($this->_data[$pos + 1]) << 8;
$length = ord($this->_data[$pos + 2]) | ord($this->_data[$pos + 3]) << 8;
$version = ord($this->_data[$pos + 4]) | ord($this->_data[$pos + 5]) << 8;
$substreamType = ord($this->_data[$pos + 6]) | ord($this->_data[$pos + 7]) << 8;
if ($version != self::XLS_BIFF8 && $version != self::XLS_BIFF7) {
return false;
}
if ($substreamType != self::XLS_WorkbookGlobals) {
return false;
}
$pos += $length + 4;
$code = ord($this->_data[$pos]) | ord($this->_data[$pos + 1]) << 8;
$length = ord($this->_data[$pos + 2]) | ord($this->_data[$pos + 3]) << 8;
$recordData = substr($this->_data, $pos + 4, $length);
while ($code != self::XLS_Type_EOF) {
switch ($code) {
case self::XLS_Type_SST:
/**
* SST - Shared String Table
*
* This record contains a list of all strings used anywhere
* in the workbook. Each string occurs only once. The
* workbook uses indexes into the list to reference the
* strings.
*
* -- "OpenOffice.org's Documentation of the Microsoft
* Excel File Format"
**/
// offset: 0; size: 4; total number of strings
// offset: 4; size: 4; number of unique strings
$spos = $pos + 4;
$limitpos = $spos + $length;
$uniqueStrings = $this->_GetInt4d($this->_data, $spos + 4);
$spos += 8;
// loop through the Unicode strings (16-bit length)
for ($i = 0; $i < $uniqueStrings; $i++) {
if ($spos == $limitpos) {
// then we have reached end of SST record data
$opcode = ord($this->_data[$spos]) | ord($this->_data[$spos + 1]) << 8;
$conlength = ord($this->_data[$spos + 2]) | ord($this->_data[$spos + 3]) << 8;
if ($opcode != self::XLS_Type_CONTINUE) {
// broken file, something is wrong
return -1;
}
$spos += 4;
$limitpos = $spos + $conlength;
}
// Read in the number of characters in the Unicode string
$numChars = ord($this->_data[$spos]) | ord($this->_data[$spos + 1]) << 8;
$spos += 2;
// option flags
$optionFlags = ord($this->_data[$spos]);
$spos++;
// bit: 0; mask: 0x01; 0 = compressed; 1 = uncompressed
$asciiEncoding = ($optionFlags & 0x1) == 0;
// bit: 2; mask: 0x02; 0 = ordinary; 1 = Asian phonetic
$extendedString = ($optionFlags & 0x4) != 0;
// Asian phonetic
// bit: 3; mask: 0x03; 0 = ordinary; 1 = Rich-Text
$richString = ($optionFlags & 0x8) != 0;
if ($richString) {
// Read in the crun
// number of Rich-Text formatting runs
$formattingRuns = ord($this->_data[$spos]) | ord($this->_data[$spos + 1]) << 8;
$spos += 2;
}
if ($extendedString) {
// size of Asian phonetic setting
$extendedRunLength = $this->_GetInt4d($this->_data, $spos);
$spos += 4;
}
// read in the characters
$len = $asciiEncoding ? $numChars : $numChars * 2;
if ($spos + $len < $limitpos) {
$retstr = substr($this->_data, $spos, $len);
$spos += $len;
} else {
// found countinue record
$retstr = substr($this->_data, $spos, $limitpos - $spos);
$bytesRead = $limitpos - $spos;
// remaining characters in Unicode string
$charsLeft = $numChars - ($asciiEncoding ? $bytesRead : $bytesRead / 2);
$spos = $limitpos;
// keep reading the characters
while ($charsLeft > 0) {
// record data
$opcode = ord($this->_data[$spos]) | ord($this->_data[$spos + 1]) << 8;
// length of continue record data
$conlength = ord($this->_data[$spos + 2]) | ord($this->_data[$spos + 3]) << 8;
if ($opcode != self::XLS_Type_CONTINUE) {
// broken file, something is wrong
return -1;
}
$spos += 4;
$limitpos = $spos + $conlength;
// option flags are repeated when Unicode string is split by a continue record
// OpenOffice.org documentation 5.21
$option = ord($this->_data[$spos]);
$spos += 1;
if ($asciiEncoding && $option == 0) {
// 1st fragment compressed
// this fragment compressed
$len = min($charsLeft, $limitpos - $spos);
$retstr .= substr($this->_data, $spos, $len);
$charsLeft -= $len;
$asciiEncoding = true;
} elseif (!$asciiEncoding && $option != 0) {
// 1st fragment uncompressed
// this fragment uncompressed
$len = min($charsLeft * 2, $limitpos - $spos);
$retstr .= substr($this->_data, $spos, $len);
$charsLeft -= $len / 2;
$asciiEncoding = false;
} elseif (!$asciiEncoding && $option == 0) {
// 1st fragment uncompressed
// this fragment compressed
$len = min($charsLeft, $limitpos - $spos);
for ($j = 0; $j < $len; $j++) {
$retstr .= $this->_data[$spos + $j] . chr(0);
}
$charsLeft -= $len;
$asciiEncoding = false;
} else {
// 1st fragment compressed
// this fragment uncompressed
$newstr = '';
for ($j = 0; $j < strlen($retstr); $j++) {
$newstr = $retstr[$j] . chr(0);
}
$retstr = $newstr;
$len = min($charsLeft * 2, $limitpos - $spos);
$retstr .= substr($this->_data, $spos, $len);
$charsLeft -= $len / 2;
$asciiEncoding = false;
}
$spos += $len;
}
}
//$retstr = ($asciiEncoding) ?
// $retstr : $this->_encodeUTF16($retstr);
// convert string according codepage and BIFF version
if ($version == self::XLS_BIFF8) {
$retstr = $this->_encodeUTF16($retstr, $asciiEncoding);
} else {
// SST only occurs in BIFF8, so why this part?
$retstr = $this->_decodeCodepage($retstr);
}
$fmtRuns = array();
if ($richString) {
// list of formatting runs
for ($j = 0; $j < $formattingRuns; $j++) {
// first formatted character; zero-based
$charPos = $this->_getInt2d($this->_data, $spos + $j * 4);
// index to font record
$fontIndex = $this->_getInt2d($this->_data, $spos + 2 + $j * 4);
$fmtRuns[] = array('charPos' => $charPos, 'fontIndex' => $fontIndex);
}
$spos += 4 * $formattingRuns;
}
if ($extendedString) {
// For Asian phonetic settings, we skip the extended string data
$spos += $extendedRunLength;
}
$this->_sst[] = array('value' => $retstr, 'fmtRuns' => $fmtRuns);
}
break;
case self::XLS_Type_FILEPASS:
/**
* SHEETPROTECTION
*
* This record is part of the File Protection Block. It
* contains information about the read/write password of the
* file. All record contents following this record will be
* encrypted.
*
* -- "OpenOffice.org's Documentation of the Microsoft
* Excel File Format"
*/
return false;
break;
case self::XLS_Type_EXTERNALBOOK:
break;
case self::XLS_Type_EXTSHEET:
// external sheet references provided for named cells
if ($version == self::XLS_BIFF8) {
$xpos = $pos + 4;
$xcnt = ord($this->_data[$xpos]) | ord($this->_data[$xpos + 1]) << 8;
for ($x = 0; $x < $xcnt; $x++) {
$this->_extshref[$x] = ord($this->_data[$xpos + 4 + 6 * $x]) | ord($this->_data[$xpos + 5 + 6 * $x]) << 8;
}
}
// this if statement is going to replace the above one later
if ($version == self::XLS_BIFF8) {
// offset: 0; size: 2; number of following ref structures
$nm = $this->_GetInt2d($recordData, 0);
for ($i = 0; $i < $nm; $i++) {
$this->_ref[] = array('externalBookIndex' => $this->_getInt2d($recordData, 2 + 6 * $i), 'firstSheetIndex' => $this->_getInt2d($recordData, 4 + 6 * $i), 'lastSheetIndex' => $this->_getInt2d($recordData, 6 + 6 * $i));
}
}
break;
case self::XLS_Type_NAME:
/**
* DEFINEDNAME
*
* This record is part of a Link Table. It contains the name
* and the token array of an internal defined name. Token
* arrays of defined names contain tokens with aberrant
* token classes.
*
* -- "OpenOffice.org's Documentation of the Microsoft
* Excel File Format"
*/
// retrieves named cells
$npos = $pos + 4;
$opts = ord($this->_data[$npos]) | ord($this->_data[$npos + 1]) << 8;
$nlen = ord($this->_data[$npos + 3]);
$flen = ord($this->_data[$npos + 4]) | ord($this->_data[$npos + 5]) << 8;
$fpos = $npos + 14 + 1 + $nlen;
$nstr = substr($this->_data, $npos + 15, $nlen);
$ftoken = ord($this->_data[$fpos]);
if ($ftoken == 58 && $opts == 0 && $flen == 7) {
$xref = ord($this->_data[$fpos + 1]) | ord($this->_data[$fpos + 2]) << 8;
$frow = ord($this->_data[$fpos + 3]) | ord($this->_data[$fpos + 4]) << 8;
$fcol = ord($this->_data[$fpos + 5]);
if (array_key_exists($xref, $this->_extshref)) {
$fsheet = $this->_extshref[$xref];
} else {
$fsheet = '';
}
$this->_namedcells[$nstr] = array('sheet' => $fsheet, 'row' => $frow, 'column' => $fcol);
}
break;
case self::XLS_Type_FORMAT:
/**
* FORMAT
*
* This record contains information about a number format.
* All FORMAT records occur together in a sequential list.
*
* In BIFF2-BIFF4 other records referencing a FORMAT record
* contain a zero-based index into this list. From BIFF5 on
* the FORMAT record contains the index itself that will be
* used by other records.
*
* -- "OpenOffice.org's Documentation of the Microsoft
* Excel File Format"
*/
//$indexCode = ord($this->_data[$pos + 4]) | ord($this->_data[$pos + 5]) << 8;
$indexCode = $this->_GetInt2d($recordData, 0);
/*
if ($version == self::XLS_BIFF8) {
*/
$formatString = $this->_readUnicodeStringLong(substr($recordData, 2));
/*
} else {
$numchars = ord($this->_data[$pos + 6]);
$formatString = substr($this->_data, $pos + 7, $numchars*2);
}
*/
$this->_formatRecords[$indexCode] = $formatString;
// now also stored in array _format[]
// _formatRecords[] will be removed from code later
$this->_numberFormat[$indexCode] = $formatString;
break;
case self::XLS_Type_FONT:
/**
* FONT
*/
$this->_font[] = $this->_readFont($recordData);
break;
case self::XLS_Type_XF:
/**
* XF - Extended Format
*
* This record contains formatting information for cells,
* rows, columns or styles.
*
* -- "OpenOffice.org's Documentation of the Microsoft
* Excel File Format"
*/
$indexCode = ord($this->_data[$pos + 6]) | ord($this->_data[$pos + 7]) << 8;
if (array_key_exists($indexCode, $this->_dateFormats)) {
$this->_formatRecords['xfrecords'][] = array('type' => 'date', 'format' => $this->_dateFormats[$indexCode], 'code' => $indexCode);
} elseif (array_key_exists($indexCode, $this->_percentFormats)) {
$this->_formatRecords['xfrecords'][] = array('type' => 'percent', 'format' => $this->_percentFormats[$indexCode], 'code' => $indexCode);
} elseif (array_key_exists($indexCode, $this->_numberFormats)) {
$this->_formatRecords['xfrecords'][] = array('type' => 'number', 'format' => $this->_numberFormats[$indexCode], 'code' => $indexCode);
} else {
if ($indexCode > 0 && isset($this->_formatRecords[$indexCode])) {
// custom formats...
$formatstr = $this->_formatRecords[$indexCode];
if ($formatstr) {
// dvc: reg exp changed to custom date/time format chars
if (preg_match("/^[hmsdy]/i", $formatstr)) {
// custom datetime format
// dvc: convert Excel formats to PHP date formats
// first remove escapes related to non-format characters
$formatstr = str_replace('\\', '', $formatstr);
// 4-digit year
$formatstr = str_replace('yyyy', 'Y', $formatstr);
// 2-digit year
$formatstr = str_replace('yy', 'y', $formatstr);
// first letter of month - no php equivalent
$formatstr = str_replace('mmmmm', 'M', $formatstr);
// full month name
$formatstr = str_replace('mmmm', 'F', $formatstr);
// short month name
$formatstr = str_replace('mmm', 'M', $formatstr);
// mm is minutes if time or month w/leading zero
$formatstr = str_replace(':mm', ':i', $formatstr);
// tmp place holder
$formatstr = str_replace('mm', 'x', $formatstr);
// month no leading zero
$formatstr = str_replace('m', 'n', $formatstr);
// month leading zero
$formatstr = str_replace('x', 'm', $formatstr);
// 12-hour suffix
$formatstr = str_replace('AM/PM', 'A', $formatstr);
// tmp place holder
$formatstr = str_replace('dd', 'x', $formatstr);
// days no leading zero
$formatstr = str_replace('d', 'j', $formatstr);
// days leading zero
$formatstr = str_replace('x', 'd', $formatstr);
// seconds
$formatstr = str_replace('ss', 's', $formatstr);
// fractional seconds - no php equivalent
$formatstr = str_replace('.S', '', $formatstr);
if (!strpos($formatstr, 'A')) {
// 24-hour format
$formatstr = str_replace('h', 'H', $formatstr);
}
// user defined flag symbol????
$formatstr = str_replace(';@', '', $formatstr);
$this->_formatRecords['xfrecords'][] = array('type' => 'date', 'format' => $formatstr, 'code' => $indexCode);
} else {
if (preg_match('/%$/', $formatstr)) {
// % number format
if (preg_match('/\\.[#0]+/i', $formatstr, $m)) {
$s = substr($m[0], 0, 1) . (strlen($m[0]) - 1);
$formatstr = str_replace($m[0], $s, $formatstr);
}
if (preg_match('/^[#0]+/', $formatstr, $m)) {
$formatstr = str_replace($m[0], strlen($m[0]), $formatstr);
}
$formatstr = '%' . str_replace('%', "f%%", $formatstr);
$this->_formatRecords['xfrecords'][] = array('type' => 'percent', 'format' => $formatstr, 'code' => $indexCode);
} else {
// dvc: changed to add format to unknown for debug
$this->_formatRecords['xfrecords'][] = array('type' => 'other', 'format' => $this->_defaultFormat, 'code' => $indexCode);
}
}
}
} else {
// dvc: changed to add format to unknown for debug
if (isset($this->_formatRecords[$indexCode])) {
$formatstr = $this->_formatRecords[$indexCode];
$type = 'undefined';
} else {
$formatstr = $this->_defaultFormat;
$type = 'default';
}
$this->_formatRecords['xfrecords'][] = array('type' => $type, 'format' => $formatstr, 'code' => $indexCode);
}
}
// store styles in xf array
$this->_xf[] = $this->_readBIFF8Style($recordData);
break;
case self::XLS_Type_NINETEENFOUR:
/**
* DATEMODE
*
* This record specifies the base date for displaying date
* values. All dates are stored as count of days past this
* base date. In BIFF2-BIFF4 this record is part of the
* Calculation Settings Block. In BIFF5-BIFF8 it is
* stored in the Workbook Globals Substream.
*
* -- "OpenOffice.org's Documentation of the Microsoft
* Excel File Format"
*/
$this->_nineteenFour = ord($this->_data[$pos + 4]) == 1;
/*
if (ord($this->_data[$pos + 4]) == 1) {
PHPExcel_Shared_Date::setExcelCalendar(PHPExcel_Shared_Date::CALENDAR_MAC_1904);
} else {
PHPExcel_Shared_Date::setExcelCalendar(PHPExcel_Shared_Date::CALENDAR_WINDOWS_1900);
}
*/
break;
case self::XLS_Type_BOUNDSHEET:
/**
* SHEET
*
* This record is located in the Workbook Globals
* Substream and represents a sheet inside the workbook.
* One SHEET record is written for each sheet. It stores the
* sheet name and a stream offset to the BOF record of the
* respective Sheet Substream within the Workbook Stream.
*
* -- "OpenOffice.org's Documentation of the Microsoft
* Excel File Format"
*/
$rec_offset = $this->_GetInt4d($this->_data, $pos + 4);
$rec_typeFlag = ord($this->_data[$pos + 8]);
$rec_visibilityFlag = ord($this->_data[$pos + 9]);
$rec_length = ord($this->_data[$pos + 10]);
if ($version == self::XLS_BIFF8) {
$compressedUTF16 = (ord($this->_data[$pos + 11]) & 0x1) == 0;
$rec_length = $compressedUTF16 ? $rec_length : $rec_length * 2;
$rec_name = $this->_encodeUTF16(substr($this->_data, $pos + 12, $rec_length), $compressedUTF16);
} elseif ($version == self::XLS_BIFF7) {
$rec_name = substr($this->_data, $pos + 11, $rec_length);
}
$this->_boundsheets[] = array('name' => $rec_name, 'offset' => $rec_offset);
break;
case self::XLS_Type_CODEPAGE:
/**
* CODEPAGE
*
* This record stores the text encoding used to write byte
* strings, stored as MS Windows code page identifier.
*
* -- "OpenOffice.org's Documentation of the Microsoft
* Excel File Format"
*/
$codepage = $this->_GetInt2d($this->_data, $pos + 4);
switch ($codepage) {
case 367:
// ASCII
$this->_codepage = "ASCII";
break;
case 437:
//OEM US
$this->_codepage = "CP437";
break;
case 720:
//OEM Arabic
// currently not supported by libiconv
$this->_codepage = "";
break;
case 737:
//OEM Greek
$this->_codepage = "CP737";
break;
case 775:
//OEM Baltic
$this->_codepage = "CP775";
break;
case 850:
//OEM Latin I
$this->_codepage = "CP850";
break;
case 852:
//OEM Latin II (Central European)
$this->_codepage = "CP852";
break;
case 855:
//OEM Cyrillic
$this->_codepage = "CP855";
break;
case 857:
//OEM Turkish
$this->_codepage = "CP857";
break;
case 858:
//OEM Multilingual Latin I with Euro
$this->_codepage = "CP858";
break;
case 860:
//OEM Portugese
$this->_codepage = "CP860";
break;
case 861:
//OEM Icelandic
$this->_codepage = "CP861";
break;
case 862:
//OEM Hebrew
$this->_codepage = "CP862";
break;
case 863:
//OEM Canadian (French)
$this->_codepage = "CP863";
break;
case 864:
//OEM Arabic
$this->_codepage = "CP864";
break;
case 865:
//OEM Nordic
$this->_codepage = "CP865";
break;
case 866:
//OEM Cyrillic (Russian)
$this->_codepage = "CP866";
break;
case 869:
//OEM Greek (Modern)
$this->_codepage = "CP869";
break;
case 874:
//ANSI Thai
$this->_codepage = "CP874";
break;
case 932:
//ANSI Japanese Shift-JIS
$this->_codepage = "CP932";
break;
case 936:
//ANSI Chinese Simplified GBK
$this->_codepage = "CP936";
break;
case 949:
//ANSI Korean (Wansung)
$this->_codepage = "CP949";
break;
case 950:
//ANSI Chinese Traditional BIG5
$this->_codepage = "CP950";
break;
case 1200:
//UTF-16 (BIFF8)
$this->_codepage = "UTF-16LE";
break;
case 1250:
// ANSI Latin II (Central European)
$this->_codepage = "CP1250";
break;
case 1251:
//ANSI Cyrillic
$this->_codepage = "CP1251";
break;
case 1252:
//ANSI Latin I (BIFF4-BIFF7)
$this->_codepage = "CP1252";
break;
case 1253:
//ANSI Greek
$this->_codepage = "CP1253";
break;
case 1254:
//ANSI Turkish
$this->_codepage = "CP1254";
break;
case 1255:
//ANSI Hebrew
$this->_codepage = "CP1255";
break;
case 1256:
//ANSI Arabic
$this->_codepage = "CP1256";
break;
case 1257:
//ANSI Baltic
$this->_codepage = "CP1257";
break;
case 1258:
//ANSI Vietnamese
$this->_codepage = "CP1258";
break;
case 1361:
//ANSI Korean (Johab)
$this->_codepage = "CP1361";
break;
case 10000:
//Apple Roman
// currently not supported by libiconv
$this->_codepage = "";
break;
case 32768:
//Apple Roman
// currently not supported by libiconv
$this->_codepage = "";
break;
case 32769:
//ANSI Latin I (BIFF2-BIFF3)
// currently not supported by libiconv
$this->_codepage = "";
break;
}
break;
}
$pos += $length + 4;
$code = ord($this->_data[$pos]) | ord($this->_data[$pos + 1]) << 8;
$length = ord($this->_data[$pos + 2]) | ord($this->_data[$pos + 3]) << 8;
$recordData = substr($this->_data, $pos + 4, $length);
}
/**
*
* PARSE THE INDIVIDUAL SHEETS
*
**/
foreach ($this->_boundsheets as $key => $val) {
// add sheet to PHPExcel object
$sheet = $excel->createSheet();
$sheet->setTitle((string) $val['name']);
$this->_sn = $key;
$spos = $val['offset'];
$cont = true;
// read BOF
$code = ord($this->_data[$spos]) | ord($this->_data[$spos + 1]) << 8;
$length = ord($this->_data[$spos + 2]) | ord($this->_data[$spos + 3]) << 8;
$version = ord($this->_data[$spos + 4]) | ord($this->_data[$spos + 5]) << 8;
$substreamType = ord($this->_data[$spos + 6]) | ord($this->_data[$spos + 7]) << 8;
if ($version != self::XLS_BIFF8 && $version != self::XLS_BIFF7) {
return -1;
}
if ($substreamType != self::XLS_Worksheet) {
return -2;
}
$spos += $length + 4;
while ($cont) {
$lowcode = ord($this->_data[$spos]);
if ($lowcode == self::XLS_Type_EOF) {
break;
}
$code = $lowcode | ord($this->_data[$spos + 1]) << 8;
$length = ord($this->_data[$spos + 2]) | ord($this->_data[$spos + 3]) << 8;
$recordData = substr($this->_data, $spos + 4, $length);
$spos += 4;
$this->_sheets[$this->_sn]['maxrow'] = $this->_rowoffset - 1;
$this->_sheets[$this->_sn]['maxcol'] = $this->_coloffset - 1;
unset($this->_rectype);
unset($this->_formula);
unset($this->_formula_result);
$this->_multiplier = 1;
// need for format with %
switch ($code) {
case self::XLS_Type_DIMENSION:
/**
* DIMENSION
*
* This record contains the range address of the used area
* in the current sheet.
*
* -- "OpenOffice.org's Documentation of the Microsoft
* Excel File Format"
*/
if (!isset($this->_numRows)) {
if ($length == 10 || $version == self::XLS_BIFF7) {
$this->_sheets[$this->_sn]['numRows'] = ord($this->_data[$spos + 2]) | ord($this->_data[$spos + 3]) << 8;
$this->_sheets[$this->_sn]['numCols'] = ord($this->_data[$spos + 6]) | ord($this->_data[$spos + 7]) << 8;
} else {
$this->_sheets[$this->_sn]['numRows'] = ord($this->_data[$spos + 4]) | ord($this->_data[$spos + 5]) << 8;
$this->_sheets[$this->_sn]['numCols'] = ord($this->_data[$spos + 10]) | ord($this->_data[$spos + 11]) << 8;
}
}
break;
case self::XLS_Type_MERGEDCELLS:
/**
* MERGEDCELLS
*
* This record contains the addresses of merged cell ranges
* in the current sheet.
*
* -- "OpenOffice.org's Documentation of the Microsoft
* Excel File Format"
*/
if ($version == self::XLS_BIFF8 && !$this->_readDataOnly) {
$cellRanges = $this->_GetInt2d($this->_data, $spos);
for ($i = 0; $i < $cellRanges; $i++) {
$fr = $this->_GetInt2d($this->_data, $spos + 8 * $i + 2);
// first row
$lr = $this->_GetInt2d($this->_data, $spos + 8 * $i + 4);
// last row
$fc = $this->_GetInt2d($this->_data, $spos + 8 * $i + 6);
// first column
$lc = $this->_GetInt2d($this->_data, $spos + 8 * $i + 8);
// last column
// this part no longer needed, instead apply cell merge on PHPExcel worksheet object
/*
if ($lr - $fr > 0) {
$this->_sheets[$this->_sn]['cellsInfo'][$fr + 1][$fc + 1]['rowspan'] = $lr - $fr + 1;
}
if ($lc - $fc > 0) {
$this->_sheets[$this->_sn]['cellsInfo'][$fr + 1][$fc + 1]['colspan'] = $lc - $fc + 1;
}
*/
$sheet->mergeCellsByColumnAndRow($fc, $fr + 1, $lc, $lr + 1);
}
}
break;
case self::XLS_Type_RK:
case self::XLS_Type_RK2:
/**
* RK
*
* This record represents a cell that contains an RK value
* (encoded integer or floating-point value). If a
* floating-point value cannot be encoded to an RK value,
* a NUMBER record will be written. This record replaces the
* record INTEGER written in BIFF2.
*
* -- "OpenOffice.org's Documentation of the Microsoft
* Excel File Format"
*/
$row = ord($this->_data[$spos]) | ord($this->_data[$spos + 1]) << 8;
$column = ord($this->_data[$spos + 2]) | ord($this->_data[$spos + 3]) << 8;
$rknum = $this->_GetInt4d($this->_data, $spos + 6);
$numValue = $this->_GetIEEE754($rknum);
/*
if ($this->_isDate($spos)) {
list($string, $raw) = $this->_createDate($numValue);
} else {
$raw = $numValue;
if (isset($this->_columnsFormat[$column + 1])){
$this->_curformat = $this->_columnsFormat[$column + 1];
}
$string = sprintf($this->_curformat,$numValue*$this->_multiplier);
}
*/
// offset 4; size: 2; index to XF record
$xfindex = $this->_getInt2d($recordData, 4);
// add BIFF8 style information
if ($version == self::XLS_BIFF8 && !$this->_readDataOnly) {
$sheet->getStyleByColumnAndRow($column, $row + 1)->applyFromArray($this->_xf[$xfindex]);
if (PHPExcel_Shared_Date::isDateTimeFormatCode($this->_xf[$xfindex]['numberformat']['code'])) {
$numValue = (int) PHPExcel_Shared_Date::ExcelToPHP($numValue);
}
}
//$this->_addcell($row, $column, $string, $raw);
//$sheet->setCellValueByColumnAndRow($column, $row + 1, $string);
$sheet->setCellValueByColumnAndRow($column, $row + 1, $numValue);
break;
case self::XLS_Type_LABELSST:
/**
* LABELSST
*
* 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"
*/
$row = ord($this->_data[$spos]) | ord($this->_data[$spos + 1]) << 8;
$column = ord($this->_data[$spos + 2]) | ord($this->_data[$spos + 3]) << 8;
$xfindex = ord($this->_data[$spos + 4]) | ord($this->_data[$spos + 5]) << 8;
$index = $this->_GetInt4d($this->_data, $spos + 6);
//$this->_addcell($row, $column, $this->_sst[$index]);
if ($fmtRuns = $this->_sst[$index]['fmtRuns']) {
// then we have rich text
$richText = new PHPExcel_RichText($sheet->getCellByColumnAndRow($column, $row + 1));
$charPos = 0;
for ($i = 0; $i <= count($this->_sst[$index]['fmtRuns']); $i++) {
if (isset($fmtRuns[$i])) {
$text = mb_substr($this->_sst[$index]['value'], $charPos, $fmtRuns[$i]['charPos'] - $charPos, 'UTF-8');
$charPos = $fmtRuns[$i]['charPos'];
} else {
$text = mb_substr($this->_sst[$index]['value'], $charPos, mb_strlen($this->_sst[$index]['value']), 'UTF-8');
}
if (mb_strlen($text) > 0) {
$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->getFont()->applyFromArray($this->_font[$fontIndex]);
}
}
}
} else {
$sheet->setCellValueByColumnAndRow($column, $row + 1, $this->_sst[$index]['value']);
}
// add BIFF8 style information
if ($version == self::XLS_BIFF8 && !$this->_readDataOnly) {
$sheet->getStyleByColumnAndRow($column, $row + 1)->applyFromArray($this->_xf[$xfindex]);
}
break;
case self::XLS_Type_MULRK:
/**
* MULRK - Multiple RK
*
* This record represents a cell range containing RK value
* cells. All cells are located in the same row.
*
* -- "OpenOffice.org's Documentation of the Microsoft
* Excel File Format"
*/
$row = ord($this->_data[$spos]) | ord($this->_data[$spos + 1]) << 8;
$colFirst = ord($this->_data[$spos + 2]) | ord($this->_data[$spos + 3]) << 8;
$colLast = ord($this->_data[$spos + $length - 2]) | ord($this->_data[$spos + $length - 1]) << 8;
$columns = $colLast - $colFirst + 1;
$tmppos = $spos + 4;
for ($i = 0; $i < $columns; $i++) {
// offset: 0; size: 2; index to XF record
$xfindex = $this->_getInt2d($recordData, 4 + 6 * $i);
// offset: 2; size: 4; RK value
$numValue = $this->_GetIEEE754($this->_GetInt4d($this->_data, $tmppos + 2));
/*
if ($this->_isDate($tmppos-4)) {
list($string, $raw) = $this->_createDate($numValue);
} else {
$raw = $numValue;
if (isset($this->_columnsFormat[$colFirst + $i + 1])){
$this->_curformat = $this->_columnsFormat[$colFirst+ $i + 1];
}
$string = sprintf($this->_curformat, $numValue *
$this->_multiplier);
}
*/
//$this->_addcell($row, $colFirst + $i, $string, $raw);
if ($version == self::XLS_BIFF8 && !$this->_readDataOnly) {
$sheet->getStyleByColumnAndRow($colFirst + $i, $row + 1)->applyFromArray($this->_xf[$xfindex]);
if (PHPExcel_Shared_Date::isDateTimeFormatCode($this->_xf[$xfindex]['numberformat']['code'])) {
$numValue = (int) PHPExcel_Shared_Date::ExcelToPHP($numValue);
}
}
//$sheet->setCellValueByColumnAndRow($colFirst + $i, $row + 1, $string);
$sheet->setCellValueByColumnAndRow($colFirst + $i, $row + 1, $numValue);
$tmppos += 6;
}
break;
case self::XLS_Type_NUMBER:
/**
* NUMBER
*
* This record represents a cell that contains a
* floating-point value.
*
* -- "OpenOffice.org's Documentation of the Microsoft
* Excel File Format"
*/
$row = ord($this->_data[$spos]) | ord($this->_data[$spos + 1]) << 8;
$column = ord($this->_data[$spos + 2]) | ord($this->_data[$spos + 3]) << 8;
// offset 4; size: 2; index to XF record
$xfindex = $this->_GetInt2d($recordData, 4);
$numValue = $this->_createNumber($spos);
/*
if ($this->_isDate($spos)) {
$numValue = $this->_createNumber($spos);
list($string, $raw) = $this->_createDate($numValue);
} else {
if (isset($this->_columnsFormat[$column + 1])) {
$this->_curformat = $this->_columnsFormat[$column + 1];
}
$raw = $this->_createNumber($spos);
$string = sprintf($this->_curformat, $raw * $this->_multiplier);
}
*/
// add BIFF8 style information
if ($version == self::XLS_BIFF8 && !$this->_readDataOnly) {
$sheet->getStyleByColumnAndRow($column, $row + 1)->applyFromArray($this->_xf[$xfindex]);
if (PHPExcel_Shared_Date::isDateTimeFormatCode($this->_xf[$xfindex]['numberformat']['code'])) {
$numValue = (int) PHPExcel_Shared_Date::ExcelToPHP($numValue);
}
}
//$this->_addcell($row, $column, $string, $raw);
//$sheet->setCellValueByColumnAndRow($column, $row + 1, $string);
$sheet->setCellValueByColumnAndRow($column, $row + 1, $numValue);
break;
case self::XLS_Type_FORMULA:
case self::XLS_Type_FORMULA2:
/**
* FORMULA
*
* This record contains the token array and the result of a
* formula cell.
*
* -- "OpenOffice.org's Documentation of the Microsoft
* Excel File Format"
*/
// offset: 0; size: 2; row index
$row = ord($this->_data[$spos]) | ord($this->_data[$spos + 1]) << 8;
// offset: 2; size: 2; col index
$column = ord($this->_data[$spos + 2]) | ord($this->_data[$spos + 3]) << 8;
// offset: 4; size: 2; XF index
$xfindex = ord($this->_data[$spos + 4]) | ord($this->_data[$spos + 5]) << 8;
// offset: 6; size: 8; result of the formula
if (ord($this->_data[$spos + 6]) == 0 && ord($this->_data[$spos + 12]) == 255 && ord($this->_data[$spos + 13]) == 255) {
//String formula. Result follows in appended STRING record
$this->_formula_result = 'string';
$soff = $spos + $length;
$scode = ord($this->_data[$soff]) | ord($this->_data[$soff + 1]) << 8;
$sopt = ord($this->_data[$soff + 6]);
// only reads byte strings...
if ($scode == self::XLS_Type_STRING && $sopt == '0') {
$slen = ord($this->_data[$soff + 4]) | ord($this->_data[$soff + 5]) << 8;
$string = substr($this->_data, $soff + 7, ord($this->_data[$soff + 4]) | ord($this->_data[$soff + 5]) << 8);
} else {
$string = 'NOT FOUND';
}
$raw = $string;
} elseif (ord($this->_data[$spos + 6]) == 1 && ord($this->_data[$spos + 12]) == 255 && ord($this->_data[$spos + 13]) == 255) {
//Boolean formula. Result is in +2; 0=false,1=true
$this->_formula_result = 'boolean';
$raw = ord($this->_data[$spos + 8]);
if ($raw) {
$string = "TRUE";
} else {
$string = "FALSE";
}
} elseif (ord($this->_data[$spos + 6]) == 2 && ord($this->_data[$spos + 12]) == 255 && ord($this->_data[$spos + 13]) == 255) {
//Error formula. Error code is in +2
$this->_formula_result = 'error';
$raw = ord($this->_data[$spos + 8]);
$string = 'ERROR:' . $raw;
} elseif (ord($this->_data[$spos + 6]) == 3 && ord($this->_data[$spos + 12]) == 255 && ord($this->_data[$spos + 13]) == 255) {
//Formula result is a null string
$this->_formula_result = 'null';
$raw = '';
$string = '';
} else {
// forumla result is a number, first 14 bytes like _NUMBER record
$string = $this->_createNumber($spos);
/*
$this->_formula_result = 'number';
if ($this->_isDate($spos)) {
$numValue = $this->_createNumber($spos);
list($string, $raw) = $this->_createDate($numValue);
} else {
if (isset($this->_columnsFormat[$column + 1])){
$this->_curformat = $this->_columnsFormat[$column + 1];
}
$raw = $this->_createNumber($spos);
$string = sprintf($this->_curformat, $raw * $this->_multiplier);
}
*/
}
// save the raw formula tokens for end user interpretation
// Excel stores as a token record
$this->_rectype = 'formula';
// read formula record tokens ...
$tokenlength = ord($this->_data[$spos + 20]) | ord($this->_data[$spos + 21]) << 8;
for ($i = 0; $i < $tokenlength; $i++) {
$this->_formula[$i] = ord($this->_data[$spos + 22 + $i]);
}
// add BIFF8 style information
if ($version == self::XLS_BIFF8 && !$this->_readDataOnly) {
$sheet->getStyleByColumnAndRow($column, $row + 1)->applyFromArray($this->_xf[$xfindex]);
if (PHPExcel_Shared_Date::isDateTimeFormatCode($this->_xf[$xfindex]['numberformat']['code'])) {
$string = (int) PHPExcel_Shared_Date::ExcelToPHP($string);
}
}
//$this->_addcell($row, $column, $string, $raw);
$sheet->setCellValueByColumnAndRow($column, $row + 1, $string);
// offset: 14: size: 2; option flags, recalculate always, recalculate on open etc.
// offset: 16: size: 4; not used
// offset: 20: size: variable; formula structure
// WORK IN PROGRESS: TRUE FORMULA SUPPORT
// resolve BIFF8 formula tokens into human readable formula
// so it can be added as formula
// $formulaStructure = substr($recordData, 20);
// $formulaString = $this->_getFormulaStringFromStructure($formulaStructure); // get human language
break;
case self::XLS_Type_BOOLERR:
/**
* BOOLERR
*
* This record represents a Boolean value or error value
* cell.
*
* -- "OpenOffice.org's Documentation of the Microsoft
* Excel File Format"
*/
// offset: 0; size: 2; row index
$row = ord($this->_data[$spos]) | ord($this->_data[$spos + 1]) << 8;
// offset: 2; size: 2; column index
$column = ord($this->_data[$spos + 2]) | ord($this->_data[$spos + 3]) << 8;
// offset: 4; size: 2; index to XF record
$xfindex = $this->_GetInt2d($recordData, 4);
// offset: 6; size: 1; the boolean value or error value
$value = ord($recordData[6]);
// offset: 7; size: 1; 0=boolean; 1=error
$isError = ord($recordData[7]);
if (!$isError) {
$sheet->getCellByColumnAndRow($column, $row + 1)->setValueExplicit((bool) $value, PHPExcel_Cell_DataType::TYPE_BOOL);
}
// add BIFF8 style information
if ($version == self::XLS_BIFF8 && !$this->_readDataOnly) {
$sheet->getStyleByColumnAndRow($column, $row + 1)->applyFromArray($this->_xf[$xfindex]);
}
break;
case self::XLS_Type_ROW:
/**
* ROW
*
* This record contains the properties of a single row in a
* sheet. Rows and cells in a sheet are divided into blocks
* of 32 rows.
*
* -- "OpenOffice.org's Documentation of the Microsoft
* Excel File Format"
*/
if (!$this->_readDataOnly) {
// offset: 0; size: 2; index of this row
$r = $this->_GetInt2d($recordData, 0);
// offset: 2; size: 2; index to column of the first cell which is described by a cell record
// offset: 4; size: 2; index to column of the last cell which is described by a cell record, increased by 1
// offset: 6; size: 2;
// bit: 14-0; mask: 0x7FF; height of the row, in twips = 1/20 of a point
$height = (0x7ff & $this->_GetInt2d($recordData, 6)) >> 0;
// bit: 15: mask: 0x8000; 0 = row has custom height; 1= row has default height
$useDefaultHeight = (0x8000 & $this->_GetInt2d($recordData, 6)) >> 15;
if (!$useDefaultHeight) {
$sheet->getRowDimension($r + 1)->setRowHeight($height / 20);
}
// offset: 8; size: 2; not used
// offset: 10; size: 2; not used in BIFF5-BIFF8
// offset: 12; size: 4; option flags and default row formatting
// bit: 2-0: mask: 0x00000007; outline level of the row
$level = (0x7 & $this->_GetInt4d($recordData, 12)) >> 0;
$sheet->getRowDimension($r + 1)->setOutlineLevel($level);
// bit: 4; mask: 0x00000010; 1 = outline group start or ends here... and is collapsed
$isCollapsed = (0x10 & $this->_GetInt4d($recordData, 12)) >> 4;
$sheet->getRowDimension($r + 1)->setCollapsed($isCollapsed);
// bit: 5; mask: 0x00000020; 1 = row is hidden
$isHidden = (0x20 & $this->_GetInt4d($recordData, 12)) >> 5;
$sheet->getRowDimension($r + 1)->setVisible(!$isHidden);
}
break;
case self::XLS_Type_DBCELL:
/**
* DBCELL
*
* This record is written once in a Row Block. It contains
* relative offsets to calculate the stream position of the
* first cell record for each row. The offset list in this
* record contains as many offsets as ROW records are
* present in the Row Block.
*
* -- "OpenOffice.org's Documentation of the Microsoft
* Excel File Format"
*/
break;
case self::XLS_Type_MULBLANK:
/**
* MULBLANK - Multiple BLANK
*
* This record represents a cell range of empty cells. All
* cells are located in the same row
*
* -- "OpenOffice.org's Documentation of the Microsoft
* Excel File Format"
*/
// offset: 0; size: 2; index to row
$row = $this->_GetInt2d($recordData, 0);
// offset: 2; size: 2; index to first column
$fc = $this->_GetInt2d($recordData, 2);
// offset: 4; size: 2 x nc; list of indexes to XF records
// add BIFF8 style information
if ($version == self::XLS_BIFF8 && !$this->_readDataOnly) {
for ($i = 0; $i < $length / 2 - 4; $i++) {
$xfindex = $this->_GetInt2d($recordData, 4 + 2 * $i);
$sheet->getStyleByColumnAndRow($fc + $i, $row + 1)->applyFromArray($this->_xf[$xfindex]);
}
}
// offset: 6; size 2; index to last column (not needed)
break;
case self::XLS_Type_LABEL:
/**
* LABEL
*
* This record represents a cell that contains a string. In
* BIFF8 it is usually replaced by the LABELSST record.
* Excel still uses this record, if it copies unformatted
* text cells to the clipboard.
*
* -- "OpenOffice.org's Documentation of the Microsoft
* Excel File Format"
*/
$row = ord($this->_data[$spos]) | ord($this->_data[$spos + 1]) << 8;
$column = ord($this->_data[$spos + 2]) | ord($this->_data[$spos + 3]) << 8;
/*
$this->_addcell($row, $column, substr($this->_data, $spos + 8,
ord($this->_data[$spos + 6]) | ord($this->_data[$spos + 7]) << 8));
*/
$sheet->setCellValueByColumnAndRow($column, $row + 1, substr($this->_data, $spos + 8, ord($this->_data[$spos + 6]) | ord($this->_data[$spos + 7]) << 8));
break;
case self::XLS_Type_PROTECT:
/**
* PROTECT - Sheet protection (BIFF2 through BIFF8)
* if this record is omitted, then it also means no sheet protection
*/
if (!$this->_readDataOnly) {
// offset: 0; size: 2;
// bit 0, mask 0x01; sheet protection
$isSheetProtected = (0x1 & $this->_GetInt2d($recordData, 0)) >> 0;
switch ($isSheetProtected) {
case 0:
break;
case 1:
$sheet->getProtection()->setSheet(true);
break;
}
}
break;
case self::XLS_Type_PASSWORD:
/**
* PASSWORD - Sheet protection (hashed) password (BIFF2 through BIFF8)
*/
if (!$this->_readDataOnly) {
// offset: 0; size: 2; 16-bit hash value of password
$password = strtoupper(dechex($this->_GetInt2d($recordData, 0)));
// the hashed password
$sheet->getProtection()->setPassword($password, true);
}
break;
case self::XLS_Type_COLINFO:
/**
* COLINFO - Column information
*/
if (!$this->_readDataOnly) {
// offset: 0; size: 2; index to first column in range
$fc = $this->_GetInt2d($recordData, 0);
// first column index
// offset: 2; size: 2; index to last column in range
$lc = $this->_GetInt2d($recordData, 2);
// first column index
// offset: 4; size: 2; width of the column in 1/256 of the width of the zero character
$width = $this->_GetInt2d($recordData, 4);
// offset: 6; size: 2; index to XF record for default column formatting
// offset: 8; size: 2; option flags
// bit: 0; mask: 0x0001; 1= columns are hidden
$isHidden = (0x1 & $this->_GetInt2d($recordData, 8)) >> 0;
// bit: 10-8; mask: 0x0700; outline level of the columns (0 = no outline)
$level = (0x700 & $this->_GetInt2d($recordData, 8)) >> 8;
// bit: 12; mask: 0x1000; 1 = collapsed
$isCollapsed = (0x1000 & $this->_GetInt2d($recordData, 8)) >> 12;
// offset: 10; size: 2; not used
for ($i = $fc; $i <= $lc; $i++) {
$sheet->getColumnDimensionByColumn($i)->setWidth($width / 256);
$sheet->getColumnDimensionByColumn($i)->setVisible(!$isHidden);
$sheet->getColumnDimensionByColumn($i)->setOutlineLevel($level);
$sheet->getColumnDimensionByColumn($i)->setCollapsed($isCollapsed);
}
}
break;
case self::XLS_Type_DEFCOLWIDTH:
// offset: 0; size: 2; row index
$width = $this->_GetInt2d($recordData, 0);
$sheet->getDefaultColumnDimension()->setWidth($width);
break;
case self::XLS_Type_DEFAULTROWHEIGHT:
// offset: 0; size: 2; option flags
// offset: 2; size: 2; default height for unused rows, (twips 1/20 point)
$height = $this->_GetInt2d($recordData, 2);
$sheet->getDefaultRowDimension()->setRowHeight($height / 20);
break;
case self::XLS_Type_BLANK:
// offset: 0; size: 2; row index
$row = $this->_GetInt2d($recordData, 0);
// offset: 2; size: 2; col index
$col = $this->_GetInt2d($recordData, 2);
// offset: 4; size: 2; XF index
$xfindex = $this->_GetInt2d($recordData, 4);
// add BIFF8 style information
if ($version == self::XLS_BIFF8 && !$this->_readDataOnly) {
$sheet->getStyleByColumnAndRow($col, $row + 1)->applyFromArray($this->_xf[$xfindex]);
}
break;
case self::XLS_Type_SHEETPR:
// offset: 0; size: 2
// bit: 6; mask: 0x0040; 0 = outline buttons above outline group
$isSummaryBelow = (0x40 & $this->_GetInt2d($recordData, 0)) >> 6;
$sheet->setShowSummaryBelow($isSummaryBelow);
// bit: 7; mask: 0x0080; 0 = outline buttons left of outline group
$isSummaryRight = (0x80 & $this->_GetInt2d($recordData, 0)) >> 7;
$sheet->setShowSummaryRight($isSummaryRight);
break;
case self::XLS_Type_EOF:
$cont = false;
break;
default:
break;
}
$spos += $length;
}
if (!isset($this->_sheets[$this->_sn]['numRows'])) {
$this->_sheets[$this->_sn]['numRows'] = $this->_sheets[$this->_sn]['maxrow'];
}
if (!isset($this->_sheets[$this->_sn]['numCols'])) {
$this->_sheets[$this->_sn]['numCols'] = $this->_sheets[$this->_sn]['maxcol'];
}
}
/*
foreach($this->_boundsheets as $index => $details) {
$sheet = $excel->getSheet($index);
// read all the columns of all the rows !
$numrows = $this->_sheets[$index]['numRows'];
$numcols = $this->_sheets[$index]['numCols'];
for ($row = 0; $row < $numrows; $row++) {
for ($col = 0; $col < $numcols; $col++) {
$cellcontent = $cellinfo = null;
if (isset($this->_sheets[$index]['cells'][$row][$col])===true) {
$cellcontent = $this->_sheets[$index]['cells'][$row][$col];
} else {
continue;
}
if (isset($this->_sheets[$index]['cellsInfo'][$row][$col])===true) {
$cellinfo = $this->_sheets[$index]['cellsInfo'][$row][$col];
}
$sheet->setCellValueByColumnAndRow($col, $row + 1,
$cellcontent);
}
}
};
*/
return $excel;
}
/**
* Read FORMULA record
* This record contains the token array and the result of a
* formula cell.
*
* -- "OpenOffice.org's Documentation of the Microsoft
* Excel File Format"
*/
private function _readFormula()
{
$pos = $this->_pos;
$length = $this->_GetInt2d($this->_data, $pos + 2);
$recordData = substr($this->_data, $pos + 4, $length);
$pos += 4;
// offset: 0; size: 2; row index
$row = $this->_GetInt2d($this->_data, $pos);
// offset: 2; size: 2; col index
$column = $this->_GetInt2d($this->_data, $pos + 2);
$columnString = PHPExcel_Cell::stringFromColumnIndex($column);
// Read cell?
if (!is_null($this->getReadFilter()) && $this->getReadFilter()->readCell($columnString, $row + 1, $this->_phpSheet->getTitle())) {
// offset: 4; size: 2; XF index
$xfindex = $this->_GetInt2d($this->_data, $pos + 4);
// offset: 6; size: 8; result of the formula
if (ord($this->_data[$pos + 6]) == 0 && ord($this->_data[$pos + 12]) == 255 && ord($this->_data[$pos + 13]) == 255) {
//String formula. Result follows in appended STRING record
$dataType = PHPExcel_Cell_DataType::TYPE_STRING;
$soff = $pos + $length;
$scode = $this->_GetInt2d($this->_data, $soff);
$slength = $this->_GetInt2d($this->_data, $soff + 2);
$sdata = substr($this->_data, $soff + 4, $slength);
if ($this->_version == self::XLS_BIFF8) {
$string = $this->_readUnicodeStringLong($sdata);
$value = $string['value'];
} else {
$string = $this->_readByteStringLong($sdata);
$value = $string['value'];
}
} elseif (ord($this->_data[$pos + 6]) == 1 && ord($this->_data[$pos + 12]) == 255 && ord($this->_data[$pos + 13]) == 255) {
//Boolean formula. Result is in +2; 0=false,1=true
$dataType = PHPExcel_Cell_DataType::TYPE_BOOL;
$value = (bool) ord($this->_data[$pos + 8]);
} elseif (ord($this->_data[$pos + 6]) == 2 && ord($this->_data[$pos + 12]) == 255 && ord($this->_data[$pos + 13]) == 255) {
//Error formula. Error code is in +2
$dataType = PHPExcel_Cell_DataType::TYPE_ERROR;
$value = $this->_mapErrorCode(ord($this->_data[$pos + 8]));
} elseif (ord($this->_data[$pos + 6]) == 3 && ord($this->_data[$pos + 12]) == 255 && ord($this->_data[$pos + 13]) == 255) {
//Formula result is a null string
$dataType = PHPExcel_Cell_DataType::TYPE_NULL;
$value = '';
} else {
// forumla result is a number, first 14 bytes like _NUMBER record
$dataType = PHPExcel_Cell_DataType::TYPE_NUMERIC;
$value = $this->_createNumber($pos);
}
// add cell style
if (!$this->_readDataOnly) {
$this->_phpSheet->getStyle($columnString . ($row + 1))->applyFromArray($this->_xf[$xfindex]);
if (PHPExcel_Shared_Date::isDateTimeFormatCode($this->_xf[$xfindex]['numberformat']['code'])) {
$value = PHPExcel_Shared_Date::ExcelToPHP($value);
}
}
// offset: 14: size: 2; option flags, recalculate always, recalculate on open etc.
// offset: 16: size: 4; not used
// offset: 20: size: variable; formula structure
$formulaStructure = substr($recordData, 20);
// add cell value
try {
if ($this->_version != self::XLS_BIFF8) {
throw new Exception('Not BIFF8. Can only read BIFF8 formulas');
}
$formula = $this->_getFormulaFromStructure($formulaStructure);
// get human language
$this->_phpSheet->getCell($columnString . ($row + 1))->setValueExplicit('=' . $formula, PHPExcel_Cell_DataType::TYPE_FORMULA);
} catch (Exception $e) {
$this->_phpSheet->setCellValueExplicit($columnString . ($row + 1), $value, $dataType);
}
}
// move stream pointer to next record
$this->_pos += 4 + $length;
}
/**
* Map to the appropriate write method acording to the token recieved.
*
* @access public
* @param integer $row The row of the cell we are writing to
* @param integer $col The column of the cell we are writing to
* @param mixed $token What we are writing
* @param mixed $format The optional format to apply to the cell
*/
function write($row, $col, $token, $format = null, $numberFormat = null)
{
// Check for a cell reference in A1 notation and substitute row and column
/*if ($_[0] =~ /^\D/) {
@_ = $this->_substituteCellref(@_);
}*/
if (($numberFormat != 'General') && (PHPExcel_Shared_Date::isDateTimeFormatCode($numberFormat))) {
if (is_string($token)) {
// Error string
return $this->writeString($row, $col, $token, $format);
} elseif (!is_float($token)) {
// PHP serialized date/time or date/time object
return $this->writeNumber($row, $col, PHPExcel_Shared_Date::PHPToExcel($token), $format);
} else {
// Excel serialized date/time
return $this->writeNumber($row, $col, $token, $format);
}
} elseif (preg_match("/^([+-]?)(?=\d|\.\d)\d*(\.\d*)?([Ee]([+-]?\d+))?$/", $token)) {
// Match number
return $this->writeNumber($row, $col, $token, $format);
} elseif (preg_match("/^[fh]tt?p:\/\//", $token)) {
// Match http or ftp URL
return $this->writeUrl($row, $col, $token, '', $format);
} elseif (preg_match("/^mailto:/", $token)) {
// Match mailto:
return $this->writeUrl($row, $col, $token, '', $format);
} elseif (preg_match("/^(?:in|ex)ternal:/", $token)) {
// Match internal or external sheet link
return $this->writeUrl($row, $col, $token, '', $format);
} elseif (preg_match("/^=/", $token)) {
// Match formula
return $this->writeFormula($row, $col, $token, $format);
} elseif (preg_match("/^@/", $token)) {
// Match formula
return $this->writeFormula($row, $col, $token, $format);
} elseif ($token == '') {
// Match blank
return $this->writeBlank($row, $col, $format);
} else {
// Default: match string
return $this->writeString($row, $col, $token, $format);
}
}
