/** * Insert a new column or row, updating all possible related data * * @param string $pBefore Insert before this cell address (e.g. 'A1') * @param integer $pNumCols Number of columns to insert/delete (negative values indicate deletion) * @param integer $pNumRows Number of rows to insert/delete (negative values indicate deletion) * @param Worksheet $pSheet The worksheet that we're editing * @throws Exception */ public function insertNewBefore($pBefore = 'A1', $pNumCols = 0, $pNumRows = 0, Worksheet $pSheet = null) { $remove = $pNumCols < 0 || $pNumRows < 0; $aCellCollection = $pSheet->getCellCollection(); // Get coordinates of $pBefore $beforeColumn = 'A'; $beforeRow = 1; list($beforeColumn, $beforeRow) = Cell::coordinateFromString($pBefore); $beforeColumnIndex = Cell::columnIndexFromString($beforeColumn); // Clear cells if we are removing columns or rows $highestColumn = $pSheet->getHighestColumn(); $highestRow = $pSheet->getHighestRow(); // 1. Clear column strips if we are removing columns if ($pNumCols < 0 && $beforeColumnIndex - 2 + $pNumCols > 0) { for ($i = 1; $i <= $highestRow - 1; ++$i) { for ($j = $beforeColumnIndex - 1 + $pNumCols; $j <= $beforeColumnIndex - 2; ++$j) { $coordinate = 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 = $beforeColumnIndex - 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 if ($remove) { // It's faster to reverse and pop than to use unshift, especially with large cell collections $aCellCollection = array_reverse($aCellCollection); } while ($cellID = array_pop($aCellCollection)) { $cell = $pSheet->getCell($cellID); $cellIndex = Cell::columnIndexFromString($cell->getColumn()); if ($cellIndex - 1 + $pNumCols < 0) { continue; } // New coordinates $newCoordinates = Cell::stringFromColumnIndex($cellIndex - 1 + $pNumCols) . ($cell->getRow() + $pNumRows); // Should the cell be updated? Move value and cellXf index from one cell to another. if ($cellIndex >= $beforeColumnIndex && $cell->getRow() >= $beforeRow) { // Update cell styles $pSheet->getCell($newCoordinates)->setXfIndex($cell->getXfIndex()); // Insert this cell at its new location if ($cell->getDataType() == Cell\DataType::TYPE_FORMULA) { // Formula should be adjusted $pSheet->getCell($newCoordinates)->setValue($this->updateFormulaReferences($cell->getValue(), $pBefore, $pNumCols, $pNumRows, $pSheet->getTitle())); } else { // Formula should not be adjusted $pSheet->getCell($newCoordinates)->setValue($cell->getValue()); } // Clear the original cell $pSheet->getCellCacheController()->deleteCacheData($cellID); } else { /* We don't need to update styles for rows/columns before our insertion position, but we do still need to adjust any formulae in those cells */ if ($cell->getDataType() == Cell\DataType::TYPE_FORMULA) { // Formula should be adjusted $cell->setValue($this->updateFormulaReferences($cell->getValue(), $pBefore, $pNumCols, $pNumRows, $pSheet->getTitle())); } } } // Duplicate styles for the newly inserted cells $highestColumn = $pSheet->getHighestColumn(); $highestRow = $pSheet->getHighestRow(); if ($pNumCols > 0 && $beforeColumnIndex - 2 > 0) { for ($i = $beforeRow; $i <= $highestRow - 1; ++$i) { // Style $coordinate = Cell::stringFromColumnIndex($beforeColumnIndex - 2) . $i; if ($pSheet->cellExists($coordinate)) { $xfIndex = $pSheet->getCell($coordinate)->getXfIndex(); $conditionalStyles = $pSheet->conditionalStylesExists($coordinate) ? $pSheet->getConditionalStyles($coordinate) : false; for ($j = $beforeColumnIndex - 1; $j <= $beforeColumnIndex - 2 + $pNumCols; ++$j) { $pSheet->getCellByColumnAndRow($j, $i)->setXfIndex($xfIndex); if ($conditionalStyles) { $cloned = array(); foreach ($conditionalStyles as $conditionalStyle) { $cloned[] = clone $conditionalStyle; } $pSheet->setConditionalStyles(Cell::stringFromColumnIndex($j) . $i, $cloned); } } } } } if ($pNumRows > 0 && $beforeRow - 1 > 0) { for ($i = $beforeColumnIndex - 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 $this->adjustColumnDimensions($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows); // Update worksheet: row dimensions $this->adjustRowDimensions($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows); // Update worksheet: page breaks $this->adjustPageBreaks($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows); // Update worksheet: comments $this->adjustComments($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows); // Update worksheet: hyperlinks $this->adjustHyperlinks($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows); // Update worksheet: data validations $this->adjustDataValidations($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows); // Update worksheet: merge cells $this->adjustMergeCells($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows); // Update worksheet: protected cells $this->adjustProtectedCells($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows); // Update worksheet: autofilter $autoFilter = $pSheet->getAutoFilter(); $autoFilterRange = $autoFilter->getRange(); if (!empty($autoFilterRange)) { if ($pNumCols != 0) { $autoFilterColumns = array_keys($autoFilter->getColumns()); if (count($autoFilterColumns) > 0) { sscanf($pBefore, '%[A-Z]%d', $column, $row); $columnIndex = Cell::columnIndexFromString($column); list($rangeStart, $rangeEnd) = 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(Cell::stringFromColumnIndex($deleteColumn), $autoFilterColumns)) { $autoFilter->clearColumn(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 = Cell::stringFromColumnIndex($startCol - 1); $toColID = Cell::stringFromColumnIndex($startCol + $pNumCols - 1); $endColID = Cell::stringFromColumnIndex($rangeEnd[0]); $startColRef = $startCol; $endColRef = $rangeEnd[0]; $toColRef = $rangeEnd[0] + $pNumCols; do { $autoFilter->shiftColumn(Cell::stringFromColumnIndex($endColRef - 1), Cell::stringFromColumnIndex($toColRef - 1)); --$endColRef; --$toColRef; } while ($startColRef <= $endColRef); } else { // For delete, we shuffle from beginning to end to avoid overwriting $startColID = Cell::stringFromColumnIndex($startCol - 1); $toColID = Cell::stringFromColumnIndex($startCol + $pNumCols - 1); $endColID = 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(); }
/** * Add data to the beginning of the workbook (note the reverse order) * and to the end of the workbook. * * @access public * @see Writer_Excel5_Workbook::storeWorkbook() */ function close() { $num_sheets = $this->_phpSheet->getParent()->getSheetCount(); // Write BOF record $this->_storeBof(0x10); // Write PRINTHEADERS $this->_writePrintHeaders(); // Write PRINTGRIDLINES $this->_writePrintGridlines(); // Write GRIDSET $this->_writeGridset(); // Calculate column widths $this->_phpSheet->calculateColumnWidths(); // Column dimensions $columnDimensions = $this->_phpSheet->getColumnDimensions(); for ($i = 0; $i < 256; ++$i) { $hidden = 0; $level = 0; $xfIndex = 15; // there are 15 cell style Xfs if ($this->_phpSheet->getDefaultColumnDimension()->getWidth() >= 0) { $width = $this->_phpSheet->getDefaultColumnDimension()->getWidth(); } else { $width = Shared_Font::getDefaultColumnWidthByFont($this->_phpSheet->getParent()->getDefaultStyle()->getFont()); } $columnLetter = Cell::stringFromColumnIndex($i); if (isset($columnDimensions[$columnLetter])) { $columnDimension = $columnDimensions[$columnLetter]; if ($columnDimension->getWidth() >= 0) { $width = $columnDimension->getWidth(); } $hidden = $columnDimension->getVisible() ? 0 : 1; $level = $columnDimension->getOutlineLevel(); $xfIndex = $columnDimension->getXfIndex() + 15; // there are 15 cell style Xfs } // Components of _colinfo: // $firstcol first column on the range // $lastcol last column on the range // $width width to set // $xfIndex The optional cell style Xf index to apply to the columns // $hidden The optional hidden atribute // $level The optional outline level $this->_colinfo[] = array($i, $i, $width, $xfIndex, $hidden, $level); } // Write GUTS $this->_writeGuts(); // Write DEFAULTROWHEIGHT if ($this->_BIFF_version == 0x600) { $this->_writeDefaultRowHeight(); } // Write WSBOOL $this->_writeWsbool(); // Write horizontal and vertical page breaks $this->_writeBreaks(); // Write page header $this->_writeHeader(); // Write page footer $this->_writeFooter(); // Write page horizontal centering $this->_writeHcenter(); // Write page vertical centering $this->_writeVcenter(); // Write left margin $this->_writeMarginLeft(); // Write right margin $this->_writeMarginRight(); // Write top margin $this->_writeMarginTop(); // Write bottom margin $this->_writeMarginBottom(); // Write page setup $this->_writeSetup(); // Write sheet protection $this->_writeProtect(); // Write SCENPROTECT $this->_writeScenProtect(); // Write OBJECTPROTECT $this->_writeObjectProtect(); // Write sheet password $this->_writePassword(); // Write DEFCOLWIDTH record $this->_writeDefcol(); // Write the COLINFO records if they exist if (!empty($this->_colinfo)) { $colcount = count($this->_colinfo); for ($i = 0; $i < $colcount; ++$i) { $this->_writeColinfo($this->_colinfo[$i]); } } // Write EXTERNCOUNT of external references if ($this->_BIFF_version == 0x500) { $this->_writeExterncount($num_sheets); } // Write EXTERNSHEET references if ($this->_BIFF_version == 0x500) { for ($i = 0; $i < $num_sheets; ++$i) { $this->_writeExternsheet($this->_phpSheet->getParent()->getSheet($i)->getTitle()); } } // Write sheet dimensions $this->_writeDimensions(); // Row dimensions foreach ($this->_phpSheet->getRowDimensions() as $rowDimension) { $xfIndex = $rowDimension->getXfIndex() + 15; // there are 15 cellXfs $this->_writeRow($rowDimension->getRowIndex() - 1, $rowDimension->getRowHeight(), $xfIndex, $rowDimension->getVisible() ? '0' : '1', $rowDimension->getOutlineLevel()); } // Write Cells foreach ($this->_phpSheet->getCellCollection() as $cellID) { $cell = $this->_phpSheet->getCell($cellID); $row = $cell->getRow() - 1; $column = Cell::columnIndexFromString($cell->getColumn()) - 1; // Don't break Excel! if ($row + 1 > 65536 or $column + 1 > 256) { break; } // Write cell value $xfIndex = $cell->getXfIndex() + 15; // there are 15 cell style Xfs if ($cell->getValue() instanceof RichText) { $this->_writeString($row, $column, $cell->getValue()->getPlainText(), $xfIndex); } else { switch ($cell->getDatatype()) { case Cell_DataType::TYPE_STRING: if ($cell->getValue() === '' or $cell->getValue() === null) { $this->_writeBlank($row, $column, $xfIndex); } else { $this->_writeString($row, $column, $cell->getValue(), $xfIndex); } break; case Cell_DataType::TYPE_FORMULA: $calculatedValue = $this->_preCalculateFormulas ? $cell->getCalculatedValue() : null; $this->_writeFormula($row, $column, $cell->getValue(), $xfIndex, $calculatedValue); break; case Cell_DataType::TYPE_BOOL: $this->_writeBoolErr($row, $column, $cell->getValue(), 0, $xfIndex); break; case Cell_DataType::TYPE_ERROR: $this->_writeBoolErr($row, $column, $this->_mapErrorCode($cell->getValue()), 1, $xfIndex); break; case Cell_DataType::TYPE_NUMERIC: $this->_writeNumber($row, $column, $cell->getValue(), $xfIndex); break; } } } // Append if ($this->_BIFF_version == 0x600) { $this->_writeMsoDrawing(); } $this->_writeWindow2(); $this->_writeZoom(); if ($this->_phpSheet->getFreezePane()) { $this->_writePanes(); } $this->_writeSelection(); $this->_writeMergedCells(); // Hyperlinks if ($this->_BIFF_version == 0x600) { foreach ($this->_phpSheet->getHyperLinkCollection() as $coordinate => $hyperlink) { list($column, $row) = Cell::coordinateFromString($coordinate); $url = $hyperlink->getUrl(); if (strpos($url, 'sheet://') !== false) { // internal to current workbook $url = str_replace('sheet://', 'internal:', $url); } else { if (preg_match('/^(http:|https:|ftp:|mailto:)/', $url)) { // URL // $url = $url; } else { // external (local file) $url = 'external:' . $url; } } $this->_writeUrl($row - 1, Cell::columnIndexFromString($column) - 1, $url); } } if ($this->_BIFF_version == 0x600) { $this->_writeDataValidity(); $this->_writeSheetLayout(); $this->_writeSheetProtection(); $this->_writeRangeProtection(); } $this->_storeEof(); }
/** * 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(); }
/** * Add external sheet * * @param Worksheet $pSheet External sheet to add * @param int|null $iSheetIndex Index where sheet should go (0,1,..., or null for last) * @throws Exception * @return Worksheet */ public function addExternalSheet(Worksheet $pSheet, $iSheetIndex = null) { if (!is_null($this->getSheetByName($pSheet->getTitle()))) { throw new Exception("Workbook already contains a worksheet named '{$pSheet->getTitle()}'. Rename the external sheet first."); } // count how many cellXfs there are in this workbook currently, we will need this below $countCellXfs = count($this->_cellXfCollection); // copy all the shared cellXfs from the external workbook and append them to the current foreach ($pSheet->getParent()->getCellXfCollection() as $cellXf) { $this->addCellXf(clone $cellXf); } // move sheet to this workbook $pSheet->rebindParent($this); // update the cellXfs foreach ($pSheet->getCellCollection(false) as $cellID) { $cell = $sheet->getCell($cellID); $cell->setXfIndex($cell->getXfIndex() + $countCellXfs); } return $this->addSheet($pSheet, $iSheetIndex); }
/** * Write SheetData * * @param Shared_XMLWriter $objWriter XML Writer * @param Worksheet $pSheet Worksheet * @param string[] $pStringTable String table * @throws Exception */ private function _writeSheetData(Shared_XMLWriter $objWriter = null, Worksheet $pSheet = null, $pStringTable = null) { if (is_array($pStringTable)) { // Flipped stringtable, for faster index searching $aFlippedStringTable = $this->getParentWriter()->getWriterPart('stringtable')->flipStringTable($pStringTable); // sheetData $objWriter->startElement('sheetData'); // Get column count $colCount = Cell::columnIndexFromString($pSheet->getHighestColumn()); // Highest row number $highestRow = $pSheet->getHighestRow(); // Loop through cells $cellsByRow = array(); foreach ($pSheet->getCellCollection() as $cellID) { $cellAddress = Cell::coordinateFromString($cellID); $cellsByRow[$cellAddress[1]][] = $cellID; } for ($currentRow = 1; $currentRow <= $highestRow; ++$currentRow) { // Get row dimension $rowDimension = $pSheet->getRowDimension($currentRow); // Write current row? $writeCurrentRow = isset($cellsByRow[$currentRow]) || $rowDimension->getRowHeight() >= 0 || $rowDimension->getVisible() == false || $rowDimension->getCollapsed() == true || $rowDimension->getOutlineLevel() > 0 || $rowDimension->getXfIndex() !== null; if ($writeCurrentRow) { // Start a new row $objWriter->startElement('row'); $objWriter->writeAttribute('r', $currentRow); $objWriter->writeAttribute('spans', '1:' . $colCount); // Row dimensions if ($rowDimension->getRowHeight() >= 0) { $objWriter->writeAttribute('customHeight', '1'); $objWriter->writeAttribute('ht', Shared_String::FormatNumber($rowDimension->getRowHeight())); } // Row visibility if ($rowDimension->getVisible() == false) { $objWriter->writeAttribute('hidden', 'true'); } // Collapsed if ($rowDimension->getCollapsed() == true) { $objWriter->writeAttribute('collapsed', 'true'); } // Outline level if ($rowDimension->getOutlineLevel() > 0) { $objWriter->writeAttribute('outlineLevel', $rowDimension->getOutlineLevel()); } // Style if ($rowDimension->getXfIndex() !== null) { $objWriter->writeAttribute('s', $rowDimension->getXfIndex()); $objWriter->writeAttribute('customFormat', '1'); } // Write cells if (isset($cellsByRow[$currentRow])) { foreach ($cellsByRow[$currentRow] as $cellAddress) { // Write cell $this->_writeCell($objWriter, $pSheet, $cellAddress, $pStringTable, $aFlippedStringTable); } } // End row $objWriter->endElement(); } } $objWriter->endElement(); } else { throw new Exception("Invalid parameters passed."); } }