示例#1
5
 /**
  * Insert a new column, updating all possible related data
  *
  * @param	int	$pBefore	Insert before this one
  * @param	int	$pNumCols	Number of columns to insert
  * @param	int	$pNumRows	Number of rows to insert
  * @throws	Exception
  */
 public function insertNewBefore($pBefore = 'A1', $pNumCols = 0, $pNumRows = 0, PHPExcel_Worksheet $pSheet = null)
 {
     // Get a copy of the cell collection
     /*$aTemp = $pSheet->getCellCollection();
     		$aCellCollection = array();
     		foreach ($aTemp as $key => $value) {
     			$aCellCollection[$key] = clone $value;
     		}*/
     $aCellCollection = $pSheet->getCellCollection();
     // Get coordinates of $pBefore
     $beforeColumn = 'A';
     $beforeRow = 1;
     list($beforeColumn, $beforeRow) = PHPExcel_Cell::coordinateFromString($pBefore);
     // 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 && PHPExcel_Cell::columnIndexFromString($beforeColumn) - 2 + $pNumCols > 0) {
         for ($i = 1; $i <= $highestRow - 1; ++$i) {
             for ($j = PHPExcel_Cell::columnIndexFromString($beforeColumn) - 1 + $pNumCols; $j <= PHPExcel_Cell::columnIndexFromString($beforeColumn) - 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 = PHPExcel_Cell::columnIndexFromString($beforeColumn) - 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 ($cell = $pNumCols < 0 || $pNumRows < 0 ? array_shift($aCellCollection) : array_pop($aCellCollection)) {
         // New coordinates
         $newCoordinates = PHPExcel_Cell::stringFromColumnIndex(PHPExcel_Cell::columnIndexFromString($cell->getColumn()) - 1 + $pNumCols) . ($cell->getRow() + $pNumRows);
         // Should the cell be updated? Move value and cellXf index from one cell to another.
         if (PHPExcel_Cell::columnIndexFromString($cell->getColumn()) >= PHPExcel_Cell::columnIndexFromString($beforeColumn) && $cell->getRow() >= $beforeRow) {
             // Update cell styles
             $pSheet->getCell($newCoordinates)->setXfIndex($cell->getXfIndex());
             $cell->setXfIndex(0);
             // Insert this cell at its new location
             if ($cell->getDataType() == PHPExcel_Cell_DataType::TYPE_FORMULA) {
                 // Formula should be adjusted
                 $pSheet->setCellValue($newCoordinates, $this->updateFormulaReferences($cell->getValue(), $pBefore, $pNumCols, $pNumRows));
             } else {
                 // Formula should not be adjusted
                 $pSheet->setCellValue($newCoordinates, $cell->getValue());
             }
             // Clear the original cell
             $pSheet->setCellValue($cell->getCoordinate(), '');
         }
     }
     // Duplicate styles for the newly inserted cells
     $highestColumn = $pSheet->getHighestColumn();
     $highestRow = $pSheet->getHighestRow();
     if ($pNumCols > 0 && PHPExcel_Cell::columnIndexFromString($beforeColumn) - 2 > 0) {
         for ($i = $beforeRow; $i <= $highestRow - 1; ++$i) {
             // Style
             $coordinate = PHPExcel_Cell::stringFromColumnIndex(PHPExcel_Cell::columnIndexFromString($beforeColumn) - 2) . $i;
             if ($pSheet->cellExists($coordinate)) {
                 $xfIndex = $pSheet->getCell($coordinate)->getXfIndex();
                 $conditionalStyles = $pSheet->conditionalStylesExists($coordinate) ? $pSheet->getConditionalStyles($coordinate) : false;
                 for ($j = PHPExcel_Cell::columnIndexFromString($beforeColumn) - 1; $j <= PHPExcel_Cell::columnIndexFromString($beforeColumn) - 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 = PHPExcel_Cell::columnIndexFromString($beforeColumn) - 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
     $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;
     }
     $pSheet->setMergeCells($aNewMergeCells);
     // replace the merge cells array
     // 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
     if ($pSheet->getAutoFilter() != '') {
         $pSheet->setAutoFilter($this->updateCellReference($pSheet->getAutoFilter(), $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();
 }
示例#2
0
 /**
  * Set Worksheet
  *
  * @param PHPExcel_Worksheet $pValue            
  * @param bool $pOverrideOld
  *            Worksheet has already been assigned, overwrite it and remove image from old Worksheet?
  * @throws PHPExcel_Exception
  * @return PHPExcel_Worksheet_BaseDrawing
  */
 public function setWorksheet(PHPExcel_Worksheet $pValue = null, $pOverrideOld = false)
 {
     if (is_null($this->_worksheet)) {
         // Add drawing to PHPExcel_Worksheet
         $this->_worksheet = $pValue;
         $this->_worksheet->getCell($this->_coordinates);
         $this->_worksheet->getDrawingCollection()->append($this);
     } else {
         if ($pOverrideOld) {
             // Remove drawing from old PHPExcel_Worksheet
             $iterator = $this->_worksheet->getDrawingCollection()->getIterator();
             
             while ($iterator->valid()) {
                 if ($iterator->current()->getHashCode() == $this->getHashCode()) {
                     $this->_worksheet->getDrawingCollection()->offsetUnset($iterator->key());
                     $this->_worksheet = null;
                     break;
                 }
             }
             
             // Set new PHPExcel_Worksheet
             $this->setWorksheet($pValue);
         } else {
             throw new PHPExcel_Exception("A PHPExcel_Worksheet has already been assigned. Drawings can only exist on one PHPExcel_Worksheet.");
         }
     }
     return $this;
 }
示例#3
0
 /**
  * Write drawings to XML format
  *
  * @param 	PHPExcel_Worksheet				$pWorksheet
  * @return 	string 								XML Output
  * @throws 	Exception
  */
 public function writeDrawings(PHPExcel_Worksheet $pWorksheet = null)
 {
     // Create XML writer
     $objWriter = null;
     if ($this->getParentWriter()->getUseDiskCaching()) {
         $objWriter = new PHPExcel_Shared_XMLWriter(PHPExcel_Shared_XMLWriter::STORAGE_DISK);
     } else {
         $objWriter = new PHPExcel_Shared_XMLWriter(PHPExcel_Shared_XMLWriter::STORAGE_MEMORY);
     }
     // XML header
     $objWriter->startDocument('1.0', 'UTF-8', 'yes');
     // xdr:wsDr
     $objWriter->startElement('xdr:wsDr');
     $objWriter->writeAttribute('xmlns:xdr', 'http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing');
     $objWriter->writeAttribute('xmlns:a', 'http://schemas.openxmlformats.org/drawingml/2006/main');
     // Loop trough images and write drawings
     $i = 1;
     $iterator = $pWorksheet->getDrawingCollection()->getIterator();
     while ($iterator->valid()) {
         $this->_writeDrawing($objWriter, $iterator->current(), $i);
         $iterator->next();
         ++$i;
     }
     $objWriter->endElement();
     // Return
     return $objWriter->getData();
 }
示例#4
0
	/**
	 * Write drawings to XML format
	 *
	 * @param 	PHPExcel_Worksheet	$pWorksheet
	 * @param	int					&$chartRef		Chart ID
	 * @param	boolean				$includeCharts	Flag indicating if we should include drawing details for charts
	 * @return 	string 				XML Output
	 * @throws 	PHPExcel_Writer_Exception
	 */
	public function writeDrawings(PHPExcel_Worksheet $pWorksheet = null, &$chartRef, $includeCharts = FALSE)
	{
		// Create XML writer
		$objWriter = null;
		if ($this->getParentWriter()->getUseDiskCaching()) {
			$objWriter = new PHPExcel_Shared_XMLWriter(PHPExcel_Shared_XMLWriter::STORAGE_DISK, $this->getParentWriter()->getDiskCachingDirectory());
		} else {
			$objWriter = new PHPExcel_Shared_XMLWriter(PHPExcel_Shared_XMLWriter::STORAGE_MEMORY);
		}

		// XML header
		$objWriter->startDocument('1.0','UTF-8','yes');

		// xdr:wsDr
		$objWriter->startElement('xdr:wsDr');
		$objWriter->writeAttribute('xmlns:xdr', 'http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing');
		$objWriter->writeAttribute('xmlns:a', 'http://schemas.openxmlformats.org/drawingml/2006/main');

			// Loop through ATS_REPORT_IMAGE and write drawings
			$i = 1;
			$iterator = $pWorksheet->getDrawingCollection()->getIterator();
			while ($iterator->valid()) {
				$this->_writeDrawing($objWriter, $iterator->current(), $i);

				$iterator->next();
				++$i;
			}

			if ($includeCharts) {
				$chartCount = $pWorksheet->getChartCount();
				// Loop through charts and write the chart position
				if ($chartCount > 0) {
					for ($c = 0; $c < $chartCount; ++$c) {
						$this->_writeChart($objWriter, $pWorksheet->getChartByIndex($c), $c+$i);
					}
				}
			}


		$objWriter->endElement();

		// Return
		return $objWriter->getData();
	}
示例#5
0
 /**
  * Generate image tag in cell
  *
  * @param	PHPExcel_Worksheet	$pSheet			PHPExcel_Worksheet
  * @param	string				$coordinates	Cell coordinates
  * @return	string
  * @throws	PHPExcel_Writer_Exception
  */
 private function _writeImageInCell(PHPExcel_Worksheet $pSheet, $coordinates)
 {
     // Construct HTML
     $html = '';
     // Write images
     foreach ($pSheet->getDrawingCollection() as $drawing) {
         if ($drawing instanceof PHPExcel_Worksheet_Drawing) {
             if ($drawing->getCoordinates() == $coordinates) {
                 $filename = $drawing->getPath();
                 // Strip off eventual '.'
                 if (substr($filename, 0, 1) == '.') {
                     $filename = substr($filename, 1);
                 }
                 // Prepend images root
                 $filename = $this->getImagesRoot() . $filename;
                 // Strip off eventual '.'
                 if (substr($filename, 0, 1) == '.' && substr($filename, 0, 2) != './') {
                     $filename = substr($filename, 1);
                 }
                 // Convert UTF8 data to PCDATA
                 $filename = htmlspecialchars($filename);
                 $html .= PHP_EOL;
                 if (!$this->_embedImages || $this->_isPdf) {
                     $imageData = $filename;
                 } else {
                     $imageDetails = getimagesize($filename);
                     if ($fp = fopen($filename, "rb", 0)) {
                         $picture = fread($fp, filesize($filename));
                         fclose($fp);
                         // base64 encode the binary data, then break it
                         // into chunks according to RFC 2045 semantics
                         $base64 = chunk_split(base64_encode($picture));
                         $imageData = 'data:' . $imageDetails['mime'] . ';base64,' . $base64;
                     } else {
                         $imageData = $filename;
                     }
                 }
                 $html .= '<div style="position: relative;">';
                 $html .= '<img style="position: absolute; z-index: 1; left: ' . $drawing->getOffsetX() . 'px; top: ' . $drawing->getOffsetY() . 'px; width: ' . $drawing->getWidth() . 'px; height: ' . $drawing->getHeight() . 'px;" src="' . $imageData . '" border="0" />' . PHP_EOL;
                 $html .= '</div>';
             }
         }
     }
     // Return
     return $html;
 }
示例#6
0
 /**
  * Write Drawings
  *
  * @param	PHPExcel_Shared_XMLWriter		$objWriter		XML Writer
  * @param	PHPExcel_Worksheet				$pSheet			Worksheet
  * @throws	Exception
  */
 private function _writeDrawings(PHPExcel_Shared_XMLWriter $objWriter = null, PHPExcel_Worksheet $pSheet = null)
 {
     // If sheet contains drawings, add the relationships
     if ($pSheet->getDrawingCollection()->count() > 0) {
         $objWriter->startElement('drawing');
         $objWriter->writeAttribute('r:id', 'rId1');
         $objWriter->endElement();
     }
 }
 /**
  * Write drawing relationships to XML format
  *
  * @param 	PHPExcel_Worksheet			$pWorksheet
  * @return 	string 						XML Output
  * @throws 	Exception
  */
 public function writeDrawingRelationships(PHPExcel_Worksheet $pWorksheet = null)
 {
     // Create XML writer
     $objWriter = null;
     if ($this->getParentWriter()->getUseDiskCaching()) {
         $objWriter = new PHPExcel_Shared_XMLWriter(PHPExcel_Shared_XMLWriter::STORAGE_DISK, $this->getParentWriter()->getDiskCachingDirectory());
     } else {
         $objWriter = new PHPExcel_Shared_XMLWriter(PHPExcel_Shared_XMLWriter::STORAGE_MEMORY);
     }
     // XML header
     $objWriter->startDocument('1.0', 'UTF-8', 'yes');
     // Relationships
     $objWriter->startElement('Relationships');
     $objWriter->writeAttribute('xmlns', 'http://schemas.openxmlformats.org/package/2006/relationships');
     // Loop trough images and write relationships
     $i = 1;
     $iterator = $pWorksheet->getDrawingCollection()->getIterator();
     while ($iterator->valid()) {
         if ($iterator->current() instanceof PHPExcel_Worksheet_Drawing || $iterator->current() instanceof PHPExcel_Worksheet_MemoryDrawing) {
             // Write relationship for image drawing
             $this->_writeRelationship($objWriter, $i, 'http://schemas.openxmlformats.org/officeDocument/2006/relationships/image', '../media/' . str_replace(' ', '', $iterator->current()->getIndexedFilename()));
         }
         $iterator->next();
         ++$i;
     }
     $objWriter->endElement();
     // Return
     return $objWriter->getData();
 }
示例#8
0
文件: HTML.php 项目: ohjack/newErp
 /**
  * Generate image tag in cell
  *
  * @param	PHPExcel_Worksheet 	$pSheet			PHPExcel_Worksheet
  * @param	string				$coordinates	Cell coordinates
  * @return	string
  * @throws	Exception
  */
 private function _writeImageTagInCell(PHPExcel_Worksheet $pSheet, $coordinates)
 {
     // Construct HTML
     $html = '';
     // Write images
     foreach ($pSheet->getDrawingCollection() as $drawing) {
         if ($drawing instanceof PHPExcel_Worksheet_Drawing) {
             if ($drawing->getCoordinates() == $coordinates) {
                 $filename = $drawing->getPath();
                 // Strip off eventual '.'
                 if (substr($filename, 0, 1) == '.') {
                     $filename = substr($filename, 1);
                 }
                 // Prepend images root
                 $filename = $this->getImagesRoot() . $filename;
                 // Strip off eventual '.'
                 if (substr($filename, 0, 1) == '.' && substr($filename, 0, 2) != './') {
                     $filename = substr($filename, 1);
                 }
                 // Convert UTF8 data to PCDATA
                 $filename = htmlspecialchars($filename);
                 $html .= "\r\n";
                 $html .= '        <img style="position: relative; left: ' . $drawing->getOffsetX() . 'px; top: ' . $drawing->getOffsetY() . 'px; width: ' . $drawing->getWidth() . 'px; height: ' . $drawing->getHeight() . 'px;" src="' . $filename . '" border="0" width="' . $drawing->getWidth() . '" height="' . $drawing->getHeight() . '" />' . "\r\n";
             }
         }
     }
     // Return
     return $html;
 }
示例#9
0
    /**
     * Insert a new column, updating all possible related data
     *
     * @param 	int	$pBefore	Insert before this one
     * @param 	int	$pNumCols	Number of columns to insert
     * @param 	int	$pNumRows	Number of rows to insert
     * @throws 	Exception
     */
    public function insertNewBefore($pBefore = 'A1', $pNumCols = 0, $pNumRows = 0, PHPExcel_Worksheet $pSheet = null) {
		// Get a copy of the cell collection
		/*$aTemp = $pSheet->getCellCollection();
		$aCellCollection = array();
		foreach ($aTemp as $key => $value) {
			$aCellCollection[$key] = clone $value;
		}*/
		$aCellCollection = $pSheet->getCellCollection();

    	// Get coordinates of $pBefore
    	$beforeColumn 	= 'A';
    	$beforeRow		= 1;
    	list($beforeColumn, $beforeRow) = PHPExcel_Cell::coordinateFromString( $pBefore );


		// Remove cell styles?
		$highestColumn 	= $pSheet->getHighestColumn();
		$highestRow 	= $pSheet->getHighestRow();

		if ($pNumCols < 0 && PHPExcel_Cell::columnIndexFromString($beforeColumn) - 2 + $pNumCols > 0) {
			for ($i = 1; $i <= $highestRow - 1; $i++) {

				$pSheet->duplicateStyle(
					new PHPExcel_Style(),
					(PHPExcel_Cell::stringFromColumnIndex( PHPExcel_Cell::columnIndexFromString($beforeColumn) - 1 + $pNumCols ) . $i) . ':' . (PHPExcel_Cell::stringFromColumnIndex( PHPExcel_Cell::columnIndexFromString($beforeColumn) - 2 ) . $i)
				);

			}
		}

		if ($pNumRows < 0 && $beforeRow - 1 + $pNumRows > 0) {
			for ($i = PHPExcel_Cell::columnIndexFromString($beforeColumn) - 1; $i <= PHPExcel_Cell::columnIndexFromString($highestColumn) - 1; $i++) {

				$pSheet->duplicateStyle(
					new PHPExcel_Style(),
					(PHPExcel_Cell::stringFromColumnIndex($i) . ($beforeRow + $pNumRows)) . ':' . (PHPExcel_Cell::stringFromColumnIndex($i) . ($beforeRow - 1))
				);

			}
		}


   		// Loop trough cells, bottom-up, and change cell coordinates
		while ( ($cell = ($pNumCols < 0 || $pNumRows < 0) ? array_shift($aCellCollection) : array_pop($aCellCollection)) ) {
			// New coordinates
			$newCoordinates = PHPExcel_Cell::stringFromColumnIndex( PHPExcel_Cell::columnIndexFromString($cell->getColumn()) - 1 + $pNumCols ) . ($cell->getRow() + $pNumRows);

			// Should the cell be updated?
			if (
					(PHPExcel_Cell::columnIndexFromString( $cell->getColumn() ) >= PHPExcel_Cell::columnIndexFromString($beforeColumn)) &&
				 	($cell->getRow() >= $beforeRow)
				 ) {

				// Update cell styles
				$pSheet->duplicateStyle( $pSheet->getStyle($cell->getCoordinate()), $newCoordinates . ':' . $newCoordinates );
				$pSheet->duplicateStyle( $pSheet->getDefaultStyle(), $cell->getCoordinate() . ':' . $cell->getCoordinate() );

				// Insert this cell at its new location
				if ($cell->getDataType() == PHPExcel_Cell_DataType::TYPE_FORMULA) {
					// Formula should be adjusted
					$pSheet->setCellValue(
						  $newCoordinates
						, $this->updateFormulaReferences($cell->getValue(), $pBefore, $pNumCols, $pNumRows)
					);
				} else {
					// Formula should not be adjusted
					$pSheet->setCellValue($newCoordinates, $cell->getValue());
				}

				// Clear the original cell
				$pSheet->setCellValue($cell->getCoordinate(), '');
			}
		}


		// Duplicate styles for the newly inserted cells
		$highestColumn 	= $pSheet->getHighestColumn();
		$highestRow 	= $pSheet->getHighestRow();

		if ($pNumCols > 0 && PHPExcel_Cell::columnIndexFromString($beforeColumn) - 2 > 0) {
			for ($i = $beforeRow; $i <= $highestRow - 1; $i++) {

				// Style
				$pSheet->duplicateStyle(
					$pSheet->getStyle(
						(PHPExcel_Cell::stringFromColumnIndex( PHPExcel_Cell::columnIndexFromString($beforeColumn) - 2 ) . $i)
					),
					($beforeColumn . $i) . ':' . (PHPExcel_Cell::stringFromColumnIndex( PHPExcel_Cell::columnIndexFromString($beforeColumn) - 2 + $pNumCols ) . $i)
				);

			}
		}

		if ($pNumRows > 0 && $beforeRow - 1 > 0) {
			for ($i = PHPExcel_Cell::columnIndexFromString($beforeColumn) - 1; $i <= PHPExcel_Cell::columnIndexFromString($highestColumn) - 1; $i++) {

				// Style
				$pSheet->duplicateStyle(
					$pSheet->getStyle(
						(PHPExcel_Cell::stringFromColumnIndex($i) . ($beforeRow - 1))
					),
					(PHPExcel_Cell::stringFromColumnIndex($i) . $beforeRow) . ':' . (PHPExcel_Cell::stringFromColumnIndex($i) . ($beforeRow - 1 + $pNumRows))
				);

			}
		}


		// Update worksheet: column dimensions
		$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
		if ($pSheet->getAutoFilter() != '') {
			$pSheet->setAutoFilter( $this->updateCellReference($pSheet->getAutoFilter(), $pBefore, $pNumCols, $pNumRows) );
		}


		// Update worksheet: freeze pane
		if ($pSheet->getFreezePane() != '') {
			$pSheet->setFreezePane( $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();
    }
示例#10
0
 /**
  * Write MSODRAWING record
  */
 private function _writeMsoDrawing()
 {
     // check if there are any shapes for this sheet
     if (count($this->_phpSheet->getDrawingCollection()) == 0) {
         return;
     }
     // create intermediate Escher object
     $escher = new PHPExcel_Shared_Escher();
     // dgContainer
     $dgContainer = new PHPExcel_Shared_Escher_DgContainer();
     // set the drawing index (we use sheet index + 1)
     $dgContainer->setDgId($this->_phpSheet->getParent()->getIndex($this->_phpSheet) + 1);
     $escher->setDgContainer($dgContainer);
     // spgrContainer
     $spgrContainer = new PHPExcel_Shared_Escher_DgContainer_SpgrContainer();
     $dgContainer->setSpgrContainer($spgrContainer);
     // add one shape which is the group shape
     $spContainer = new PHPExcel_Shared_Escher_DgContainer_SpgrContainer_SpContainer();
     $spContainer->setSpgr(true);
     $spContainer->setSpType(0);
     $spContainer->setSpId($this->_phpSheet->getParent()->getIndex($this->_phpSheet) + 1 << 10);
     $spgrContainer->addChild($spContainer);
     // add the shapes
     // outer loop is for determining BSE index
     $blipIndex = 0;
     // 1-based index to BstoreContainer
     $countShapes = 0;
     // count number of shapes (minus group shape), in this sheet
     foreach ($this->_phpSheet->getParent()->getAllsheets() as $sheet) {
         foreach ($sheet->getDrawingCollection() as $drawing) {
             ++$blipIndex;
             if ($sheet === $this->_phpSheet) {
                 ++$countShapes;
                 // add the shape
                 $spContainer = new PHPExcel_Shared_Escher_DgContainer_SpgrContainer_SpContainer();
                 // set the shape type
                 $spContainer->setSpType(0x4b);
                 // set the shape index (we combine 1-based sheet index and $countShapes to create unique shape index)
                 $spId = $countShapes | $this->_phpSheet->getParent()->getIndex($this->_phpSheet) + 1 << 10;
                 $spContainer->setSpId($spId);
                 // keep track of last spId
                 $lastSpId = $spId;
                 // set the BLIP index
                 $spContainer->setOPT(0x4104, $blipIndex);
                 // set coordinates and offsets, client anchor
                 $coordinates = $drawing->getCoordinates();
                 $offsetX = $drawing->getOffsetX();
                 $offsetY = $drawing->getOffsetY();
                 $width = $drawing->getWidth();
                 $height = $drawing->getHeight();
                 $twoAnchor = PHPExcel_Shared_Excel5::oneAnchor2twoAnchor($this->_phpSheet, $coordinates, $offsetX, $offsetY, $width, $height);
                 $spContainer->setStartCoordinates($twoAnchor['startCoordinates']);
                 $spContainer->setStartOffsetX($twoAnchor['startOffsetX']);
                 $spContainer->setStartOffsetY($twoAnchor['startOffsetY']);
                 $spContainer->setEndCoordinates($twoAnchor['endCoordinates']);
                 $spContainer->setEndOffsetX($twoAnchor['endOffsetX']);
                 $spContainer->setEndOffsetY($twoAnchor['endOffsetY']);
                 $spgrContainer->addChild($spContainer);
             }
         }
     }
     // set last shape index
     $dgContainer->setLastSpId($lastSpId);
     // write the Escher stream
     $writer = new PHPExcel_Writer_Excel5_Escher($escher);
     $data = $writer->close();
     $spOffsets = $writer->getSpOffsets();
     // write the neccesary MSODRAWING, OBJ records
     // split the Escher stream
     $spOffsets[0] = 0;
     $nm = count($spOffsets) - 1;
     // number of shapes excluding first shape
     for ($i = 1; $i <= $nm; ++$i) {
         // MSODRAWING record
         $record = 0xec;
         // Record identifier
         // chunk of Escher stream for one shape
         $dataChunk = substr($data, $spOffsets[$i - 1], $spOffsets[$i] - $spOffsets[$i - 1]);
         $length = strlen($dataChunk);
         $header = pack("vv", $record, $length);
         $this->_append($header . $dataChunk);
         // OBJ record
         $record = 0x5d;
         // record identifier
         $objData = '';
         // ftCmo
         $objData .= pack('vvvvvVVV', 0x15, 0x12, 0x8, $i, 0x6011, 0, 0, 0);
         // ftEnd
         $objData .= pack('vv', 0x0, 0x0);
         $length = strlen($objData);
         $header = pack('vv', $record, $length);
         $this->_append($header . $objData);
     }
 }
示例#11
0
 /**
  * Write images to file
  * 
  * @param	mixed				$pFileHandle	PHP filehandle
  * @param	PHPExcel_Worksheet 	$pSheet			PHPExcel_Worksheet
  * @param	string				$coordinates	Cell coordinates
  * @throws	Exception
  */
 private function _writeImageInCell($pFileHandle = null, PHPExcel_Worksheet $pSheet, $coordinates)
 {
     if (!is_null($pFileHandle)) {
         // Construct HTML
         $html = '';
         // Write images
         foreach ($pSheet->getDrawingCollection() as $drawing) {
             if ($drawing instanceof PHPExcel_Worksheet_BaseDrawing) {
                 if ($drawing->getCoordinates() == $coordinates) {
                     $filename = $drawing->getPath();
                     $html .= "\r\n";
                     $html .= '        <img  style="position: relative; left: ' . $drawing->getOffsetX() . 'px; top: ' . $drawing->getOffsetY() . 'px; width: ' . $drawing->getWidth() . 'px; height: ' . $drawing->getHeight() . 'px;" src="' . $filename . '" border="0">' . "\r\n";
                 }
             }
         }
         // Write to file
         fwrite($pFileHandle, $html);
     } else {
         throw new Exception("Invalid parameters passed.");
     }
 }
示例#12
0
 /**
  * Insert a new column, updating all possible related data
  *
  * @param	int	$pBefore	Insert before this one
  * @param	int	$pNumCols	Number of columns to insert
  * @param	int	$pNumRows	Number of rows to insert
  * @throws	Exception
  */
 public function insertNewBefore($pBefore = 'A1', $pNumCols = 0, $pNumRows = 0, PHPExcel_Worksheet $pSheet = null)
 {
     $aCellCollection = $pSheet->getCellCollection();
     // Get coordinates of $pBefore
     $beforeColumn = 'A';
     $beforeRow = 1;
     list($beforeColumn, $beforeRow) = PHPExcel_Cell::coordinateFromString($pBefore);
     // 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 && PHPExcel_Cell::columnIndexFromString($beforeColumn) - 2 + $pNumCols > 0) {
         for ($i = 1; $i <= $highestRow - 1; ++$i) {
             for ($j = PHPExcel_Cell::columnIndexFromString($beforeColumn) - 1 + $pNumCols; $j <= PHPExcel_Cell::columnIndexFromString($beforeColumn) - 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 = PHPExcel_Cell::columnIndexFromString($beforeColumn) - 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 = $pNumCols < 0 || $pNumRows < 0 ? array_shift($aCellCollection) : array_pop($aCellCollection)) {
         $cell = $pSheet->getCell($cellID);
         // New coordinates
         $newCoordinates = PHPExcel_Cell::stringFromColumnIndex(PHPExcel_Cell::columnIndexFromString($cell->getColumn()) - 1 + $pNumCols) . ($cell->getRow() + $pNumRows);
         // Should the cell be updated? Move value and cellXf index from one cell to another.
         if (PHPExcel_Cell::columnIndexFromString($cell->getColumn()) >= PHPExcel_Cell::columnIndexFromString($beforeColumn) && $cell->getRow() >= $beforeRow) {
             // Update cell styles
             $pSheet->getCell($newCoordinates)->setXfIndex($cell->getXfIndex());
             $cell->setXfIndex(0);
             // 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->getCell($cell->getCoordinate())->setValue('');
         } 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 && PHPExcel_Cell::columnIndexFromString($beforeColumn) - 2 > 0) {
         for ($i = $beforeRow; $i <= $highestRow - 1; ++$i) {
             // Style
             $coordinate = PHPExcel_Cell::stringFromColumnIndex(PHPExcel_Cell::columnIndexFromString($beforeColumn) - 2) . $i;
             if ($pSheet->cellExists($coordinate)) {
                 $xfIndex = $pSheet->getCell($coordinate)->getXfIndex();
                 $conditionalStyles = $pSheet->conditionalStylesExists($coordinate) ? $pSheet->getConditionalStyles($coordinate) : false;
                 for ($j = PHPExcel_Cell::columnIndexFromString($beforeColumn) - 1; $j <= PHPExcel_Cell::columnIndexFromString($beforeColumn) - 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 = PHPExcel_Cell::columnIndexFromString($beforeColumn) - 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
     $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);
             $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;
     }
     $pSheet->setMergeCells($aNewMergeCells);
     // replace the merge cells array
     // 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
     $autoFilter = $pSheet->getAutoFilter();
     $autoFilterRange = $autoFilter->getRange();
     if (!empty($autoFilterRange)) {
         if ($pNumCols != 0) {
             $autoFilterColumns = array_keys($autoFilter->getColumns());
             if (count($autoFilterColumns) > 0) {
                 list($column, $row) = sscanf($pBefore, '%[A-Z]%d');
                 $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();
 }
示例#13
0
 /**
  * Write Drawings
  *
  * @param    PHPExcel_Shared_XMLWriter    $objWriter        XML Writer
  * @param    PHPExcel_Worksheet            $pSheet            Worksheet
  * @param    boolean                        $includeCharts    Flag indicating if we should include drawing details for charts
  * @throws    PHPExcel_Writer_Exception
  */
 private function writeDrawings(PHPExcel_Shared_XMLWriter $objWriter = null, PHPExcel_Worksheet $pSheet = null, $includeCharts = false)
 {
     $chartCount = $includeCharts ? $pSheet->getChartCollection()->count() : 0;
     // If sheet contains drawings, add the relationships
     if ($pSheet->getDrawingCollection()->count() > 0 || $chartCount > 0) {
         $objWriter->startElement('drawing');
         $objWriter->writeAttribute('r:id', 'rId1');
         $objWriter->endElement();
     }
 }
示例#14
0
 private function _extendRowsForChartsAndImages(PHPExcel_Worksheet $pSheet, $row)
 {
     $rowMax = $row;
     $colMax = 'A';
     if ($this->_includeCharts) {
         foreach ($pSheet->getChartCollection() as $chart) {
             if ($chart instanceof PHPExcel_Chart) {
                 $chartCoordinates = $chart->getTopLeftPosition();
                 $chartTL = PHPExcel_Cell::coordinateFromString($chartCoordinates['cell']);
                 $chartCol = PHPExcel_Cell::columnIndexFromString($chartTL[0]);
                 if ($chartTL[1] > $rowMax) {
                     $rowMax = $chartTL[1];
                     if ($chartCol > PHPExcel_Cell::columnIndexFromString($colMax)) {
                         $colMax = $chartTL[0];
                     }
                 }
             }
         }
     }
     foreach ($pSheet->getDrawingCollection() as $drawing) {
         if ($drawing instanceof PHPExcel_Worksheet_Drawing) {
             $imageTL = PHPExcel_Cell::coordinateFromString($drawing->getCoordinates());
             $imageCol = PHPExcel_Cell::columnIndexFromString($imageTL[0]);
             if ($imageTL[1] > $rowMax) {
                 $rowMax = $imageTL[1];
                 if ($imageCol > PHPExcel_Cell::columnIndexFromString($colMax)) {
                     $colMax = $imageTL[0];
                 }
             }
         }
     }
     $html = '';
     $colMax++;
     while ($row < $rowMax) {
         $html .= '<tr>';
         for ($col = 'A'; $col != $colMax; ++$col) {
             $html .= '<td>';
             $html .= $this->_writeImageInCell($pSheet, $col . $row);
             if ($this->_includeCharts) {
                 $html .= $this->_writeChartInCell($pSheet, $col . $row);
             }
             $html .= '</td>';
         }
         ++$row;
         $html .= '</tr>';
     }
     return $html;
 }
示例#15
0
 /**
  * Generate image tag in cell
  *
  * @param    PHPExcel_Worksheet    $pSheet            PHPExcel_Worksheet
  * @param    string                $coordinates    Cell coordinates
  * @return    string
  * @throws    PHPExcel_Writer_Exception
  */
 private function writeImageInCell(PHPExcel_Worksheet $pSheet, $coordinates)
 {
     // Construct HTML
     $html = '';
     // Write images
     foreach ($pSheet->getDrawingCollection() as $drawing) {
         if ($drawing instanceof PHPExcel_Worksheet_Drawing) {
             if ($drawing->getCoordinates() == $coordinates) {
                 $filename = $drawing->getPath();
                 // Strip off eventual '.'
                 if (substr($filename, 0, 1) == '.') {
                     $filename = substr($filename, 1);
                 }
                 // Prepend images root
                 $filename = $this->getImagesRoot() . $filename;
                 // Strip off eventual '.'
                 if (substr($filename, 0, 1) == '.' && substr($filename, 0, 2) != './') {
                     $filename = substr($filename, 1);
                 }
                 // Convert UTF8 data to PCDATA
                 $filename = htmlspecialchars($filename);
                 $html .= PHP_EOL;
                 if (!$this->embedImages || $this->isPdf) {
                     $imageData = $filename;
                 } else {
                     $imageDetails = getimagesize($filename);
                     if ($fp = fopen($filename, "rb", 0)) {
                         $picture = fread($fp, filesize($filename));
                         fclose($fp);
                         // base64 encode the binary data, then break it
                         // into chunks according to RFC 2045 semantics
                         $base64 = chunk_split(base64_encode($picture));
                         $imageData = 'data:' . $imageDetails['mime'] . ';base64,' . $base64;
                     } else {
                         $imageData = $filename;
                     }
                 }
                 $html .= '<div style="position: relative;">';
                 $html .= '<img style="position: absolute; z-index: 1; left: ' . $drawing->getOffsetX() . 'px; top: ' . $drawing->getOffsetY() . 'px; width: ' . $drawing->getWidth() . 'px; height: ' . $drawing->getHeight() . 'px;" src="' . $imageData . '" border="0" />';
                 $html .= '</div>';
             }
         } elseif ($drawing instanceof PHPExcel_Worksheet_MemoryDrawing) {
             if ($drawing->getCoordinates() != $coordinates) {
                 continue;
             }
             ob_start();
             //  Let's start output buffering.
             imagepng($drawing->getImageResource());
             //  This will normally output the image, but because of ob_start(), it won't.
             $contents = ob_get_contents();
             //  Instead, output above is saved to $contents
             ob_end_clean();
             //  End the output buffer.
             $dataUri = "data:image/jpeg;base64," . base64_encode($contents);
             //  Because of the nature of tables, width is more important than height.
             //  max-width: 100% ensures that image doesnt overflow containing cell
             //  width: X sets width of supplied image.
             //  As a result, images bigger than cell will be contained and images smaller will not get stretched
             $html .= '<img src="' . $dataUri . '" style="max-width:100%;width:' . $drawing->getWidth() . 'px;" />';
         }
     }
     return $html;
 }