// FIXME: what if sheetname contains comma? $extractedRanges = array(); foreach ($ranges as $range) { // $range should look like one of these // Foo!$C$7:$J$66 // Bar!$A$1:$IV$2 $explodes = explode('!', $range); // FIXME: what if sheetname contains exclamation mark? $sheetName = $explodes[0]; if (count($explodes) == 2) { $extractedRanges[] = str_replace('$', '', $explodes[1]); // C7:J66 } } if ($docSheet = $this->_phpExcel->getSheetByName($sheetName)) { $docSheet->getPageSetup()->setPrintArea(implode(',', $extractedRanges)); // C7:J66,A1:IV2 } break; case pack('C', 0x7): // print titles (repeating rows) // Assuming BIFF8, there are 3 cases // 1. repeating rows // formula looks like this: Sheet!$A$1:$IV$2 // rows 1-2 repeat // 2. repeating columns // formula looks like this: Sheet!$A$1:$B$65536 // columns A-B repeat // 3. both repeating rows and repeating columns // formula looks like this: Sheet!$A$1:$B$65536,Sheet!$A$1:$IV$2 $ranges = explode(',', $definedName['formula']); // FIXME: what if sheetname contains comma? foreach ($ranges as $range) { // $range should look like this one of these // Sheet!$A$1:$B$65536 // Sheet!$A$1:$IV$2 $explodes = explode('!', $range); if (count($explodes) == 2) { if ($docSheet = $this->_phpExcel->getSheetByName($explodes[0])) { $extractedRange = $explodes[1]; $extractedRange = str_replace('$', '', $extractedRange); $coordinateStrings = explode(':', $extractedRange); if (count($coordinateStrings) == 2) { list($firstColumn, $firstRow) = PHPExcel_Cell::coordinateFromString($coordinateStrings[0]); list($lastColumn, $lastRow) = PHPExcel_Cell::coordinateFromString($coordinateStrings[1]); if ($firstColumn == 'A' and $lastColumn == 'IV') { // then we have repeating rows $docSheet->getPageSetup()->setRowsToRepeatAtTop(array($firstRow, $lastRow)); } elseif ($firstRow == 1 and $lastRow == 65536) { // then we have repeating columns $docSheet->getPageSetup()->setColumnsToRepeatAtLeft(array($firstColumn, $lastColumn)); } } } } } break; } } else { // Extract range $explodes = explode('!', $definedName['formula']); if (count($explodes) == 2) { if ($docSheet = $this->_phpExcel->getSheetByName($explodes[0])) { $extractedRange = $explodes[1]; $extractedRange = str_replace('$', '', $extractedRange); $localOnly = $definedName['scope'] == 0 ? false : true; $scope = $definedName['scope'] == 0 ? null : $this->_phpExcel->getSheetByName($this->_sheets[$definedName['scope'] - 1]['name']); $this->_phpExcel->addNamedRange(new PHPExcel_NamedRange((string) $definedName['name'], $docSheet, $extractedRange, $localOnly, $scope)); } } } } return $this->_phpExcel; } /** * Use OLE reader to extract the relevant data streams from the OLE file * * @param string $pFilename */ private function _loadOLE($pFilename) { // OLE reader $ole = new PHPExcel_Shared_OLERead(); // get excel data, $res = $ole->read($pFilename); // Get workbook data: workbook stream + sheet streams $this->_data = $ole->getStream($ole->wrkbook); // Get summary information data $this->_summaryInformation = $ole->getStream($ole->summaryInformation); // Get additional document summary information data $this->_documentSummaryInformation = $ole->getStream($ole->documentSummaryInformation); // Get user-defined property data // $this->_userDefinedProperties = $ole->getUserDefinedProperties(); } /** * Read summary information */ private function _readSummaryInformation() { if (!isset($this->_summaryInformation)) { return; } // offset: 0; size: 2; must be 0xFE 0xFF (UTF-16 LE byte order mark) // offset: 2; size: 2; // offset: 4; size: 2; OS version // offset: 6; size: 2; OS indicator // offset: 8; size: 16 // offset: 24; size: 4; section count $secCount = self::_GetInt4d($this->_summaryInformation, 24); // offset: 28; size: 16; first section's class id: e0 85 9f f2 f9 4f 68 10 ab 91 08 00 2b 27 b3 d9 // offset: 44; size: 4 $secOffset = self::_GetInt4d($this->_summaryInformation, 44); // section header // offset: $secOffset; size: 4; section length $secLength = self::_GetInt4d($this->_summaryInformation, $secOffset); // offset: $secOffset+4; size: 4; property count $countProperties = self::_GetInt4d($this->_summaryInformation, $secOffset + 4); // initialize code page (used to resolve string values) $codePage = 'CP1252'; // offset: ($secOffset+8); size: var // loop through property decarations and properties for ($i = 0; $i < $countProperties; ++$i) { // offset: ($secOffset+8) + (8 * $i); size: 4; property ID $id = self::_GetInt4d($this->_summaryInformation, $secOffset + 8 + 8 * $i); // Use value of property id as appropriate // offset: ($secOffset+12) + (8 * $i); size: 4; offset from beginning of section (48) $offset = self::_GetInt4d($this->_summaryInformation, $secOffset + 12 + 8 * $i); $type = self::_GetInt4d($this->_summaryInformation, $secOffset + $offset); // initialize property value $value = null; // extract property value based on property type switch ($type) { case 0x2: // 2 byte signed integer $value = self::_GetInt2d($this->_summaryInformation, $secOffset + 4 + $offset); break; case 0x3: // 4 byte signed integer $value = self::_GetInt4d($this->_summaryInformation, $secOffset + 4 + $offset); break;
/** * Read summary information */ private function _readSummaryInformation() { if (!isset($this->_summaryInformation)) { return; } // offset: 0; size: 2; must be 0xFE 0xFF (UTF-16 LE byte order mark) // offset: 2; size: 2; // offset: 4; size: 2; OS version // offset: 6; size: 2; OS indicator // offset: 8; size: 16 // offset: 24; size: 4; section count // offset: 28; size: 16; first section's class id: e0 85 9f f2 f9 4f 68 10 ab 91 08 00 2b 27 b3 d9 // offset: 44; size: 4 // section header // offset: 48; size: 4; section length $secLength = $this->_GetInt4d($this->_summaryInformation, 48); // offset: 52; size: 4; property count $countProperties = $this->_GetInt4d($this->_summaryInformation, 52); // initialize code page (used to resolve string values) $codePage = 'CP1252'; // offset: 56; size: var // loop through property decarations and properties for ($i = 0; $i < $countProperties; ++$i) { // offset: 56 + 8 * $i; size: 4; property ID $id = $this->_GetInt4d($this->_summaryInformation, 56 + 8 * $i); // offset: 60 + 8 * $i; size: 4; offset from beginning of section (48) $offset = $this->_GetInt4d($this->_summaryInformation, 60 + 8 * $i); $type = $this->_GetInt4d($this->_summaryInformation, 48 + $offset); // initialize property value $value = null; // extract property value based on property type switch ($type) { case 0x2: // 2 byte signed integer $value = $this->_GetInt2d($this->_summaryInformation, 52 + $offset); break; case 0x3: // 4 byte signed integer $value = $this->_GetInt4d($this->_summaryInformation, 52 + $offset); break; case 0x13: // 4 byte unsigned integer // not needed yet, fix later if necessary break; case 0x1e: // null-terminated string prepended by dword string length $byteLength = $this->_GetInt4d($this->_summaryInformation, 52 + $offset); $value = substr($this->_summaryInformation, 56 + $offset, $byteLength); $value = PHPExcel_Shared_String::ConvertEncoding($value, 'UTF-8', $codePage); $value = rtrim($value); break; case 0x40: // Filetime (64-bit value representing the number of 100-nanosecond intervals since January 1, 1601) // PHP-time $value = PHPExcel_Shared_OLE::OLE2LocalDate(substr($this->_summaryInformation, 52 + $offset, 8)); break; case 0x47: // Clipboard format // not needed yet, fix later if necessary break; } // Use value of property id as appropriate switch ($id) { case 0x1: // Code Page $codePage = PHPExcel_Shared_CodePage::NumberToName($value); break; case 0x2: // Title $this->_phpExcel->getProperties()->setTitle($value); break; case 0x3: // Subject $this->_phpExcel->getProperties()->setSubject($value); break; case 0x4: // Author (Creator) $this->_phpExcel->getProperties()->setCreator($value); break; case 0x5: // Keywords $this->_phpExcel->getProperties()->setKeywords($value); break; case 0x6: // Comments (Description) $this->_phpExcel->getProperties()->setDescription($value); break; case 0x8: // Last Saved By (LastModifiedBy) $this->_phpExcel->getProperties()->setLastModifiedBy($value); break; case 0x9: // Revision // not supported by PHPExcel break; case 0xc: // Created $this->_phpExcel->getProperties()->setCreated($value); break; case 0xd: // Modified $this->_phpExcel->getProperties()->setModified($value); break; case 0x12: // Name of creating application // not supported by PHPExcel break; } } }