PHP PHPExcel_Worksheet::getBreaks方法代码示例

//.........这里部分代码省略.........
     // Update worksheet: column dimensions
     $aColumnDimensions = array_reverse($pSheet->getColumnDimensions(), true);
     if (count($aColumnDimensions) > 0) {
         foreach ($aColumnDimensions as $objColumnDimension) {
             $newReference = $this->updateCellReference($objColumnDimension->getColumnIndex() . '1', $pBefore, $pNumCols, $pNumRows);
             list($newReference) = PHPExcel_Cell::coordinateFromString($newReference);
             if ($objColumnDimension->getColumnIndex() != $newReference) {
                 $objColumnDimension->setColumnIndex($newReference);
             }
         }
         $pSheet->refreshColumnDimensions();
     }
     // Update worksheet: row dimensions
     $aRowDimensions = array_reverse($pSheet->getRowDimensions(), true);
     if (count($aRowDimensions) > 0) {
         foreach ($aRowDimensions as $objRowDimension) {
             $newReference = $this->updateCellReference('A' . $objRowDimension->getRowIndex(), $pBefore, $pNumCols, $pNumRows);
             list(, $newReference) = PHPExcel_Cell::coordinateFromString($newReference);
             if ($objRowDimension->getRowIndex() != $newReference) {
                 $objRowDimension->setRowIndex($newReference);
             }
         }
         $pSheet->refreshRowDimensions();
         $copyDimension = $pSheet->getRowDimension($beforeRow - 1);
         for ($i = $beforeRow; $i <= $beforeRow - 1 + $pNumRows; ++$i) {
             $newDimension = $pSheet->getRowDimension($i);
             $newDimension->setRowHeight($copyDimension->getRowHeight());
             $newDimension->setVisible($copyDimension->getVisible());
             $newDimension->setOutlineLevel($copyDimension->getOutlineLevel());
             $newDimension->setCollapsed($copyDimension->getCollapsed());
         }
     }
     // Update worksheet: breaks
     $aBreaks = array_reverse($pSheet->getBreaks(), true);
     foreach ($aBreaks as $key => $value) {
         $newReference = $this->updateCellReference($key, $pBefore, $pNumCols, $pNumRows);
         if ($key != $newReference) {
             $pSheet->setBreak($newReference, $value);
             $pSheet->setBreak($key, PHPExcel_Worksheet::BREAK_NONE);
         }
     }
     // Update worksheet: hyperlinks
     $aHyperlinkCollection = array_reverse($pSheet->getHyperlinkCollection(), true);
     foreach ($aHyperlinkCollection as $key => $value) {
         $newReference = $this->updateCellReference($key, $pBefore, $pNumCols, $pNumRows);
         if ($key != $newReference) {
             $pSheet->setHyperlink($newReference, $value);
             $pSheet->setHyperlink($key, null);
         }
     }
     // Update worksheet: data validations
     $aDataValidationCollection = array_reverse($pSheet->getDataValidationCollection(), true);
     foreach ($aDataValidationCollection as $key => $value) {
         $newReference = $this->updateCellReference($key, $pBefore, $pNumCols, $pNumRows);
         if ($key != $newReference) {
             $pSheet->setDataValidation($newReference, $value);
             $pSheet->setDataValidation($key, null);
         }
     }
     // Update worksheet: merge cells
     $aMergeCells = $pSheet->getMergeCells();
     $aNewMergeCells = array();
     // the new array of all merge cells
     foreach ($aMergeCells as $key => &$value) {
         $newReference = $this->updateCellReference($key, $pBefore, $pNumCols, $pNumRows);
         $aNewMergeCells[$newReference] = $newReference;

 /**
  * 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 = PHPExcel_Cell::coordinateFromString($cell);
         // Decide what to do by the type of break
         switch ($breakType) {
             case PHPExcel_Worksheet::BREAK_COLUMN:
                 // Add to list of vertical breaks
                 $vbreaks[] = PHPExcel_Cell::columnIndexFromString($coordinates[0]) - 1;
                 break;
             case PHPExcel_Worksheet::BREAK_ROW:
                 // Add to list of horizontal breaks
                 $hbreaks[] = $coordinates[1];
                 break;
             case PHPExcel_Worksheet::BREAK_NONE:
             default:
                 // Nothing to do
                 break;
         }
     }
     //horizontal page breaks
     if (!empty($hbreaks)) {
         // 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
         $length = 2 + 6 * $cbrk;
         // Bytes to follow
         $header = pack("vv", $record, $length);
         $data = pack("v", $cbrk);
         // Append each page break
         foreach ($hbreaks as $hbreak) {
             $data .= pack("vvv", $hbreak, 0x0, 0xff);
         }
         $this->_append($header . $data);
     }
     // vertical page breaks
     if (!empty($vbreaks)) {
         // 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
         $length = 2 + 6 * $cbrk;
         // Bytes to follow
         $header = pack("vv", $record, $length);
         $data = pack("v", $cbrk);
         // Append each page break
         foreach ($vbreaks as $vbreak) {
             $data .= pack("vvv", $vbreak, 0x0, 0xffff);
         }
         $this->_append($header . $data);
     }
 }

 /**
  * Write Breaks
  *
  * @param	PHPExcel_Shared_XMLWriter		$objWriter		XML Writer
  * @param	PHPExcel_Worksheet				$pSheet			Worksheet
  * @throws	Exception
  */
 private function _writeBreaks(PHPExcel_Shared_XMLWriter $objWriter = null, PHPExcel_Worksheet $pSheet = null)
 {
     // Get row and column breaks
     $aRowBreaks = array();
     $aColumnBreaks = array();
     foreach ($pSheet->getBreaks() as $cell => $breakType) {
         if ($breakType == PHPExcel_Worksheet::BREAK_ROW) {
             $aRowBreaks[] = $cell;
         } else {
             if ($breakType == PHPExcel_Worksheet::BREAK_COLUMN) {
                 $aColumnBreaks[] = $cell;
             }
         }
     }
     // rowBreaks
     if (count($aRowBreaks) > 0) {
         $objWriter->startElement('rowBreaks');
         $objWriter->writeAttribute('count', count($aRowBreaks));
         $objWriter->writeAttribute('manualBreakCount', count($aRowBreaks));
         foreach ($aRowBreaks as $cell) {
             $coords = PHPExcel_Cell::coordinateFromString($cell);
             $objWriter->startElement('brk');
             $objWriter->writeAttribute('id', $coords[1]);
             $objWriter->writeAttribute('man', '1');
             $objWriter->endElement();
         }
         $objWriter->endElement();
     }
     // Second, write column breaks
     if (count($aColumnBreaks) > 0) {
         $objWriter->startElement('colBreaks');
         $objWriter->writeAttribute('count', count($aColumnBreaks));
         $objWriter->writeAttribute('manualBreakCount', count($aColumnBreaks));
         foreach ($aColumnBreaks as $cell) {
             $coords = PHPExcel_Cell::coordinateFromString($cell);
             $objWriter->startElement('brk');
             $objWriter->writeAttribute('id', PHPExcel_Cell::columnIndexFromString($coords[0]) - 1);
             $objWriter->writeAttribute('man', '1');
             $objWriter->endElement();
         }
         $objWriter->endElement();
     }
 }

 /**
  * Update page breaks when inserting/deleting rows/columns
  *
  * @param   PHPExcel_Worksheet  $pSheet             The worksheet that we're editing
  * @param   string              $pBefore            Insert/Delete before this cell address (e.g. 'A1')
  * @param   integer             $beforeColumnIndex  Index number of the column we're inserting/deleting before
  * @param   integer             $pNumCols           Number of columns to insert/delete (negative values indicate deletion)
  * @param   integer             $beforeRow          Number of the row we're inserting/deleting before
  * @param   integer             $pNumRows           Number of rows to insert/delete (negative values indicate deletion)
  */
 protected function _adjustPageBreaks(PHPExcel_Worksheet $pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows)
 {
     $aBreaks = $pSheet->getBreaks();
     $pNumCols > 0 || $pNumRows > 0 ? uksort($aBreaks, array('PHPExcel_ReferenceHelper', 'cellReverseSort')) : uksort($aBreaks, array('PHPExcel_ReferenceHelper', 'cellSort'));
     foreach ($aBreaks as $key => $value) {
         if (self::cellAddressInDeleteRange($key, $beforeRow, $pNumRows, $beforeColumnIndex, $pNumCols)) {
             //	If we're deleting, then clear any defined breaks that are within the range
             //		of rows/columns that we're deleting
             $pSheet->setBreak($key, PHPExcel_Worksheet::BREAK_NONE);
         } else {
             //	Otherwise update any affected breaks by inserting a new break at the appropriate point
             //		and removing the old affected break
             $newReference = $this->updateCellReference($key, $pBefore, $pNumCols, $pNumRows);
             if ($key != $newReference) {
                 $pSheet->setBreak($newReference, $value)->setBreak($key, PHPExcel_Worksheet::BREAK_NONE);
             }
         }
     }
 }

//.........这里部分代码省略.........
		$aColumnDimensions = array_reverse($pSheet->getColumnDimensions(), true);
		foreach ($aColumnDimensions as $objColumnDimension) {
			$newReference = $this->updateCellReference($objColumnDimension->getColumnIndex() . '1', $pBefore, $pNumCols, $pNumRows);
			list($newReference) = PHPExcel_Cell::coordinateFromString($newReference);
			if ($objColumnDimension->getColumnIndex() != $newReference) {
				$objColumnDimension->setColumnIndex($newReference);
			}
		}
		$pSheet->refreshColumnDimensions();


		// Update worksheet: row dimensions
		$aRowDimensions = array_reverse($pSheet->getRowDimensions(), true);
		foreach ($aRowDimensions as $objRowDimension) {
			$newReference = $this->updateCellReference('A' . $objRowDimension->getRowIndex(), $pBefore, $pNumCols, $pNumRows);
			list(, $newReference) = PHPExcel_Cell::coordinateFromString($newReference);
			if ($objRowDimension->getRowIndex() != $newReference) {
				$objRowDimension->setRowIndex($newReference);
			}
		}
		$pSheet->refreshRowDimensions();

		$copyDimension = $pSheet->getRowDimension($beforeRow - 1);
		for ($i = $beforeRow; $i <= $beforeRow - 1 + $pNumRows; $i++) {
			$newDimension = $pSheet->getRowDimension($i);
			$newDimension->setRowHeight($copyDimension->getRowHeight());
			$newDimension->setVisible($copyDimension->getVisible());
			$newDimension->setOutlineLevel($copyDimension->getOutlineLevel());
			$newDimension->setCollapsed($copyDimension->getCollapsed());
		}


		// Update worksheet: breaks
		$aBreaks = array_reverse($pSheet->getBreaks(), true);
		foreach ($aBreaks as $key => $value) {
			$newReference = $this->updateCellReference($key, $pBefore, $pNumCols, $pNumRows);
			if ($key != $newReference) {
				$pSheet->setBreak( $newReference, $value );
				$pSheet->setBreak( $key, PHPExcel_Worksheet::BREAK_NONE );
			}
		}


		// Update worksheet: merge cells
		$aMergeCells = array_reverse($pSheet->getMergeCells(), true);
		foreach ($aMergeCells as $key => $value) {
			$newReference = $this->updateCellReference($key, $pBefore, $pNumCols, $pNumRows);
			if ($key != $newReference) {
				$pSheet->mergeCells( $newReference );
				$pSheet->unmergeCells( $key );
			}
		}


		// Update worksheet: protected cells
		$aProtectedCells = array_reverse($pSheet->getProtectedCells(), true);
		foreach ($aProtectedCells as $key => $value) {
			$newReference = $this->updateCellReference($key, $pBefore, $pNumCols, $pNumRows);
			if ($key != $newReference) {
				$pSheet->protectCells( $newReference, $value, true );
				$pSheet->unprotectCells( $key );
			}
		}


		// Update worksheet: autofilter

//.........这里部分代码省略.........
     // Update worksheet: column dimensions
     $aColumnDimensions = array_reverse($pSheet->getColumnDimensions(), true);
     if (!empty($aColumnDimensions)) {
         foreach ($aColumnDimensions as $objColumnDimension) {
             $newReference = $this->updateCellReference($objColumnDimension->getColumnIndex() . '1', $pBefore, $pNumCols, $pNumRows);
             list($newReference) = PHPExcel_Cell::coordinateFromString($newReference);
             if ($objColumnDimension->getColumnIndex() != $newReference) {
                 $objColumnDimension->setColumnIndex($newReference);
             }
         }
         $pSheet->refreshColumnDimensions();
     }
     // Update worksheet: row dimensions
     $aRowDimensions = array_reverse($pSheet->getRowDimensions(), true);
     if (!empty($aRowDimensions)) {
         foreach ($aRowDimensions as $objRowDimension) {
             $newReference = $this->updateCellReference('A' . $objRowDimension->getRowIndex(), $pBefore, $pNumCols, $pNumRows);
             list(, $newReference) = PHPExcel_Cell::coordinateFromString($newReference);
             if ($objRowDimension->getRowIndex() != $newReference) {
                 $objRowDimension->setRowIndex($newReference);
             }
         }
         $pSheet->refreshRowDimensions();
         $copyDimension = $pSheet->getRowDimension($beforeRow - 1);
         for ($i = $beforeRow; $i <= $beforeRow - 1 + $pNumRows; ++$i) {
             $newDimension = $pSheet->getRowDimension($i);
             $newDimension->setRowHeight($copyDimension->getRowHeight());
             $newDimension->setVisible($copyDimension->getVisible());
             $newDimension->setOutlineLevel($copyDimension->getOutlineLevel());
             $newDimension->setCollapsed($copyDimension->getCollapsed());
         }
     }
     // Update worksheet: breaks
     $aBreaks = array_reverse($pSheet->getBreaks(), true);
     foreach ($aBreaks as $key => $value) {
         $newReference = $this->updateCellReference($key, $pBefore, $pNumCols, $pNumRows);
         if ($key != $newReference) {
             $pSheet->setBreak($newReference, $value);
             $pSheet->setBreak($key, PHPExcel_Worksheet::BREAK_NONE);
         }
     }
     // Update worksheet: comments
     $aComments = $pSheet->getComments();
     $aNewComments = array();
     // the new array of all comments
     foreach ($aComments as $key => &$value) {
         $newReference = $this->updateCellReference($key, $pBefore, $pNumCols, $pNumRows);
         $aNewComments[$newReference] = $value;
     }
     $pSheet->setComments($aNewComments);
     // replace the comments array
     // Update worksheet: hyperlinks
     $aHyperlinkCollection = array_reverse($pSheet->getHyperlinkCollection(), true);
     foreach ($aHyperlinkCollection as $key => $value) {
         $newReference = $this->updateCellReference($key, $pBefore, $pNumCols, $pNumRows);
         if ($key != $newReference) {
             $pSheet->setHyperlink($newReference, $value);
             $pSheet->setHyperlink($key, null);
         }
     }
     // Update worksheet: data validations
     $aDataValidationCollection = array_reverse($pSheet->getDataValidationCollection(), true);
     foreach ($aDataValidationCollection as $key => $value) {
         $newReference = $this->updateCellReference($key, $pBefore, $pNumCols, $pNumRows);
         if ($key != $newReference) {
             $pSheet->setDataValidation($newReference, $value);