You are here

Home » API reference » Loft Data Grids 6.2 » ReferenceHelper.php » PHPExcel_ReferenceHelper

public function PHPExcel_ReferenceHelper::insertNewBefore in Loft Data Grids 6.2

Same name and namespace in other branches
  1. 7.2 vendor/phpoffice/phpexcel/Classes/PHPExcel/ReferenceHelper.php \PHPExcel_ReferenceHelper::insertNewBefore()

* Insert a new column or row, updating all possible related data * *

Parameters

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

File

vendor/phpoffice/phpexcel/Classes/PHPExcel/ReferenceHelper.php, line 383

Class

PHPExcel_ReferenceHelper
PHPExcel_ReferenceHelper (Singleton)

Code

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
  if ($remove) {

    // It's faster to reverse and pop than to use unshift, especially with large cell collections
    $aCellCollection = array_reverse($aCellCollection);
  }
  while ($cellID = 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();
}