PHP PHPExcel_Calculation::_wrapResult方法代码示例

 /**
  *	MINIF
  *
  *	Returns the minimum value within a range of cells that contain numbers within the list of arguments
  *
  *	Excel Function:
  *		MINIF(value1[,value2[, ...]],condition)
  *
  *	@access	public
  *	@category Mathematical and Trigonometric Functions
  *	@param	mixed		$arg,...		Data values
  *	@param	string		$condition		The criteria that defines which cells will be checked.
  *	@return	float
  */
 public static function MINIF($aArgs, $condition, $sumArgs = array())
 {
     // Return value
     $returnValue = null;
     $aArgs = PHPExcel_Calculation_Functions::flattenArray($aArgs);
     $sumArgs = PHPExcel_Calculation_Functions::flattenArray($sumArgs);
     if (count($sumArgs) == 0) {
         $sumArgs = $aArgs;
     }
     $condition = PHPExcel_Calculation_Functions::_ifCondition($condition);
     // Loop through arguments
     foreach ($aArgs as $key => $arg) {
         if (!is_numeric($arg)) {
             $arg = PHPExcel_Calculation::_wrapResult(strtoupper($arg));
         }
         $testCondition = '=' . $arg . $condition;
         if (PHPExcel_Calculation::getInstance()->_calculateFormulaValue($testCondition)) {
             if (is_null($returnValue) || $arg < $returnValue) {
                 $returnValue = $arg;
             }
         }
     }
     // Return
     return $returnValue;
 }

 /**
  *	SUMIF
  *
  *	Counts the number of cells that contain numbers within the list of arguments
  *
  *	Excel Function:
  *		SUMIF(value1[,value2[, ...]],condition)
  *
  *	@access	public
  *	@category Mathematical and Trigonometric Functions
  *	@param	mixed		$arg,...		Data values
  *	@param	string		$condition		The criteria that defines which cells will be summed.
  *	@return	float
  */
 public static function SUMIF($aArgs, $condition, $sumArgs = array())
 {
     // Return value
     $returnValue = 0;
     $aArgs = self::flattenArray($aArgs);
     $sumArgs = self::flattenArray($sumArgs);
     if (count($sumArgs) == 0) {
         $sumArgs = $aArgs;
     }
     if (!in_array($condition[0], array('>', '<', '='))) {
         if (!is_numeric($condition)) {
             $condition = PHPExcel_Calculation::_wrapResult(strtoupper($condition));
         }
         $condition = '=' . $condition;
     }
     // Loop through arguments
     foreach ($aArgs as $key => $arg) {
         if (!is_numeric($arg)) {
             $arg = PHPExcel_Calculation::_wrapResult(strtoupper($arg));
         }
         $testCondition = '=' . $arg . $condition;
         if (PHPExcel_Calculation::getInstance()->_calculateFormulaValue($testCondition)) {
             // Is it a value within our criteria
             $returnValue += $sumArgs[$key];
         }
     }
     // Return
     return $returnValue;
 }

 /**
  * __filter
  *
  * Parses the selection criteria, extracts the database rows that match those criteria, and
  * returns that subset of rows.
  *
  * @access    private
  * @param    mixed[] $database The range of cells that makes up the list or database.
  *                                        A database is a list of related data in which rows of related
  *                                        information are records, and columns of data are fields. The
  *                                        first row of the list contains labels for each column.
  * @param    mixed[] $criteria The range of cells that contains the conditions you specify.
  *                                        You can use any range for the criteria argument, as long as it
  *                                        includes at least one column label and at least one cell below
  *                                        the column label in which you specify a condition for the
  *                                        column.
  * @return    array of mixed
  *
  */
 private static function __filter($database, $criteria)
 {
     $fieldNames = array_shift($database);
     $criteriaNames = array_shift($criteria);
     //	Convert the criteria into a set of AND/OR conditions with [:placeholders]
     $testConditions = $testValues = array();
     $testConditionsCount = 0;
     foreach ($criteriaNames as $key => $criteriaName) {
         $testCondition = array();
         $testConditionCount = 0;
         foreach ($criteria as $row => $criterion) {
             if ($criterion[$key] > '') {
                 $testCondition[] = '[:' . $criteriaName . ']' . PHPExcel_Calculation_Functions::_ifCondition($criterion[$key]);
                 $testConditionCount++;
             }
         }
         if ($testConditionCount > 1) {
             $testConditions[] = 'OR(' . implode(',', $testCondition) . ')';
             $testConditionsCount++;
         } elseif ($testConditionCount == 1) {
             $testConditions[] = $testCondition[0];
             $testConditionsCount++;
         }
     }
     if ($testConditionsCount > 1) {
         $testConditionSet = 'AND(' . implode(',', $testConditions) . ')';
     } elseif ($testConditionsCount == 1) {
         $testConditionSet = $testConditions[0];
     }
     //	Loop through each row of the database
     foreach ($database as $dataRow => $dataValues) {
         //	Substitute actual values from the database row for our [:placeholders]
         $testConditionList = $testConditionSet;
         foreach ($criteriaNames as $key => $criteriaName) {
             $k = array_search($criteriaName, $fieldNames);
             if (isset($dataValues[$k])) {
                 $dataValue = $dataValues[$k];
                 $dataValue = is_string($dataValue) ? PHPExcel_Calculation::_wrapResult(strtoupper($dataValue)) : $dataValue;
                 $testConditionList = str_replace('[:' . $criteriaName . ']', $dataValue, $testConditionList);
             }
         }
         //	evaluate the criteria against the row data
         $result = PHPExcel_Calculation::getInstance()->_calculateFormulaValue('=' . $testConditionList);
         //	If the row failed to meet the criteria, remove it from the database
         if (!$result) {
             unset($database[$dataRow]);
         }
     }
     return $database;
 }

 /**
  *	AVERAGEIF
  *
  *	Returns the average value from a range of cells that contain numbers within the list of arguments
  *
  *	Excel Function:
  *		AVERAGEIF(value1[,value2[, ...]],condition)
  *
  *	@access	public
  *	@category Mathematical and Trigonometric Functions
  *	@param	mixed		$arg,...		Data values
  *	@param	string		$condition		The criteria that defines which cells will be checked.
  *	@return	float
  */
 public static function AVERAGEIF($aArgs, $condition, $averageArgs = array())
 {
     // Return value
     $returnValue = 0;
     $aArgs = self::flattenArray($aArgs);
     $averageArgs = self::flattenArray($averageArgs);
     if (count($averageArgs) == 0) {
         $averageArgs = $aArgs;
     }
     $condition = self::_ifCondition($condition);
     // Loop through arguments
     $aCount = 0;
     foreach ($aArgs as $key => $arg) {
         if (!is_numeric($arg)) {
             $arg = PHPExcel_Calculation::_wrapResult(strtoupper($arg));
         }
         $testCondition = '=' . $arg . $condition;
         if (PHPExcel_Calculation::getInstance()->_calculateFormulaValue($testCondition)) {
             if (is_null($returnValue) || $arg > $returnValue) {
                 $returnValue += $arg;
                 ++$aCount;
             }
         }
     }
     // Return
     if ($aCount > 0) {
         return $returnValue / $aCount;
     } else {
         return self::$_errorCodes['divisionbyzero'];
     }
 }

 public static function _ifCondition($condition)
 {
     $condition = PHPExcel_Calculation_Functions::flattenSingleValue($condition);
     if (!in_array($condition[0], array('>', '<', '='))) {
         if (!is_numeric($condition)) {
             $condition = PHPExcel_Calculation::_wrapResult(strtoupper($condition));
         }
         return '=' . $condition;
     } else {
         preg_match('/([<>=]+)(.*)/', $condition, $matches);
         list(, $operator, $operand) = $matches;
         if (!is_numeric($operand)) {
             $operand = PHPExcel_Calculation::_wrapResult(strtoupper($operand));
         }
         return $operator . $operand;
     }
 }

 /**
  * SUMIF
  *
  * Counts the number of cells that contain numbers within the list of arguments
  *
  * Excel Function:
  *		SUMIF(value1[,value2[, ...]],condition)
  *
  * @access	public
  * @category Mathematical and Trigonometric Functions
  * @param	mixed		$arg,...		Data values
  * @param	string		$condition		The criteria that defines which cells will be summed.
  * @return	float
  */
 public static function SUMIF($aArgs, $condition, $sumArgs = array())
 {
     // Return value
     $returnValue = 0;
     $aArgs = PHPExcel_Calculation_Functions::flattenArray($aArgs);
     $sumArgs = PHPExcel_Calculation_Functions::flattenArray($sumArgs);
     if (empty($sumArgs)) {
         $sumArgs = $aArgs;
     }
     $condition = PHPExcel_Calculation_Functions::_ifCondition($condition);
     // Loop through arguments
     foreach ($aArgs as $key => $arg) {
         if (!is_numeric($arg)) {
             $arg = str_replace('"', '""', $arg);
             $arg = PHPExcel_Calculation::_wrapResult(strtoupper($arg));
         }
         $testCondition = '=' . $arg . $condition;
         if (PHPExcel_Calculation::getInstance()->_calculateFormulaValue($testCondition)) {
             // Is it a value within our criteria
             $returnValue += $sumArgs[$key];
         }
     }
     // Return
     return $returnValue;
 }