/**
  * Set Worksheet
  *
  * @param 	Worksheet 	$pValue
  * @param 	bool				$pOverrideOld	If a Worksheet has already been assigned, overwrite it and remove image from old Worksheet?
  * @throws 	Exception
  * @return Worksheet_BaseDrawing
  */
 public function setWorksheet(Worksheet $pValue = null, $pOverrideOld = false)
 {
     if (is_null($this->_worksheet)) {
         // Add drawing to Worksheet
         $this->_worksheet = $pValue;
         $this->_worksheet->getCell($this->_coordinates);
         $this->_worksheet->getDrawingCollection()->append($this);
     } else {
         if ($pOverrideOld) {
             // Remove drawing from old Worksheet
             $iterator = $this->_worksheet->getDrawingCollection()->getIterator();
             while ($iterator->valid()) {
                 if ($iterator->current()->getHashCode() == $this->getHashCode()) {
                     $this->_worksheet->getDrawingCollection()->offsetUnset($iterator->key());
                     $this->_worksheet = null;
                     break;
                 }
             }
             // Set new Worksheet
             $this->setWorksheet($pValue);
         } else {
             throw new Exception("A Worksheet has already been assigned. Drawings can only exist on one Worksheet.");
         }
     }
     return $this;
 }
Exemple #2
0
 /**
  * Read DATAVALIDATION record
  */
 private function _readDataValidation()
 {
     $length = $this->_GetInt2d($this->_data, $this->_pos + 2);
     $recordData = substr($this->_data, $this->_pos + 4, $length);
     // move stream pointer forward to next record
     $this->_pos += 4 + $length;
     if ($this->_readDataOnly) {
         return;
     }
     // offset: 0; size: 4; Options
     $options = $this->_GetInt4d($recordData, 0);
     // bit: 0-3; mask: 0x0000000F; type
     $type = (0xf & $options) >> 0;
     switch ($type) {
         case 0x0:
             $type = Cell_DataValidation::TYPE_NONE;
             break;
         case 0x1:
             $type = Cell_DataValidation::TYPE_WHOLE;
             break;
         case 0x2:
             $type = Cell_DataValidation::TYPE_DECIMAL;
             break;
         case 0x3:
             $type = Cell_DataValidation::TYPE_LIST;
             break;
         case 0x4:
             $type = Cell_DataValidation::TYPE_DATE;
             break;
         case 0x5:
             $type = Cell_DataValidation::TYPE_TIME;
             break;
         case 0x6:
             $type = Cell_DataValidation::TYPE_TEXTLENGTH;
             break;
         case 0x7:
             $type = Cell_DataValidation::TYPE_CUSTOM;
             break;
     }
     // bit: 4-6; mask: 0x00000070; error type
     $errorStyle = (0x70 & $options) >> 4;
     switch ($errorStyle) {
         case 0x0:
             $errorStyle = Cell_DataValidation::STYLE_STOP;
             break;
         case 0x1:
             $errorStyle = Cell_DataValidation::STYLE_WARNING;
             break;
         case 0x2:
             $errorStyle = Cell_DataValidation::STYLE_INFORMATION;
             break;
     }
     // bit: 7; mask: 0x00000080; 1= formula is explicit (only applies to list)
     // I have only seen cases where this is 1
     $explicitFormula = (0x80 & $options) >> 7;
     // bit: 8; mask: 0x00000100; 1= empty cells allowed
     $allowBlank = (0x100 & $options) >> 8;
     // bit: 9; mask: 0x00000200; 1= suppress drop down arrow in list type validity
     $suppressDropDown = (0x200 & $options) >> 9;
     // bit: 18; mask: 0x00040000; 1= show prompt box if cell selected
     $showInputMessage = (0x40000 & $options) >> 18;
     // bit: 19; mask: 0x00080000; 1= show error box if invalid values entered
     $showErrorMessage = (0x80000 & $options) >> 19;
     // bit: 20-23; mask: 0x00F00000; condition operator
     $operator = (0xf00000 & $options) >> 20;
     switch ($operator) {
         case 0x0:
             $operator = Cell_DataValidation::OPERATOR_BETWEEN;
             break;
         case 0x1:
             $operator = Cell_DataValidation::OPERATOR_NOTBETWEEN;
             break;
         case 0x2:
             $operator = Cell_DataValidation::OPERATOR_EQUAL;
             break;
         case 0x3:
             $operator = Cell_DataValidation::OPERATOR_NOTEQUAL;
             break;
         case 0x4:
             $operator = Cell_DataValidation::OPERATOR_GREATERTHAN;
             break;
         case 0x5:
             $operator = Cell_DataValidation::OPERATOR_LESSTHAN;
             break;
         case 0x6:
             $operator = Cell_DataValidation::OPERATOR_GREATERTHANOREQUAL;
             break;
         case 0x7:
             $operator = Cell_DataValidation::OPERATOR_LESSTHANOREQUAL;
             break;
     }
     // offset: 4; size: var; title of the prompt box
     $offset = 4;
     $string = $this->_readUnicodeStringLong(substr($recordData, $offset));
     $promptTitle = $string['value'] !== chr(0) ? $string['value'] : '';
     $offset += $string['size'];
     // offset: var; size: var; title of the error box
     $string = $this->_readUnicodeStringLong(substr($recordData, $offset));
     $errorTitle = $string['value'] !== chr(0) ? $string['value'] : '';
     $offset += $string['size'];
     // offset: var; size: var; text of the prompt box
     $string = $this->_readUnicodeStringLong(substr($recordData, $offset));
     $prompt = $string['value'] !== chr(0) ? $string['value'] : '';
     $offset += $string['size'];
     // offset: var; size: var; text of the error box
     $string = $this->_readUnicodeStringLong(substr($recordData, $offset));
     $error = $string['value'] !== chr(0) ? $string['value'] : '';
     $offset += $string['size'];
     // offset: var; size: 2; size of the formula data for the first condition
     $sz1 = $this->_GetInt2d($recordData, $offset);
     $offset += 2;
     // offset: var; size: 2; not used
     $offset += 2;
     // offset: var; size: $sz1; formula data for first condition (without size field)
     $formula1 = substr($recordData, $offset, $sz1);
     $formula1 = pack('v', $sz1) . $formula1;
     // prepend the length
     try {
         $formula1 = $this->_getFormulaFromStructure($formula1);
         // in list type validity, null characters are used as item separators
         if ($type == Cell_DataValidation::TYPE_LIST) {
             $formula1 = str_replace(chr(0), ',', $formula1);
         }
     } catch (Exception $e) {
         return;
     }
     $offset += $sz1;
     // offset: var; size: 2; size of the formula data for the first condition
     $sz2 = $this->_GetInt2d($recordData, $offset);
     $offset += 2;
     // offset: var; size: 2; not used
     $offset += 2;
     // offset: var; size: $sz2; formula data for second condition (without size field)
     $formula2 = substr($recordData, $offset, $sz2);
     $formula2 = pack('v', $sz2) . $formula2;
     // prepend the length
     try {
         $formula2 = $this->_getFormulaFromStructure($formula2);
     } catch (Exception $e) {
         return;
     }
     $offset += $sz2;
     // offset: var; size: var; cell range address list with
     $cellRangeAddressList = $this->_readBIFF8CellRangeAddressList(substr($recordData, $offset));
     $cellRangeAddresses = $cellRangeAddressList['cellRangeAddresses'];
     foreach ($cellRangeAddresses as $cellRange) {
         $stRange = $this->_phpSheet->shrinkRangeToFit($cellRange);
         $stRange = Cell::extractAllCellReferencesInRange($stRange);
         foreach ($stRange as $coordinate) {
             $objValidation = $this->_phpSheet->getCell($coordinate)->getDataValidation();
             $objValidation->setType($type);
             $objValidation->setErrorStyle($errorStyle);
             $objValidation->setAllowBlank((bool) $allowBlank);
             $objValidation->setShowInputMessage((bool) $showInputMessage);
             $objValidation->setShowErrorMessage((bool) $showErrorMessage);
             $objValidation->setShowDropDown(!$suppressDropDown);
             $objValidation->setOperator($operator);
             $objValidation->setErrorTitle($errorTitle);
             $objValidation->setError($error);
             $objValidation->setPromptTitle($promptTitle);
             $objValidation->setPrompt($prompt);
             $objValidation->setFormula1($formula1);
             $objValidation->setFormula2($formula2);
         }
     }
 }
 /**
  * Insert a new column or row, updating all possible related data
  *
  * @param   string              $pBefore    Insert before this cell address (e.g. 'A1')
  * @param   integer             $pNumCols   Number of columns to insert/delete (negative values indicate deletion)
  * @param   integer             $pNumRows   Number of rows to insert/delete (negative values indicate deletion)
  * @param   Worksheet  $pSheet     The worksheet that we're editing
  * @throws  Exception
  */
 public function insertNewBefore($pBefore = 'A1', $pNumCols = 0, $pNumRows = 0, Worksheet $pSheet = null)
 {
     $remove = $pNumCols < 0 || $pNumRows < 0;
     $aCellCollection = $pSheet->getCellCollection();
     // Get coordinates of $pBefore
     $beforeColumn = 'A';
     $beforeRow = 1;
     list($beforeColumn, $beforeRow) = Cell::coordinateFromString($pBefore);
     $beforeColumnIndex = Cell::columnIndexFromString($beforeColumn);
     // Clear cells if we are removing columns or rows
     $highestColumn = $pSheet->getHighestColumn();
     $highestRow = $pSheet->getHighestRow();
     // 1. Clear column strips if we are removing columns
     if ($pNumCols < 0 && $beforeColumnIndex - 2 + $pNumCols > 0) {
         for ($i = 1; $i <= $highestRow - 1; ++$i) {
             for ($j = $beforeColumnIndex - 1 + $pNumCols; $j <= $beforeColumnIndex - 2; ++$j) {
                 $coordinate = Cell::stringFromColumnIndex($j) . $i;
                 $pSheet->removeConditionalStyles($coordinate);
                 if ($pSheet->cellExists($coordinate)) {
                     $pSheet->getCell($coordinate)->setValueExplicit('', Cell\DataType::TYPE_NULL);
                     $pSheet->getCell($coordinate)->setXfIndex(0);
                 }
             }
         }
     }
     // 2. Clear row strips if we are removing rows
     if ($pNumRows < 0 && $beforeRow - 1 + $pNumRows > 0) {
         for ($i = $beforeColumnIndex - 1; $i <= Cell::columnIndexFromString($highestColumn) - 1; ++$i) {
             for ($j = $beforeRow + $pNumRows; $j <= $beforeRow - 1; ++$j) {
                 $coordinate = Cell::stringFromColumnIndex($i) . $j;
                 $pSheet->removeConditionalStyles($coordinate);
                 if ($pSheet->cellExists($coordinate)) {
                     $pSheet->getCell($coordinate)->setValueExplicit('', Cell\DataType::TYPE_NULL);
                     $pSheet->getCell($coordinate)->setXfIndex(0);
                 }
             }
         }
     }
     // Loop through cells, bottom-up, and change cell coordinates
     if ($remove) {
         // It's faster to reverse and pop than to use unshift, especially with large cell collections
         $aCellCollection = array_reverse($aCellCollection);
     }
     while ($cellID = array_pop($aCellCollection)) {
         $cell = $pSheet->getCell($cellID);
         $cellIndex = Cell::columnIndexFromString($cell->getColumn());
         if ($cellIndex - 1 + $pNumCols < 0) {
             continue;
         }
         // New coordinates
         $newCoordinates = Cell::stringFromColumnIndex($cellIndex - 1 + $pNumCols) . ($cell->getRow() + $pNumRows);
         // Should the cell be updated? Move value and cellXf index from one cell to another.
         if ($cellIndex >= $beforeColumnIndex && $cell->getRow() >= $beforeRow) {
             // Update cell styles
             $pSheet->getCell($newCoordinates)->setXfIndex($cell->getXfIndex());
             // Insert this cell at its new location
             if ($cell->getDataType() == Cell\DataType::TYPE_FORMULA) {
                 // Formula should be adjusted
                 $pSheet->getCell($newCoordinates)->setValue($this->updateFormulaReferences($cell->getValue(), $pBefore, $pNumCols, $pNumRows, $pSheet->getTitle()));
             } else {
                 // Formula should not be adjusted
                 $pSheet->getCell($newCoordinates)->setValue($cell->getValue());
             }
             // Clear the original cell
             $pSheet->getCellCacheController()->deleteCacheData($cellID);
         } else {
             /*    We don't need to update styles for rows/columns before our insertion position,
                   but we do still need to adjust any formulae    in those cells                    */
             if ($cell->getDataType() == Cell\DataType::TYPE_FORMULA) {
                 // Formula should be adjusted
                 $cell->setValue($this->updateFormulaReferences($cell->getValue(), $pBefore, $pNumCols, $pNumRows, $pSheet->getTitle()));
             }
         }
     }
     // Duplicate styles for the newly inserted cells
     $highestColumn = $pSheet->getHighestColumn();
     $highestRow = $pSheet->getHighestRow();
     if ($pNumCols > 0 && $beforeColumnIndex - 2 > 0) {
         for ($i = $beforeRow; $i <= $highestRow - 1; ++$i) {
             // Style
             $coordinate = Cell::stringFromColumnIndex($beforeColumnIndex - 2) . $i;
             if ($pSheet->cellExists($coordinate)) {
                 $xfIndex = $pSheet->getCell($coordinate)->getXfIndex();
                 $conditionalStyles = $pSheet->conditionalStylesExists($coordinate) ? $pSheet->getConditionalStyles($coordinate) : false;
                 for ($j = $beforeColumnIndex - 1; $j <= $beforeColumnIndex - 2 + $pNumCols; ++$j) {
                     $pSheet->getCellByColumnAndRow($j, $i)->setXfIndex($xfIndex);
                     if ($conditionalStyles) {
                         $cloned = array();
                         foreach ($conditionalStyles as $conditionalStyle) {
                             $cloned[] = clone $conditionalStyle;
                         }
                         $pSheet->setConditionalStyles(Cell::stringFromColumnIndex($j) . $i, $cloned);
                     }
                 }
             }
         }
     }
     if ($pNumRows > 0 && $beforeRow - 1 > 0) {
         for ($i = $beforeColumnIndex - 1; $i <= Cell::columnIndexFromString($highestColumn) - 1; ++$i) {
             // Style
             $coordinate = Cell::stringFromColumnIndex($i) . ($beforeRow - 1);
             if ($pSheet->cellExists($coordinate)) {
                 $xfIndex = $pSheet->getCell($coordinate)->getXfIndex();
                 $conditionalStyles = $pSheet->conditionalStylesExists($coordinate) ? $pSheet->getConditionalStyles($coordinate) : false;
                 for ($j = $beforeRow; $j <= $beforeRow - 1 + $pNumRows; ++$j) {
                     $pSheet->getCell(Cell::stringFromColumnIndex($i) . $j)->setXfIndex($xfIndex);
                     if ($conditionalStyles) {
                         $cloned = array();
                         foreach ($conditionalStyles as $conditionalStyle) {
                             $cloned[] = clone $conditionalStyle;
                         }
                         $pSheet->setConditionalStyles(Cell::stringFromColumnIndex($i) . $j, $cloned);
                     }
                 }
             }
         }
     }
     // Update worksheet: column dimensions
     $this->adjustColumnDimensions($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows);
     // Update worksheet: row dimensions
     $this->adjustRowDimensions($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows);
     //    Update worksheet: page breaks
     $this->adjustPageBreaks($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows);
     //    Update worksheet: comments
     $this->adjustComments($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows);
     // Update worksheet: hyperlinks
     $this->adjustHyperlinks($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows);
     // Update worksheet: data validations
     $this->adjustDataValidations($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows);
     // Update worksheet: merge cells
     $this->adjustMergeCells($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows);
     // Update worksheet: protected cells
     $this->adjustProtectedCells($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows);
     // Update worksheet: autofilter
     $autoFilter = $pSheet->getAutoFilter();
     $autoFilterRange = $autoFilter->getRange();
     if (!empty($autoFilterRange)) {
         if ($pNumCols != 0) {
             $autoFilterColumns = array_keys($autoFilter->getColumns());
             if (count($autoFilterColumns) > 0) {
                 sscanf($pBefore, '%[A-Z]%d', $column, $row);
                 $columnIndex = Cell::columnIndexFromString($column);
                 list($rangeStart, $rangeEnd) = Cell::rangeBoundaries($autoFilterRange);
                 if ($columnIndex <= $rangeEnd[0]) {
                     if ($pNumCols < 0) {
                         //    If we're actually deleting any columns that fall within the autofilter range,
                         //        then we delete any rules for those columns
                         $deleteColumn = $columnIndex + $pNumCols - 1;
                         $deleteCount = abs($pNumCols);
                         for ($i = 1; $i <= $deleteCount; ++$i) {
                             if (in_array(Cell::stringFromColumnIndex($deleteColumn), $autoFilterColumns)) {
                                 $autoFilter->clearColumn(Cell::stringFromColumnIndex($deleteColumn));
                             }
                             ++$deleteColumn;
                         }
                     }
                     $startCol = $columnIndex > $rangeStart[0] ? $columnIndex : $rangeStart[0];
                     //    Shuffle columns in autofilter range
                     if ($pNumCols > 0) {
                         //    For insert, we shuffle from end to beginning to avoid overwriting
                         $startColID = Cell::stringFromColumnIndex($startCol - 1);
                         $toColID = Cell::stringFromColumnIndex($startCol + $pNumCols - 1);
                         $endColID = Cell::stringFromColumnIndex($rangeEnd[0]);
                         $startColRef = $startCol;
                         $endColRef = $rangeEnd[0];
                         $toColRef = $rangeEnd[0] + $pNumCols;
                         do {
                             $autoFilter->shiftColumn(Cell::stringFromColumnIndex($endColRef - 1), Cell::stringFromColumnIndex($toColRef - 1));
                             --$endColRef;
                             --$toColRef;
                         } while ($startColRef <= $endColRef);
                     } else {
                         //    For delete, we shuffle from beginning to end to avoid overwriting
                         $startColID = Cell::stringFromColumnIndex($startCol - 1);
                         $toColID = Cell::stringFromColumnIndex($startCol + $pNumCols - 1);
                         $endColID = Cell::stringFromColumnIndex($rangeEnd[0]);
                         do {
                             $autoFilter->shiftColumn($startColID, $toColID);
                             ++$startColID;
                             ++$toColID;
                         } while ($startColID != $endColID);
                     }
                 }
             }
         }
         $pSheet->setAutoFilter($this->updateCellReference($autoFilterRange, $pBefore, $pNumCols, $pNumRows));
     }
     // Update worksheet: freeze pane
     if ($pSheet->getFreezePane() != '') {
         $pSheet->freezePane($this->updateCellReference($pSheet->getFreezePane(), $pBefore, $pNumCols, $pNumRows));
     }
     // Page setup
     if ($pSheet->getPageSetup()->isPrintAreaSet()) {
         $pSheet->getPageSetup()->setPrintArea($this->updateCellReference($pSheet->getPageSetup()->getPrintArea(), $pBefore, $pNumCols, $pNumRows));
     }
     // Update worksheet: drawings
     $aDrawings = $pSheet->getDrawingCollection();
     foreach ($aDrawings as $objDrawing) {
         $newReference = $this->updateCellReference($objDrawing->getCoordinates(), $pBefore, $pNumCols, $pNumRows);
         if ($objDrawing->getCoordinates() != $newReference) {
             $objDrawing->setCoordinates($newReference);
         }
     }
     // Update workbook: named ranges
     if (count($pSheet->getParent()->getNamedRanges()) > 0) {
         foreach ($pSheet->getParent()->getNamedRanges() as $namedRange) {
             if ($namedRange->getWorksheet()->getHashCode() == $pSheet->getHashCode()) {
                 $namedRange->setRange($this->updateCellReference($namedRange->getRange(), $pBefore, $pNumCols, $pNumRows));
             }
         }
     }
     // Garbage collect
     $pSheet->garbageCollect();
 }
