예제 #1
0
 /**
  * Get style for cell
  *
  * @param 	string 	$pCellCoordinate	Cell coordinate to get style for
  * @return 	PHPExcel_Style
  * @throws 	Exception
  */
 public function getStyle($pCellCoordinate = 'A1')
 {
     // Worksheet reference?
     if (strpos($pCellCoordinate, '!') !== false) {
         $worksheetReference = PHPExcel_Worksheet::extractSheetTitle($pCellCoordinate, true);
         return $this->getParent()->getSheetByName($worksheetReference[0])->getStyle($worksheetReference[1]);
     }
     // Named range?
     $namedRange = PHPExcel_NamedRange::resolveRange($pCellCoordinate, $this);
     if (!is_null($namedRange)) {
         $pCoordinate = $namedRange->getRange();
         if ($this->getHashCode() != $namedRange->getWorksheet()->getHashCode()) {
             if (!$namedRange->getLocalOnly()) {
                 return $namedRange->getWorksheet()->getStyle($pCellCoordinate);
             } else {
                 throw new Exception('Named range ' . $namedRange->getName() . ' is not accessible from within sheet ' . $this->getTitle());
             }
         }
     }
     // Uppercase coordinate
     $pCellCoordinate = strtoupper($pCellCoordinate);
     if (eregi(':', $pCellCoordinate)) {
         throw new Exception('Cell coordinate string can not be a range of cells.');
     } else {
         if (eregi('\\$', $pCellCoordinate)) {
             throw new Exception('Cell coordinate string must not be absolute.');
         } else {
             if ($pCellCoordinate == '') {
                 throw new Exception('Cell coordinate can not be zero-length string.');
             } else {
                 // Create a cell for this coordinate.
                 // Reason: When we have an empty cell that has style information,
                 // it should exist for our IWriter
                 $this->getCell($pCellCoordinate);
                 // Check if we already have style information for this cell.
                 // If not, create a new style.
                 if (isset($this->_styles[$pCellCoordinate])) {
                     return $this->_styles[$pCellCoordinate];
                 } else {
                     $newStyle = clone $this->getDefaultStyle();
                     $this->_styles[$pCellCoordinate] = $newStyle;
                     return $newStyle;
                 }
             }
         }
     }
 }
예제 #2
0
 /**
  * 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 PHPExcel_Exception
  */
 public function namedRangeToArray($pNamedRange = '', $nullValue = null, $calculateFormulas = true, $formatData = true, $returnCellRef = false)
 {
     $namedRange = PHPExcel_NamedRange::resolveRange($pNamedRange, $this);
     if ($namedRange !== NULL) {
         $pWorkSheet = $namedRange->getWorksheet();
         $pCellRange = $namedRange->getRange();
         return $pWorkSheet->rangeToArray($pCellRange, $nullValue, $calculateFormulas, $formatData, $returnCellRef);
     }
     throw new PHPExcel_Exception('Named Range ' . $pNamedRange . ' does not exist.');
 }
예제 #3
0
 /**
  * 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;
 }
예제 #4
0
 /**
  * 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 = PHPExcel_Worksheet::extractSheetTitle($pCoordinate, true);
         return $this->getParent()->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_CELLREF . '$/i', $pCoordinate, $matches)) {
         $namedRange = PHPExcel_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 = PHPExcel_Cell::coordinateFromString($pCoordinate);
         // Cell exists?
         return isset($this->_cellCollection[$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.
  * @throws	Exception
  */
 public function extractNamedRange(&$pRange = 'A1', PHPExcel_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 = PHPExcel_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 = PHPExcel_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 PHPExcel_Calculation_Functions::REF();
         }
         // Extract range
         $aReferences = PHPExcel_Cell::extractAllCellReferencesInRange($pRange);
         if (count($aReferences) == 1) {
             list($currentCol, $currentRow) = PHPExcel_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) = PHPExcel_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;
 }
예제 #6
0
 /**
  * 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.
  * @throws	Exception
  */
 public function extractRange($pRange = 'A1', PHPExcel_Worksheet $pSheet = null)
 {
     // Return value
     $returnValue = array();
     // Worksheet given?
     if (!is_null($pSheet)) {
         // Worksheet reference?
         if (strpos($pRange, '!') !== false) {
             $worksheetReference = PHPExcel_Worksheet::extractSheetTitle($pRange, true);
             $pSheet = $pSheet->getParent()->getSheetByName($worksheetReference[0]);
             $pRange = $worksheetReference[1];
         }
         // Named range?
         $namedRange = PHPExcel_NamedRange::resolveRange($pRange, $pSheet);
         if (!is_null($namedRange)) {
             $pRange = $namedRange->getRange();
             if ($pSheet->getHashCode() != $namedRange->getWorksheet()->getHashCode()) {
                 if (!$namedRange->getLocalOnly()) {
                     $pSheet = $namedRange->getWorksheet();
                 } else {
                     return '';
                 }
             }
         }
         // Extract range
         $aReferences = PHPExcel_Cell::extractAllCellReferencesInRange($pRange);
         if (count($aReferences) == 1) {
             return $pSheet->getCell($aReferences[0])->getCalculatedValue();
         }
         // Extract cell data
         foreach ($aReferences as $reference) {
             // Extract range
             list($currentCol, $currentRow) = PHPExcel_Cell::coordinateFromString($reference);
             $returnValue[$currentCol][$currentRow] = $pSheet->getCell($reference)->getCalculatedValue();
         }
     }
     // Return
     return $returnValue;
 }
예제 #7
0
 /**
  * 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();
     if ($pSheet !== null) {
         $pSheetName = $pSheet->getTitle();
         if (strpos($pRange, '!') !== false) {
             list($pSheetName, $pRange) = PHPExcel_Worksheet::extractSheetTitle($pRange, true);
             $pSheet = $this->_workbook->getSheetByName($pSheetName);
         }
         // Named range?
         $namedRange = PHPExcel_NamedRange::resolveRange($pRange, $pSheet);
         if ($namedRange !== null) {
             $pSheet = $namedRange->getWorksheet();
             $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];
             }
         } else {
             return PHPExcel_Calculation_Functions::REF();
         }
         // Extract range
         $aReferences = PHPExcel_Cell::extractAllCellReferencesInRange($pRange);
         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);
                 $cellValue = null;
                 if ($pSheet->cellExists($reference)) {
                     $returnValue[$currentRow][$currentCol] = $pSheet->getCell($reference)->getCalculatedValue($resetLog);
                 } else {
                     $returnValue[$currentRow][$currentCol] = null;
                 }
             }
         }
     }
     // Return
     return $returnValue;
 }