/**
* 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;
}
/**
* 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.
* @throws Exception
*/
public function extractNamedRange(&$pRange = 'A1', Worksheet $pSheet = null, $resetLog = true)
{
// Return value
$returnValue = array();
// echo 'extractNamedRange('.$pRange.')<br />';
if (!is_null($pSheet)) {
// echo 'Current sheet name is '.$pSheet->getTitle().'<br />';
// echo 'Range reference is '.$pRange.'<br />';
if (strpos($pRange, '!') !== false) {
// echo '$pRange reference includes sheet reference<br />';
$worksheetReference = Worksheet::extractSheetTitle($pRange, true);
$pSheet = $pSheet->getParent()->getSheetByName($worksheetReference[0]);
// echo 'New sheet name is '.$pSheet->getTitle().'<br />';
$pRange = $worksheetReference[1];
// echo 'Adjusted Range reference is '.$pRange.'<br />';
}
// Named range?
$namedRange = NamedRange::resolveRange($pRange, $pSheet);
if (!is_null($namedRange)) {
$pSheet = $namedRange->getWorksheet();
//// echo 'Named Range '.$pRange.' (';
$pRange = $namedRange->getRange();
//// 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);
if (count($aReferences) == 1) {
list($currentCol, $currentRow) = Cell::coordinateFromString($aReferences[0]);
if ($pSheet->cellExists($aReferences[0])) {
$returnValue[$currentRow][$currentCol] = $pSheet->getCell($aReferences[0])->getCalculatedValue($resetLog);
} else {
$returnValue[$currentRow][$currentCol] = NULL;
}
} else {
// Extract cell data
foreach ($aReferences as $reference) {
// Extract range
list($currentCol, $currentRow) = Cell::coordinateFromString($reference);
// echo 'NAMED RANGE: $currentCol='.$currentCol.' $currentRow='.$currentRow.'<br />';
if ($pSheet->cellExists($reference)) {
$returnValue[$currentRow][$currentCol] = $pSheet->getCell($reference)->getCalculatedValue($resetLog);
} else {
$returnValue[$currentRow][$currentCol] = NULL;
}
}
}
// print_r($returnValue);
// echo '<br />';
}
// Return
return $returnValue;
}
/**
* Add named range
*
* @param NamedRange $namedRange
* @return PHPExcel
*/
public function addNamedRange(NamedRange $namedRange)
{
if ($namedRange->getScope() == null) {
// global scope
$this->_namedRanges[$namedRange->getName()] = $namedRange;
} else {
// local scope
$this->_namedRanges[$namedRange->getScope()->getTitle() . '!' . $namedRange->getName()] = $namedRange;
}
return true;
}
/**
* Cell at a specific coordinate exists?
*
* @param string $pCoordinate Coordinate of the cell
* @throws Exception
* @return boolean
*/
public function cellExists($pCoordinate = 'A1')
{
// Worksheet reference?
if (strpos($pCoordinate, '!') !== false) {
$worksheetReference = Worksheet::extractSheetTitle($pCoordinate, true);
return $this->getParent()->getSheetByName($worksheetReference[0])->cellExists($worksheetReference[1]);
}
// Named range?
if (!preg_match('/^' . Calculation::CALCULATION_REGEXP_CELLREF . '$/i', $pCoordinate, $matches) && preg_match('/^' . Calculation::CALCULATION_REGEXP_NAMEDRANGE . '$/i', $pCoordinate, $matches)) {
$namedRange = NamedRange::resolveRange($pCoordinate, $this);
if (!is_null($namedRange)) {
$pCoordinate = $namedRange->getRange();
if ($this->getHashCode() != $namedRange->getWorksheet()->getHashCode()) {
if (!$namedRange->getLocalOnly()) {
return $namedRange->getWorksheet()->cellExists($pCoordinate);
} else {
throw new Exception('Named range ' . $namedRange->getName() . ' is not accessible from within sheet ' . $this->getTitle());
}
}
}
}
// Uppercase coordinate
$pCoordinate = strtoupper($pCoordinate);
if (strpos($pCoordinate, ':') !== false || strpos($pCoordinate, ',') !== false) {
throw new Exception('Cell coordinate can not be a range of cells.');
} elseif (strpos($pCoordinate, '$') !== false) {
throw new Exception('Cell coordinate must not be absolute.');
} else {
// Coordinates
$aCoordinates = Cell::coordinateFromString($pCoordinate);
// Cell exists?
return $this->_cellCollection->isDataSet($pCoordinate);
}
}
/**
* Write Defined Name for autoFilter
*
* @param Shared_XMLWriter $objWriter XML Writer
* @param NamedRange $pNamedRange
* @throws Exception
*/
private function _writeDefinedNameForNamedRange(Shared_XMLWriter $objWriter = null, NamedRange $pNamedRange)
{
// definedName for named range
$objWriter->startElement('definedName');
$objWriter->writeAttribute('name', $pNamedRange->getName());
if ($pNamedRange->getLocalOnly()) {
$objWriter->writeAttribute('localSheetId', $pNamedRange->getScope()->getParent()->getIndex($pNamedRange->getScope()));
}
// Create absolute coordinate and write as raw text
$range = Cell::splitRange($pNamedRange->getRange());
for ($i = 0; $i < count($range); $i++) {
$range[$i][0] = '\'' . str_replace("'", "''", $pNamedRange->getWorksheet()->getTitle()) . '\'!' . Cell::absoluteCoordinate($range[$i][0]);
if (isset($range[$i][1])) {
$range[$i][1] = Cell::absoluteCoordinate($range[$i][1]);
}
}
$range = Cell::buildRange($range);
$objWriter->writeRaw($range);
$objWriter->endElement();
}
/**
* Create array from a range of cells
*
* @param string $pNamedRange Name of the Named Range
* @param mixed $nullValue Value returned in the array entry if a cell doesn't exist
* @param boolean $calculateFormulas Should formulas be calculated?
* @param boolean $formatData Should formatting be applied to cell values?
* @param boolean $returnCellRef False - Return a simple array of rows and columns indexed by number counting from zero
* True - Return rows and columns indexed by their actual row and column IDs
* @return array
* @throws Exception
*/
public function namedRangeToArray($pNamedRange = '', $nullValue = null, $calculateFormulas = true, $formatData = true, $returnCellRef = false)
{
$namedRange = NamedRange::resolveRange($pNamedRange, $this);
if ($namedRange !== null) {
$pWorkSheet = $namedRange->getWorksheet();
$pCellRange = $namedRange->getRange();
return $pWorkSheet->rangeToArray($pCellRange, $nullValue, $calculateFormulas, $formatData, $returnCellRef);
}
throw new Exception('Named Range ' . $pNamedRange . ' does not exist.');
}