Exemple #4
0
 /**
  * Add data to the beginning of the workbook (note the reverse order)
  * and to the end of the workbook.
  *
  * @access public
  * @see Writer_Excel5_Workbook::storeWorkbook()
  */
 function close()
 {
     $num_sheets = $this->_phpSheet->getParent()->getSheetCount();
     // Write BOF record
     $this->_storeBof(0x10);
     // Write PRINTHEADERS
     $this->_writePrintHeaders();
     // Write PRINTGRIDLINES
     $this->_writePrintGridlines();
     // Write GRIDSET
     $this->_writeGridset();
     // Calculate column widths
     $this->_phpSheet->calculateColumnWidths();
     // Column dimensions
     $columnDimensions = $this->_phpSheet->getColumnDimensions();
     for ($i = 0; $i < 256; ++$i) {
         $hidden = 0;
         $level = 0;
         $xfIndex = 15;
         // there are 15 cell style Xfs
         if ($this->_phpSheet->getDefaultColumnDimension()->getWidth() >= 0) {
             $width = $this->_phpSheet->getDefaultColumnDimension()->getWidth();
         } else {
             $width = Shared_Font::getDefaultColumnWidthByFont($this->_phpSheet->getParent()->getDefaultStyle()->getFont());
         }
         $columnLetter = Cell::stringFromColumnIndex($i);
         if (isset($columnDimensions[$columnLetter])) {
             $columnDimension = $columnDimensions[$columnLetter];
             if ($columnDimension->getWidth() >= 0) {
                 $width = $columnDimension->getWidth();
             }
             $hidden = $columnDimension->getVisible() ? 0 : 1;
             $level = $columnDimension->getOutlineLevel();
             $xfIndex = $columnDimension->getXfIndex() + 15;
             // there are 15 cell style Xfs
         }
         // Components of _colinfo:
         // $firstcol first column on the range
         // $lastcol  last column on the range
         // $width	width to set
         // $xfIndex  The optional cell style Xf index to apply to the columns
         // $hidden   The optional hidden atribute
         // $level	The optional outline level
         $this->_colinfo[] = array($i, $i, $width, $xfIndex, $hidden, $level);
     }
     // Write GUTS
     $this->_writeGuts();
     // Write DEFAULTROWHEIGHT
     if ($this->_BIFF_version == 0x600) {
         $this->_writeDefaultRowHeight();
     }
     // Write WSBOOL
     $this->_writeWsbool();
     // Write horizontal and vertical page breaks
     $this->_writeBreaks();
     // Write page header
     $this->_writeHeader();
     // Write page footer
     $this->_writeFooter();
     // Write page horizontal centering
     $this->_writeHcenter();
     // Write page vertical centering
     $this->_writeVcenter();
     // Write left margin
     $this->_writeMarginLeft();
     // Write right margin
     $this->_writeMarginRight();
     // Write top margin
     $this->_writeMarginTop();
     // Write bottom margin
     $this->_writeMarginBottom();
     // Write page setup
     $this->_writeSetup();
     // Write sheet protection
     $this->_writeProtect();
     // Write SCENPROTECT
     $this->_writeScenProtect();
     // Write OBJECTPROTECT
     $this->_writeObjectProtect();
     // Write sheet password
     $this->_writePassword();
     // Write DEFCOLWIDTH record
     $this->_writeDefcol();
     // Write the COLINFO records if they exist
     if (!empty($this->_colinfo)) {
         $colcount = count($this->_colinfo);
         for ($i = 0; $i < $colcount; ++$i) {
             $this->_writeColinfo($this->_colinfo[$i]);
         }
     }
     // Write EXTERNCOUNT of external references
     if ($this->_BIFF_version == 0x500) {
         $this->_writeExterncount($num_sheets);
     }
     // Write EXTERNSHEET references
     if ($this->_BIFF_version == 0x500) {
         for ($i = 0; $i < $num_sheets; ++$i) {
             $this->_writeExternsheet($this->_phpSheet->getParent()->getSheet($i)->getTitle());
         }
     }
     // Write sheet dimensions
     $this->_writeDimensions();
     // Row dimensions
     foreach ($this->_phpSheet->getRowDimensions() as $rowDimension) {
         $xfIndex = $rowDimension->getXfIndex() + 15;
         // there are 15 cellXfs
         $this->_writeRow($rowDimension->getRowIndex() - 1, $rowDimension->getRowHeight(), $xfIndex, $rowDimension->getVisible() ? '0' : '1', $rowDimension->getOutlineLevel());
     }
     // Write Cells
     foreach ($this->_phpSheet->getCellCollection() as $cellID) {
         $cell = $this->_phpSheet->getCell($cellID);
         $row = $cell->getRow() - 1;
         $column = Cell::columnIndexFromString($cell->getColumn()) - 1;
         // Don't break Excel!
         if ($row + 1 > 65536 or $column + 1 > 256) {
             break;
         }
         // Write cell value
         $xfIndex = $cell->getXfIndex() + 15;
         // there are 15 cell style Xfs
         if ($cell->getValue() instanceof RichText) {
             $this->_writeString($row, $column, $cell->getValue()->getPlainText(), $xfIndex);
         } else {
             switch ($cell->getDatatype()) {
                 case Cell_DataType::TYPE_STRING:
                     if ($cell->getValue() === '' or $cell->getValue() === null) {
                         $this->_writeBlank($row, $column, $xfIndex);
                     } else {
                         $this->_writeString($row, $column, $cell->getValue(), $xfIndex);
                     }
                     break;
                 case Cell_DataType::TYPE_FORMULA:
                     $calculatedValue = $this->_preCalculateFormulas ? $cell->getCalculatedValue() : null;
                     $this->_writeFormula($row, $column, $cell->getValue(), $xfIndex, $calculatedValue);
                     break;
                 case Cell_DataType::TYPE_BOOL:
                     $this->_writeBoolErr($row, $column, $cell->getValue(), 0, $xfIndex);
                     break;
                 case Cell_DataType::TYPE_ERROR:
                     $this->_writeBoolErr($row, $column, $this->_mapErrorCode($cell->getValue()), 1, $xfIndex);
                     break;
                 case Cell_DataType::TYPE_NUMERIC:
                     $this->_writeNumber($row, $column, $cell->getValue(), $xfIndex);
                     break;
             }
         }
     }
     // Append
     if ($this->_BIFF_version == 0x600) {
         $this->_writeMsoDrawing();
     }
     $this->_writeWindow2();
     $this->_writeZoom();
     if ($this->_phpSheet->getFreezePane()) {
         $this->_writePanes();
     }
     $this->_writeSelection();
     $this->_writeMergedCells();
     // Hyperlinks
     if ($this->_BIFF_version == 0x600) {
         foreach ($this->_phpSheet->getHyperLinkCollection() as $coordinate => $hyperlink) {
             list($column, $row) = Cell::coordinateFromString($coordinate);
             $url = $hyperlink->getUrl();
             if (strpos($url, 'sheet://') !== false) {
                 // internal to current workbook
                 $url = str_replace('sheet://', 'internal:', $url);
             } else {
                 if (preg_match('/^(http:|https:|ftp:|mailto:)/', $url)) {
                     // URL
                     // $url = $url;
                 } else {
                     // external (local file)
                     $url = 'external:' . $url;
                 }
             }
             $this->_writeUrl($row - 1, Cell::columnIndexFromString($column) - 1, $url);
         }
     }
     if ($this->_BIFF_version == 0x600) {
         $this->_writeDataValidity();
         $this->_writeSheetLayout();
         $this->_writeSheetProtection();
         $this->_writeRangeProtection();
     }
     $this->_storeEof();
 }
 /**
  * Insert a new column, updating all possible related data
  *
  * @param	int	$pBefore	Insert before this one
  * @param	int	$pNumCols	Number of columns to insert
  * @param	int	$pNumRows	Number of rows to insert
  * @throws	Exception
  */
 public function insertNewBefore($pBefore = 'A1', $pNumCols = 0, $pNumRows = 0, Worksheet $pSheet = null)
 {
     $aCellCollection = $pSheet->getCellCollection();
     // Get coordinates of $pBefore
     $beforeColumn = 'A';
     $beforeRow = 1;
     list($beforeColumn, $beforeRow) = Cell::coordinateFromString($pBefore);
     // Clear cells if we are removing columns or rows
     $highestColumn = $pSheet->getHighestColumn();
     $highestRow = $pSheet->getHighestRow();
     // 1. Clear column strips if we are removing columns
     if ($pNumCols < 0 && Cell::columnIndexFromString($beforeColumn) - 2 + $pNumCols > 0) {
         for ($i = 1; $i <= $highestRow - 1; ++$i) {
             for ($j = Cell::columnIndexFromString($beforeColumn) - 1 + $pNumCols; $j <= Cell::columnIndexFromString($beforeColumn) - 2; ++$j) {
                 $coordinate = Cell::stringFromColumnIndex($j) . $i;
                 $pSheet->removeConditionalStyles($coordinate);
                 if ($pSheet->cellExists($coordinate)) {
                     $pSheet->getCell($coordinate)->setValueExplicit('', Cell_DataType::TYPE_NULL);
                     $pSheet->getCell($coordinate)->setXfIndex(0);
                 }
             }
         }
     }
     // 2. Clear row strips if we are removing rows
     if ($pNumRows < 0 && $beforeRow - 1 + $pNumRows > 0) {
         for ($i = Cell::columnIndexFromString($beforeColumn) - 1; $i <= Cell::columnIndexFromString($highestColumn) - 1; ++$i) {
             for ($j = $beforeRow + $pNumRows; $j <= $beforeRow - 1; ++$j) {
                 $coordinate = Cell::stringFromColumnIndex($i) . $j;
                 $pSheet->removeConditionalStyles($coordinate);
                 if ($pSheet->cellExists($coordinate)) {
                     $pSheet->getCell($coordinate)->setValueExplicit('', Cell_DataType::TYPE_NULL);
                     $pSheet->getCell($coordinate)->setXfIndex(0);
                 }
             }
         }
     }
     // Loop through cells, bottom-up, and change cell coordinates
     while ($cellID = $pNumCols < 0 || $pNumRows < 0 ? array_shift($aCellCollection) : array_pop($aCellCollection)) {
         $cell = $pSheet->getCell($cellID);
         // New coordinates
         $newCoordinates = Cell::stringFromColumnIndex(Cell::columnIndexFromString($cell->getColumn()) - 1 + $pNumCols) . ($cell->getRow() + $pNumRows);
         // Should the cell be updated? Move value and cellXf index from one cell to another.
         if (Cell::columnIndexFromString($cell->getColumn()) >= Cell::columnIndexFromString($beforeColumn) && $cell->getRow() >= $beforeRow) {
             // Update cell styles
             $pSheet->getCell($newCoordinates)->setXfIndex($cell->getXfIndex());
             $cell->setXfIndex(0);
             // Insert this cell at its new location
             if ($cell->getDataType() == Cell_DataType::TYPE_FORMULA) {
                 // Formula should be adjusted
                 $pSheet->getCell($newCoordinates)->setValue($this->updateFormulaReferences($cell->getValue(), $pBefore, $pNumCols, $pNumRows));
             } else {
                 // Formula should not be adjusted
                 $pSheet->getCell($newCoordinates)->setValue($cell->getValue());
             }
             // Clear the original cell
             $pSheet->getCell($cell->getCoordinate())->setValue('');
         }
     }
     // Duplicate styles for the newly inserted cells
     $highestColumn = $pSheet->getHighestColumn();
     $highestRow = $pSheet->getHighestRow();
     if ($pNumCols > 0 && Cell::columnIndexFromString($beforeColumn) - 2 > 0) {
         for ($i = $beforeRow; $i <= $highestRow - 1; ++$i) {
             // Style
             $coordinate = Cell::stringFromColumnIndex(Cell::columnIndexFromString($beforeColumn) - 2) . $i;
             if ($pSheet->cellExists($coordinate)) {
                 $xfIndex = $pSheet->getCell($coordinate)->getXfIndex();
                 $conditionalStyles = $pSheet->conditionalStylesExists($coordinate) ? $pSheet->getConditionalStyles($coordinate) : false;
                 for ($j = Cell::columnIndexFromString($beforeColumn) - 1; $j <= Cell::columnIndexFromString($beforeColumn) - 2 + $pNumCols; ++$j) {
                     $pSheet->getCellByColumnAndRow($j, $i)->setXfIndex($xfIndex);
                     if ($conditionalStyles) {
                         $cloned = array();
                         foreach ($conditionalStyles as $conditionalStyle) {
                             $cloned[] = clone $conditionalStyle;
                         }
                         $pSheet->setConditionalStyles(Cell::stringFromColumnIndex($j) . $i, $cloned);
                     }
                 }
             }
         }
     }
     if ($pNumRows > 0 && $beforeRow - 1 > 0) {
         for ($i = Cell::columnIndexFromString($beforeColumn) - 1; $i <= Cell::columnIndexFromString($highestColumn) - 1; ++$i) {
             // Style
             $coordinate = Cell::stringFromColumnIndex($i) . ($beforeRow - 1);
             if ($pSheet->cellExists($coordinate)) {
                 $xfIndex = $pSheet->getCell($coordinate)->getXfIndex();
                 $conditionalStyles = $pSheet->conditionalStylesExists($coordinate) ? $pSheet->getConditionalStyles($coordinate) : false;
                 for ($j = $beforeRow; $j <= $beforeRow - 1 + $pNumRows; ++$j) {
                     $pSheet->getCell(Cell::stringFromColumnIndex($i) . $j)->setXfIndex($xfIndex);
                     if ($conditionalStyles) {
                         $cloned = array();
                         foreach ($conditionalStyles as $conditionalStyle) {
                             $cloned[] = clone $conditionalStyle;
                         }
                         $pSheet->setConditionalStyles(Cell::stringFromColumnIndex($i) . $j, $cloned);
                     }
                 }
             }
         }
     }
     // Update worksheet: column dimensions
     $aColumnDimensions = array_reverse($pSheet->getColumnDimensions(), true);
     if (count($aColumnDimensions) > 0) {
         foreach ($aColumnDimensions as $objColumnDimension) {
             $newReference = $this->updateCellReference($objColumnDimension->getColumnIndex() . '1', $pBefore, $pNumCols, $pNumRows);
             list($newReference) = Cell::coordinateFromString($newReference);
             if ($objColumnDimension->getColumnIndex() != $newReference) {
                 $objColumnDimension->setColumnIndex($newReference);
             }
         }
         $pSheet->refreshColumnDimensions();
     }
     // Update worksheet: row dimensions
     $aRowDimensions = array_reverse($pSheet->getRowDimensions(), true);
     if (count($aRowDimensions) > 0) {
         foreach ($aRowDimensions as $objRowDimension) {
             $newReference = $this->updateCellReference('A' . $objRowDimension->getRowIndex(), $pBefore, $pNumCols, $pNumRows);
             list(, $newReference) = Cell::coordinateFromString($newReference);
             if ($objRowDimension->getRowIndex() != $newReference) {
                 $objRowDimension->setRowIndex($newReference);
             }
         }
         $pSheet->refreshRowDimensions();
         $copyDimension = $pSheet->getRowDimension($beforeRow - 1);
         for ($i = $beforeRow; $i <= $beforeRow - 1 + $pNumRows; ++$i) {
             $newDimension = $pSheet->getRowDimension($i);
             $newDimension->setRowHeight($copyDimension->getRowHeight());
             $newDimension->setVisible($copyDimension->getVisible());
             $newDimension->setOutlineLevel($copyDimension->getOutlineLevel());
             $newDimension->setCollapsed($copyDimension->getCollapsed());
         }
     }
     // Update worksheet: breaks
     $aBreaks = array_reverse($pSheet->getBreaks(), true);
     foreach ($aBreaks as $key => $value) {
         $newReference = $this->updateCellReference($key, $pBefore, $pNumCols, $pNumRows);
         if ($key != $newReference) {
             $pSheet->setBreak($newReference, $value);
             $pSheet->setBreak($key, Worksheet::BREAK_NONE);
         }
     }
     // Update worksheet: hyperlinks
     $aHyperlinkCollection = array_reverse($pSheet->getHyperlinkCollection(), true);
     foreach ($aHyperlinkCollection as $key => $value) {
         $newReference = $this->updateCellReference($key, $pBefore, $pNumCols, $pNumRows);
         if ($key != $newReference) {
             $pSheet->setHyperlink($newReference, $value);
             $pSheet->setHyperlink($key, null);
         }
     }
     // Update worksheet: data validations
     $aDataValidationCollection = array_reverse($pSheet->getDataValidationCollection(), true);
     foreach ($aDataValidationCollection as $key => $value) {
         $newReference = $this->updateCellReference($key, $pBefore, $pNumCols, $pNumRows);
         if ($key != $newReference) {
             $pSheet->setDataValidation($newReference, $value);
             $pSheet->setDataValidation($key, null);
         }
     }
     // Update worksheet: merge cells
     $aMergeCells = $pSheet->getMergeCells();
     $aNewMergeCells = array();
     // the new array of all merge cells
     foreach ($aMergeCells as $key => &$value) {
         $newReference = $this->updateCellReference($key, $pBefore, $pNumCols, $pNumRows);
         $aNewMergeCells[$newReference] = $newReference;
     }
     $pSheet->setMergeCells($aNewMergeCells);
     // replace the merge cells array
     // Update worksheet: protected cells
     $aProtectedCells = array_reverse($pSheet->getProtectedCells(), true);
     foreach ($aProtectedCells as $key => $value) {
         $newReference = $this->updateCellReference($key, $pBefore, $pNumCols, $pNumRows);
         if ($key != $newReference) {
             $pSheet->protectCells($newReference, $value, true);
             $pSheet->unprotectCells($key);
         }
     }
     // Update worksheet: autofilter
     if ($pSheet->getAutoFilter() != '') {
         $pSheet->setAutoFilter($this->updateCellReference($pSheet->getAutoFilter(), $pBefore, $pNumCols, $pNumRows));
     }
     // Update worksheet: freeze pane
     if ($pSheet->getFreezePane() != '') {
         $pSheet->freezePane($this->updateCellReference($pSheet->getFreezePane(), $pBefore, $pNumCols, $pNumRows));
     }
     // Page setup
     if ($pSheet->getPageSetup()->isPrintAreaSet()) {
         $pSheet->getPageSetup()->setPrintArea($this->updateCellReference($pSheet->getPageSetup()->getPrintArea(), $pBefore, $pNumCols, $pNumRows));
     }
     // Update worksheet: drawings
     $aDrawings = $pSheet->getDrawingCollection();
     foreach ($aDrawings as $objDrawing) {
         $newReference = $this->updateCellReference($objDrawing->getCoordinates(), $pBefore, $pNumCols, $pNumRows);
         if ($objDrawing->getCoordinates() != $newReference) {
             $objDrawing->setCoordinates($newReference);
         }
     }
     // Update workbook: named ranges
     if (count($pSheet->getParent()->getNamedRanges()) > 0) {
         foreach ($pSheet->getParent()->getNamedRanges() as $namedRange) {
             if ($namedRange->getWorksheet()->getHashCode() == $pSheet->getHashCode()) {
                 $namedRange->setRange($this->updateCellReference($namedRange->getRange(), $pBefore, $pNumCols, $pNumRows));
             }
         }
     }
     // Garbage collect
     $pSheet->garbageCollect();
 }
 /**
  * Add external sheet
  *
  * @param  Worksheet $pSheet External sheet to add
  * @param  int|null $iSheetIndex Index where sheet should go (0,1,..., or null for last)
  * @throws Exception
  * @return Worksheet
  */
 public function addExternalSheet(Worksheet $pSheet, $iSheetIndex = null)
 {
     if ($this->sheetNameExists($pSheet->getTitle())) {
         throw new Exception("Workbook already contains a worksheet named '{$pSheet->getTitle()}'. Rename the external sheet first.");
     }
     // count how many cellXfs there are in this workbook currently, we will need this below
     $countCellXfs = count($this->cellXfCollection);
     // copy all the shared cellXfs from the external workbook and append them to the current
     foreach ($pSheet->getParent()->getCellXfCollection() as $cellXf) {
         $this->addCellXf(clone $cellXf);
     }
     // move sheet to this workbook
     $pSheet->rebindParent($this);
     // update the cellXfs
     foreach ($pSheet->getCellCollection(false) as $cellID) {
         $cell = $pSheet->getCell($cellID);
         $cell->setXfIndex($cell->getXfIndex() + $countCellXfs);
     }
     return $this->addSheet($pSheet, $iSheetIndex);
 }
