/**
* Extract range values
*
* @param string &$pRange String based range representation
* @param Worksheet $pSheet Worksheet
* @return mixed Array of values in range if range contains more than one element. Otherwise, a single value is returned.
* @throws Exception
*/
public function extractNamedRange(&$pRange = 'A1', Worksheet $pSheet = null, $resetLog = true)
{
// Return value
$returnValue = array();
// echo 'extractNamedRange('.$pRange.')<br />';
if (!is_null($pSheet)) {
// echo 'Current sheet name is '.$pSheet->getTitle().'<br />';
// echo 'Range reference is '.$pRange.'<br />';
if (strpos($pRange, '!') !== false) {
// echo '$pRange reference includes sheet reference<br />';
$worksheetReference = Worksheet::extractSheetTitle($pRange, true);
$pSheet = $pSheet->getParent()->getSheetByName($worksheetReference[0]);
// echo 'New sheet name is '.$pSheet->getTitle().'<br />';
$pRange = $worksheetReference[1];
// echo 'Adjusted Range reference is '.$pRange.'<br />';
}
// Named range?
$namedRange = NamedRange::resolveRange($pRange, $pSheet);
if (!is_null($namedRange)) {
$pSheet = $namedRange->getWorksheet();
//// echo 'Named Range '.$pRange.' (';
$pRange = $namedRange->getRange();
//// echo $pRange.') is in sheet '.$namedRange->getWorksheet()->getTitle().'<br />';
// if ($pSheet->getTitle() != $namedRange->getWorksheet()->getTitle()) {
// if (!$namedRange->getLocalOnly()) {
// $pSheet = $namedRange->getWorksheet();
// } else {
// return $returnValue;
// }
// }
} else {
return Calculation_Functions::REF();
}
// Extract range
$aReferences = Cell::extractAllCellReferencesInRange($pRange);
if (count($aReferences) == 1) {
list($currentCol, $currentRow) = Cell::coordinateFromString($aReferences[0]);
if ($pSheet->cellExists($aReferences[0])) {
$returnValue[$currentRow][$currentCol] = $pSheet->getCell($aReferences[0])->getCalculatedValue($resetLog);
} else {
$returnValue[$currentRow][$currentCol] = NULL;
}
} else {
// Extract cell data
foreach ($aReferences as $reference) {
// Extract range
list($currentCol, $currentRow) = Cell::coordinateFromString($reference);
// echo 'NAMED RANGE: $currentCol='.$currentCol.' $currentRow='.$currentRow.'<br />';
if ($pSheet->cellExists($reference)) {
$returnValue[$currentRow][$currentCol] = $pSheet->getCell($reference)->getCalculatedValue($resetLog);
} else {
$returnValue[$currentRow][$currentCol] = NULL;
}
}
}
// print_r($returnValue);
// echo '<br />';
}
// Return
return $returnValue;
}
/**
* Update single cell reference
*
* @param string $pCellReference Single cell reference
* @param int $pBefore Insert before this one
* @param int $pNumCols Number of columns to increment
* @param int $pNumRows Number of rows to increment
* @return string Updated cell reference
* @throws Exception
*/
private function updateSingleCellReference($pCellReference = 'A1', $pBefore = 'A1', $pNumCols = 0, $pNumRows = 0)
{
if (strpos($pCellReference, ':') === false && strpos($pCellReference, ',') === false) {
// Get coordinates of $pBefore
list($beforeColumn, $beforeRow) = Cell::coordinateFromString($pBefore);
// Get coordinates of $pCellReference
list($newColumn, $newRow) = Cell::coordinateFromString($pCellReference);
// Verify which parts should be updated
$updateColumn = $newColumn[0] != '$' && $beforeColumn[0] != '$' && Cell::columnIndexFromString($newColumn) >= Cell::columnIndexFromString($beforeColumn);
$updateRow = $newRow[0] != '$' && $beforeRow[0] != '$' && $newRow >= $beforeRow;
// Create new column reference
if ($updateColumn) {
$newColumn = Cell::stringFromColumnIndex(Cell::columnIndexFromString($newColumn) - 1 + $pNumCols);
}
// Create new row reference
if ($updateRow) {
$newRow = $newRow + $pNumRows;
}
// Return new reference
return $newColumn . $newRow;
} else {
throw new Exception("Only single cell references may be passed to this method.");
}
}
/**
* Reads a cell range address in BIFF8 for shared formulas. Uses positive and negative values for row and column
* to indicate offsets from a base cell
* section 3.3.4
*
* @param string $subData
* @param string $baseCell Base cell
* @return string Cell range address
*/
private function _readBIFF8CellRangeAddressB($subData, $baseCell = 'A1')
{
list($baseCol, $baseRow) = Cell::coordinateFromString($baseCell);
$baseCol = Cell::columnIndexFromString($baseCol) - 1;
// TODO: if cell range is just a single cell, should this funciton
// not just return e.g. 'A1' and not 'A1:A1' ?
// offset: 0; size: 2; first row
$frIndex = $this->_GetInt2d($subData, 0);
// adjust below
// offset: 2; size: 2; relative index to first row (0... 65535) should be treated as offset (-32768... 32767)
$lrIndex = $this->_GetInt2d($subData, 2);
// adjust below
// offset: 4; size: 2; first column with relative/absolute flags
// bit: 7-0; mask 0x00FF; column index
$fcIndex = 0xff & $this->_GetInt2d($subData, 4);
// bit: 14; mask 0x4000; (1 = relative column index, 0 = absolute column index)
if (!(0x4000 & $this->_GetInt2d($subData, 4))) {
// absolute column index
$fc = Cell::stringFromColumnIndex($fcIndex);
$fc = '$' . $fc;
} else {
// column offset
$fcIndex = $fcIndex <= 127 ? $fcIndex : $fcIndex - 256;
$fc = Cell::stringFromColumnIndex($baseCol + $fcIndex);
}
// bit: 15; mask 0x8000; (1 = relative row index, 0 = absolute row index)
if (!(0x8000 & $this->_GetInt2d($subData, 4))) {
// absolute row index
$fr = $frIndex + 1;
$fr = '$' . $fr;
} else {
// row offset
$frIndex = $frIndex <= 32767 ? $frIndex : $frIndex - 65536;
$fr = $baseRow + $frIndex;
}
// offset: 6; size: 2; last column with relative/absolute flags
// bit: 7-0; mask 0x00FF; column index
$lcIndex = 0xff & $this->_GetInt2d($subData, 6);
$lcIndex = $lcIndex <= 127 ? $lcIndex : $lcIndex - 256;
$lc = Cell::stringFromColumnIndex($baseCol + $lcIndex);
// bit: 14; mask 0x4000; (1 = relative column index, 0 = absolute column index)
if (!(0x4000 & $this->_GetInt2d($subData, 6))) {
// absolute column index
$lc = Cell::stringFromColumnIndex($lcIndex);
$lc = '$' . $lc;
} else {
// column offset
$lcIndex = $lcIndex <= 127 ? $lcIndex : $lcIndex - 256;
$lc = Cell::stringFromColumnIndex($baseCol + $lcIndex);
}
// bit: 15; mask 0x8000; (1 = relative row index, 0 = absolute row index)
if (!(0x8000 & $this->_GetInt2d($subData, 6))) {
// absolute row index
$lr = $lrIndex + 1;
$lr = '$' . $lr;
} else {
// row offset
$lrIndex = $lrIndex <= 32767 ? $lrIndex : $lrIndex - 65536;
$lr = $baseRow + $lrIndex;
}
return "{$fc}{$fr}:{$lc}{$lr}";
}
/**
* Write the HORIZONTALPAGEBREAKS and VERTICALPAGEBREAKS BIFF records.
*/
private function _writeBreaks()
{
// initialize
$vbreaks = array();
$hbreaks = array();
foreach ($this->_phpSheet->getBreaks() as $cell => $breakType) {
// Fetch coordinates
$coordinates = Cell::coordinateFromString($cell);
// Decide what to do by the type of break
switch ($breakType) {
case Worksheet::BREAK_COLUMN:
// Add to list of vertical breaks
$vbreaks[] = Cell::columnIndexFromString($coordinates[0]) - 1;
break;
case Worksheet::BREAK_ROW:
// Add to list of horizontal breaks
$hbreaks[] = $coordinates[1];
break;
case Worksheet::BREAK_NONE:
default:
// Nothing to do
break;
}
}
//horizontal page breaks
if (count($hbreaks) > 0) {
// Sort and filter array of page breaks
sort($hbreaks, SORT_NUMERIC);
if ($hbreaks[0] == 0) {
// don't use first break if it's 0
array_shift($hbreaks);
}
$record = 0x1b;
// Record identifier
$cbrk = count($hbreaks);
// Number of page breaks
if ($this->_BIFF_version == 0x600) {
$length = 2 + 6 * $cbrk;
// Bytes to follow
} else {
$length = 2 + 2 * $cbrk;
// Bytes to follow
}
$header = pack("vv", $record, $length);
$data = pack("v", $cbrk);
// Append each page break
foreach ($hbreaks as $hbreak) {
if ($this->_BIFF_version == 0x600) {
$data .= pack("vvv", $hbreak, 0x0, 0xff);
} else {
$data .= pack("v", $hbreak);
}
}
$this->_append($header . $data);
}
// vertical page breaks
if (count($vbreaks) > 0) {
// 1000 vertical pagebreaks appears to be an internal Excel 5 limit.
// It is slightly higher in Excel 97/200, approx. 1026
$vbreaks = array_slice($vbreaks, 0, 1000);
// Sort and filter array of page breaks
sort($vbreaks, SORT_NUMERIC);
if ($vbreaks[0] == 0) {
// don't use first break if it's 0
array_shift($vbreaks);
}
$record = 0x1a;
// Record identifier
$cbrk = count($vbreaks);
// Number of page breaks
if ($this->_BIFF_version == 0x600) {
$length = 2 + 6 * $cbrk;
// Bytes to follow
} else {
$length = 2 + 2 * $cbrk;
// Bytes to follow
}
$header = pack("vv", $record, $length);
$data = pack("v", $cbrk);
// Append each page break
foreach ($vbreaks as $vbreak) {
if ($this->_BIFF_version == 0x600) {
$data .= pack("vvv", $vbreak, 0x0, 0xffff);
} else {
$data .= pack("v", $vbreak);
}
}
$this->_append($header . $data);
}
}
/**
* Apply styles from array
*
* <code>
* $objPHPExcel->getActiveSheet()->getStyle('B2')->applyFromArray(
* array(
* 'font' => array(
* 'name' => 'Arial',
* 'bold' => true,
* 'italic' => false,
* 'underline' => \PHPExcel\Style\Font::UNDERLINE_DOUBLE,
* 'strike' => false,
* 'color' => array(
* 'rgb' => '808080'
* )
* ),
* 'borders' => array(
* 'bottom' => array(
* 'style' => \PHPExcel\Style\Border::BORDER_DASHDOT,
* 'color' => array(
* 'rgb' => '808080'
* )
* ),
* 'top' => array(
* 'style' => \PHPExcel\Style\Border::BORDER_DASHDOT,
* 'color' => array(
* 'rgb' => '808080'
* )
* )
* ),
* 'quotePrefix' => true
* )
* );
* </code>
*
* @param array $pStyles Array containing style information
* @param boolean $pAdvanced Advanced mode for setting borders.
* @throws Exception
* @return Style
*/
public function applyFromArray($pStyles = null, $pAdvanced = true)
{
if (is_array($pStyles)) {
if ($this->isSupervisor) {
$pRange = $this->getSelectedCells();
// Uppercase coordinate
$pRange = strtoupper($pRange);
// Is it a cell range or a single cell?
if (strpos($pRange, ':') === false) {
$rangeA = $pRange;
$rangeB = $pRange;
} else {
list($rangeA, $rangeB) = explode(':', $pRange);
}
// Calculate range outer borders
$rangeStart = Cell::coordinateFromString($rangeA);
$rangeEnd = Cell::coordinateFromString($rangeB);
// Translate column into index
$rangeStart[0] = Cell::columnIndexFromString($rangeStart[0]) - 1;
$rangeEnd[0] = Cell::columnIndexFromString($rangeEnd[0]) - 1;
// Make sure we can loop upwards on rows and columns
if ($rangeStart[0] > $rangeEnd[0] && $rangeStart[1] > $rangeEnd[1]) {
$tmp = $rangeStart;
$rangeStart = $rangeEnd;
$rangeEnd = $tmp;
}
// ADVANCED MODE:
if ($pAdvanced && isset($pStyles['borders'])) {
// 'allborders' is a shorthand property for 'outline' and 'inside' and
// it applies to components that have not been set explicitly
if (isset($pStyles['borders']['allborders'])) {
foreach (array('outline', 'inside') as $component) {
if (!isset($pStyles['borders'][$component])) {
$pStyles['borders'][$component] = $pStyles['borders']['allborders'];
}
}
unset($pStyles['borders']['allborders']);
// not needed any more
}
// 'outline' is a shorthand property for 'top', 'right', 'bottom', 'left'
// it applies to components that have not been set explicitly
if (isset($pStyles['borders']['outline'])) {
foreach (array('top', 'right', 'bottom', 'left') as $component) {
if (!isset($pStyles['borders'][$component])) {
$pStyles['borders'][$component] = $pStyles['borders']['outline'];
}
}
unset($pStyles['borders']['outline']);
// not needed any more
}
// 'inside' is a shorthand property for 'vertical' and 'horizontal'
// it applies to components that have not been set explicitly
if (isset($pStyles['borders']['inside'])) {
foreach (array('vertical', 'horizontal') as $component) {
if (!isset($pStyles['borders'][$component])) {
$pStyles['borders'][$component] = $pStyles['borders']['inside'];
}
}
unset($pStyles['borders']['inside']);
// not needed any more
}
// width and height characteristics of selection, 1, 2, or 3 (for 3 or more)
$xMax = min($rangeEnd[0] - $rangeStart[0] + 1, 3);
$yMax = min($rangeEnd[1] - $rangeStart[1] + 1, 3);
// loop through up to 3 x 3 = 9 regions
for ($x = 1; $x <= $xMax; ++$x) {
// start column index for region
$colStart = $x == 3 ? Cell::stringFromColumnIndex($rangeEnd[0]) : Cell::stringFromColumnIndex($rangeStart[0] + $x - 1);
// end column index for region
$colEnd = $x == 1 ? Cell::stringFromColumnIndex($rangeStart[0]) : Cell::stringFromColumnIndex($rangeEnd[0] - $xMax + $x);
for ($y = 1; $y <= $yMax; ++$y) {
// which edges are touching the region
$edges = array();
if ($x == 1) {
// are we at left edge
$edges[] = 'left';
}
if ($x == $xMax) {
// are we at right edge
$edges[] = 'right';
}
if ($y == 1) {
// are we at top edge?
$edges[] = 'top';
}
if ($y == $yMax) {
// are we at bottom edge?
$edges[] = 'bottom';
}
// start row index for region
$rowStart = $y == 3 ? $rangeEnd[1] : $rangeStart[1] + $y - 1;
// end row index for region
$rowEnd = $y == 1 ? $rangeStart[1] : $rangeEnd[1] - $yMax + $y;
// build range for region
$range = $colStart . $rowStart . ':' . $colEnd . $rowEnd;
// retrieve relevant style array for region
$regionStyles = $pStyles;
unset($regionStyles['borders']['inside']);
// what are the inner edges of the region when looking at the selection
$innerEdges = array_diff(array('top', 'right', 'bottom', 'left'), $edges);
// inner edges that are not touching the region should take the 'inside' border properties if they have been set
foreach ($innerEdges as $innerEdge) {
switch ($innerEdge) {
case 'top':
case 'bottom':
// should pick up 'horizontal' border property if set
if (isset($pStyles['borders']['horizontal'])) {
$regionStyles['borders'][$innerEdge] = $pStyles['borders']['horizontal'];
} else {
unset($regionStyles['borders'][$innerEdge]);
}
break;
case 'left':
case 'right':
// should pick up 'vertical' border property if set
if (isset($pStyles['borders']['vertical'])) {
$regionStyles['borders'][$innerEdge] = $pStyles['borders']['vertical'];
} else {
unset($regionStyles['borders'][$innerEdge]);
}
break;
}
}
// apply region style to region by calling applyFromArray() in simple mode
$this->getActiveSheet()->getStyle($range)->applyFromArray($regionStyles, false);
}
}
return $this;
}
// SIMPLE MODE:
// Selection type, inspect
if (preg_match('/^[A-Z]+1:[A-Z]+1048576$/', $pRange)) {
$selectionType = 'COLUMN';
} elseif (preg_match('/^A[0-9]+:XFD[0-9]+$/', $pRange)) {
$selectionType = 'ROW';
} else {
$selectionType = 'CELL';
}
// First loop through columns, rows, or cells to find out which styles are affected by this operation
switch ($selectionType) {
case 'COLUMN':
$oldXfIndexes = array();
for ($col = $rangeStart[0]; $col <= $rangeEnd[0]; ++$col) {
$oldXfIndexes[$this->getActiveSheet()->getColumnDimensionByColumn($col)->getXfIndex()] = true;
}
break;
case 'ROW':
$oldXfIndexes = array();
for ($row = $rangeStart[1]; $row <= $rangeEnd[1]; ++$row) {
if ($this->getActiveSheet()->getRowDimension($row)->getXfIndex() == null) {
$oldXfIndexes[0] = true;
// row without explicit style should be formatted based on default style
} else {
$oldXfIndexes[$this->getActiveSheet()->getRowDimension($row)->getXfIndex()] = true;
}
}
break;
case 'CELL':
$oldXfIndexes = array();
for ($col = $rangeStart[0]; $col <= $rangeEnd[0]; ++$col) {
for ($row = $rangeStart[1]; $row <= $rangeEnd[1]; ++$row) {
$oldXfIndexes[$this->getActiveSheet()->getCellByColumnAndRow($col, $row)->getXfIndex()] = true;
}
}
break;
}
// clone each of the affected styles, apply the style array, and add the new styles to the workbook
$workbook = $this->getActiveSheet()->getParent();
foreach ($oldXfIndexes as $oldXfIndex => $dummy) {
$style = $workbook->getCellXfByIndex($oldXfIndex);
$newStyle = clone $style;
$newStyle->applyFromArray($pStyles);
if ($existingStyle = $workbook->getCellXfByHashCode($newStyle->getHashCode())) {
// there is already such cell Xf in our collection
$newXfIndexes[$oldXfIndex] = $existingStyle->getIndex();
} else {
// we don't have such a cell Xf, need to add
$workbook->addCellXf($newStyle);
$newXfIndexes[$oldXfIndex] = $newStyle->getIndex();
}
}
// Loop through columns, rows, or cells again and update the XF index
switch ($selectionType) {
case 'COLUMN':
for ($col = $rangeStart[0]; $col <= $rangeEnd[0]; ++$col) {
$columnDimension = $this->getActiveSheet()->getColumnDimensionByColumn($col);
$oldXfIndex = $columnDimension->getXfIndex();
$columnDimension->setXfIndex($newXfIndexes[$oldXfIndex]);
}
break;
case 'ROW':
for ($row = $rangeStart[1]; $row <= $rangeEnd[1]; ++$row) {
$rowDimension = $this->getActiveSheet()->getRowDimension($row);
$oldXfIndex = $rowDimension->getXfIndex() === null ? 0 : $rowDimension->getXfIndex();
// row without explicit style should be formatted based on default style
$rowDimension->setXfIndex($newXfIndexes[$oldXfIndex]);
}
break;
case 'CELL':
for ($col = $rangeStart[0]; $col <= $rangeEnd[0]; ++$col) {
for ($row = $rangeStart[1]; $row <= $rangeEnd[1]; ++$row) {
$cell = $this->getActiveSheet()->getCellByColumnAndRow($col, $row);
$oldXfIndex = $cell->getXfIndex();
$cell->setXfIndex($newXfIndexes[$oldXfIndex]);
}
}
break;
}
} else {
// not a supervisor, just apply the style array directly on style object
if (array_key_exists('fill', $pStyles)) {
$this->getFill()->applyFromArray($pStyles['fill']);
}
if (array_key_exists('font', $pStyles)) {
$this->getFont()->applyFromArray($pStyles['font']);
}
if (array_key_exists('borders', $pStyles)) {
$this->getBorders()->applyFromArray($pStyles['borders']);
}
if (array_key_exists('alignment', $pStyles)) {
$this->getAlignment()->applyFromArray($pStyles['alignment']);
}
if (array_key_exists('numberformat', $pStyles)) {
$this->getNumberFormat()->applyFromArray($pStyles['numberformat']);
}
if (array_key_exists('protection', $pStyles)) {
$this->getProtection()->applyFromArray($pStyles['protection']);
}
if (array_key_exists('quotePrefix', $pStyles)) {
$this->quotePrefix = $pStyles['quotePrefix'];
}
}
} else {
throw new Exception("Invalid style array passed.");
}
return $this;
}
/**
* Writes all the DEFINEDNAME records (BIFF8).
* So far this is only used for repeating rows/columns (print titles) and print areas
*/
private function _writeAllDefinedNamesBiff8()
{
$chunk = '';
// Named ranges
if (count($this->_phpExcel->getNamedRanges()) > 0) {
// Loop named ranges
$namedRanges = $this->_phpExcel->getNamedRanges();
foreach ($namedRanges as $namedRange) {
// Create absolute coordinate
$range = Cell::splitRange($namedRange->getRange());
for ($i = 0; $i < count($range); $i++) {
$range[$i][0] = '\'' . str_replace("'", "''", $namedRange->getWorksheet()->getTitle()) . '\'!' . Cell::absoluteCoordinate($range[$i][0]);
if (isset($range[$i][1])) {
$range[$i][1] = Cell::absoluteCoordinate($range[$i][1]);
}
}
$range = Cell::buildRange($range);
// e.g. Sheet1!$A$1:$B$2
// parse formula
try {
$error = $this->_parser->parse($range);
$formulaData = $this->_parser->toReversePolish();
// make sure tRef3d is of type tRef3dR (0x3A)
if (isset($formulaData[0]) and ($formulaData[0] == "z" or $formulaData[0] == "Z")) {
$formulaData = ":" . substr($formulaData, 1);
}
if ($namedRange->getLocalOnly()) {
// local scope
$scope = $this->_phpExcel->getIndex($namedRange->getScope()) + 1;
} else {
// global scope
$scope = 0;
}
$chunk .= $this->writeData($this->_writeDefinedNameBiff8($namedRange->getName(), $formulaData, $scope, false));
} catch (Exception $e) {
// do nothing
}
}
}
// total number of sheets
$total_worksheets = $this->_phpExcel->getSheetCount();
// write the print titles (repeating rows, columns), if any
for ($i = 0; $i < $total_worksheets; ++$i) {
$sheetSetup = $this->_phpExcel->getSheet($i)->getPageSetup();
// simultaneous repeatColumns repeatRows
if ($sheetSetup->isColumnsToRepeatAtLeftSet() && $sheetSetup->isRowsToRepeatAtTopSet()) {
$repeat = $sheetSetup->getColumnsToRepeatAtLeft();
$colmin = Cell::columnIndexFromString($repeat[0]) - 1;
$colmax = Cell::columnIndexFromString($repeat[1]) - 1;
$repeat = $sheetSetup->getRowsToRepeatAtTop();
$rowmin = $repeat[0] - 1;
$rowmax = $repeat[1] - 1;
// construct formula data manually
$formulaData = pack('Cv', 0x29, 0x17);
// tMemFunc
$formulaData .= pack('Cvvvvv', 0x3b, $i, 0, 65535, $colmin, $colmax);
// tArea3d
$formulaData .= pack('Cvvvvv', 0x3b, $i, $rowmin, $rowmax, 0, 255);
// tArea3d
$formulaData .= pack('C', 0x10);
// tList
// store the DEFINEDNAME record
$chunk .= $this->writeData($this->_writeDefinedNameBiff8(pack('C', 0x7), $formulaData, $i + 1, true));
// (exclusive) either repeatColumns or repeatRows
} else {
if ($sheetSetup->isColumnsToRepeatAtLeftSet() || $sheetSetup->isRowsToRepeatAtTopSet()) {
// Columns to repeat
if ($sheetSetup->isColumnsToRepeatAtLeftSet()) {
$repeat = $sheetSetup->getColumnsToRepeatAtLeft();
$colmin = Cell::columnIndexFromString($repeat[0]) - 1;
$colmax = Cell::columnIndexFromString($repeat[1]) - 1;
} else {
$colmin = 0;
$colmax = 255;
}
// Rows to repeat
if ($sheetSetup->isRowsToRepeatAtTopSet()) {
$repeat = $sheetSetup->getRowsToRepeatAtTop();
$rowmin = $repeat[0] - 1;
$rowmax = $repeat[1] - 1;
} else {
$rowmin = 0;
$rowmax = 65535;
}
// construct formula data manually because parser does not recognize absolute 3d cell references
$formulaData = pack('Cvvvvv', 0x3b, $i, $rowmin, $rowmax, $colmin, $colmax);
// store the DEFINEDNAME record
$chunk .= $this->writeData($this->_writeDefinedNameBiff8(pack('C', 0x7), $formulaData, $i + 1, true));
}
}
}
// write the print areas, if any
for ($i = 0; $i < $total_worksheets; ++$i) {
$sheetSetup = $this->_phpExcel->getSheet($i)->getPageSetup();
if ($sheetSetup->isPrintAreaSet()) {
// Print area, e.g. A3:J6,H1:X20
$printArea = Cell::splitRange($sheetSetup->getPrintArea());
$countPrintArea = count($printArea);
$formulaData = '';
for ($j = 0; $j < $countPrintArea; ++$j) {
$printAreaRect = $printArea[$j];
// e.g. A3:J6
$printAreaRect[0] = Cell::coordinateFromString($printAreaRect[0]);
$printAreaRect[1] = Cell::coordinateFromString($printAreaRect[1]);
$print_rowmin = $printAreaRect[0][1] - 1;
$print_rowmax = $printAreaRect[1][1] - 1;
$print_colmin = Cell::columnIndexFromString($printAreaRect[0][0]) - 1;
$print_colmax = Cell::columnIndexFromString($printAreaRect[1][0]) - 1;
// construct formula data manually because parser does not recognize absolute 3d cell references
$formulaData .= pack('Cvvvvv', 0x3b, $i, $print_rowmin, $print_rowmax, $print_colmin, $print_colmax);
if ($j > 0) {
$formulaData .= pack('C', 0x10);
// list operator token ','
}
}
// store the DEFINEDNAME record
$chunk .= $this->writeData($this->_writeDefinedNameBiff8(pack('C', 0x6), $formulaData, $i + 1, true));
}
}
return $chunk;
}
/**
* Write drawings to XML format
*
* @param Shared_XMLWriter $objWriter XML Writer
* @param Worksheet_BaseDrawing $pDrawing
* @param int $pRelationId
* @throws Exception
*/
public function _writeDrawing(Shared_XMLWriter $objWriter = null, Worksheet_BaseDrawing $pDrawing = null, $pRelationId = -1)
{
if ($pRelationId >= 0) {
// xdr:oneCellAnchor
$objWriter->startElement('xdr:oneCellAnchor');
// Image location
$aCoordinates = Cell::coordinateFromString($pDrawing->getCoordinates());
$aCoordinates[0] = Cell::columnIndexFromString($aCoordinates[0]);
// xdr:from
$objWriter->startElement('xdr:from');
$objWriter->writeElement('xdr:col', $aCoordinates[0] - 1);
$objWriter->writeElement('xdr:colOff', Shared_Drawing::pixelsToEMU($pDrawing->getOffsetX()));
$objWriter->writeElement('xdr:row', $aCoordinates[1] - 1);
$objWriter->writeElement('xdr:rowOff', Shared_Drawing::pixelsToEMU($pDrawing->getOffsetY()));
$objWriter->endElement();
// xdr:ext
$objWriter->startElement('xdr:ext');
$objWriter->writeAttribute('cx', Shared_Drawing::pixelsToEMU($pDrawing->getWidth()));
$objWriter->writeAttribute('cy', Shared_Drawing::pixelsToEMU($pDrawing->getHeight()));
$objWriter->endElement();
// xdr:pic
$objWriter->startElement('xdr:pic');
// xdr:nvPicPr
$objWriter->startElement('xdr:nvPicPr');
// xdr:cNvPr
$objWriter->startElement('xdr:cNvPr');
$objWriter->writeAttribute('id', $pRelationId);
$objWriter->writeAttribute('name', $pDrawing->getName());
$objWriter->writeAttribute('descr', $pDrawing->getDescription());
$objWriter->endElement();
// xdr:cNvPicPr
$objWriter->startElement('xdr:cNvPicPr');
// a:picLocks
$objWriter->startElement('a:picLocks');
$objWriter->writeAttribute('noChangeAspect', '1');
$objWriter->endElement();
$objWriter->endElement();
$objWriter->endElement();
// xdr:blipFill
$objWriter->startElement('xdr:blipFill');
// a:blip
$objWriter->startElement('a:blip');
$objWriter->writeAttribute('xmlns:r', 'http://schemas.openxmlformats.org/officeDocument/2006/relationships');
$objWriter->writeAttribute('r:embed', 'rId' . $pRelationId);
$objWriter->endElement();
// a:stretch
$objWriter->startElement('a:stretch');
$objWriter->writeElement('a:fillRect', null);
$objWriter->endElement();
$objWriter->endElement();
// xdr:spPr
$objWriter->startElement('xdr:spPr');
// a:xfrm
$objWriter->startElement('a:xfrm');
$objWriter->writeAttribute('rot', Shared_Drawing::degreesToAngle($pDrawing->getRotation()));
$objWriter->endElement();
// a:prstGeom
$objWriter->startElement('a:prstGeom');
$objWriter->writeAttribute('prst', 'rect');
// a:avLst
$objWriter->writeElement('a:avLst', null);
$objWriter->endElement();
// // a:solidFill
// $objWriter->startElement('a:solidFill');
// // a:srgbClr
// $objWriter->startElement('a:srgbClr');
// $objWriter->writeAttribute('val', 'FFFFFF');
///* SHADE
// // a:shade
// $objWriter->startElement('a:shade');
// $objWriter->writeAttribute('val', '85000');
// $objWriter->endElement();
//*/
// $objWriter->endElement();
// $objWriter->endElement();
/*
// a:ln
$objWriter->startElement('a:ln');
$objWriter->writeAttribute('w', '88900');
$objWriter->writeAttribute('cap', 'sq');
// a:solidFill
$objWriter->startElement('a:solidFill');
// a:srgbClr
$objWriter->startElement('a:srgbClr');
$objWriter->writeAttribute('val', 'FFFFFF');
$objWriter->endElement();
$objWriter->endElement();
// a:miter
$objWriter->startElement('a:miter');
$objWriter->writeAttribute('lim', '800000');
$objWriter->endElement();
$objWriter->endElement();
*/
if ($pDrawing->getShadow()->getVisible()) {
// a:effectLst
$objWriter->startElement('a:effectLst');
// a:outerShdw
$objWriter->startElement('a:outerShdw');
$objWriter->writeAttribute('blurRad', Shared_Drawing::pixelsToEMU($pDrawing->getShadow()->getBlurRadius()));
$objWriter->writeAttribute('dist', Shared_Drawing::pixelsToEMU($pDrawing->getShadow()->getDistance()));
$objWriter->writeAttribute('dir', Shared_Drawing::degreesToAngle($pDrawing->getShadow()->getDirection()));
$objWriter->writeAttribute('algn', $pDrawing->getShadow()->getAlignment());
$objWriter->writeAttribute('rotWithShape', '0');
// a:srgbClr
$objWriter->startElement('a:srgbClr');
$objWriter->writeAttribute('val', $pDrawing->getShadow()->getColor()->getRGB());
// a:alpha
$objWriter->startElement('a:alpha');
$objWriter->writeAttribute('val', $pDrawing->getShadow()->getAlpha() * 1000);
$objWriter->endElement();
$objWriter->endElement();
$objWriter->endElement();
$objWriter->endElement();
}
/*
// a:scene3d
$objWriter->startElement('a:scene3d');
// a:camera
$objWriter->startElement('a:camera');
$objWriter->writeAttribute('prst', 'orthographicFront');
$objWriter->endElement();
// a:lightRig
$objWriter->startElement('a:lightRig');
$objWriter->writeAttribute('rig', 'twoPt');
$objWriter->writeAttribute('dir', 't');
// a:rot
$objWriter->startElement('a:rot');
$objWriter->writeAttribute('lat', '0');
$objWriter->writeAttribute('lon', '0');
$objWriter->writeAttribute('rev', '0');
$objWriter->endElement();
$objWriter->endElement();
$objWriter->endElement();
*/
/*
// a:sp3d
$objWriter->startElement('a:sp3d');
// a:bevelT
$objWriter->startElement('a:bevelT');
$objWriter->writeAttribute('w', '25400');
$objWriter->writeAttribute('h', '19050');
$objWriter->endElement();
// a:contourClr
$objWriter->startElement('a:contourClr');
// a:srgbClr
$objWriter->startElement('a:srgbClr');
$objWriter->writeAttribute('val', 'FFFFFF');
$objWriter->endElement();
$objWriter->endElement();
$objWriter->endElement();
*/
$objWriter->endElement();
$objWriter->endElement();
// xdr:clientData
$objWriter->writeElement('xdr:clientData', null);
$objWriter->endElement();
} else {
throw new Exception("Invalid parameters passed.");
}
}
/**
* Extract all cell references in range
*
* @param string $pRange Range (e.g. A1 or A1:A10 or A1:A10 A100:A1000)
* @return array Array containing single cell references
*/
public static function extractAllCellReferencesInRange($pRange = 'A1')
{
// Returnvalue
$returnValue = array();
// Explode spaces
$aExplodeSpaces = explode(' ', str_replace('$', '', strtoupper($pRange)));
foreach ($aExplodeSpaces as $explodedSpaces) {
// Single cell?
if (strpos($explodedSpaces, ':') === false && strpos($explodedSpaces, ',') === false) {
$col = 'A';
$row = 1;
list($col, $row) = Cell::coordinateFromString($explodedSpaces);
if (strlen($col) <= 2) {
$returnValue[] = $explodedSpaces;
}
continue;
}
// Range...
$range = Cell::splitRange($explodedSpaces);
for ($i = 0; $i < count($range); ++$i) {
// Single cell?
if (count($range[$i]) == 1) {
$col = 'A';
$row = 1;
list($col, $row) = Cell::coordinateFromString($range[$i]);
if (strlen($col) <= 2) {
$returnValue[] = $explodedSpaces;
}
}
// Range...
$rangeStart = $rangeEnd = '';
$startingCol = $startingRow = $endingCol = $endingRow = 0;
list($rangeStart, $rangeEnd) = $range[$i];
list($startingCol, $startingRow) = Cell::coordinateFromString($rangeStart);
list($endingCol, $endingRow) = Cell::coordinateFromString($rangeEnd);
// Conversions...
$startingCol = Cell::columnIndexFromString($startingCol);
$endingCol = Cell::columnIndexFromString($endingCol);
// Current data
$currentCol = --$startingCol;
$currentRow = $startingRow;
// Loop cells
while ($currentCol < $endingCol) {
$loopColumn = Cell::stringFromColumnIndex($currentCol);
while ($currentRow <= $endingRow) {
$returnValue[] = $loopColumn . $currentRow;
++$currentRow;
}
++$currentCol;
$currentRow = $startingRow;
}
}
}
// Return value
return $returnValue;
}
/**
* Create array from worksheet
*
* @param mixed $nullValue Value treated as "null"
* @param boolean $calculateFormulas Should formulas be calculated?
* @return array
*/
public function toArray($nullValue = null, $calculateFormulas = true)
{
// Returnvalue
$returnValue = array();
// Garbage collect...
$this->garbageCollect();
// Get worksheet dimension
$dimension = explode(':', $this->calculateWorksheetDimension());
$dimension[0] = Cell::coordinateFromString($dimension[0]);
$dimension[0][0] = Cell::columnIndexFromString($dimension[0][0]) - 1;
$dimension[1] = Cell::coordinateFromString($dimension[1]);
$dimension[1][0] = Cell::columnIndexFromString($dimension[1][0]) - 1;
// Loop through cells
for ($row = $dimension[0][1]; $row <= $dimension[1][1]; ++$row) {
for ($column = $dimension[0][0]; $column <= $dimension[1][0]; ++$column) {
// Cell exists?
if ($this->cellExistsByColumnAndRow($column, $row)) {
$cell = $this->getCellByColumnAndRow($column, $row);
if ($cell->getValue() instanceof RichText) {
$returnValue[$row][$column] = $cell->getValue()->getPlainText();
} else {
if ($calculateFormulas) {
$returnValue[$row][$column] = $cell->getCalculatedValue();
} else {
$returnValue[$row][$column] = $cell->getValue();
}
}
$style = $this->_parent->getCellXfByIndex($cell->getXfIndex());
$returnValue[$row][$column] = Style_NumberFormat::toFormattedString($returnValue[$row][$column], $style->getNumberFormat()->getFormatCode());
} else {
$returnValue[$row][$column] = $nullValue;
}
}
}
// Return
return $returnValue;
}
/**
* Convert 1-cell anchor coordinates to 2-cell anchor coordinates
* This function is ported from PEAR Spreadsheet_Writer_Excel with small modifications
*
* Calculate the vertices that define the position of the image as required by
* the OBJ record.
*
* +------------+------------+
* | A | B |
* +-----+------------+------------+
* | |(x1,y1) | |
* | 1 |(A1)._______|______ |
* | | | | |
* | | | | |
* +-----+----| BITMAP |-----+
* | | | | |
* | 2 | |______________. |
* | | | (B2)|
* | | | (x2,y2)|
* +---- +------------+------------+
*
* Example of a bitmap that covers some of the area from cell A1 to cell B2.
*
* Based on the width and height of the bitmap we need to calculate 8 vars:
* $col_start, $row_start, $col_end, $row_end, $x1, $y1, $x2, $y2.
* The width and height of the cells are also variable and have to be taken into
* account.
* The values of $col_start and $row_start are passed in from the calling
* function. The values of $col_end and $row_end are calculated by subtracting
* the width and height of the bitmap from the width and height of the
* underlying cells.
* The vertices are expressed as a percentage of the underlying cell width as
* follows (rhs values are in pixels):
*
* x1 = X / W *1024
* y1 = Y / H *256
* x2 = (X-1) / W *1024
* y2 = (Y-1) / H *256
*
* Where: X is distance from the left side of the underlying cell
* Y is distance from the top of the underlying cell
* W is the width of the cell
* H is the height of the cell
*
* @param Worksheet $sheet
* @param string $coordinates E.g. 'A1'
* @param integer $offsetX Horizontal offset in pixels
* @param integer $offsetY Vertical offset in pixels
* @param integer $width Width in pixels
* @param integer $height Height in pixels
* @return array
*/
public static function oneAnchor2twoAnchor($sheet, $coordinates, $offsetX, $offsetY, $width, $height)
{
list($column, $row) = Cell::coordinateFromString($coordinates);
$col_start = Cell::columnIndexFromString($column) - 1;
$row_start = $row - 1;
$x1 = $offsetX;
$y1 = $offsetY;
// Initialise end cell to the same as the start cell
$col_end = $col_start;
// Col containing lower right corner of object
$row_end = $row_start;
// Row containing bottom right corner of object
// Zero the specified offset if greater than the cell dimensions
if ($x1 >= self::sizeCol($sheet, Cell::stringFromColumnIndex($col_start))) {
$x1 = 0;
}
if ($y1 >= self::sizeRow($sheet, $row_start + 1)) {
$y1 = 0;
}
$width = $width + $x1 - 1;
$height = $height + $y1 - 1;
// Subtract the underlying cell widths to find the end cell of the image
while ($width >= self::sizeCol($sheet, Cell::stringFromColumnIndex($col_end))) {
$width -= self::sizeCol($sheet, Cell::stringFromColumnIndex($col_end));
++$col_end;
}
// Subtract the underlying cell heights to find the end cell of the image
while ($height >= self::sizeRow($sheet, $row_end + 1)) {
$height -= self::sizeRow($sheet, $row_end + 1);
++$row_end;
}
// Bitmap isn't allowed to start or finish in a hidden cell, i.e. a cell
// with zero height or width.
if (self::sizeCol($sheet, Cell::stringFromColumnIndex($col_start)) == 0) {
return;
}
if (self::sizeCol($sheet, Cell::stringFromColumnIndex($col_end)) == 0) {
return;
}
if (self::sizeRow($sheet, $row_start + 1) == 0) {
return;
}
if (self::sizeRow($sheet, $row_end + 1) == 0) {
return;
}
// Convert the pixel values to the percentage value expected by Excel
$x1 = $x1 / self::sizeCol($sheet, Cell::stringFromColumnIndex($col_start)) * 1024;
$y1 = $y1 / self::sizeRow($sheet, $row_start + 1) * 256;
$x2 = ($width + 1) / self::sizeCol($sheet, Cell::stringFromColumnIndex($col_end)) * 1024;
// Distance to right side of object
$y2 = ($height + 1) / self::sizeRow($sheet, $row_end + 1) * 256;
// Distance to bottom of object
$startCoordinates = Cell::stringFromColumnIndex($col_start) . ($row_start + 1);
$endCoordinates = Cell::stringFromColumnIndex($col_end) . ($row_end + 1);
$twoAnchor = array('startCoordinates' => $startCoordinates, 'startOffsetX' => $x1, 'startOffsetY' => $y1, 'endCoordinates' => $endCoordinates, 'endOffsetX' => $x2, 'endOffsetY' => $y2);
return $twoAnchor;
}
/**
* Write VML comment to XML format
*
* @param Shared_XMLWriter $objWriter XML Writer
* @param string $pCellReference Cell reference
* @param Comment $pComment Comment
* @throws Exception
*/
public function _writeVMLComment(Shared_XMLWriter $objWriter = null, $pCellReference = 'A1', Comment $pComment = null)
{
// Metadata
list($column, $row) = Cell::coordinateFromString($pCellReference);
$column = Cell::columnIndexFromString($column);
$id = 1024 + $column + $row;
$id = substr($id, 0, 4);
// v:shape
$objWriter->startElement('v:shape');
$objWriter->writeAttribute('id', '_x0000_s' . $id);
$objWriter->writeAttribute('type', '#_x0000_t202');
$objWriter->writeAttribute('style', 'position:absolute;margin-left:' . $pComment->getMarginLeft() . ';margin-top:' . $pComment->getMarginTop() . ';width:' . $pComment->getWidth() . ';height:' . $pComment->getHeight() . ';z-index:1;visibility:' . ($pComment->getVisible() ? 'visible' : 'hidden'));
$objWriter->writeAttribute('fillcolor', '#' . $pComment->getFillColor()->getRGB());
$objWriter->writeAttribute('o:insetmode', 'auto');
// v:fill
$objWriter->startElement('v:fill');
$objWriter->writeAttribute('color2', '#' . $pComment->getFillColor()->getRGB());
$objWriter->endElement();
// v:shadow
$objWriter->startElement('v:shadow');
$objWriter->writeAttribute('on', 't');
$objWriter->writeAttribute('color', 'black');
$objWriter->writeAttribute('obscured', 't');
$objWriter->endElement();
// v:path
$objWriter->startElement('v:path');
$objWriter->writeAttribute('o:connecttype', 'none');
$objWriter->endElement();
// v:textbox
$objWriter->startElement('v:textbox');
$objWriter->writeAttribute('style', 'mso-direction-alt:auto');
// div
$objWriter->startElement('div');
$objWriter->writeAttribute('style', 'text-align:left');
$objWriter->endElement();
$objWriter->endElement();
// x:ClientData
$objWriter->startElement('x:ClientData');
$objWriter->writeAttribute('ObjectType', 'Note');
// x:MoveWithCells
$objWriter->writeElement('x:MoveWithCells', '');
// x:SizeWithCells
$objWriter->writeElement('x:SizeWithCells', '');
// x:Anchor
//$objWriter->writeElement('x:Anchor', $column . ', 15, ' . ($row - 2) . ', 10, ' . ($column + 4) . ', 15, ' . ($row + 5) . ', 18');
// x:AutoFill
$objWriter->writeElement('x:AutoFill', 'False');
// x:Row
$objWriter->writeElement('x:Row', $row - 1);
// x:Column
$objWriter->writeElement('x:Column', $column - 1);
$objWriter->endElement();
$objWriter->endElement();
}
/**
* Fill worksheet from values in array
*
* @param array $source Source array
* @param mixed $nullValue Value in source array that stands for blank cell
* @param string $startCell Insert array starting from this cell address as the top left coordinate
* @param boolean $strictNullComparison Apply strict comparison when testing for null values in the array
* @throws Exception
* @return Worksheet
*/
public function fromArray($source = null, $nullValue = null, $startCell = 'A1', $strictNullComparison = false)
{
if (is_array($source)) {
// Convert a 1-D array to 2-D (for ease of looping)
if (!is_array(end($source))) {
$source = array($source);
}
// start coordinate
list($startColumn, $startRow) = Cell::coordinateFromString($startCell);
// Loop through $source
foreach ($source as $rowData) {
$currentColumn = $startColumn;
foreach ($rowData as $cellValue) {
if ($strictNullComparison) {
if ($cellValue !== $nullValue) {
// Set cell value
$this->getCell($currentColumn . $startRow)->setValue($cellValue);
}
} else {
if ($cellValue != $nullValue) {
// Set cell value
$this->getCell($currentColumn . $startRow)->setValue($cellValue);
}
}
++$currentColumn;
}
++$startRow;
}
} else {
throw new Exception("Parameter \$source should be an array.");
}
return $this;
}
/**
* Calculate information about HTML colspan and rowspan which is not always the same as Excel's
*/
private function _calculateSpans()
{
// Identify all cells that should be omitted in HTML due to cell merge.
// In HTML only the upper-left cell should be written and it should have
// appropriate rowspan / colspan attribute
$sheetIndexes = $this->_sheetIndex !== null ? array($this->_sheetIndex) : range(0, $this->_phpExcel->getSheetCount() - 1);
foreach ($sheetIndexes as $sheetIndex) {
$sheet = $this->_phpExcel->getSheet($sheetIndex);
$candidateSpannedRow = array();
// loop through all Excel merged cells
foreach ($sheet->getMergeCells() as $cells) {
list($cells, ) = Cell::splitRange($cells);
$first = $cells[0];
$last = $cells[1];
list($fc, $fr) = Cell::coordinateFromString($first);
$fc = Cell::columnIndexFromString($fc) - 1;
list($lc, $lr) = Cell::coordinateFromString($last);
$lc = Cell::columnIndexFromString($lc) - 1;
// loop through the individual cells in the individual merge
for ($r = $fr; $r <= $lr; ++$r) {
// also, flag this row as a HTML row that is candidate to be omitted
$candidateSpannedRow[$r] = $r;
for ($c = $fc; $c <= $lc; ++$c) {
if (!($c == $fc && $r == $fr)) {
// not the upper-left cell (should not be written in HTML)
$this->_isSpannedCell[$sheetIndex][$r][$c] = array('baseCell' => array($fr, $fc));
} else {
// upper-left is the base cell that should hold the colspan/rowspan attribute
$this->_isBaseCell[$sheetIndex][$r][$c] = array('xlrowspan' => $lr - $fr + 1, 'rowspan' => $lr - $fr + 1, 'xlcolspan' => $lc - $fc + 1, 'colspan' => $lc - $fc + 1);
}
}
}
}
// Identify which rows should be omitted in HTML. These are the rows where all the cells
// participate in a merge and the where base cells are somewhere above.
$countColumns = Cell::columnIndexFromString($sheet->getHighestColumn());
foreach ($candidateSpannedRow as $rowIndex) {
if (isset($this->_isSpannedCell[$sheetIndex][$rowIndex])) {
if (count($this->_isSpannedCell[$sheetIndex][$rowIndex]) == $countColumns) {
$this->_isSpannedRow[$sheetIndex][$rowIndex] = $rowIndex;
}
}
}
// For each of the omitted rows we found above, the affected rowspans should be subtracted by 1
if (isset($this->_isSpannedRow[$sheetIndex])) {
foreach ($this->_isSpannedRow[$sheetIndex] as $rowIndex) {
$adjustedBaseCells = array();
for ($c = 0; $c < $countColumns; ++$c) {
$baseCell = $this->_isSpannedCell[$sheetIndex][$rowIndex][$c]['baseCell'];
if (!in_array($baseCell, $adjustedBaseCells)) {
// subtract rowspan by 1
--$this->_isBaseCell[$sheetIndex][$baseCell[0]][$baseCell[1]]['rowspan'];
$adjustedBaseCells[] = $baseCell;
}
}
}
}
// TODO: Same for columns
}
// We have calculated the spans
$this->_spansAreCalculated = true;
}
/**
* OFFSET
*
* Returns a reference to a range that is a specified number of rows and columns from a cell or range of cells.
* The reference that is returned can be a single cell or a range of cells. You can specify the number of rows and
* the number of columns to be returned.
*
* @param cellAddress The reference from which you want to base the offset. Reference must refer to a cell or
* range of adjacent cells; otherwise, OFFSET returns the #VALUE! error value.
* @param rows The number of rows, up or down, that you want the upper-left cell to refer to.
* Using 5 as the rows argument specifies that the upper-left cell in the reference is
* five rows below reference. Rows can be positive (which means below the starting reference)
* or negative (which means above the starting reference).
* @param cols The number of columns, to the left or right, that you want the upper-left cell of the result
* to refer to. Using 5 as the cols argument specifies that the upper-left cell in the
* reference is five columns to the right of reference. Cols can be positive (which means
* to the right of the starting reference) or negative (which means to the left of the
* starting reference).
* @param height The height, in number of rows, that you want the returned reference to be. Height must be a positive number.
* @param width The width, in number of columns, that you want the returned reference to be. Width must be a positive number.
* @return string A reference to a cell or range of cells
*/
public static function OFFSET($cellAddress = Null, $rows = 0, $columns = 0, $height = null, $width = null)
{
$rows = self::flattenSingleValue($rows);
$columns = self::flattenSingleValue($columns);
$height = self::flattenSingleValue($height);
$width = self::flattenSingleValue($width);
if ($cellAddress == Null) {
return 0;
}
$args = func_get_args();
$pCell = array_pop($args);
if (!is_object($pCell)) {
return self::$_errorCodes['reference'];
}
$sheetName = null;
if (strpos($cellAddress, "!")) {
list($sheetName, $cellAddress) = explode("!", $cellAddress);
}
if (strpos($cellAddress, ":")) {
list($startCell, $endCell) = explode(":", $cellAddress);
} else {
$startCell = $endCell = $cellAddress;
}
list($startCellColumn, $startCellRow) = Cell::coordinateFromString($startCell);
list($endCellColumn, $endCellRow) = Cell::coordinateFromString($endCell);
$startCellRow += $rows;
$startCellColumn = Cell::columnIndexFromString($startCellColumn) - 1;
$startCellColumn += $columns;
if ($startCellRow <= 0 || $startCellColumn < 0) {
return self::$_errorCodes['reference'];
}
$endCellColumn = Cell::columnIndexFromString($endCellColumn) - 1;
if ($width != null && !is_object($width)) {
$endCellColumn = $startCellColumn + $width - 1;
} else {
$endCellColumn += $columns;
}
$startCellColumn = Cell::stringFromColumnIndex($startCellColumn);
if ($height != null && !is_object($height)) {
$endCellRow = $startCellRow + $height - 1;
} else {
$endCellRow += $rows;
}
if ($endCellRow <= 0 || $endCellColumn < 0) {
return self::$_errorCodes['reference'];
}
$endCellColumn = Cell::stringFromColumnIndex($endCellColumn);
$cellAddress = $startCellColumn . $startCellRow;
if ($startCellColumn != $endCellColumn || $startCellRow != $endCellRow) {
$cellAddress .= ':' . $endCellColumn . $endCellRow;
}
if ($sheetName !== null) {
$pSheet = $pCell->getParent()->getParent()->getSheetByName($sheetName);
} else {
$pSheet = $pCell->getParent();
}
return Calculation::getInstance()->extractCellRange($cellAddress, $pSheet, False);
}
/**
* 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);
if (!$this->_readDataOnly) {
$excel->removeCellStyleXfByIndex(0);
// remove the default style
$excel->removeCellXfByIndex(0);
// remove the default style
}
$zip = new ZipArchive();
$zip->open($pFilename);
$rels = simplexml_load_string($this->_getFromZipArchive($zip, "_rels/.rels"));
//~ http://schemas.openxmlformats.org/package/2006/relationships");
foreach ($rels->Relationship as $rel) {
switch ($rel["Type"]) {
case "http://schemas.openxmlformats.org/package/2006/relationships/metadata/core-properties":
$xmlCore = simplexml_load_string($this->_getFromZipArchive($zip, "{$rel['Target']}"));
if ($xmlCore) {
$xmlCore->registerXPathNamespace("dc", "http://purl.org/dc/elements/1.1/");
$xmlCore->registerXPathNamespace("dcterms", "http://purl.org/dc/terms/");
$xmlCore->registerXPathNamespace("cp", "http://schemas.openxmlformats.org/package/2006/metadata/core-properties");
$docProps = $excel->getProperties();
$docProps->setCreator((string) self::array_item($xmlCore->xpath("dc:creator")));
$docProps->setLastModifiedBy((string) self::array_item($xmlCore->xpath("cp:lastModifiedBy")));
$docProps->setCreated(strtotime(self::array_item($xmlCore->xpath("dcterms:created"))));
//! respect xsi:type
$docProps->setModified(strtotime(self::array_item($xmlCore->xpath("dcterms:modified"))));
//! respect xsi:type
$docProps->setTitle((string) self::array_item($xmlCore->xpath("dc:title")));
$docProps->setDescription((string) self::array_item($xmlCore->xpath("dc:description")));
$docProps->setSubject((string) self::array_item($xmlCore->xpath("dc:subject")));
$docProps->setKeywords((string) self::array_item($xmlCore->xpath("cp:keywords")));
$docProps->setCategory((string) self::array_item($xmlCore->xpath("cp:category")));
}
break;
case "http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument":
$dir = dirname($rel["Target"]);
$relsWorkbook = simplexml_load_string($this->_getFromZipArchive($zip, "{$dir}/_rels/" . basename($rel["Target"]) . ".rels"));
//~ http://schemas.openxmlformats.org/package/2006/relationships");
$relsWorkbook->registerXPathNamespace("rel", "http://schemas.openxmlformats.org/package/2006/relationships");
$sharedStrings = array();
$xpath = self::array_item($relsWorkbook->xpath("rel:Relationship[@Type='http://schemas.openxmlformats.org/officeDocument/2006/relationships/sharedStrings']"));
$xmlStrings = simplexml_load_string($this->_getFromZipArchive($zip, "{$dir}/{$xpath['Target']}"));
//~ http://schemas.openxmlformats.org/spreadsheetml/2006/main");
if (isset($xmlStrings) && isset($xmlStrings->si)) {
foreach ($xmlStrings->si as $val) {
if (isset($val->t)) {
$sharedStrings[] = Shared_String::ControlCharacterOOXML2PHP((string) $val->t);
} elseif (isset($val->r)) {
$sharedStrings[] = $this->_parseRichText($val);
}
}
}
$worksheets = array();
foreach ($relsWorkbook->Relationship as $ele) {
if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet") {
$worksheets[(string) $ele["Id"]] = $ele["Target"];
}
}
$styles = array();
$cellStyles = array();
$xpath = self::array_item($relsWorkbook->xpath("rel:Relationship[@Type='http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles']"));
$xmlStyles = simplexml_load_string($this->_getFromZipArchive($zip, "{$dir}/{$xpath['Target']}"));
//~ http://schemas.openxmlformats.org/spreadsheetml/2006/main");
$numFmts = null;
if ($xmlStyles && $xmlStyles->numFmts[0]) {
$numFmts = $xmlStyles->numFmts[0];
}
if (isset($numFmts) && !is_null($numFmts)) {
$numFmts->registerXPathNamespace("sml", "http://schemas.openxmlformats.org/spreadsheetml/2006/main");
}
if (!$this->_readDataOnly && $xmlStyles) {
foreach ($xmlStyles->cellXfs->xf as $xf) {
$numFmt = Style_NumberFormat::FORMAT_GENERAL;
if ($xf["numFmtId"]) {
if (isset($numFmts)) {
$tmpNumFmt = self::array_item($numFmts->xpath("sml:numFmt[@numFmtId={$xf['numFmtId']}]"));
if (isset($tmpNumFmt["formatCode"])) {
$numFmt = (string) $tmpNumFmt["formatCode"];
}
}
if ((int) $xf["numFmtId"] < 164) {
$numFmt = Style_NumberFormat::builtInFormatCode((int) $xf["numFmtId"]);
}
}
//$numFmt = str_replace('mm', 'i', $numFmt);
//$numFmt = str_replace('h', 'H', $numFmt);
$style = (object) array("numFmt" => $numFmt, "font" => $xmlStyles->fonts->font[intval($xf["fontId"])], "fill" => $xmlStyles->fills->fill[intval($xf["fillId"])], "border" => $xmlStyles->borders->border[intval($xf["borderId"])], "alignment" => $xf->alignment, "protection" => $xf->protection);
$styles[] = $style;
// add style to cellXf collection
$objStyle = new Style();
$this->_readStyle($objStyle, $style);
$excel->addCellXf($objStyle);
}
foreach ($xmlStyles->cellStyleXfs->xf as $xf) {
$numFmt = Style_NumberFormat::FORMAT_GENERAL;
if ($numFmts && $xf["numFmtId"]) {
$tmpNumFmt = self::array_item($numFmts->xpath("sml:numFmt[@numFmtId={$xf['numFmtId']}]"));
if (isset($tmpNumFmt["formatCode"])) {
$numFmt = (string) $tmpNumFmt["formatCode"];
} else {
if ((int) $xf["numFmtId"] < 165) {
$numFmt = Style_NumberFormat::builtInFormatCode((int) $xf["numFmtId"]);
}
}
}
$cellStyle = (object) array("numFmt" => $numFmt, "font" => $xmlStyles->fonts->font[intval($xf["fontId"])], "fill" => $xmlStyles->fills->fill[intval($xf["fillId"])], "border" => $xmlStyles->borders->border[intval($xf["borderId"])], "alignment" => $xf->alignment, "protection" => $xf->protection);
$cellStyles[] = $cellStyle;
// add style to cellStyleXf collection
$objStyle = new Style();
$this->_readStyle($objStyle, $cellStyle);
$excel->addCellStyleXf($objStyle);
}
}
$dxfs = array();
if (!$this->_readDataOnly && $xmlStyles) {
if ($xmlStyles->dxfs) {
foreach ($xmlStyles->dxfs->dxf as $dxf) {
$style = new Style();
$this->_readStyle($style, $dxf);
$dxfs[] = $style;
}
}
if ($xmlStyles->cellStyles) {
foreach ($xmlStyles->cellStyles->cellStyle as $cellStyle) {
if (intval($cellStyle['builtinId']) == 0) {
if (isset($cellStyles[intval($cellStyle['xfId'])])) {
// Set default style
$style = new Style();
$this->_readStyle($style, $cellStyles[intval($cellStyle['xfId'])]);
// normal style, currently not using it for anything
}
}
}
}
}
$xmlWorkbook = simplexml_load_string($this->_getFromZipArchive($zip, "{$rel['Target']}"));
//~ http://schemas.openxmlformats.org/spreadsheetml/2006/main");
// Set base date
if ($xmlWorkbook->workbookPr) {
Shared_Date::setExcelCalendar(Shared_Date::CALENDAR_WINDOWS_1900);
if (isset($xmlWorkbook->workbookPr['date1904'])) {
$date1904 = (string) $xmlWorkbook->workbookPr['date1904'];
if ($date1904 == "true" || $date1904 == "1") {
Shared_Date::setExcelCalendar(Shared_Date::CALENDAR_MAC_1904);
}
}
}
$sheetId = 0;
// keep track of new sheet id in final workbook
$oldSheetId = -1;
// keep track of old sheet id in final workbook
$countSkippedSheets = 0;
// keep track of number of skipped sheets
$mapSheetId = array();
// mapping of sheet ids from old to new
if ($xmlWorkbook->sheets) {
foreach ($xmlWorkbook->sheets->sheet as $eleSheet) {
++$oldSheetId;
// Check if sheet should be skipped
if (isset($this->_loadSheetsOnly) && !in_array((string) $eleSheet["name"], $this->_loadSheetsOnly)) {
++$countSkippedSheets;
$mapSheetId[$oldSheetId] = null;
continue;
}
// Map old sheet id in original workbook to new sheet id.
// They will differ if loadSheetsOnly() is being used
$mapSheetId[$oldSheetId] = $oldSheetId - $countSkippedSheets;
// Load sheet
$docSheet = $excel->createSheet();
$docSheet->setTitle((string) $eleSheet["name"]);
$fileWorksheet = $worksheets[(string) self::array_item($eleSheet->attributes("http://schemas.openxmlformats.org/officeDocument/2006/relationships"), "id")];
$xmlSheet = simplexml_load_string($this->_getFromZipArchive($zip, "{$dir}/{$fileWorksheet}"));
//~ http://schemas.openxmlformats.org/spreadsheetml/2006/main");
$sharedFormulas = array();
if (isset($eleSheet["state"]) && (string) $eleSheet["state"] != '') {
$docSheet->setSheetState((string) $eleSheet["state"]);
}
if (isset($xmlSheet->sheetViews) && isset($xmlSheet->sheetViews->sheetView)) {
if (isset($xmlSheet->sheetViews->sheetView['zoomScale'])) {
$docSheet->getSheetView()->setZoomScale(intval($xmlSheet->sheetViews->sheetView['zoomScale']));
}
if (isset($xmlSheet->sheetViews->sheetView['zoomScaleNormal'])) {
$docSheet->getSheetView()->setZoomScaleNormal(intval($xmlSheet->sheetViews->sheetView['zoomScaleNormal']));
}
if (isset($xmlSheet->sheetViews->sheetView['showGridLines'])) {
$docSheet->setShowGridLines((string) $xmlSheet->sheetViews->sheetView['showGridLines'] ? true : false);
}
if (isset($xmlSheet->sheetViews->sheetView['showRowColHeaders'])) {
$docSheet->setShowRowColHeaders((string) $xmlSheet->sheetViews->sheetView['showRowColHeaders'] ? true : false);
}
if (isset($xmlSheet->sheetViews->sheetView['rightToLeft'])) {
$docSheet->setRightToLeft((string) $xmlSheet->sheetViews->sheetView['rightToLeft'] ? true : false);
}
if (isset($xmlSheet->sheetViews->sheetView->pane)) {
if (isset($xmlSheet->sheetViews->sheetView->pane['topLeftCell'])) {
$docSheet->freezePane((string) $xmlSheet->sheetViews->sheetView->pane['topLeftCell']);
} else {
$xSplit = 0;
$ySplit = 0;
if (isset($xmlSheet->sheetViews->sheetView->pane['xSplit'])) {
$xSplit = 1 + intval($xmlSheet->sheetViews->sheetView->pane['xSplit']);
}
if (isset($xmlSheet->sheetViews->sheetView->pane['ySplit'])) {
$ySplit = 1 + intval($xmlSheet->sheetViews->sheetView->pane['ySplit']);
}
$docSheet->freezePaneByColumnAndRow($xSplit, $ySplit);
}
}
if (isset($xmlSheet->sheetViews->sheetView->selection)) {
if (isset($xmlSheet->sheetViews->sheetView->selection['sqref'])) {
$sqref = (string) $xmlSheet->sheetViews->sheetView->selection['sqref'];
$sqref = explode(' ', $sqref);
$sqref = $sqref[0];
$docSheet->setSelectedCells($sqref);
}
}
}
if (isset($xmlSheet->sheetPr) && isset($xmlSheet->sheetPr->tabColor)) {
if (isset($xmlSheet->sheetPr->tabColor['rgb'])) {
$docSheet->getTabColor()->setARGB((string) $xmlSheet->sheetPr->tabColor['rgb']);
}
}
if (isset($xmlSheet->sheetPr) && isset($xmlSheet->sheetPr->outlinePr)) {
if (isset($xmlSheet->sheetPr->outlinePr['summaryRight']) && $xmlSheet->sheetPr->outlinePr['summaryRight'] == false) {
$docSheet->setShowSummaryRight(false);
} else {
$docSheet->setShowSummaryRight(true);
}
if (isset($xmlSheet->sheetPr->outlinePr['summaryBelow']) && $xmlSheet->sheetPr->outlinePr['summaryBelow'] == false) {
$docSheet->setShowSummaryBelow(false);
} else {
$docSheet->setShowSummaryBelow(true);
}
}
if (isset($xmlSheet->sheetPr) && isset($xmlSheet->sheetPr->pageSetUpPr)) {
if (isset($xmlSheet->sheetPr->pageSetUpPr['fitToPage']) && $xmlSheet->sheetPr->pageSetUpPr['fitToPage'] == false) {
$docSheet->getPageSetup()->setFitToPage(false);
} else {
$docSheet->getPageSetup()->setFitToPage(true);
}
}
if (isset($xmlSheet->sheetFormatPr)) {
if (isset($xmlSheet->sheetFormatPr['customHeight']) && ((string) $xmlSheet->sheetFormatPr['customHeight'] == '1' || strtolower((string) $xmlSheet->sheetFormatPr['customHeight']) == 'true') && isset($xmlSheet->sheetFormatPr['defaultRowHeight'])) {
$docSheet->getDefaultRowDimension()->setRowHeight((double) $xmlSheet->sheetFormatPr['defaultRowHeight']);
}
if (isset($xmlSheet->sheetFormatPr['defaultColWidth'])) {
$docSheet->getDefaultColumnDimension()->setWidth((double) $xmlSheet->sheetFormatPr['defaultColWidth']);
}
}
if (isset($xmlSheet->cols) && !$this->_readDataOnly) {
foreach ($xmlSheet->cols->col as $col) {
for ($i = intval($col["min"]) - 1; $i < intval($col["max"]); ++$i) {
if ($col["style"] && !$this->_readDataOnly) {
$docSheet->getColumnDimension(Cell::stringFromColumnIndex($i))->setXfIndex(intval($col["style"]));
}
if ($col["bestFit"]) {
//$docSheet->getColumnDimension(Cell::stringFromColumnIndex($i))->setAutoSize(true);
}
if ($col["hidden"]) {
$docSheet->getColumnDimension(Cell::stringFromColumnIndex($i))->setVisible(false);
}
if ($col["collapsed"]) {
$docSheet->getColumnDimension(Cell::stringFromColumnIndex($i))->setCollapsed(true);
}
if ($col["outlineLevel"] > 0) {
$docSheet->getColumnDimension(Cell::stringFromColumnIndex($i))->setOutlineLevel(intval($col["outlineLevel"]));
}
$docSheet->getColumnDimension(Cell::stringFromColumnIndex($i))->setWidth(floatval($col["width"]));
if (intval($col["max"]) == 16384) {
break;
}
}
}
}
if (isset($xmlSheet->printOptions) && !$this->_readDataOnly) {
if ($xmlSheet->printOptions['gridLinesSet'] == 'true' && $xmlSheet->printOptions['gridLinesSet'] == '1') {
$docSheet->setShowGridlines(true);
}
if ($xmlSheet->printOptions['gridLines'] == 'true' || $xmlSheet->printOptions['gridLines'] == '1') {
$docSheet->setPrintGridlines(true);
}
if ($xmlSheet->printOptions['horizontalCentered']) {
$docSheet->getPageSetup()->setHorizontalCentered(true);
}
if ($xmlSheet->printOptions['verticalCentered']) {
$docSheet->getPageSetup()->setVerticalCentered(true);
}
}
if ($xmlSheet && $xmlSheet->sheetData && $xmlSheet->sheetData->row) {
foreach ($xmlSheet->sheetData->row as $row) {
if ($row["ht"] && !$this->_readDataOnly) {
$docSheet->getRowDimension(intval($row["r"]))->setRowHeight(floatval($row["ht"]));
}
if ($row["hidden"] && !$this->_readDataOnly) {
$docSheet->getRowDimension(intval($row["r"]))->setVisible(false);
}
if ($row["collapsed"]) {
$docSheet->getRowDimension(intval($row["r"]))->setCollapsed(true);
}
if ($row["outlineLevel"] > 0) {
$docSheet->getRowDimension(intval($row["r"]))->setOutlineLevel(intval($row["outlineLevel"]));
}
if ($row["s"] && !$this->_readDataOnly) {
$docSheet->getRowDimension(intval($row["r"]))->setXfIndex(intval($row["s"]));
}
foreach ($row->c as $c) {
$r = (string) $c["r"];
$cellDataType = (string) $c["t"];
$value = null;
$calculatedValue = null;
// Read cell?
if (!is_null($this->getReadFilter())) {
$coordinates = Cell::coordinateFromString($r);
if (!$this->getReadFilter()->readCell($coordinates[0], $coordinates[1], $docSheet->getTitle())) {
continue;
}
}
// echo '<b>Reading cell '.$coordinates[0].$coordinates[1].'</b><br />';
// print_r($c);
// echo '<br />';
// echo 'Cell Data Type is '.$cellDataType.': ';
//
// Read cell!
switch ($cellDataType) {
case "s":
// echo 'String<br />';
if ((string) $c->v != '') {
$value = $sharedStrings[intval($c->v)];
if ($value instanceof RichText) {
$value = clone $value;
}
} else {
$value = '';
}
break;
case "b":
// echo 'Boolean<br />';
if (!isset($c->f)) {
$value = $this->_castToBool($c);
} else {
// Formula
$this->_castToFormula($c, $r, $cellDataType, $value, $calculatedValue, $sharedFormulas, '_castToBool');
// echo '$calculatedValue = '.$calculatedValue.'<br />';
}
break;
case "inlineStr":
// echo 'Inline String<br />';
$value = $this->_parseRichText($c->is);
break;
case "e":
// echo 'Error<br />';
if (!isset($c->f)) {
$value = $this->_castToError($c);
} else {
// Formula
$this->_castToFormula($c, $r, $cellDataType, $value, $calculatedValue, $sharedFormulas, '_castToError');
// echo '$calculatedValue = '.$calculatedValue.'<br />';
}
break;
default:
// echo 'Default<br />';
if (!isset($c->f)) {
// echo 'Not a Formula<br />';
$value = $this->_castToString($c);
} else {
// echo 'Treat as Formula<br />';
// Formula
$this->_castToFormula($c, $r, $cellDataType, $value, $calculatedValue, $sharedFormulas, '_castToString');
// echo '$calculatedValue = '.$calculatedValue.'<br />';
}
break;
}
// echo 'Value is '.$value.'<br />';
// Check for numeric values
if (is_numeric($value) && $cellDataType != 's') {
if ($value == (int) $value) {
$value = (int) $value;
} elseif ($value == (double) $value) {
$value = (double) $value;
} elseif ($value == (double) $value) {
$value = (double) $value;
}
}
// Rich text?
if ($value instanceof RichText && $this->_readDataOnly) {
$value = $value->getPlainText();
}
$cell = $docSheet->getCell($r);
// Assign value
if ($cellDataType != '') {
$cell->setValueExplicit($value, $cellDataType);
} else {
$cell->setValue($value);
}
if (!is_null($calculatedValue)) {
$cell->setCalculatedValue($calculatedValue);
}
// Style information?
if ($c["s"] && !$this->_readDataOnly) {
// no style index means 0, it seems
$cell->setXfIndex(isset($styles[intval($c["s"])]) ? intval($c["s"]) : 0);
}
}
}
}
$conditionals = array();
if (!$this->_readDataOnly && $xmlSheet && $xmlSheet->conditionalFormatting) {
foreach ($xmlSheet->conditionalFormatting as $conditional) {
foreach ($conditional->cfRule as $cfRule) {
if (((string) $cfRule["type"] == Style_Conditional::CONDITION_NONE || (string) $cfRule["type"] == Style_Conditional::CONDITION_CELLIS || (string) $cfRule["type"] == Style_Conditional::CONDITION_CONTAINSTEXT || (string) $cfRule["type"] == Style_Conditional::CONDITION_EXPRESSION) && isset($dxfs[intval($cfRule["dxfId"])])) {
$conditionals[(string) $conditional["sqref"]][intval($cfRule["priority"])] = $cfRule;
}
}
}
foreach ($conditionals as $ref => $cfRules) {
ksort($cfRules);
$conditionalStyles = array();
foreach ($cfRules as $cfRule) {
$objConditional = new Style_Conditional();
$objConditional->setConditionType((string) $cfRule["type"]);
$objConditional->setOperatorType((string) $cfRule["operator"]);
if ((string) $cfRule["text"] != '') {
$objConditional->setText((string) $cfRule["text"]);
}
if (count($cfRule->formula) > 1) {
foreach ($cfRule->formula as $formula) {
$objConditional->addCondition((string) $formula);
}
} else {
$objConditional->addCondition((string) $cfRule->formula);
}
$objConditional->setStyle(clone $dxfs[intval($cfRule["dxfId"])]);
$conditionalStyles[] = $objConditional;
}
// Extract all cell references in $ref
$aReferences = Cell::extractAllCellReferencesInRange($ref);
foreach ($aReferences as $reference) {
$docSheet->getStyle($reference)->setConditionalStyles($conditionalStyles);
}
}
}
$aKeys = array("sheet", "objects", "scenarios", "formatCells", "formatColumns", "formatRows", "insertColumns", "insertRows", "insertHyperlinks", "deleteColumns", "deleteRows", "selectLockedCells", "sort", "autoFilter", "pivotTables", "selectUnlockedCells");
if (!$this->_readDataOnly && $xmlSheet && $xmlSheet->sheetProtection) {
foreach ($aKeys as $key) {
$method = "set" . ucfirst($key);
$docSheet->getProtection()->{$method}($xmlSheet->sheetProtection[$key] == "true");
}
}
if (!$this->_readDataOnly && $xmlSheet && $xmlSheet->sheetProtection) {
$docSheet->getProtection()->setPassword((string) $xmlSheet->sheetProtection["password"], true);
if ($xmlSheet->protectedRanges->protectedRange) {
foreach ($xmlSheet->protectedRanges->protectedRange as $protectedRange) {
$docSheet->protectCells((string) $protectedRange["sqref"], (string) $protectedRange["password"], true);
}
}
}
if ($xmlSheet && $xmlSheet->autoFilter && !$this->_readDataOnly) {
$docSheet->setAutoFilter((string) $xmlSheet->autoFilter["ref"]);
}
if ($xmlSheet && $xmlSheet->mergeCells && $xmlSheet->mergeCells->mergeCell && !$this->_readDataOnly) {
foreach ($xmlSheet->mergeCells->mergeCell as $mergeCell) {
$docSheet->mergeCells((string) $mergeCell["ref"]);
}
}
if ($xmlSheet && $xmlSheet->pageMargins && !$this->_readDataOnly) {
$docPageMargins = $docSheet->getPageMargins();
$docPageMargins->setLeft(floatval($xmlSheet->pageMargins["left"]));
$docPageMargins->setRight(floatval($xmlSheet->pageMargins["right"]));
$docPageMargins->setTop(floatval($xmlSheet->pageMargins["top"]));
$docPageMargins->setBottom(floatval($xmlSheet->pageMargins["bottom"]));
$docPageMargins->setHeader(floatval($xmlSheet->pageMargins["header"]));
$docPageMargins->setFooter(floatval($xmlSheet->pageMargins["footer"]));
}
if ($xmlSheet && $xmlSheet->pageSetup && !$this->_readDataOnly) {
$docPageSetup = $docSheet->getPageSetup();
if (isset($xmlSheet->pageSetup["orientation"])) {
$docPageSetup->setOrientation((string) $xmlSheet->pageSetup["orientation"]);
}
if (isset($xmlSheet->pageSetup["paperSize"])) {
$docPageSetup->setPaperSize(intval($xmlSheet->pageSetup["paperSize"]));
}
if (isset($xmlSheet->pageSetup["scale"])) {
$docPageSetup->setScale(intval($xmlSheet->pageSetup["scale"]), false);
}
if (isset($xmlSheet->pageSetup["fitToHeight"]) && intval($xmlSheet->pageSetup["fitToHeight"]) >= 0) {
$docPageSetup->setFitToHeight(intval($xmlSheet->pageSetup["fitToHeight"]), false);
}
if (isset($xmlSheet->pageSetup["fitToWidth"]) && intval($xmlSheet->pageSetup["fitToWidth"]) >= 0) {
$docPageSetup->setFitToWidth(intval($xmlSheet->pageSetup["fitToWidth"]), false);
}
if (isset($xmlSheet->pageSetup["firstPageNumber"]) && isset($xmlSheet->pageSetup["useFirstPageNumber"]) && ((string) $xmlSheet->pageSetup["useFirstPageNumber"] == 'true' || (string) $xmlSheet->pageSetup["useFirstPageNumber"] == '1')) {
$docPageSetup->setFirstPageNumber(intval($xmlSheet->pageSetup["firstPageNumber"]));
}
}
if ($xmlSheet && $xmlSheet->headerFooter && !$this->_readDataOnly) {
$docHeaderFooter = $docSheet->getHeaderFooter();
if (isset($xmlSheet->headerFooter["differentOddEven"]) && ((string) $xmlSheet->headerFooter["differentOddEven"] == 'true' || (string) $xmlSheet->headerFooter["differentOddEven"] == '1')) {
$docHeaderFooter->setDifferentOddEven(true);
} else {
$docHeaderFooter->setDifferentOddEven(false);
}
if (isset($xmlSheet->headerFooter["differentFirst"]) && ((string) $xmlSheet->headerFooter["differentFirst"] == 'true' || (string) $xmlSheet->headerFooter["differentFirst"] == '1')) {
$docHeaderFooter->setDifferentFirst(true);
} else {
$docHeaderFooter->setDifferentFirst(false);
}
if (isset($xmlSheet->headerFooter["scaleWithDoc"]) && ((string) $xmlSheet->headerFooter["scaleWithDoc"] == 'false' || (string) $xmlSheet->headerFooter["scaleWithDoc"] == '0')) {
$docHeaderFooter->setScaleWithDocument(false);
} else {
$docHeaderFooter->setScaleWithDocument(true);
}
if (isset($xmlSheet->headerFooter["alignWithMargins"]) && ((string) $xmlSheet->headerFooter["alignWithMargins"] == 'false' || (string) $xmlSheet->headerFooter["alignWithMargins"] == '0')) {
$docHeaderFooter->setAlignWithMargins(false);
} else {
$docHeaderFooter->setAlignWithMargins(true);
}
$docHeaderFooter->setOddHeader((string) $xmlSheet->headerFooter->oddHeader);
$docHeaderFooter->setOddFooter((string) $xmlSheet->headerFooter->oddFooter);
$docHeaderFooter->setEvenHeader((string) $xmlSheet->headerFooter->evenHeader);
$docHeaderFooter->setEvenFooter((string) $xmlSheet->headerFooter->evenFooter);
$docHeaderFooter->setFirstHeader((string) $xmlSheet->headerFooter->firstHeader);
$docHeaderFooter->setFirstFooter((string) $xmlSheet->headerFooter->firstFooter);
}
if ($xmlSheet && $xmlSheet->rowBreaks && $xmlSheet->rowBreaks->brk && !$this->_readDataOnly) {
foreach ($xmlSheet->rowBreaks->brk as $brk) {
if ($brk["man"]) {
$docSheet->setBreak("A{$brk['id']}", Worksheet::BREAK_ROW);
}
}
}
if ($xmlSheet && $xmlSheet->colBreaks && $xmlSheet->colBreaks->brk && !$this->_readDataOnly) {
foreach ($xmlSheet->colBreaks->brk as $brk) {
if ($brk["man"]) {
$docSheet->setBreak(Cell::stringFromColumnIndex($brk["id"]) . "1", Worksheet::BREAK_COLUMN);
}
}
}
if ($xmlSheet && $xmlSheet->dataValidations && !$this->_readDataOnly) {
foreach ($xmlSheet->dataValidations->dataValidation as $dataValidation) {
// Uppercase coordinate
$range = strtoupper($dataValidation["sqref"]);
$rangeSet = explode(' ', $range);
foreach ($rangeSet as $range) {
$stRange = $docSheet->shrinkRangeToFit($range);
// Extract all cell references in $range
$aReferences = Cell::extractAllCellReferencesInRange($stRange);
foreach ($aReferences as $reference) {
// Create validation
$docValidation = $docSheet->getCell($reference)->getDataValidation();
$docValidation->setType((string) $dataValidation["type"]);
$docValidation->setErrorStyle((string) $dataValidation["errorStyle"]);
$docValidation->setOperator((string) $dataValidation["operator"]);
$docValidation->setAllowBlank($dataValidation["allowBlank"] != 0);
$docValidation->setShowDropDown($dataValidation["showDropDown"] == 0);
$docValidation->setShowInputMessage($dataValidation["showInputMessage"] != 0);
$docValidation->setShowErrorMessage($dataValidation["showErrorMessage"] != 0);
$docValidation->setErrorTitle((string) $dataValidation["errorTitle"]);
$docValidation->setError((string) $dataValidation["error"]);
$docValidation->setPromptTitle((string) $dataValidation["promptTitle"]);
$docValidation->setPrompt((string) $dataValidation["prompt"]);
$docValidation->setFormula1((string) $dataValidation->formula1);
$docValidation->setFormula2((string) $dataValidation->formula2);
}
}
}
}
// Add hyperlinks
$hyperlinks = array();
if (!$this->_readDataOnly) {
// Locate hyperlink relations
if ($zip->locateName(dirname("{$dir}/{$fileWorksheet}") . "/_rels/" . basename($fileWorksheet) . ".rels")) {
$relsWorksheet = simplexml_load_string($this->_getFromZipArchive($zip, dirname("{$dir}/{$fileWorksheet}") . "/_rels/" . basename($fileWorksheet) . ".rels"));
//~ http://schemas.openxmlformats.org/package/2006/relationships");
foreach ($relsWorksheet->Relationship as $ele) {
if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/hyperlink") {
$hyperlinks[(string) $ele["Id"]] = (string) $ele["Target"];
}
}
}
// Loop through hyperlinks
if ($xmlSheet && $xmlSheet->hyperlinks) {
foreach ($xmlSheet->hyperlinks->hyperlink as $hyperlink) {
// Link url
$linkRel = $hyperlink->attributes('http://schemas.openxmlformats.org/officeDocument/2006/relationships');
foreach (Cell::extractAllCellReferencesInRange($hyperlink['ref']) as $cellReference) {
$cell = $docSheet->getCell($cellReference);
if (isset($linkRel['id'])) {
$cell->getHyperlink()->setUrl($hyperlinks[(string) $linkRel['id']]);
}
if (isset($hyperlink['location'])) {
$cell->getHyperlink()->setUrl('sheet://' . (string) $hyperlink['location']);
}
// Tooltip
if (isset($hyperlink['tooltip'])) {
$cell->getHyperlink()->setTooltip((string) $hyperlink['tooltip']);
}
}
}
}
}
// Add comments
$comments = array();
$vmlComments = array();
if (!$this->_readDataOnly) {
// Locate comment relations
if ($zip->locateName(dirname("{$dir}/{$fileWorksheet}") . "/_rels/" . basename($fileWorksheet) . ".rels")) {
$relsWorksheet = simplexml_load_string($this->_getFromZipArchive($zip, dirname("{$dir}/{$fileWorksheet}") . "/_rels/" . basename($fileWorksheet) . ".rels"));
//~ http://schemas.openxmlformats.org/package/2006/relationships");
foreach ($relsWorksheet->Relationship as $ele) {
if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/comments") {
$comments[(string) $ele["Id"]] = (string) $ele["Target"];
}
if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/vmlDrawing") {
$vmlComments[(string) $ele["Id"]] = (string) $ele["Target"];
}
}
}
// Loop through comments
foreach ($comments as $relName => $relPath) {
// Load comments file
$relPath = Shared_File::realpath(dirname("{$dir}/{$fileWorksheet}") . "/" . $relPath);
$commentsFile = simplexml_load_string($this->_getFromZipArchive($zip, $relPath));
// Utility variables
$authors = array();
// Loop through authors
foreach ($commentsFile->authors->author as $author) {
$authors[] = (string) $author;
}
// Loop through contents
foreach ($commentsFile->commentList->comment as $comment) {
$docSheet->getComment((string) $comment['ref'])->setAuthor($authors[(string) $comment['authorId']]);
$docSheet->getComment((string) $comment['ref'])->setText($this->_parseRichText($comment->text));
}
}
// Loop through VML comments
foreach ($vmlComments as $relName => $relPath) {
// Load VML comments file
$relPath = Shared_File::realpath(dirname("{$dir}/{$fileWorksheet}") . "/" . $relPath);
$vmlCommentsFile = simplexml_load_string($this->_getFromZipArchive($zip, $relPath));
$vmlCommentsFile->registerXPathNamespace('v', 'urn:schemas-microsoft-com:vml');
$shapes = $vmlCommentsFile->xpath('//v:shape');
foreach ($shapes as $shape) {
$shape->registerXPathNamespace('v', 'urn:schemas-microsoft-com:vml');
if (isset($shape['style'])) {
$style = (string) $shape['style'];
$fillColor = strtoupper(substr((string) $shape['fillcolor'], 1));
$column = null;
$row = null;
$clientData = $shape->xpath('.//x:ClientData');
if (is_array($clientData) && count($clientData) > 0) {
$clientData = $clientData[0];
if (isset($clientData['ObjectType']) && (string) $clientData['ObjectType'] == 'Note') {
$temp = $clientData->xpath('.//x:Row');
if (is_array($temp)) {
$row = $temp[0];
}
$temp = $clientData->xpath('.//x:Column');
if (is_array($temp)) {
$column = $temp[0];
}
}
}
if (!is_null($column) && !is_null($row)) {
// Set comment properties
$comment = $docSheet->getCommentByColumnAndRow($column, $row + 1);
$comment->getFillColor()->setRGB($fillColor);
// Parse style
$styleArray = explode(';', str_replace(' ', '', $style));
foreach ($styleArray as $stylePair) {
$stylePair = explode(':', $stylePair);
if ($stylePair[0] == 'margin-left') {
$comment->setMarginLeft($stylePair[1]);
}
if ($stylePair[0] == 'margin-top') {
$comment->setMarginTop($stylePair[1]);
}
if ($stylePair[0] == 'width') {
$comment->setWidth($stylePair[1]);
}
if ($stylePair[0] == 'height') {
$comment->setHeight($stylePair[1]);
}
if ($stylePair[0] == 'visibility') {
$comment->setVisible($stylePair[1] == 'visible');
}
}
}
}
}
}
// Header/footer images
if ($xmlSheet && $xmlSheet->legacyDrawingHF && !$this->_readDataOnly) {
if ($zip->locateName(dirname("{$dir}/{$fileWorksheet}") . "/_rels/" . basename($fileWorksheet) . ".rels")) {
$relsWorksheet = simplexml_load_string($this->_getFromZipArchive($zip, dirname("{$dir}/{$fileWorksheet}") . "/_rels/" . basename($fileWorksheet) . ".rels"));
//~ http://schemas.openxmlformats.org/package/2006/relationships");
$vmlRelationship = '';
foreach ($relsWorksheet->Relationship as $ele) {
if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/vmlDrawing") {
$vmlRelationship = self::dir_add("{$dir}/{$fileWorksheet}", $ele["Target"]);
}
}
if ($vmlRelationship != '') {
// Fetch linked images
$relsVML = simplexml_load_string($this->_getFromZipArchive($zip, dirname($vmlRelationship) . '/_rels/' . basename($vmlRelationship) . '.rels'));
//~ http://schemas.openxmlformats.org/package/2006/relationships");
$drawings = array();
foreach ($relsVML->Relationship as $ele) {
if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/image") {
$drawings[(string) $ele["Id"]] = self::dir_add($vmlRelationship, $ele["Target"]);
}
}
// Fetch VML document
$vmlDrawing = simplexml_load_string($this->_getFromZipArchive($zip, $vmlRelationship));
$vmlDrawing->registerXPathNamespace('v', 'urn:schemas-microsoft-com:vml');
$hfImages = array();
$shapes = $vmlDrawing->xpath('//v:shape');
foreach ($shapes as $shape) {
$shape->registerXPathNamespace('v', 'urn:schemas-microsoft-com:vml');
$imageData = $shape->xpath('//v:imagedata');
$imageData = $imageData[0];
$imageData = $imageData->attributes('urn:schemas-microsoft-com:office:office');
$style = self::toCSSArray((string) $shape['style']);
$hfImages[(string) $shape['id']] = new Worksheet_HeaderFooterDrawing();
if (isset($imageData['title'])) {
$hfImages[(string) $shape['id']]->setName((string) $imageData['title']);
}
$hfImages[(string) $shape['id']]->setPath("zip://{$pFilename}#" . $drawings[(string) $imageData['relid']], false);
$hfImages[(string) $shape['id']]->setResizeProportional(false);
$hfImages[(string) $shape['id']]->setWidth($style['width']);
$hfImages[(string) $shape['id']]->setHeight($style['height']);
$hfImages[(string) $shape['id']]->setOffsetX($style['margin-left']);
$hfImages[(string) $shape['id']]->setOffsetY($style['margin-top']);
$hfImages[(string) $shape['id']]->setResizeProportional(true);
}
$docSheet->getHeaderFooter()->setImages($hfImages);
}
}
}
}
// TODO: Make sure drawings and graph are loaded differently!
if ($zip->locateName(dirname("{$dir}/{$fileWorksheet}") . "/_rels/" . basename($fileWorksheet) . ".rels")) {
$relsWorksheet = simplexml_load_string($this->_getFromZipArchive($zip, dirname("{$dir}/{$fileWorksheet}") . "/_rels/" . basename($fileWorksheet) . ".rels"));
//~ http://schemas.openxmlformats.org/package/2006/relationships");
$drawings = array();
foreach ($relsWorksheet->Relationship as $ele) {
if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/drawing") {
$drawings[(string) $ele["Id"]] = self::dir_add("{$dir}/{$fileWorksheet}", $ele["Target"]);
}
}
if ($xmlSheet->drawing && !$this->_readDataOnly) {
foreach ($xmlSheet->drawing as $drawing) {
$fileDrawing = $drawings[(string) self::array_item($drawing->attributes("http://schemas.openxmlformats.org/officeDocument/2006/relationships"), "id")];
$relsDrawing = simplexml_load_string($this->_getFromZipArchive($zip, dirname($fileDrawing) . "/_rels/" . basename($fileDrawing) . ".rels"));
//~ http://schemas.openxmlformats.org/package/2006/relationships");
$images = array();
if ($relsDrawing && $relsDrawing->Relationship) {
foreach ($relsDrawing->Relationship as $ele) {
if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/image") {
$images[(string) $ele["Id"]] = self::dir_add($fileDrawing, $ele["Target"]);
}
}
}
$xmlDrawing = simplexml_load_string($this->_getFromZipArchive($zip, $fileDrawing))->children("http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing");
if ($xmlDrawing->oneCellAnchor) {
foreach ($xmlDrawing->oneCellAnchor as $oneCellAnchor) {
if ($oneCellAnchor->pic->blipFill) {
$blip = $oneCellAnchor->pic->blipFill->children("http://schemas.openxmlformats.org/drawingml/2006/main")->blip;
$xfrm = $oneCellAnchor->pic->spPr->children("http://schemas.openxmlformats.org/drawingml/2006/main")->xfrm;
$outerShdw = $oneCellAnchor->pic->spPr->children("http://schemas.openxmlformats.org/drawingml/2006/main")->effectLst->outerShdw;
$objDrawing = new Worksheet_Drawing();
$objDrawing->setName((string) self::array_item($oneCellAnchor->pic->nvPicPr->cNvPr->attributes(), "name"));
$objDrawing->setDescription((string) self::array_item($oneCellAnchor->pic->nvPicPr->cNvPr->attributes(), "descr"));
$objDrawing->setPath("zip://{$pFilename}#" . $images[(string) self::array_item($blip->attributes("http://schemas.openxmlformats.org/officeDocument/2006/relationships"), "embed")], false);
$objDrawing->setCoordinates(Cell::stringFromColumnIndex($oneCellAnchor->from->col) . ($oneCellAnchor->from->row + 1));
$objDrawing->setOffsetX(Shared_Drawing::EMUToPixels($oneCellAnchor->from->colOff));
$objDrawing->setOffsetY(Shared_Drawing::EMUToPixels($oneCellAnchor->from->rowOff));
$objDrawing->setResizeProportional(false);
$objDrawing->setWidth(Shared_Drawing::EMUToPixels(self::array_item($oneCellAnchor->ext->attributes(), "cx")));
$objDrawing->setHeight(Shared_Drawing::EMUToPixels(self::array_item($oneCellAnchor->ext->attributes(), "cy")));
if ($xfrm) {
$objDrawing->setRotation(Shared_Drawing::angleToDegrees(self::array_item($xfrm->attributes(), "rot")));
}
if ($outerShdw) {
$shadow = $objDrawing->getShadow();
$shadow->setVisible(true);
$shadow->setBlurRadius(Shared_Drawing::EMUTopixels(self::array_item($outerShdw->attributes(), "blurRad")));
$shadow->setDistance(Shared_Drawing::EMUTopixels(self::array_item($outerShdw->attributes(), "dist")));
$shadow->setDirection(Shared_Drawing::angleToDegrees(self::array_item($outerShdw->attributes(), "dir")));
$shadow->setAlignment((string) self::array_item($outerShdw->attributes(), "algn"));
$shadow->getColor()->setRGB(self::array_item($outerShdw->srgbClr->attributes(), "val"));
$shadow->setAlpha(self::array_item($outerShdw->srgbClr->alpha->attributes(), "val") / 1000);
}
$objDrawing->setWorksheet($docSheet);
}
}
}
if ($xmlDrawing->twoCellAnchor) {
foreach ($xmlDrawing->twoCellAnchor as $twoCellAnchor) {
if ($twoCellAnchor->pic->blipFill) {
$blip = $twoCellAnchor->pic->blipFill->children("http://schemas.openxmlformats.org/drawingml/2006/main")->blip;
$xfrm = $twoCellAnchor->pic->spPr->children("http://schemas.openxmlformats.org/drawingml/2006/main")->xfrm;
$outerShdw = $twoCellAnchor->pic->spPr->children("http://schemas.openxmlformats.org/drawingml/2006/main")->effectLst->outerShdw;
$objDrawing = new Worksheet_Drawing();
$objDrawing->setName((string) self::array_item($twoCellAnchor->pic->nvPicPr->cNvPr->attributes(), "name"));
$objDrawing->setDescription((string) self::array_item($twoCellAnchor->pic->nvPicPr->cNvPr->attributes(), "descr"));
$objDrawing->setPath("zip://{$pFilename}#" . $images[(string) self::array_item($blip->attributes("http://schemas.openxmlformats.org/officeDocument/2006/relationships"), "embed")], false);
$objDrawing->setCoordinates(Cell::stringFromColumnIndex($twoCellAnchor->from->col) . ($twoCellAnchor->from->row + 1));
$objDrawing->setOffsetX(Shared_Drawing::EMUToPixels($twoCellAnchor->from->colOff));
$objDrawing->setOffsetY(Shared_Drawing::EMUToPixels($twoCellAnchor->from->rowOff));
$objDrawing->setResizeProportional(false);
$objDrawing->setWidth(Shared_Drawing::EMUToPixels(self::array_item($xfrm->ext->attributes(), "cx")));
$objDrawing->setHeight(Shared_Drawing::EMUToPixels(self::array_item($xfrm->ext->attributes(), "cy")));
if ($xfrm) {
$objDrawing->setRotation(Shared_Drawing::angleToDegrees(self::array_item($xfrm->attributes(), "rot")));
}
if ($outerShdw) {
$shadow = $objDrawing->getShadow();
$shadow->setVisible(true);
$shadow->setBlurRadius(Shared_Drawing::EMUTopixels(self::array_item($outerShdw->attributes(), "blurRad")));
$shadow->setDistance(Shared_Drawing::EMUTopixels(self::array_item($outerShdw->attributes(), "dist")));
$shadow->setDirection(Shared_Drawing::angleToDegrees(self::array_item($outerShdw->attributes(), "dir")));
$shadow->setAlignment((string) self::array_item($outerShdw->attributes(), "algn"));
$shadow->getColor()->setRGB(self::array_item($outerShdw->srgbClr->attributes(), "val"));
$shadow->setAlpha(self::array_item($outerShdw->srgbClr->alpha->attributes(), "val") / 1000);
}
$objDrawing->setWorksheet($docSheet);
}
}
}
}
}
}
// Loop through definedNames
if ($xmlWorkbook->definedNames) {
foreach ($xmlWorkbook->definedNames->definedName as $definedName) {
// Extract range
$extractedRange = (string) $definedName;
$extractedRange = preg_replace('/\'(\\w+)\'\\!/', '', $extractedRange);
$extractedRange = str_replace('$', '', $extractedRange);
// Valid range?
if (stripos((string) $definedName, '#REF!') !== false || $extractedRange == '') {
continue;
}
// Some definedNames are only applicable if we are on the same sheet...
if ((string) $definedName['localSheetId'] != '' && (string) $definedName['localSheetId'] == $sheetId) {
// Switch on type
switch ((string) $definedName['name']) {
case '_xlnm._FilterDatabase':
$docSheet->setAutoFilter($extractedRange);
break;
case '_xlnm.Print_Titles':
// Split $extractedRange
$extractedRange = explode(',', $extractedRange);
// Set print titles
foreach ($extractedRange as $range) {
$matches = array();
// check for repeating columns, e g. 'A:A' or 'A:D'
if (preg_match('/^([A-Z]+)\\:([A-Z]+)$/', $range, $matches)) {
$docSheet->getPageSetup()->setColumnsToRepeatAtLeft(array($matches[1], $matches[2]));
} elseif (preg_match('/^(\\d+)\\:(\\d+)$/', $range, $matches)) {
$docSheet->getPageSetup()->setRowsToRepeatAtTop(array($matches[1], $matches[2]));
}
}
break;
case '_xlnm.Print_Area':
$range = explode('!', $extractedRange);
$extractedRange = isset($range[1]) ? $range[1] : $range[0];
$docSheet->getPageSetup()->setPrintArea($extractedRange);
break;
default:
break;
}
}
}
}
// Next sheet id
++$sheetId;
}
// Loop through definedNames
if ($xmlWorkbook->definedNames) {
foreach ($xmlWorkbook->definedNames->definedName as $definedName) {
// Extract range
$extractedRange = (string) $definedName;
$extractedRange = preg_replace('/\'(\\w+)\'\\!/', '', $extractedRange);
$extractedRange = str_replace('$', '', $extractedRange);
// Valid range?
if (stripos((string) $definedName, '#REF!') !== false || $extractedRange == '') {
continue;
}
// Some definedNames are only applicable if we are on the same sheet...
if ((string) $definedName['localSheetId'] != '') {
// Local defined name
// Switch on type
switch ((string) $definedName['name']) {
case '_xlnm._FilterDatabase':
case '_xlnm.Print_Titles':
case '_xlnm.Print_Area':
break;
default:
$range = explode('!', (string) $definedName);
if (count($range) == 2) {
$range[0] = str_replace("''", "'", $range[0]);
$range[0] = str_replace("'", "", $range[0]);
if ($worksheet = $docSheet->getParent()->getSheetByName($range[0])) {
$extractedRange = str_replace('$', '', $range[1]);
$scope = $docSheet->getParent()->getSheet((string) $definedName['localSheetId']);
$excel->addNamedRange(new NamedRange((string) $definedName['name'], $worksheet, $extractedRange, true, $scope));
}
}
break;
}
} else {
if (!isset($definedName['localSheetId'])) {
// "Global" definedNames
$locatedSheet = null;
$extractedSheetName = '';
if (strpos((string) $definedName, '!') !== false) {
// Extract sheet name
$extractedSheetName = Worksheet::extractSheetTitle((string) $definedName, true);
$extractedSheetName = $extractedSheetName[0];
// Locate sheet
$locatedSheet = $excel->getSheetByName($extractedSheetName);
// Modify range
$range = explode('!', $extractedRange);
$extractedRange = isset($range[1]) ? $range[1] : $range[0];
}
if (!is_null($locatedSheet)) {
$excel->addNamedRange(new NamedRange((string) $definedName['name'], $locatedSheet, $extractedRange, false));
}
}
}
}
}
}
if (!$this->_readDataOnly) {
// active sheet index
$activeTab = intval($xmlWorkbook->bookViews->workbookView["activeTab"]);
// refers to old sheet index
// keep active sheet index if sheet is still loaded, else first sheet is set as the active
if (isset($mapSheetId[$activeTab]) && $mapSheetId[$activeTab] !== null) {
$excel->setActiveSheetIndex($mapSheetId[$activeTab]);
} else {
if ($excel->getSheetCount() == 0) {
$excel->createSheet();
}
$excel->setActiveSheetIndex(0);
}
}
break;
}
}
return $excel;
}
/**
* Process the object to be written
*/
public function close()
{
// initialize
$this->_data = '';
switch (get_class($this->_object)) {
case 'Shared_Escher':
if ($dggContainer = $this->_object->getDggContainer()) {
$writer = new Writer_Excel5_Escher($dggContainer);
$this->_data = $writer->close();
} else {
if ($dgContainer = $this->_object->getDgContainer()) {
$writer = new Writer_Excel5_Escher($dgContainer);
$this->_data = $writer->close();
$this->_spOffsets = $writer->getSpOffsets();
}
}
break;
case 'Shared_Escher_DggContainer':
// this is a container record
// initialize
$innerData = '';
// write the dgg
$recVer = 0x0;
$recInstance = 0x0;
$recType = 0xf006;
$recVerInstance = $recVer;
$recVerInstance |= $recInstance << 4;
// dgg data
$dggData = pack('VVVV', $this->_object->getSpIdMax(), $this->_object->getCDgSaved() + 1, $this->_object->getCSpSaved(), $this->_object->getCDgSaved());
// add file identifier clusters (one per drawing)
for ($i = 0; $i < $this->_object->getCDgSaved(); ++$i) {
$dggData .= pack('VV', 0, 0);
}
$header = pack('vvV', $recVerInstance, $recType, strlen($dggData));
$innerData .= $header . $dggData;
// write the bstoreContainer
if ($bstoreContainer = $this->_object->getBstoreContainer()) {
$writer = new Writer_Excel5_Escher($bstoreContainer);
$innerData .= $writer->close();
}
// write the record
$recVer = 0xf;
$recInstance = 0x0;
$recType = 0xf000;
$length = strlen($innerData);
$recVerInstance = $recVer;
$recVerInstance |= $recInstance << 4;
$header = pack('vvV', $recVerInstance, $recType, $length);
$this->_data = $header . $innerData;
break;
case 'Shared_Escher_DggContainer_BstoreContainer':
// this is a container record
// initialize
$innerData = '';
// treat the inner data
if ($BSECollection = $this->_object->getBSECollection()) {
foreach ($BSECollection as $BSE) {
$writer = new Writer_Excel5_Escher($BSE);
$innerData .= $writer->close();
}
}
// write the record
$recVer = 0xf;
$recInstance = count($this->_object->getBSECollection());
$recType = 0xf001;
$length = strlen($innerData);
$recVerInstance = $recVer;
$recVerInstance |= $recInstance << 4;
$header = pack('vvV', $recVerInstance, $recType, $length);
$this->_data = $header . $innerData;
break;
case 'Shared_Escher_DggContainer_BstoreContainer_BSE':
// this is a semi-container record
// initialize
$innerData = '';
// here we treat the inner data
if ($blip = $this->_object->getBlip()) {
$writer = new Writer_Excel5_Escher($blip);
$innerData .= $writer->close();
}
// initialize
$data = '';
$btWin32 = $this->_object->getBlipType();
$btMacOS = $this->_object->getBlipType();
$data .= pack('CC', $btWin32, $btMacOS);
$rgbUid = pack('VVVV', 0, 0, 0, 0);
// todo
$data .= $rgbUid;
$tag = 0;
$size = strlen($innerData);
$cRef = 1;
$foDelay = 0;
//todo
$unused1 = 0x0;
$cbName = 0x0;
$unused2 = 0x0;
$unused3 = 0x0;
$data .= pack('vVVVCCCC', $tag, $size, $cRef, $foDelay, $unused1, $cbName, $unused2, $unused3);
$data .= $innerData;
// write the record
$recVer = 0x2;
$recInstance = $this->_object->getBlipType();
$recType = 0xf007;
$length = strlen($data);
$recVerInstance = $recVer;
$recVerInstance |= $recInstance << 4;
$header = pack('vvV', $recVerInstance, $recType, $length);
$this->_data = $header;
$this->_data .= $data;
break;
case 'Shared_Escher_DggContainer_BstoreContainer_BSE_Blip':
// this is an atom record
// write the record
switch ($this->_object->getParent()->getBlipType()) {
case Shared_Escher_DggContainer_BstoreContainer_BSE::BLIPTYPE_JPEG:
// initialize
$innerData = '';
$rgbUid1 = pack('VVVV', 0, 0, 0, 0);
// todo
$innerData .= $rgbUid1;
$tag = 0xff;
// todo
$innerData .= pack('C', $tag);
$innerData .= $this->_object->getData();
$recVer = 0x0;
$recInstance = 0x46a;
$recType = 0xf01d;
$length = strlen($innerData);
$recVerInstance = $recVer;
$recVerInstance |= $recInstance << 4;
$header = pack('vvV', $recVerInstance, $recType, $length);
$this->_data = $header;
$this->_data .= $innerData;
break;
case Shared_Escher_DggContainer_BstoreContainer_BSE::BLIPTYPE_PNG:
// initialize
$innerData = '';
$rgbUid1 = pack('VVVV', 0, 0, 0, 0);
// todo
$innerData .= $rgbUid1;
$tag = 0xff;
// todo
$innerData .= pack('C', $tag);
$innerData .= $this->_object->getData();
$recVer = 0x0;
$recInstance = 0x6e0;
$recType = 0xf01e;
$length = strlen($innerData);
$recVerInstance = $recVer;
$recVerInstance |= $recInstance << 4;
$header = pack('vvV', $recVerInstance, $recType, $length);
$this->_data = $header;
$this->_data .= $innerData;
break;
}
break;
case 'Shared_Escher_DgContainer':
// this is a container record
// initialize
$innerData = '';
// write the dg
$recVer = 0x0;
$recInstance = $this->_object->getDgId();
$recType = 0xf008;
$length = 8;
$recVerInstance = $recVer;
$recVerInstance |= $recInstance << 4;
$header = pack('vvV', $recVerInstance, $recType, $length);
// number of shapes in this drawing (including group shape)
$countShapes = count($this->_object->getSpgrContainer()->getChildren());
$innerData .= $header . pack('VV', $countShapes, $this->_object->getLastSpId());
//$innerData .= $header . pack('VV', 0, 0);
// write the spgrContainer
if ($spgrContainer = $this->_object->getSpgrContainer()) {
$writer = new Writer_Excel5_Escher($spgrContainer);
$innerData .= $writer->close();
// get the shape offsets relative to the spgrContainer record
$spOffsets = $writer->getSpOffsets();
// save the shape offsets relative to dgContainer
foreach ($spOffsets as &$spOffset) {
$spOffset += 24;
// add length of dgContainer header data (8 bytes) plus dg data (16 bytes)
}
$this->_spOffsets = $spOffsets;
}
// write the record
$recVer = 0xf;
$recInstance = 0x0;
$recType = 0xf002;
$length = strlen($innerData);
$recVerInstance = $recVer;
$recVerInstance |= $recInstance << 4;
$header = pack('vvV', $recVerInstance, $recType, $length);
$this->_data = $header . $innerData;
break;
case 'Shared_Escher_DgContainer_SpgrContainer':
// this is a container record
// initialize
$innerData = '';
// initialize spape offsets
$totalSize = 8;
$spOffsets = array();
// treat the inner data
foreach ($this->_object->getChildren() as $spContainer) {
$writer = new Writer_Excel5_Escher($spContainer);
$spData = $writer->close();
$innerData .= $spData;
// save the shape offsets (where new shape records begin)
$totalSize += strlen($spData);
$spOffsets[] = $totalSize;
}
// write the record
$recVer = 0xf;
$recInstance = 0x0;
$recType = 0xf003;
$length = strlen($innerData);
$recVerInstance = $recVer;
$recVerInstance |= $recInstance << 4;
$header = pack('vvV', $recVerInstance, $recType, $length);
$this->_data = $header . $innerData;
$this->_spOffsets = $spOffsets;
break;
case 'Shared_Escher_DgContainer_SpgrContainer_SpContainer':
// initialize
$data = '';
// build the data
// write group shape record, if necessary?
if ($this->_object->getSpgr()) {
$recVer = 0x1;
$recInstance = 0x0;
$recType = 0xf009;
$length = 0x10;
$recVerInstance = $recVer;
$recVerInstance |= $recInstance << 4;
$header = pack('vvV', $recVerInstance, $recType, $length);
$data .= $header . pack('VVVV', 0, 0, 0, 0);
}
// write the shape record
$recVer = 0x2;
$recInstance = $this->_object->getSpType();
// shape type
$recType = 0xf00a;
$length = 0x8;
$recVerInstance = $recVer;
$recVerInstance |= $recInstance << 4;
$header = pack('vvV', $recVerInstance, $recType, $length);
$data .= $header . pack('VV', $this->_object->getSpId(), $this->_object->getSpgr() ? 0x5 : 0xa00);
// the options
if ($this->_object->getOPTCollection()) {
$optData = '';
$recVer = 0x3;
$recInstance = count($this->_object->getOPTCollection());
$recType = 0xf00b;
foreach ($this->_object->getOPTCollection() as $property => $value) {
$optData .= pack('vV', $property, $value);
}
$length = strlen($optData);
$recVerInstance = $recVer;
$recVerInstance |= $recInstance << 4;
$header = pack('vvV', $recVerInstance, $recType, $length);
$data .= $header . $optData;
}
// the client anchor
if ($this->_object->getStartCoordinates()) {
$clientAnchorData = '';
$recVer = 0x0;
$recInstance = 0x0;
$recType = 0xf010;
// start coordinates
list($column, $row) = Cell::coordinateFromString($this->_object->getStartCoordinates());
$c1 = Cell::columnIndexFromString($column) - 1;
$r1 = $row - 1;
// start offsetX
$startOffsetX = $this->_object->getStartOffsetX();
// start offsetY
$startOffsetY = $this->_object->getStartOffsetY();
// end coordinates
list($column, $row) = Cell::coordinateFromString($this->_object->getEndCoordinates());
$c2 = Cell::columnIndexFromString($column) - 1;
$r2 = $row - 1;
// end offsetX
$endOffsetX = $this->_object->getEndOffsetX();
// end offsetY
$endOffsetY = $this->_object->getEndOffsetY();
$clientAnchorData = pack('vvvvvvvvv', 0x2, $c1, $startOffsetX, $r1, $startOffsetY, $c2, $endOffsetX, $r2, $endOffsetY);
$length = strlen($clientAnchorData);
$recVerInstance = $recVer;
$recVerInstance |= $recInstance << 4;
$header = pack('vvV', $recVerInstance, $recType, $length);
$data .= $header . $clientAnchorData;
}
// the client data, just empty for now
if (!$this->_object->getSpgr()) {
$clientDataData = '';
$recVer = 0x0;
$recInstance = 0x0;
$recType = 0xf011;
$length = strlen($clientDataData);
$recVerInstance = $recVer;
$recVerInstance |= $recInstance << 4;
$header = pack('vvV', $recVerInstance, $recType, $length);
$data .= $header . $clientDataData;
}
// write the record
$recVer = 0xf;
$recInstance = 0x0;
$recType = 0xf004;
$length = strlen($data);
$recVerInstance = $recVer;
$recVerInstance |= $recInstance << 4;
$header = pack('vvV', $recVerInstance, $recType, $length);
$this->_data = $header . $data;
break;
}
return $this->_data;
}
/**
* Extract range values
*
* @param string &$pRange String based range representation
* @param Worksheet $pSheet Worksheet
* @return mixed Array of values in range if range contains more than one element. Otherwise, a single value is returned.
* @param boolean $resetLog Flag indicating whether calculation log should be reset or not
* @throws Calculation\Exception
*/
public function extractNamedRange(&$pRange = 'A1', Worksheet $pSheet = null, $resetLog = true)
{
// Return value
$returnValue = array();
// echo 'extractNamedRange('.$pRange.')<br />';
if ($pSheet !== null) {
$pSheetName = $pSheet->getTitle();
// echo 'Current sheet name is '.$pSheetName.'<br />';
// echo 'Range reference is '.$pRange.'<br />';
if (strpos($pRange, '!') !== false) {
// echo '$pRange reference includes sheet reference', PHP_EOL;
list($pSheetName, $pRange) = Worksheet::extractSheetTitle($pRange, true);
// echo 'New sheet name is '.$pSheetName, PHP_EOL;
// echo 'Adjusted Range reference is '.$pRange, PHP_EOL;
$pSheet = $this->spreadsheet->getSheetByName($pSheetName);
}
// Named range?
$namedRange = NamedRange::resolveRange($pRange, $pSheet);
if ($namedRange !== null) {
$pSheet = $namedRange->getWorksheet();
// echo 'Named Range '.$pRange.' (';
$pRange = $namedRange->getRange();
$splitRange = Cell::splitRange($pRange);
// Convert row and column references
if (ctype_alpha($splitRange[0][0])) {
$pRange = $splitRange[0][0] . '1:' . $splitRange[0][1] . $namedRange->getWorksheet()->getHighestRow();
} elseif (ctype_digit($splitRange[0][0])) {
$pRange = 'A' . $splitRange[0][0] . ':' . $namedRange->getWorksheet()->getHighestColumn() . $splitRange[0][1];
}
// echo $pRange.') is in sheet '.$namedRange->getWorksheet()->getTitle().'<br />';
// if ($pSheet->getTitle() != $namedRange->getWorksheet()->getTitle()) {
// if (!$namedRange->getLocalOnly()) {
// $pSheet = $namedRange->getWorksheet();
// } else {
// return $returnValue;
// }
// }
} else {
return Calculation\Functions::REF();
}
// Extract range
$aReferences = Cell::extractAllCellReferencesInRange($pRange);
// var_dump($aReferences);
if (!isset($aReferences[1])) {
// Single cell (or single column or row) in range
list($currentCol, $currentRow) = Cell::coordinateFromString($aReferences[0]);
$cellValue = null;
if ($pSheet->cellExists($aReferences[0])) {
$returnValue[$currentRow][$currentCol] = $pSheet->getCell($aReferences[0])->getCalculatedValue($resetLog);
} else {
$returnValue[$currentRow][$currentCol] = null;
}
} else {
// Extract cell data for all cells in the range
foreach ($aReferences as $reference) {
// Extract range
list($currentCol, $currentRow) = Cell::coordinateFromString($reference);
// echo 'NAMED RANGE: $currentCol='.$currentCol.' $currentRow='.$currentRow.'<br />';
$cellValue = null;
if ($pSheet->cellExists($reference)) {
$returnValue[$currentRow][$currentCol] = $pSheet->getCell($reference)->getCalculatedValue($resetLog);
} else {
$returnValue[$currentRow][$currentCol] = null;
}
}
}
// print_r($returnValue);
// echo '<br />';
}
return $returnValue;
}
/**
* Write SheetData
*
* @param Shared_XMLWriter $objWriter XML Writer
* @param Worksheet $pSheet Worksheet
* @param string[] $pStringTable String table
* @throws Exception
*/
private function _writeSheetData(Shared_XMLWriter $objWriter = null, Worksheet $pSheet = null, $pStringTable = null)
{
if (is_array($pStringTable)) {
// Flipped stringtable, for faster index searching
$aFlippedStringTable = $this->getParentWriter()->getWriterPart('stringtable')->flipStringTable($pStringTable);
// sheetData
$objWriter->startElement('sheetData');
// Get column count
$colCount = Cell::columnIndexFromString($pSheet->getHighestColumn());
// Highest row number
$highestRow = $pSheet->getHighestRow();
// Loop through cells
$cellsByRow = array();
foreach ($pSheet->getCellCollection() as $cellID) {
$cellAddress = Cell::coordinateFromString($cellID);
$cellsByRow[$cellAddress[1]][] = $cellID;
}
for ($currentRow = 1; $currentRow <= $highestRow; ++$currentRow) {
// Get row dimension
$rowDimension = $pSheet->getRowDimension($currentRow);
// Write current row?
$writeCurrentRow = isset($cellsByRow[$currentRow]) || $rowDimension->getRowHeight() >= 0 || $rowDimension->getVisible() == false || $rowDimension->getCollapsed() == true || $rowDimension->getOutlineLevel() > 0 || $rowDimension->getXfIndex() !== null;
if ($writeCurrentRow) {
// Start a new row
$objWriter->startElement('row');
$objWriter->writeAttribute('r', $currentRow);
$objWriter->writeAttribute('spans', '1:' . $colCount);
// Row dimensions
if ($rowDimension->getRowHeight() >= 0) {
$objWriter->writeAttribute('customHeight', '1');
$objWriter->writeAttribute('ht', Shared_String::FormatNumber($rowDimension->getRowHeight()));
}
// Row visibility
if ($rowDimension->getVisible() == false) {
$objWriter->writeAttribute('hidden', 'true');
}
// Collapsed
if ($rowDimension->getCollapsed() == true) {
$objWriter->writeAttribute('collapsed', 'true');
}
// Outline level
if ($rowDimension->getOutlineLevel() > 0) {
$objWriter->writeAttribute('outlineLevel', $rowDimension->getOutlineLevel());
}
// Style
if ($rowDimension->getXfIndex() !== null) {
$objWriter->writeAttribute('s', $rowDimension->getXfIndex());
$objWriter->writeAttribute('customFormat', '1');
}
// Write cells
if (isset($cellsByRow[$currentRow])) {
foreach ($cellsByRow[$currentRow] as $cellAddress) {
// Write cell
$this->_writeCell($objWriter, $pSheet, $cellAddress, $pStringTable, $aFlippedStringTable);
}
}
// End row
$objWriter->endElement();
}
}
$objWriter->endElement();
} else {
throw new Exception("Invalid parameters passed.");
}
}
