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