public static function PHPExcel_Calculation_LookupRef::MATCH in Loft Data Grids 7.2

Same name and namespace in other branches

1. 6.2 vendor/phpoffice/phpexcel/Classes/PHPExcel/Calculation/LookupRef.php \PHPExcel_Calculation_LookupRef::MATCH()

* MATCH * * The MATCH function searches for a specified item in a range of cells * * Excel Function: * =MATCH(lookup_value, lookup_array, [match_type]) * *

Parameters

lookup_value The value that you want to match in lookup_array: * @param lookup_array The range of cells being searched * @param match_type The number -1, 0, or 1. -1 means above, 0 means exact match, 1 means below. If match_type is 1 or -1, the list has to be ordered. * @return integer The relative position of the found item

File

vendor/phpoffice/phpexcel/Classes/PHPExcel/Calculation/LookupRef.php, line 490

Class

PHPExcel_Calculation_LookupRef

PHPExcel_Calculation_LookupRef

Code

public static function MATCH($lookup_value, $lookup_array, $match_type = 1) {
  $lookup_array = PHPExcel_Calculation_Functions::flattenArray($lookup_array);
  $lookup_value = PHPExcel_Calculation_Functions::flattenSingleValue($lookup_value);
  $match_type = is_null($match_type) ? 1 : (int) PHPExcel_Calculation_Functions::flattenSingleValue($match_type);

  //	MATCH is not case sensitive
  $lookup_value = strtolower($lookup_value);

  //	lookup_value type has to be number, text, or logical values
  if (!is_numeric($lookup_value) && !is_string($lookup_value) && !is_bool($lookup_value)) {
    return PHPExcel_Calculation_Functions::NA();
  }

  //	match_type is 0, 1 or -1
  if ($match_type !== 0 && $match_type !== -1 && $match_type !== 1) {
    return PHPExcel_Calculation_Functions::NA();
  }

  //	lookup_array should not be empty
  $lookupArraySize = count($lookup_array);
  if ($lookupArraySize <= 0) {
    return PHPExcel_Calculation_Functions::NA();
  }

  //	lookup_array should contain only number, text, or logical values, or empty (null) cells
  foreach ($lookup_array as $i => $lookupArrayValue) {

    //	check the type of the value
    if (!is_numeric($lookupArrayValue) && !is_string($lookupArrayValue) && !is_bool($lookupArrayValue) && !is_null($lookupArrayValue)) {
      return PHPExcel_Calculation_Functions::NA();
    }

    //	convert strings to lowercase for case-insensitive testing
    if (is_string($lookupArrayValue)) {
      $lookup_array[$i] = strtolower($lookupArrayValue);
    }
    if (is_null($lookupArrayValue) && ($match_type == 1 || $match_type == -1)) {
      $lookup_array = array_slice($lookup_array, 0, $i - 1);
    }
  }

  // if match_type is 1 or -1, the list has to be ordered
  if ($match_type == 1) {
    asort($lookup_array);
    $keySet = array_keys($lookup_array);
  }
  elseif ($match_type == -1) {
    arsort($lookup_array);
    $keySet = array_keys($lookup_array);
  }

  // **
  // find the match
  // **
  // loop on the cells
  //		var_dump($lookup_array);
  //		echo '<br />';
  foreach ($lookup_array as $i => $lookupArrayValue) {
    if ($match_type == 0 && $lookupArrayValue == $lookup_value) {

      //	exact match
      return ++$i;
    }
    elseif ($match_type == -1 && $lookupArrayValue <= $lookup_value) {

      //				echo '$i = '.$i.' => ';
      //				var_dump($lookupArrayValue);
      //				echo '<br />';
      //				echo 'Keyset = ';
      //				var_dump($keySet);
      //				echo '<br />';
      $i = array_search($i, $keySet);

      //				echo '$i='.$i.'<br />';
      // if match_type is -1 <=> find the smallest value that is greater than or equal to lookup_value
      if ($i < 1) {

        // 1st cell was allready smaller than the lookup_value
        break;
      }
      else {

        // the previous cell was the match
        return $keySet[$i - 1] + 1;
      }
    }
    elseif ($match_type == 1 && $lookupArrayValue >= $lookup_value) {

      //				echo '$i = '.$i.' => ';
      //				var_dump($lookupArrayValue);
      //				echo '<br />';
      //				echo 'Keyset = ';
      //				var_dump($keySet);
      //				echo '<br />';
      $i = array_search($i, $keySet);

      //				echo '$i='.$i.'<br />';
      // if match_type is 1 <=> find the largest value that is less than or equal to lookup_value
      if ($i < 1) {

        // 1st cell was allready bigger than the lookup_value
        break;
      }
      else {

        // the previous cell was the match
        return $keySet[$i - 1] + 1;
      }
    }
  }

  //	unsuccessful in finding a match, return #N/A error value
  return PHPExcel_Calculation_Functions::NA();
}