/**
* 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;
}
/**
* 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();
}
/**
* 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);
}
/**
* 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.");
}
}