Exemple #7
0
 /**
  * Write Cell
  *
  * @param	Shared_XMLWriter	$objWriter				XML Writer
  * @param	Worksheet			$pSheet					Worksheet
  * @param	Cell				$pCell					Cell
  * @param	string[]					$pStringTable			String table
  * @param	string[]					$pFlippedStringTable	String table (flipped), for faster index searching
  * @throws	Exception
  */
 private function _writeCell(Shared_XMLWriter $objWriter = null, Worksheet $pSheet = null, $pCellAddress = null, $pStringTable = null, $pFlippedStringTable = null)
 {
     $pCell = $pSheet->getCell($pCellAddress);
     if (is_array($pStringTable) && is_array($pFlippedStringTable)) {
         // Cell
         $objWriter->startElement('c');
         $objWriter->writeAttribute('r', $pCell->getCoordinate());
         // Sheet styles
         if ($pCell->getXfIndex() != '') {
             $objWriter->writeAttribute('s', $pCell->getXfIndex());
         }
         // If cell value is supplied, write cell value
         if (is_object($pCell->getValue()) || $pCell->getValue() !== '') {
             // Map type
             $mappedType = $pCell->getDataType();
             // Write data type depending on its type
             switch (strtolower($mappedType)) {
                 case 'inlinestr':
                     // Inline string
                     $objWriter->writeAttribute('t', $mappedType);
                     break;
                 case 's':
                     // String
                     $objWriter->writeAttribute('t', $mappedType);
                     break;
                 case 'b':
                     // Boolean
                     $objWriter->writeAttribute('t', $mappedType);
                     break;
                 case 'f':
                     // Formula
                     $calculatedValue = null;
                     if ($this->getParentWriter()->getPreCalculateFormulas()) {
                         $pCell->attach($pSheet);
                         $calculatedValue = $pCell->getCalculatedValue();
                     } else {
                         $pCell->attach($pSheet);
                         $calculatedValue = $pCell->getValue();
                     }
                     if (is_string($calculatedValue)) {
                         $objWriter->writeAttribute('t', 'str');
                     }
                     break;
                 case 'e':
                     // Error
                     $objWriter->writeAttribute('t', $mappedType);
             }
             // Write data depending on its type
             switch (strtolower($mappedType)) {
                 case 'inlinestr':
                     // Inline string
                     if (!$pCell->getValue() instanceof RichText) {
                         $objWriter->writeElement('t', Shared_String::ControlCharacterPHP2OOXML(htmlspecialchars($pCell->getValue())));
                     } else {
                         if ($pCell->getValue() instanceof RichText) {
                             $objWriter->startElement('is');
                             $this->getParentWriter()->getWriterPart('stringtable')->writeRichText($objWriter, $pCell->getValue());
                             $objWriter->endElement();
                         }
                     }
                     break;
                 case 's':
                     // String
                     if (!$pCell->getValue() instanceof RichText) {
                         if (isset($pFlippedStringTable[$pCell->getValue()])) {
                             $objWriter->writeElement('v', $pFlippedStringTable[$pCell->getValue()]);
                         }
                     } else {
                         if ($pCell->getValue() instanceof RichText) {
                             $objWriter->writeElement('v', $pFlippedStringTable[$pCell->getValue()->getHashCode()]);
                         }
                     }
                     break;
                 case 'f':
                     // Formula
                     $objWriter->writeElement('f', substr($pCell->getValue(), 1));
                     if ($this->getParentWriter()->getOffice2003Compatibility() === false) {
                         if ($this->getParentWriter()->getPreCalculateFormulas()) {
                             $calculatedValue = $pCell->getCalculatedValue();
                             if (!is_array($calculatedValue) && substr($calculatedValue, 0, 1) != '#') {
                                 $v = Shared_String::FormatNumber($calculatedValue);
                                 $objWriter->writeElement('v', $v);
                             } else {
                                 $objWriter->writeElement('v', '0');
                             }
                         } else {
                             $objWriter->writeElement('v', '0');
                         }
                     }
                     break;
                 case 'n':
                     // Numeric
                     // force point as decimal separator in case current locale uses comma
                     $v = str_replace(',', '.', $pCell->getValue());
                     $objWriter->writeElement('v', $v);
                     break;
                 case 'b':
                     // Boolean
                     $objWriter->writeElement('v', $pCell->getValue() ? '1' : '0');
                     break;
                 case 'e':
                     // Error
                     if (substr($pCell->getValue(), 0, 1) == '=') {
                         $objWriter->writeElement('f', substr($pCell->getValue(), 1));
                         $objWriter->writeElement('v', substr($pCell->getValue(), 1));
                     } else {
                         $objWriter->writeElement('v', $pCell->getValue());
                     }
                     break;
             }
         }
         $objWriter->endElement();
     } else {
         throw new Exception("Invalid parameters passed.");
     }
 }