/** * 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(); }
/** * Write Cols * * @param PHPExcel_Shared_XMLWriter $objWriter XML Writer * @param PHPExcel_Worksheet $pSheet Worksheet * @throws Exception */ private function _writeCols(PHPExcel_Shared_XMLWriter $objWriter = null, PHPExcel_Worksheet $pSheet = null) { // cols $objWriter->startElement('cols'); // Check if there is at least one column dimension specified. If not, create one. if (count($pSheet->getColumnDimensions()) == 0) { $pSheet->getColumnDimension('A')->setWidth(10); } $pSheet->calculateColumnWidths(); // Loop trough column dimensions foreach ($pSheet->getColumnDimensions() as $colDimension) { // col $objWriter->startElement('col'); $objWriter->writeAttribute('min', PHPExcel_Cell::columnIndexFromString($colDimension->getColumnIndex())); $objWriter->writeAttribute('max', PHPExcel_Cell::columnIndexFromString($colDimension->getColumnIndex())); if ($colDimension->getWidth() < 0) { // No width set, apply default of 10 $objWriter->writeAttribute('width', '10'); } else { // Width set $objWriter->writeAttribute('width', PHPExcel_Shared_String::FormatNumber($colDimension->getWidth())); } // Column visibility if ($colDimension->getVisible() == false) { $objWriter->writeAttribute('hidden', 'true'); } // Auto size? if ($colDimension->getAutoSize()) { $objWriter->writeAttribute('bestFit', 'true'); } else { $objWriter->writeAttribute('customWidth', 'true'); } // Collapsed if ($colDimension->getCollapsed() == true) { $objWriter->writeAttribute('collapsed', 'true'); } // Outline level if ($colDimension->getOutlineLevel() > 0) { $objWriter->writeAttribute('outlineLevel', $colDimension->getOutlineLevel()); } $objWriter->endElement(); } $objWriter->endElement(); }
protected function prepare(\PHPExcel_Worksheet $templateSheet) { $this->output = new \PHPExcel(); $outputSheet = $this->output->getActiveSheet(); $outputSheet->setTitle('Report'); $this->templateSheet = $this->output->addExternalSheet($templateSheet); foreach ($this->templateSheet->getColumnDimensions() as $col => $columnDimension) { $outputSheet->getColumnDimension($col)->setWidth($columnDimension->getWidth()); } }
/** * Get the width of a column in pixels. We use the relationship y = ceil(7x) where * x is the width in intrinsic Excel units (measuring width in number of normal characters) * This holds for Arial 10 * * @param PHPExcel_Worksheet $sheet The sheet * @param integer $col The column * @return integer The width in pixels */ public static function sizeCol($sheet, $col = 'A') { $columnDimensions = $sheet->getColumnDimensions(); // first find the true column width in pixels (uncollapsed and unhidden) if (isset($columnDimensions[$col]) and $columnDimensions[$col]->getWidth() != -1) { // then we have column dimension with explicit width $columnDimension = $columnDimensions[$col]; $width = $columnDimension->getWidth(); $pixelWidth = (int) ceil(7 * $width); // here we assume Arial 10 } else { if ($sheet->getDefaultColumnDimension()->getWidth() != -1) { // then we have default column dimension with explicit width $defaultColumnDimension = $sheet->getDefaultColumnDimension(); $width = $defaultColumnDimension->getWidth(); $pixelWidth = (int) ceil(7 * $width); // here we assume Arial 10 } else { $pixelWidth = 64; // here we assume Arial 10 } } // now find the effective column width in pixels if (isset($columnDimensions[$col]) and !$columnDimensions[$col]->getVisible()) { $effectivePixelWidth = 0; } else { $effectivePixelWidth = $pixelWidth; } return $effectivePixelWidth; }
/** * Get the width of a column in pixels. We use the relationship y = ceil(7x) where * x is the width in intrinsic Excel units (measuring width in number of normal characters) * This holds for Arial 10 * * @param PHPExcel_Worksheet $sheet The sheet * @param string $col The column * @return integer The width in pixels */ public static function sizeCol($sheet, $col = 'A') { // default font of the workbook $font = $sheet->getParent()->getDefaultStyle()->getFont(); $columnDimensions = $sheet->getColumnDimensions(); // first find the true column width in pixels (uncollapsed and unhidden) if (isset($columnDimensions[$col]) and $columnDimensions[$col]->getWidth() != -1) { // then we have column dimension with explicit width $columnDimension = $columnDimensions[$col]; $width = $columnDimension->getWidth(); $pixelWidth = PHPExcel_Shared_Drawing::cellDimensionToPixels($width, $font); } else { if ($sheet->getDefaultColumnDimension()->getWidth() != -1) { // then we have default column dimension with explicit width $defaultColumnDimension = $sheet->getDefaultColumnDimension(); $width = $defaultColumnDimension->getWidth(); $pixelWidth = PHPExcel_Shared_Drawing::cellDimensionToPixels($width, $font); } else { // we don't even have any default column dimension. Width depends on default font $pixelWidth = PHPExcel_Shared_Font::getDefaultColumnWidthByFont($font, true); } } // now find the effective column width in pixels if (isset($columnDimensions[$col]) and !$columnDimensions[$col]->getVisible()) { $effectivePixelWidth = 0; } else { $effectivePixelWidth = $pixelWidth; } return $effectivePixelWidth; }
/** * Get the width of a column in pixels. We use the relationship y = ceil(7x) where * x is the width in intrinsic Excel units (measuring width in number of normal characters) * This holds for Arial 10 * * @param PHPExcel_Worksheet $sheet The sheet * @param integer $col The column * @return integer The width in pixels */ public static function sizeCol($sheet, $col = 'A') { // default font size of workbook $fontSize = $sheet->getParent()->getDefaultStyle()->getFont()->getSize(); $columnDimensions = $sheet->getColumnDimensions(); // first find the true column width in pixels (uncollapsed and unhidden) if (isset($columnDimensions[$col]) and $columnDimensions[$col]->getWidth() != -1) { // then we have column dimension with explicit width $columnDimension = $columnDimensions[$col]; $width = $columnDimension->getWidth(); $pixelWidth = PHPExcel_Shared_Drawing::cellDimensionToPixels($width, $fontSize); } else { if ($sheet->getDefaultColumnDimension()->getWidth() != -1) { // then we have default column dimension with explicit width $defaultColumnDimension = $sheet->getDefaultColumnDimension(); $width = $defaultColumnDimension->getWidth(); $pixelWidth = PHPExcel_Shared_Drawing::cellDimensionToPixels($width, $fontSize); } else { $pixelWidth = (int) 64 * $fontSize / 11; // here we interpolate from Calibri 11 } } // now find the effective column width in pixels if (isset($columnDimensions[$col]) and !$columnDimensions[$col]->getVisible()) { $effectivePixelWidth = 0; } else { $effectivePixelWidth = $pixelWidth; } return $effectivePixelWidth; }
/** * Write Cols * * @param PHPExcel_Shared_XMLWriter $objWriter XML Writer * @param PHPExcel_Worksheet $pSheet Worksheet * @throws Exception */ private function _writeCols(PHPExcel_Shared_XMLWriter $objWriter = null, PHPExcel_Worksheet $pSheet = null) { // cols if (count($pSheet->getColumnDimensions()) > 0) { $objWriter->startElement('cols'); $pSheet->calculateColumnWidths(); // Loop through column dimensions foreach ($pSheet->getColumnDimensions() as $colDimension) { // col $objWriter->startElement('col'); $objWriter->writeAttribute('min', PHPExcel_Cell::columnIndexFromString($colDimension->getColumnIndex())); $objWriter->writeAttribute('max', PHPExcel_Cell::columnIndexFromString($colDimension->getColumnIndex())); if ($colDimension->getWidth() < 0) { // No width set, apply default of 10 $objWriter->writeAttribute('width', '9.10'); } else { // Width set $objWriter->writeAttribute('width', PHPExcel_Shared_String::FormatNumber($colDimension->getWidth())); } // Column visibility if ($colDimension->getVisible() == false) { $objWriter->writeAttribute('hidden', 'true'); } // Auto size? if ($colDimension->getAutoSize()) { $objWriter->writeAttribute('bestFit', 'true'); } // Custom width? if ($colDimension->getWidth() != $pSheet->getDefaultColumnDimension()->getWidth()) { $objWriter->writeAttribute('customWidth', 'true'); } // Collapsed if ($colDimension->getCollapsed() == true) { $objWriter->writeAttribute('collapsed', 'true'); } // Outline level if ($colDimension->getOutlineLevel() > 0) { $objWriter->writeAttribute('outlineLevel', $colDimension->getOutlineLevel()); } // Style $objWriter->writeAttribute('style', $colDimension->getXfIndex()); $objWriter->endElement(); } $objWriter->endElement(); } }
/** * Update column dimensions when inserting/deleting rows/columns * * @param PHPExcel_Worksheet $pSheet The worksheet that we're editing * @param string $pBefore Insert/Delete before this cell address (e.g. 'A1') * @param integer $beforeColumnIndex Index number of the column we're inserting/deleting before * @param integer $pNumCols Number of columns to insert/delete (negative values indicate deletion) * @param integer $beforeRow Number of the row we're inserting/deleting before * @param integer $pNumRows Number of rows to insert/delete (negative values indicate deletion) */ protected function _adjustColumnDimensions($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows) { $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(); } }
/** * 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(); }
/** * Add data to the beginning of the workbook (note the reverse order) * and to the end of the workbook. * * @access public * @see PHPExcel_Writer_Excel5_Workbook::storeWorkbook() */ function close() { $num_sheets = count($this->_phpSheet->getParent()->getAllSheets()); // 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 = PHPExcel_Shared_Font::getDefaultColumnWidthByFont($this->_phpSheet->getParent()->getDefaultStyle()->getFont()); } $columnLetter = PHPExcel_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 = PHPExcel_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 PHPExcel_RichText) { $this->_writeString($row, $column, $cell->getValue()->getPlainText(), $xfIndex); } else { switch ($cell->getDatatype()) { case PHPExcel_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 PHPExcel_Cell_DataType::TYPE_FORMULA: $calculatedValue = $this->_preCalculateFormulas ? $cell->getCalculatedValue() : null; $this->_writeFormula($row, $column, $cell->getValue(), $xfIndex, $calculatedValue); break; case PHPExcel_Cell_DataType::TYPE_BOOL: $this->_writeBoolErr($row, $column, $cell->getValue(), 0, $xfIndex); break; case PHPExcel_Cell_DataType::TYPE_ERROR: $this->_writeBoolErr($row, $column, $this->_mapErrorCode($cell->getValue()), 1, $xfIndex); break; case PHPExcel_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) = PHPExcel_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, PHPExcel_Cell::columnIndexFromString($column) - 1, $url); } } if ($this->_BIFF_version == 0x600) { $this->_writeDataValidity(); $this->_writeSheetLayout(); $this->_writeSheetProtection(); $this->_writeRangeProtection(); } $this->_storeEof(); }
/** * Write styles to file * * @param mixed $pFileHandle PHP filehandle * @param PHPExcel_Worksheet $pSheet PHPExcel_Worksheet * @throws Exception */ private function _writeStyles($pFileHandle = null, PHPExcel_Worksheet $pSheet) { if (!is_null($pFileHandle)) { // Construct HTML $html = ''; // Start styles $html .= ' <style>' . "\r\n"; $html .= ' <!--' . "\r\n"; $html .= ' html {' . "\r\n"; $html .= ' font-family: Calibri, Arial, Helvetica, Sans Serif;' . "\r\n"; $html .= ' font-size: 10pt;' . "\r\n"; $html .= ' background-color: white;' . "\r\n"; $html .= ' }' . "\r\n"; $html .= ' table.sheet, table.sheet td {' . "\r\n"; if ($pSheet->getShowGridlines()) { $html .= ' border: 1px dotted black;' . "\r\n"; } $html .= ' }' . "\r\n"; // Calculate column widths $pSheet->calculateColumnWidths(); foreach ($pSheet->getColumnDimensions() as $columnDimension) { $column = PHPExcel_Cell::columnIndexFromString($columnDimension->getColumnIndex()) - 1; $html .= ' td.column' . $column . ' {' . "\r\n"; $html .= ' width: ' . PHPExcel_Shared_Drawing::cellDimensionToPixels($columnDimension->getWidth()) . 'px;' . "\r\n"; if ($columnDimension->getVisible() === false) { $html .= ' display: none;' . "\r\n"; $html .= ' visibility: hidden;' . "\r\n"; } $html .= ' }' . "\r\n"; } // Calculate row heights foreach ($pSheet->getRowDimensions() as $rowDimension) { $html .= ' tr.row' . ($rowDimension->getRowIndex() - 1) . ' {' . "\r\n"; // height is disproportionately large $px_height = round(PHPExcel_Shared_Drawing::cellDimensionToPixels($rowDimension->getRowHeight()) / 12); $html .= ' height: ' . $px_height . 'px;' . "\r\n"; if ($rowDimension->getVisible() === false) { $html .= ' display: none;' . "\r\n"; $html .= ' visibility: hidden;' . "\r\n"; } $html .= ' }' . "\r\n"; } // Calculate cell style hashes $cellStyleHashes = new PHPExcel_HashTable(); $cellStyleHashes->addFromSource($pSheet->getStyles()); for ($i = 0; $i < $cellStyleHashes->count(); $i++) { $html .= $this->_createCSSStyle($cellStyleHashes->getByIndex($i)); } // End styles $html .= ' -->' . "\r\n"; $html .= ' </style>' . "\r\n"; // Write to file fwrite($pFileHandle, $html); } else { throw new Exception("Invalid parameters passed."); } }
/** * 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(); }
/** * Add data to the beginning of the workbook (note the reverse order) * and to the end of the workbook. * * @access public * @see PHPExcel_Writer_Excel5_Workbook::storeWorkbook() */ function close() { $num_sheets = count($this->_phpSheet->getParent()->getAllSheets()); // Write BOF record $this->_storeBof(0x10); // Write DEFCOLWIDTH record $this->_storeDefcol(); // Calculate column widths $this->_phpSheet->calculateColumnWidths(); // Column dimensions foreach ($this->_phpSheet->getColumnDimensions() as $columnDimension) { $column = PHPExcel_Cell::columnIndexFromString($columnDimension->getColumnIndex()) - 1; if ($column < 256) { if ($columnDimension->getWidth() >= 0) { $width = $columnDimension->getWidth(); } else { if ($this->_phpSheet->getDefaultColumnDimension()->getWidth() >= 0) { $width = $this->_phpSheet->getDefaultColumnDimension()->getWidth(); } else { $width = 8; } } $this->_setColumn($column, $column, $width, null, $columnDimension->getVisible() ? '0' : '1', $columnDimension->getOutlineLevel()); } } // Write the COLINFO records if they exist if (!empty($this->_colinfo)) { $colcount = count($this->_colinfo); for ($i = 0; $i < $colcount; ++$i) { $this->_storeColinfo($this->_colinfo[$i]); } } // Write EXTERNCOUNT of external references if ($this->_BIFF_version == 0x500) { $this->_storeExterncount($num_sheets); } // Write EXTERNSHEET references if ($this->_BIFF_version == 0x500) { for ($i = 0; $i < $num_sheets; ++$i) { $this->_storeExternsheet($this->_phpSheet->getParent()->getSheet($i)->getTitle()); } } // Write PRINTHEADERS $this->_storePrintHeaders(); // Write PRINTGRIDLINES $this->_storePrintGridlines(); // Write GUTS $this->_storeGuts(); // Write GRIDSET $this->_storeGridset(); // Write DEFAULTROWHEIGHT if ($this->_BIFF_version == 0x600) { $this->_storeDefaultRowHeight(); } // Write WSBOOL $this->_storeWsbool(); // Write horizontal and vertical page breaks $this->_storeBreaks(); // Write page header $this->_storeHeader(); // Write page footer $this->_storeFooter(); // Write page horizontal centering $this->_storeHcenter(); // Write page vertical centering $this->_storeVcenter(); // Write left margin $this->_storeMarginLeft(); // Write right margin $this->_storeMarginRight(); // Write top margin $this->_storeMarginTop(); /* FIXME: margins are actually appended */ // Write bottom margin $this->_storeMarginBottom(); // Write page setup $this->_storeSetup(); // Write sheet protection $this->_storeProtect(); // Write sheet password $this->_storePassword(); // Write sheet dimensions $this->_storeDimensions(); // Write Cells $aStyles = $this->_phpSheet->getStyles(); $emptyStyle = $this->_phpSheet->getDefaultStyle(); foreach ($this->_phpSheet->getCellCollection() as $cell) { $row = $cell->getRow() - 1; $column = PHPExcel_Cell::columnIndexFromString($cell->getColumn()) - 1; // Don't break Excel! if ($row + 1 > 65536 or $column + 1 > 256) { break; } $style = $emptyStyle; if (isset($aStyles[$cell->getCoordinate()])) { $style = $aStyles[$cell->getCoordinate()]; } $styleHashIndex = $style->getHashIndex(); // Write cell value if ($cell->getValue() instanceof PHPExcel_RichText) { $this->_writeString($row, $column, $cell->getValue()->getPlainText(), $this->_xfIndexes[$styleHashIndex]); } else { switch ($cell->getDatatype()) { case PHPExcel_Cell_DataType::TYPE_STRING: if ($cell->getValue() === '' or $cell->getValue() === null) { $this->_writeBlank($row, $column, $this->_xfIndexes[$styleHashIndex]); } else { $this->_writeString($row, $column, $cell->getValue(), $this->_xfIndexes[$styleHashIndex]); } break; case PHPExcel_Cell_DataType::TYPE_FORMULA: $this->_writeFormula($row, $column, $cell->getValue(), $this->_xfIndexes[$styleHashIndex]); break; case PHPExcel_Cell_DataType::TYPE_BOOL: $this->_writeBoolErr($row, $column, $cell->getValue(), 0, $this->_xfIndexes[$styleHashIndex]); break; case PHPExcel_Cell_DataType::TYPE_ERROR: $this->_writeBoolErr($row, $column, $this->_mapErrorCode($cell->getValue()), 1, $this->_xfIndexes[$styleHashIndex]); break; default: $this->_write($row, $column, $cell->getValue(), $this->_xfIndexes[$styleHashIndex], $style->getNumberFormat()->getFormatCode()); break; } // Hyperlink? if ($cell->hasHyperlink()) { $this->_writeUrl($row, $column, str_replace('sheet://', 'internal:', $cell->getHyperlink()->getUrl())); } } } // Row dimensions foreach ($this->_phpSheet->getRowDimensions() as $rowDimension) { $this->_setRow($rowDimension->getRowIndex() - 1, $rowDimension->getRowHeight(), null, $rowDimension->getVisible() ? '0' : '1', $rowDimension->getOutlineLevel()); } // Append if ($this->_BIFF_version == 0x600) { $this->_storeMsoDrawing(); } $this->_storeWindow2(); $this->_storeZoom(); if ($this->_phpSheet->getFreezePane()) { $this->_storePanes(); } $this->_storeSelection($this->_selection); $this->_storeMergedCells(); /* TODO: add data validity */ /*if ($this->_BIFF_version == 0x0600) { $this->_storeDataValidity(); }*/ if ($this->_BIFF_version == 0x600) { $this->_storeRangeProtection(); } $this->_storeEof(); }