/** * Set Worksheet * * @param Worksheet $pValue * @param bool $pOverrideOld If a Worksheet has already been assigned, overwrite it and remove image from old Worksheet? * @throws Exception * @return Worksheet_BaseDrawing */ public function setWorksheet(Worksheet $pValue = null, $pOverrideOld = false) { if (is_null($this->_worksheet)) { // Add drawing to Worksheet $this->_worksheet = $pValue; $this->_worksheet->getCell($this->_coordinates); $this->_worksheet->getDrawingCollection()->append($this); } else { if ($pOverrideOld) { // Remove drawing from old 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 Worksheet $this->setWorksheet($pValue); } else { throw new Exception("A Worksheet has already been assigned. Drawings can only exist on one Worksheet."); } } return $this; }
/** * Write drawings to XML format * * @param Worksheet $pWorksheet * @return string XML Output * @throws Exception */ public function writeDrawings(Worksheet $pWorksheet = null) { // Create XML writer $objWriter = null; if ($this->getParentWriter()->getUseDiskCaching()) { $objWriter = new Shared_XMLWriter(Shared_XMLWriter::STORAGE_DISK, $this->getParentWriter()->getDiskCachingDirectory()); } else { $objWriter = new Shared_XMLWriter(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 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(); }
/** * 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 Shared_Escher(); // dgContainer $dgContainer = new 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 Shared_Escher_DgContainer_SpgrContainer(); $dgContainer->setSpgrContainer($spgrContainer); // add one shape which is the group shape $spContainer = new 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 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 = 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 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); } }
/** * 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, Worksheet $pSheet = null) { $aCellCollection = $pSheet->getCellCollection(); // Get coordinates of $pBefore $beforeColumn = 'A'; $beforeRow = 1; list($beforeColumn, $beforeRow) = 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 && Cell::columnIndexFromString($beforeColumn) - 2 + $pNumCols > 0) { for ($i = 1; $i <= $highestRow - 1; ++$i) { for ($j = Cell::columnIndexFromString($beforeColumn) - 1 + $pNumCols; $j <= Cell::columnIndexFromString($beforeColumn) - 2; ++$j) { $coordinate = Cell::stringFromColumnIndex($j) . $i; $pSheet->removeConditionalStyles($coordinate); if ($pSheet->cellExists($coordinate)) { $pSheet->getCell($coordinate)->setValueExplicit('', 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 = Cell::columnIndexFromString($beforeColumn) - 1; $i <= Cell::columnIndexFromString($highestColumn) - 1; ++$i) { for ($j = $beforeRow + $pNumRows; $j <= $beforeRow - 1; ++$j) { $coordinate = Cell::stringFromColumnIndex($i) . $j; $pSheet->removeConditionalStyles($coordinate); if ($pSheet->cellExists($coordinate)) { $pSheet->getCell($coordinate)->setValueExplicit('', 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 = Cell::stringFromColumnIndex(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 (Cell::columnIndexFromString($cell->getColumn()) >= 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() == Cell_DataType::TYPE_FORMULA) { // Formula should be adjusted $pSheet->getCell($newCoordinates)->setValue($this->updateFormulaReferences($cell->getValue(), $pBefore, $pNumCols, $pNumRows)); } else { // Formula should not be adjusted $pSheet->getCell($newCoordinates)->setValue($cell->getValue()); } // Clear the original cell $pSheet->getCell($cell->getCoordinate())->setValue(''); } } // Duplicate styles for the newly inserted cells $highestColumn = $pSheet->getHighestColumn(); $highestRow = $pSheet->getHighestRow(); if ($pNumCols > 0 && Cell::columnIndexFromString($beforeColumn) - 2 > 0) { for ($i = $beforeRow; $i <= $highestRow - 1; ++$i) { // Style $coordinate = Cell::stringFromColumnIndex(Cell::columnIndexFromString($beforeColumn) - 2) . $i; if ($pSheet->cellExists($coordinate)) { $xfIndex = $pSheet->getCell($coordinate)->getXfIndex(); $conditionalStyles = $pSheet->conditionalStylesExists($coordinate) ? $pSheet->getConditionalStyles($coordinate) : false; for ($j = Cell::columnIndexFromString($beforeColumn) - 1; $j <= 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(Cell::stringFromColumnIndex($j) . $i, $cloned); } } } } } if ($pNumRows > 0 && $beforeRow - 1 > 0) { for ($i = Cell::columnIndexFromString($beforeColumn) - 1; $i <= Cell::columnIndexFromString($highestColumn) - 1; ++$i) { // Style $coordinate = 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(Cell::stringFromColumnIndex($i) . $j)->setXfIndex($xfIndex); if ($conditionalStyles) { $cloned = array(); foreach ($conditionalStyles as $conditionalStyle) { $cloned[] = clone $conditionalStyle; } $pSheet->setConditionalStyles(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) = 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) = 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, 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(); }
/** * Write drawing relationships to XML format * * @param Worksheet $pWorksheet * @return string XML Output * @throws Exception */ public function writeDrawingRelationships(Worksheet $pWorksheet = null) { // Create XML writer $objWriter = null; if ($this->getParentWriter()->getUseDiskCaching()) { $objWriter = new Shared_XMLWriter(Shared_XMLWriter::STORAGE_DISK, $this->getParentWriter()->getDiskCachingDirectory()); } else { $objWriter = new Shared_XMLWriter(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 through images and write relationships $i = 1; $iterator = $pWorksheet->getDrawingCollection()->getIterator(); while ($iterator->valid()) { if ($iterator->current() instanceof Worksheet_Drawing || $iterator->current() instanceof 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(); }
/** * Generate image tag in cell * * @param Worksheet $pSheet Worksheet * @param string $coordinates Cell coordinates * @return string * @throws Exception */ private function _writeImageTagInCell(Worksheet $pSheet, $coordinates) { // Construct HTML $html = ''; // Write images foreach ($pSheet->getDrawingCollection() as $drawing) { if ($drawing instanceof 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; }
/** * Write Drawings * * @param Shared_XMLWriter $objWriter XML Writer * @param Worksheet $pSheet Worksheet * @throws Exception */ private function _writeDrawings(Shared_XMLWriter $objWriter = null, 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(); } }