/** * 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(); }
/** * * @param int $height * @return Formatter */ public function setRowsHeight($height = -1) { foreach ($this->sheet->getRowDimensions() as $rd) { $rd->setRowHeight($height); } return $this; }
/** * Convert the height of a cell from user's units to pixels. By interpolation * the relationship is: y = 4/3x. If the height hasn't been set by the user we * use the default value. If the row is hidden we use a value of zero. * * @param PHPExcel_Worksheet $sheet The sheet * @param integer $row The row index (1-based) * @return integer The width in pixels */ public static function sizeRow($sheet, $row = 1) { // default font of the workbook $font = $sheet->getParent()->getDefaultStyle()->getFont(); $rowDimensions = $sheet->getRowDimensions(); // first find the true row height in pixels (uncollapsed and unhidden) if (isset($rowDimensions[$row]) and $rowDimensions[$row]->getRowHeight() != -1) { // then we have a row dimension $rowDimension = $rowDimensions[$row]; $rowHeight = $rowDimension->getRowHeight(); $pixelRowHeight = (int) ceil(4 * $rowHeight / 3); // here we assume Arial 10 } elseif ($sheet->getDefaultRowDimension()->getRowHeight() != -1) { // then we have a default row dimension with explicit height $defaultRowDimension = $sheet->getDefaultRowDimension(); $rowHeight = $defaultRowDimension->getRowHeight(); $pixelRowHeight = PHPExcel_Shared_Drawing::pointsToPixels($rowHeight); } else { // we don't even have any default row dimension. Height depends on default font $pointRowHeight = PHPExcel_Shared_Font::getDefaultRowHeightByFont($font); $pixelRowHeight = PHPExcel_Shared_Font::fontSizeToPixels($pointRowHeight); } // now find the effective row height in pixels if (isset($rowDimensions[$row]) and !$rowDimensions[$row]->getVisible()) { $effectivePixelRowHeight = 0; } else { $effectivePixelRowHeight = $pixelRowHeight; } return $effectivePixelRowHeight; }
/** * do layouting (cell borders, font styles, background colors, * cell sizing, page setup) */ protected function doLayouting() { $this->formatDataRowsByType(); $this->addCellBorders(); $this->formatHeaders(); $this->formatSums(); // auto-width columns: for ($x = 0; $x < count($this->activeColumns_arr); $x++) { $this->sheet->getColumnDimension(self::excelColumnAddr($x))->setAutoSize(true); } // auto-height rows: foreach ($this->sheet->getRowDimensions() as $rd) { $rd->setRowHeight(-1); } // fixed header when scrolling down in Excel: $this->sheet->freezePane(self::excelAddr(0, self::EXCEL_HEADER_OFFSET)); $pageSetup = $this->sheet->getPageSetup(); // when printing, show header on every page: $pageSetup->setRowsToRepeatAtTopByStartAndEnd(1, self::EXCEL_HEADER_OFFSET); // increase chances that page can be printed out without seperating a row's data over more than one page: $pageSetup->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE); }
/** * Write the GUTS BIFF record. This is used to configure the gutter margins * where Excel outline symbols are displayed. The visibility of the gutters is * controlled by a flag in WSBOOL. * * @see _writeWsbool() */ private function _writeGuts() { $record = 0x80; // Record identifier $length = 0x8; // Bytes to follow $dxRwGut = 0x0; // Size of row gutter $dxColGut = 0x0; // Size of col gutter // determine maximum row outline level $maxRowOutlineLevel = 0; foreach ($this->_phpSheet->getRowDimensions() as $rowDimension) { $maxRowOutlineLevel = max($maxRowOutlineLevel, $rowDimension->getOutlineLevel()); } $col_level = 0; // Calculate the maximum column outline level. The equivalent calculation // for the row outline level is carried out in _writeRow(). $colcount = count($this->_colinfo); for ($i = 0; $i < $colcount; ++$i) { $col_level = max($this->_colinfo[$i][5], $col_level); } // Set the limits for the outline levels (0 <= x <= 7). $col_level = max(0, min($col_level, 7)); // The displayed level is one greater than the max outline levels if ($maxRowOutlineLevel) { ++$maxRowOutlineLevel; } if ($col_level) { ++$col_level; } $header = pack("vv", $record, $length); $data = pack("vvvv", $dxRwGut, $dxColGut, $maxRowOutlineLevel, $col_level); $this->_append($header . $data); }
/** * Convert the height of a cell from user's units to pixels. By interpolation * the relationship is: y = 4/3x. If the height hasn't been set by the user we * use the default value. If the row is hidden we use a value of zero. * * @param PHPExcel_Worksheet $sheet The sheet * @param integer $row The row index (1-based) * @return integer The width in pixels */ public static function sizeRow($sheet, $row = 1) { $rowDimensions = $sheet->getRowDimensions(); // first find the true row height in pixels (uncollapsed and unhidden) if (isset($rowDimensions[$row]) and $rowDimensions[$row]->getRowHeight() != -1) { // then we have a row dimension $rowDimension = $rowDimensions[$row]; $rowHeight = $rowDimension->getRowHeight(); $pixelRowHeight = (int) ceil(4 * $rowHeight / 3); // here we assume Arial 10 } else { if ($sheet->getDefaultRowDimension()->getRowHeight() != -1) { // then we have a default row dimension with explicit height $defaultRowDimension = $sheet->getDefaultRowDimension(); $rowHeight = $defaultRowDimension->getRowHeight(); $pixelRowHeight = (int) ceil(4 * $rowHeight / 3); // here we assume Arial 10 } else { $pixelRowHeight = 17; // here we assume Arial 10 } } // now find the effective row height in pixels if (isset($rowDimensions[$row]) and !$rowDimensions[$row]->getVisible()) { $effectivePixelRowHeight = 0; } else { $effectivePixelRowHeight = $pixelRowHeight; } return $effectivePixelRowHeight; }
/** * Write SheetFormatPr * * @param PHPExcel_Shared_XMLWriter $objWriter XML Writer * @param PHPExcel_Worksheet $pSheet Worksheet * @throws Exception */ private function _writeSheetFormatPr(PHPExcel_Shared_XMLWriter $objWriter = null, PHPExcel_Worksheet $pSheet = null) { // sheetFormatPr $objWriter->startElement('sheetFormatPr'); // Default row height if ($pSheet->getDefaultRowDimension()->getRowHeight() >= 0) { $objWriter->writeAttribute('customHeight', 'true'); $objWriter->writeAttribute('defaultRowHeight', PHPExcel_Shared_String::FormatNumber($pSheet->getDefaultRowDimension()->getRowHeight())); } else { $objWriter->writeAttribute('defaultRowHeight', '12.75'); } // Default column width if ($pSheet->getDefaultColumnDimension()->getWidth() >= 0) { $objWriter->writeAttribute('defaultColWidth', PHPExcel_Shared_String::FormatNumber($pSheet->getDefaultColumnDimension()->getWidth())); } // Outline level - row $outlineLevelRow = 0; foreach ($pSheet->getRowDimensions() as $dimension) { if ($dimension->getOutlineLevel() > $outlineLevelRow) { $outlineLevelRow = $dimension->getOutlineLevel(); } } $objWriter->writeAttribute('outlineLevelRow', (int) $outlineLevelRow); // Outline level - column $outlineLevelCol = 0; foreach ($pSheet->getColumnDimensions() as $dimension) { if ($dimension->getOutlineLevel() > $outlineLevelCol) { $outlineLevelCol = $dimension->getOutlineLevel(); } } $objWriter->writeAttribute('outlineLevelCol', (int) $outlineLevelCol); $objWriter->endElement(); }
/** * Update row 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 _adjustRowDimensions($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows) { $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()); } } }
/** * 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(); }
/** * 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->_write($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(); }