/**
* Extract range values
*
* @param string &$pRange String based range representation
* @param Worksheet $pSheet Worksheet
* @return mixed Array of values in range if range contains more than one element. Otherwise, a single value is returned.
* @param boolean $resetLog Flag indicating whether calculation log should be reset or not
* @throws Calculation\Exception
*/
public function extractNamedRange(&$pRange = 'A1', Worksheet $pSheet = null, $resetLog = true)
{
// Return value
$returnValue = array();
// echo 'extractNamedRange('.$pRange.')<br />';
if ($pSheet !== null) {
$pSheetName = $pSheet->getTitle();
// echo 'Current sheet name is '.$pSheetName.'<br />';
// echo 'Range reference is '.$pRange.'<br />';
if (strpos($pRange, '!') !== false) {
// echo '$pRange reference includes sheet reference', PHP_EOL;
list($pSheetName, $pRange) = Worksheet::extractSheetTitle($pRange, true);
// echo 'New sheet name is '.$pSheetName, PHP_EOL;
// echo 'Adjusted Range reference is '.$pRange, PHP_EOL;
$pSheet = $this->spreadsheet->getSheetByName($pSheetName);
}
// Named range?
$namedRange = NamedRange::resolveRange($pRange, $pSheet);
if ($namedRange !== null) {
$pSheet = $namedRange->getWorksheet();
// echo 'Named Range '.$pRange.' (';
$pRange = $namedRange->getRange();
$splitRange = Cell::splitRange($pRange);
// Convert row and column references
if (ctype_alpha($splitRange[0][0])) {
$pRange = $splitRange[0][0] . '1:' . $splitRange[0][1] . $namedRange->getWorksheet()->getHighestRow();
} elseif (ctype_digit($splitRange[0][0])) {
$pRange = 'A' . $splitRange[0][0] . ':' . $namedRange->getWorksheet()->getHighestColumn() . $splitRange[0][1];
}
// echo $pRange.') is in sheet '.$namedRange->getWorksheet()->getTitle().'<br />';
// if ($pSheet->getTitle() != $namedRange->getWorksheet()->getTitle()) {
// if (!$namedRange->getLocalOnly()) {
// $pSheet = $namedRange->getWorksheet();
// } else {
// return $returnValue;
// }
// }
} else {
return Calculation\Functions::REF();
}
// Extract range
$aReferences = Cell::extractAllCellReferencesInRange($pRange);
// var_dump($aReferences);
if (!isset($aReferences[1])) {
// Single cell (or single column or row) in range
list($currentCol, $currentRow) = Cell::coordinateFromString($aReferences[0]);
$cellValue = null;
if ($pSheet->cellExists($aReferences[0])) {
$returnValue[$currentRow][$currentCol] = $pSheet->getCell($aReferences[0])->getCalculatedValue($resetLog);
} else {
$returnValue[$currentRow][$currentCol] = null;
}
} else {
// Extract cell data for all cells in the range
foreach ($aReferences as $reference) {
// Extract range
list($currentCol, $currentRow) = Cell::coordinateFromString($reference);
// echo 'NAMED RANGE: $currentCol='.$currentCol.' $currentRow='.$currentRow.'<br />';
$cellValue = null;
if ($pSheet->cellExists($reference)) {
$returnValue[$currentRow][$currentCol] = $pSheet->getCell($reference)->getCalculatedValue($resetLog);
} else {
$returnValue[$currentRow][$currentCol] = null;
}
}
}
// print_r($returnValue);
// echo '<br />';
}
return $returnValue;
}
/**
* 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();
}
/**
* Read BLANK record
*/
private function _readBlank()
{
$length = $this->_GetInt2d($this->_data, $this->_pos + 2);
$recordData = substr($this->_data, $this->_pos + 4, $length);
// move stream pointer to next record
$this->_pos += 4 + $length;
// offset: 0; size: 2; row index
$row = $this->_GetInt2d($recordData, 0);
// offset: 2; size: 2; col index
$col = $this->_GetInt2d($recordData, 2);
$columnString = Cell::stringFromColumnIndex($col);
// Read cell?
if (!is_null($this->getReadFilter()) && $this->getReadFilter()->readCell($columnString, $row + 1, $this->_phpSheet->getTitle())) {
// offset: 4; size: 2; XF index
$xfIndex = $this->_GetInt2d($recordData, 4);
// add style information
if (!$this->_readDataOnly) {
$this->_phpSheet->getCell($columnString . ($row + 1))->setXfIndex($this->_mapCellXfIndex[$xfIndex]);
}
}
}
/**
* Writes the Excel BIFF EXTERNSHEET record. These references are used by
* formulas. A formula references a sheet name via an index. Since we store a
* reference to all of the external worksheets the EXTERNSHEET index is the same
* as the worksheet index.
*
* @param string $sheetname The name of a external worksheet
*/
private function _writeExternsheet($sheetname)
{
$record = 0x17;
// Record identifier
// References to the current sheet are encoded differently to references to
// external sheets.
//
if ($this->_phpSheet->getTitle() == $sheetname) {
$sheetname = '';
$length = 0x2;
// The following 2 bytes
$cch = 1;
// The following byte
$rgch = 0x2;
// Self reference
} else {
$length = 0x2 + strlen($sheetname);
$cch = strlen($sheetname);
$rgch = 0x3;
// Reference to a sheet in the current workbook
}
$header = pack("vv", $record, $length);
$data = pack("CC", $cch, $rgch);
$this->_append($header . $data . $sheetname);
}
/**
* Add external sheet
*
* @param Worksheet $pSheet External sheet to add
* @param int|null $iSheetIndex Index where sheet should go (0,1,..., or null for last)
* @throws Exception
* @return Worksheet
*/
public function addExternalSheet(Worksheet $pSheet, $iSheetIndex = null)
{
if (!is_null($this->getSheetByName($pSheet->getTitle()))) {
throw new Exception("Workbook already contains a worksheet named '{$pSheet->getTitle()}'. Rename the external sheet first.");
}
// count how many cellXfs there are in this workbook currently, we will need this below
$countCellXfs = count($this->_cellXfCollection);
// copy all the shared cellXfs from the external workbook and append them to the current
foreach ($pSheet->getParent()->getCellXfCollection() as $cellXf) {
$this->addCellXf(clone $cellXf);
}
// move sheet to this workbook
$pSheet->rebindParent($this);
// update the cellXfs
foreach ($pSheet->getCellCollection(false) as $cellID) {
$cell = $sheet->getCell($cellID);
$cell->setXfIndex($cell->getXfIndex() + $countCellXfs);
}
return $this->addSheet($pSheet, $iSheetIndex);
}
/**
* Write Defined Name for PrintTitles
*
* @param Shared_XMLWriter $objWriter XML Writer
* @param Worksheet $pSheet
* @param int $pSheetId
* @throws Exception
*/
private function _writeDefinedNameForPrintArea(Shared_XMLWriter $objWriter = null, Worksheet $pSheet = null, $pSheetId = 0)
{
// definedName for PrintArea
if ($pSheet->getPageSetup()->isPrintAreaSet()) {
$objWriter->startElement('definedName');
$objWriter->writeAttribute('name', '_xlnm.Print_Area');
$objWriter->writeAttribute('localSheetId', $pSheetId);
// Setting string
$settingString = '';
// Print area
$printArea = Cell::splitRange($pSheet->getPageSetup()->getPrintArea());
$chunks = array();
foreach ($printArea as $printAreaRect) {
$printAreaRect[0] = Cell::absoluteCoordinate($printAreaRect[0]);
$printAreaRect[1] = Cell::absoluteCoordinate($printAreaRect[1]);
$chunks[] = '\'' . str_replace("'", "''", $pSheet->getTitle()) . '\'!' . implode(':', $printAreaRect);
}
$objWriter->writeRaw(implode(',', $chunks));
$objWriter->endElement();
}
}
/**
* Remove named range
*
* @param string $namedRange
* @param Worksheet|null $pSheet Scope: use null for global scope.
* @return PHPExcel
*/
public function removeNamedRange($namedRange, Worksheet $pSheet = null)
{
if ($pSheet === null) {
if (isset($this->namedRanges[$namedRange])) {
unset($this->namedRanges[$namedRange]);
}
} else {
if (isset($this->namedRanges[$pSheet->getTitle() . '!' . $namedRange])) {
unset($this->namedRanges[$pSheet->getTitle() . '!' . $namedRange]);
}
}
return $this;
}
/**
* Writes Excel BIFF BOUNDSHEET record.
*
* @param Worksheet $sheet Worksheet name
* @param integer $offset Location of worksheet BOF
*/
private function _writeBoundsheet($sheet, $offset)
{
$sheetname = $sheet->getTitle();
$record = 0x85;
// Record identifier
// sheet state
switch ($sheet->getSheetState()) {
case Worksheet::SHEETSTATE_VISIBLE:
$ss = 0x0;
break;
case Worksheet::SHEETSTATE_HIDDEN:
$ss = 0x1;
break;
case Worksheet::SHEETSTATE_VERYHIDDEN:
$ss = 0x2;
break;
default:
$ss = 0x0;
break;
}
// sheet type
$st = 0x0;
$grbit = 0x0;
// Visibility and sheet type
if ($this->_BIFF_version == 0x600) {
$data = pack("VCC", $offset, $ss, $st);
$data .= Shared_String::UTF8toBIFF8UnicodeShort($sheetname);
} else {
$cch = strlen($sheetname);
// Length of sheet name
$data = pack("VCCC", $offset, $ss, $st, $cch);
$data .= $sheetname;
}
$length = strlen($data);
$header = pack("vv", $record, $length);
$this->_append($header . $data);
}
