/**
* Does the cell at a specific coordinate exist?
*
* @param string $pCoordinate Coordinate of the cell
* @throws PHPExcel_Exception
* @return boolean
*/
public function cellExists($pCoordinate = 'A1')
{
// Worksheet reference?
if (strpos($pCoordinate, '!') !== false) {
$worksheetReference = PHPExcel_Worksheet::extractSheetTitle($pCoordinate, true);
return $this->_parent->getSheetByName($worksheetReference[0])->cellExists($worksheetReference[1]);
}
// Named range?
if (!preg_match('/^' . PHPExcel_Calculation::CALCULATION_REGEXP_CELLREF . '$/i', $pCoordinate, $matches) && preg_match('/^' . PHPExcel_Calculation::CALCULATION_REGEXP_NAMEDRANGE . '$/i', $pCoordinate, $matches)) {
$namedRange = PHPExcel_NamedRange::resolveRange($pCoordinate, $this);
if ($namedRange !== NULL) {
$pCoordinate = $namedRange->getRange();
if ($this->getHashCode() != $namedRange->getWorksheet()->getHashCode()) {
if (!$namedRange->getLocalOnly()) {
return $namedRange->getWorksheet()->cellExists($pCoordinate);
} else {
throw new PHPExcel_Exception('Named range ' . $namedRange->getName() . ' is not accessible from within sheet ' . $this->getTitle());
}
}
} else {
return false;
}
}
// Uppercase coordinate
$pCoordinate = strtoupper($pCoordinate);
if (strpos($pCoordinate, ':') !== false || strpos($pCoordinate, ',') !== false) {
throw new PHPExcel_Exception('Cell coordinate can not be a range of cells.');
} elseif (strpos($pCoordinate, '$') !== false) {
throw new PHPExcel_Exception('Cell coordinate must not be absolute.');
} else {
// Coordinates
$aCoordinates = PHPExcel_Cell::coordinateFromString($pCoordinate);
// Cell exists?
return $this->_cellCollection->isDataSet($pCoordinate);
}
}
/**
* Extract range values
*
* @param string &$pRange String based range representation
* @param PHPExcel_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 PHPExcel_Calculation_Exception
*/
public function extractNamedRange(&$pRange = 'A1', PHPExcel_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) = PHPExcel_Worksheet::extractSheetTitle($pRange, true);
// echo 'New sheet name is '.$pSheetName,PHP_EOL;
// echo 'Adjusted Range reference is '.$pRange,PHP_EOL;
$pSheet = $this->_workbook->getSheetByName($pSheetName);
}
// Named range?
$namedRange = PHPExcel_NamedRange::resolveRange($pRange, $pSheet);
if ($namedRange !== NULL) {
$pSheet = $namedRange->getWorksheet();
// echo 'Named Range '.$pRange.' (';
$pRange = $namedRange->getRange();
$splitRange = PHPExcel_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 PHPExcel_Calculation_Functions::REF();
}
// Extract range
$aReferences = PHPExcel_Cell::extractAllCellReferencesInRange($pRange);
// var_dump($aReferences);
if (!isset($aReferences[1])) {
// Single cell (or single column or row) in range
list($currentCol, $currentRow) = PHPExcel_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) = PHPExcel_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
return $returnValue;
}
