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