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