/** * 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; }