Example #1
0
 /**
  *	Send notification to the cache controller
  *
  *	@return void
  **/
 public function notifyCacheController()
 {
     $this->_parent->getCellCacheController()->updateCacheData($this);
     return $this;
 }
Example #2
0
 /**
  * Insert a new column or row, updating all possible related data
  *
  * @param   string              $pBefore    Insert before this cell address (e.g. 'A1')
  * @param   integer             $pNumCols   Number of columns to insert/delete (negative values indicate deletion)
  * @param   integer             $pNumRows   Number of rows to insert/delete (negative values indicate deletion)
  * @param   PHPExcel_Worksheet  $pSheet     The worksheet that we're editing
  * @throws  PHPExcel_Exception
  */
 public function insertNewBefore($pBefore = 'A1', $pNumCols = 0, $pNumRows = 0, PHPExcel_Worksheet $pSheet = NULL)
 {
     $remove = $pNumCols < 0 || $pNumRows < 0;
     $aCellCollection = $pSheet->getCellCollection();
     // Get coordinates of $pBefore
     $beforeColumn = 'A';
     $beforeRow = 1;
     list($beforeColumn, $beforeRow) = PHPExcel_Cell::coordinateFromString($pBefore);
     $beforeColumnIndex = PHPExcel_Cell::columnIndexFromString($beforeColumn);
     // Clear cells if we are removing columns or rows
     $highestColumn = $pSheet->getHighestColumn();
     $highestRow = $pSheet->getHighestRow();
     // 1. Clear column strips if we are removing columns
     if ($pNumCols < 0 && $beforeColumnIndex - 2 + $pNumCols > 0) {
         for ($i = 1; $i <= $highestRow - 1; ++$i) {
             for ($j = $beforeColumnIndex - 1 + $pNumCols; $j <= $beforeColumnIndex - 2; ++$j) {
                 $coordinate = PHPExcel_Cell::stringFromColumnIndex($j) . $i;
                 $pSheet->removeConditionalStyles($coordinate);
                 if ($pSheet->cellExists($coordinate)) {
                     $pSheet->getCell($coordinate)->setValueExplicit('', PHPExcel_Cell_DataType::TYPE_NULL);
                     $pSheet->getCell($coordinate)->setXfIndex(0);
                 }
             }
         }
     }
     // 2. Clear row strips if we are removing rows
     if ($pNumRows < 0 && $beforeRow - 1 + $pNumRows > 0) {
         for ($i = $beforeColumnIndex - 1; $i <= PHPExcel_Cell::columnIndexFromString($highestColumn) - 1; ++$i) {
             for ($j = $beforeRow + $pNumRows; $j <= $beforeRow - 1; ++$j) {
                 $coordinate = PHPExcel_Cell::stringFromColumnIndex($i) . $j;
                 $pSheet->removeConditionalStyles($coordinate);
                 if ($pSheet->cellExists($coordinate)) {
                     $pSheet->getCell($coordinate)->setValueExplicit('', PHPExcel_Cell_DataType::TYPE_NULL);
                     $pSheet->getCell($coordinate)->setXfIndex(0);
                 }
             }
         }
     }
     // Loop through cells, bottom-up, and change cell coordinates
     while ($cellID = $remove ? array_shift($aCellCollection) : array_pop($aCellCollection)) {
         $cell = $pSheet->getCell($cellID);
         $cellIndex = PHPExcel_Cell::columnIndexFromString($cell->getColumn());
         if ($cellIndex - 1 + $pNumCols < 0) {
             continue;
         }
         // New coordinates
         $newCoordinates = PHPExcel_Cell::stringFromColumnIndex($cellIndex - 1 + $pNumCols) . ($cell->getRow() + $pNumRows);
         // Should the cell be updated? Move value and cellXf index from one cell to another.
         if ($cellIndex >= $beforeColumnIndex && $cell->getRow() >= $beforeRow) {
             // Update cell styles
             $pSheet->getCell($newCoordinates)->setXfIndex($cell->getXfIndex());
             // Insert this cell at its new location
             if ($cell->getDataType() == PHPExcel_Cell_DataType::TYPE_FORMULA) {
                 // Formula should be adjusted
                 $pSheet->getCell($newCoordinates)->setValue($this->updateFormulaReferences($cell->getValue(), $pBefore, $pNumCols, $pNumRows, $pSheet->getTitle()));
             } else {
                 // Formula should not be adjusted
                 $pSheet->getCell($newCoordinates)->setValue($cell->getValue());
             }
             // Clear the original cell
             $pSheet->getCellCacheController()->deleteCacheData($cellID);
         } else {
             /*	We don't need to update styles for rows/columns before our insertion position,
             			but we do still need to adjust any formulae	in those cells					*/
             if ($cell->getDataType() == PHPExcel_Cell_DataType::TYPE_FORMULA) {
                 // Formula should be adjusted
                 $cell->setValue($this->updateFormulaReferences($cell->getValue(), $pBefore, $pNumCols, $pNumRows, $pSheet->getTitle()));
             }
         }
     }
     // Duplicate styles for the newly inserted cells
     $highestColumn = $pSheet->getHighestColumn();
     $highestRow = $pSheet->getHighestRow();
     if ($pNumCols > 0 && $beforeColumnIndex - 2 > 0) {
         for ($i = $beforeRow; $i <= $highestRow - 1; ++$i) {
             // Style
             $coordinate = PHPExcel_Cell::stringFromColumnIndex($beforeColumnIndex - 2) . $i;
             if ($pSheet->cellExists($coordinate)) {
                 $xfIndex = $pSheet->getCell($coordinate)->getXfIndex();
                 $conditionalStyles = $pSheet->conditionalStylesExists($coordinate) ? $pSheet->getConditionalStyles($coordinate) : false;
                 for ($j = $beforeColumnIndex - 1; $j <= $beforeColumnIndex - 2 + $pNumCols; ++$j) {
                     $pSheet->getCellByColumnAndRow($j, $i)->setXfIndex($xfIndex);
                     if ($conditionalStyles) {
                         $cloned = array();
                         foreach ($conditionalStyles as $conditionalStyle) {
                             $cloned[] = clone $conditionalStyle;
                         }
                         $pSheet->setConditionalStyles(PHPExcel_Cell::stringFromColumnIndex($j) . $i, $cloned);
                     }
                 }
             }
         }
     }
     if ($pNumRows > 0 && $beforeRow - 1 > 0) {
         for ($i = $beforeColumnIndex - 1; $i <= PHPExcel_Cell::columnIndexFromString($highestColumn) - 1; ++$i) {
             // Style
             $coordinate = PHPExcel_Cell::stringFromColumnIndex($i) . ($beforeRow - 1);
             if ($pSheet->cellExists($coordinate)) {
                 $xfIndex = $pSheet->getCell($coordinate)->getXfIndex();
                 $conditionalStyles = $pSheet->conditionalStylesExists($coordinate) ? $pSheet->getConditionalStyles($coordinate) : false;
                 for ($j = $beforeRow; $j <= $beforeRow - 1 + $pNumRows; ++$j) {
                     $pSheet->getCell(PHPExcel_Cell::stringFromColumnIndex($i) . $j)->setXfIndex($xfIndex);
                     if ($conditionalStyles) {
                         $cloned = array();
                         foreach ($conditionalStyles as $conditionalStyle) {
                             $cloned[] = clone $conditionalStyle;
                         }
                         $pSheet->setConditionalStyles(PHPExcel_Cell::stringFromColumnIndex($i) . $j, $cloned);
                     }
                 }
             }
         }
     }
     // Update worksheet: column dimensions
     $this->_adjustColumnDimensions($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows);
     // Update worksheet: row dimensions
     $this->_adjustRowDimensions($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows);
     //	Update worksheet: page breaks
     $this->_adjustPageBreaks($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows);
     //	Update worksheet: comments
     $this->_adjustComments($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows);
     // Update worksheet: hyperlinks
     $this->_adjustHyperlinks($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows);
     // Update worksheet: data validations
     $this->_adjustDataValidations($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows);
     // Update worksheet: merge cells
     $this->_adjustMergeCells($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows);
     // Update worksheet: protected cells
     $this->_adjustProtectedCells($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows);
     // Update worksheet: autofilter
     $autoFilter = $pSheet->getAutoFilter();
     $autoFilterRange = $autoFilter->getRange();
     if (!empty($autoFilterRange)) {
         if ($pNumCols != 0) {
             $autoFilterColumns = array_keys($autoFilter->getColumns());
             if (count($autoFilterColumns) > 0) {
                 sscanf($pBefore, '%[A-Z]%d', $column, $row);
                 $columnIndex = PHPExcel_Cell::columnIndexFromString($column);
                 list($rangeStart, $rangeEnd) = PHPExcel_Cell::rangeBoundaries($autoFilterRange);
                 if ($columnIndex <= $rangeEnd[0]) {
                     if ($pNumCols < 0) {
                         //	If we're actually deleting any columns that fall within the autofilter range,
                         //		then we delete any rules for those columns
                         $deleteColumn = $columnIndex + $pNumCols - 1;
                         $deleteCount = abs($pNumCols);
                         for ($i = 1; $i <= $deleteCount; ++$i) {
                             if (in_array(PHPExcel_Cell::stringFromColumnIndex($deleteColumn), $autoFilterColumns)) {
                                 $autoFilter->clearColumn(PHPExcel_Cell::stringFromColumnIndex($deleteColumn));
                             }
                             ++$deleteColumn;
                         }
                     }
                     $startCol = $columnIndex > $rangeStart[0] ? $columnIndex : $rangeStart[0];
                     //	Shuffle columns in autofilter range
                     if ($pNumCols > 0) {
                         //	For insert, we shuffle from end to beginning to avoid overwriting
                         $startColID = PHPExcel_Cell::stringFromColumnIndex($startCol - 1);
                         $toColID = PHPExcel_Cell::stringFromColumnIndex($startCol + $pNumCols - 1);
                         $endColID = PHPExcel_Cell::stringFromColumnIndex($rangeEnd[0]);
                         $startColRef = $startCol;
                         $endColRef = $rangeEnd[0];
                         $toColRef = $rangeEnd[0] + $pNumCols;
                         do {
                             $autoFilter->shiftColumn(PHPExcel_Cell::stringFromColumnIndex($endColRef - 1), PHPExcel_Cell::stringFromColumnIndex($toColRef - 1));
                             --$endColRef;
                             --$toColRef;
                         } while ($startColRef <= $endColRef);
                     } else {
                         //	For delete, we shuffle from beginning to end to avoid overwriting
                         $startColID = PHPExcel_Cell::stringFromColumnIndex($startCol - 1);
                         $toColID = PHPExcel_Cell::stringFromColumnIndex($startCol + $pNumCols - 1);
                         $endColID = PHPExcel_Cell::stringFromColumnIndex($rangeEnd[0]);
                         do {
                             $autoFilter->shiftColumn($startColID, $toColID);
                             ++$startColID;
                             ++$toColID;
                         } while ($startColID != $endColID);
                     }
                 }
             }
         }
         $pSheet->setAutoFilter($this->updateCellReference($autoFilterRange, $pBefore, $pNumCols, $pNumRows));
     }
     // Update worksheet: freeze pane
     if ($pSheet->getFreezePane() != '') {
         $pSheet->freezePane($this->updateCellReference($pSheet->getFreezePane(), $pBefore, $pNumCols, $pNumRows));
     }
     // Page setup
     if ($pSheet->getPageSetup()->isPrintAreaSet()) {
         $pSheet->getPageSetup()->setPrintArea($this->updateCellReference($pSheet->getPageSetup()->getPrintArea(), $pBefore, $pNumCols, $pNumRows));
     }
     // Update worksheet: drawings
     $aDrawings = $pSheet->getDrawingCollection();
     foreach ($aDrawings as $objDrawing) {
         $newReference = $this->updateCellReference($objDrawing->getCoordinates(), $pBefore, $pNumCols, $pNumRows);
         if ($objDrawing->getCoordinates() != $newReference) {
             $objDrawing->setCoordinates($newReference);
         }
     }
     // Update workbook: named ranges
     if (count($pSheet->getParent()->getNamedRanges()) > 0) {
         foreach ($pSheet->getParent()->getNamedRanges() as $namedRange) {
             if ($namedRange->getWorksheet()->getHashCode() == $pSheet->getHashCode()) {
                 $namedRange->setRange($this->updateCellReference($namedRange->getRange(), $pBefore, $pNumCols, $pNumRows));
             }
         }
     }
     // Garbage collect
     $pSheet->garbageCollect();
 }
Example #3
0
 /**
  *    Re-bind parent
  *
  * @param    PHPExcel_Worksheet $parent
  *
  * @return    PHPExcel_Cell
  */
 public function rebindParent(PHPExcel_Worksheet $parent)
 {
     $this->_parent = $parent->getCellCacheController();
     return $this->notifyCacheController();
 }