Skip to content
  • P
    Projects
  • G
    Groups
  • S
    Snippets
  • Help

semour / semour_admin

  • This project
    • Loading...
  • Sign in
Go to a project
  • Project
  • Repository
  • Issues 0
  • Merge Requests 0
  • Pipelines
  • Wiki
  • Snippets
  • Settings
  • Activity
  • Graph
  • Charts
  • Create a new issue
  • Jobs
  • Commits
  • Issue Boards
  • Files
  • Commits
  • Branches
  • Tags
  • Contributors
  • Graph
  • Compare
  • Charts
Find file
Normal viewHistoryPermalink
Switch branch/tag
  • semour_admin
  • ..
  • LookupRef
  • ExcelMatch.php
ExcelMatch.php 7.33 KB
孙龙's avatar
订单
f785d072
 
孙龙 committed 2 years ago
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203
<?php

namespace PhpOffice\PhpSpreadsheet\Calculation\LookupRef;

use PhpOffice\PhpSpreadsheet\Calculation\ArrayEnabled;
use PhpOffice\PhpSpreadsheet\Calculation\Exception;
use PhpOffice\PhpSpreadsheet\Calculation\Functions;
use PhpOffice\PhpSpreadsheet\Calculation\Information\ExcelError;
use PhpOffice\PhpSpreadsheet\Calculation\Internal\WildcardMatch;
use PhpOffice\PhpSpreadsheet\Shared\StringHelper;

class ExcelMatch
{
    use ArrayEnabled;

    public const MATCHTYPE_SMALLEST_VALUE = -1;
    public const MATCHTYPE_FIRST_VALUE = 0;
    public const MATCHTYPE_LARGEST_VALUE = 1;

    /**
     * MATCH.
     *
     * The MATCH function searches for a specified item in a range of cells
     *
     * Excel Function:
     *        =MATCH(lookup_value, lookup_array, [match_type])
     *
     * @param mixed $lookupValue The value that you want to match in lookup_array
     * @param mixed $lookupArray The range of cells being searched
     * @param mixed $matchType 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 array|int|string The relative position of the found item
     */
    public static function MATCH($lookupValue, $lookupArray, $matchType = self::MATCHTYPE_LARGEST_VALUE)
    {
        if (is_array($lookupValue)) {
            return self::evaluateArrayArgumentsIgnore([self::class, __FUNCTION__], 1, $lookupValue, $lookupArray, $matchType);
        }

        $lookupArray = Functions::flattenArray($lookupArray);
        $matchType = (int) ($matchType ?? self::MATCHTYPE_LARGEST_VALUE);

        try {
            // Input validation
            self::validateLookupValue($lookupValue);
            self::validateMatchType($matchType);
            self::validateLookupArray($lookupArray);

            $keySet = array_keys($lookupArray);
            if ($matchType == self::MATCHTYPE_LARGEST_VALUE) {
                // If match_type is 1 the list has to be processed from last to first
                $lookupArray = array_reverse($lookupArray);
                $keySet = array_reverse($keySet);
            }

            $lookupArray = self::prepareLookupArray($lookupArray, $matchType);
        } catch (Exception $e) {
            return $e->getMessage();
        }

        // MATCH() is not case sensitive, so we convert lookup value to be lower cased if it's a string type.
        if (is_string($lookupValue)) {
            $lookupValue = StringHelper::strToLower($lookupValue);
        }

        $valueKey = null;
        switch ($matchType) {
            case self::MATCHTYPE_LARGEST_VALUE:
                $valueKey = self::matchLargestValue($lookupArray, $lookupValue, $keySet);

                break;
            case self::MATCHTYPE_FIRST_VALUE:
                $valueKey = self::matchFirstValue($lookupArray, $lookupValue);

                break;
            case self::MATCHTYPE_SMALLEST_VALUE:
            default:
                $valueKey = self::matchSmallestValue($lookupArray, $lookupValue);
        }

        if ($valueKey !== null) {
            return ++$valueKey;
        }

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

    private static function matchFirstValue($lookupArray, $lookupValue)
    {
        $wildcardLookup = ((bool) preg_match('/([\?\*])/', $lookupValue));
        $wildcard = WildcardMatch::wildcard($lookupValue);

        foreach ($lookupArray as $i => $lookupArrayValue) {
            $typeMatch = ((gettype($lookupValue) === gettype($lookupArrayValue)) ||
                (is_numeric($lookupValue) && is_numeric($lookupArrayValue)));

            if (
                $typeMatch && is_string($lookupValue) &&
                $wildcardLookup && WildcardMatch::compare($lookupArrayValue, $wildcard)
            ) {
                // wildcard match
                return $i;
            } elseif ($lookupArrayValue === $lookupValue) {
                // exact match
                return $i;
            }
        }

        return null;
    }

    private static function matchLargestValue($lookupArray, $lookupValue, $keySet)
    {
        foreach ($lookupArray as $i => $lookupArrayValue) {
            $typeMatch = ((gettype($lookupValue) === gettype($lookupArrayValue)) ||
                (is_numeric($lookupValue) && is_numeric($lookupArrayValue)));

            if ($typeMatch && ($lookupArrayValue <= $lookupValue)) {
                return array_search($i, $keySet);
            }
        }

        return null;
    }

    private static function matchSmallestValue($lookupArray, $lookupValue)
    {
        $valueKey = null;

        // The basic algorithm is:
        // Iterate and keep the highest match until the next element is smaller than the searched value.
        // Return immediately if perfect match is found
        foreach ($lookupArray as $i => $lookupArrayValue) {
            $typeMatch = gettype($lookupValue) === gettype($lookupArrayValue);

            if ($lookupArrayValue === $lookupValue) {
                // Another "special" case. If a perfect match is found,
                // the algorithm gives up immediately
                return $i;
            } elseif ($typeMatch && $lookupArrayValue >= $lookupValue) {
                $valueKey = $i;
            } elseif ($typeMatch && $lookupArrayValue < $lookupValue) {
                //Excel algorithm gives up immediately if the first element is smaller than the searched value
                break;
            }
        }

        return $valueKey;
    }

    private static function validateLookupValue($lookupValue): void
    {
        // Lookup_value type has to be number, text, or logical values
        if ((!is_numeric($lookupValue)) && (!is_string($lookupValue)) && (!is_bool($lookupValue))) {
            throw new Exception(ExcelError::NA());
        }
    }

    private static function validateMatchType($matchType): void
    {
        // Match_type is 0, 1 or -1
        if (
            ($matchType !== self::MATCHTYPE_FIRST_VALUE) &&
            ($matchType !== self::MATCHTYPE_LARGEST_VALUE) && ($matchType !== self::MATCHTYPE_SMALLEST_VALUE)
        ) {
            throw new Exception(ExcelError::NA());
        }
    }

    private static function validateLookupArray($lookupArray): void
    {
        // Lookup_array should not be empty
        $lookupArraySize = count($lookupArray);
        if ($lookupArraySize <= 0) {
            throw new Exception(ExcelError::NA());
        }
    }

    private static function prepareLookupArray($lookupArray, $matchType)
    {
        // Lookup_array should contain only number, text, or logical values, or empty (null) cells
        foreach ($lookupArray as $i => $value) {
            //    check the type of the value
            if ((!is_numeric($value)) && (!is_string($value)) && (!is_bool($value)) && ($value !== null)) {
                throw new Exception(ExcelError::NA());
            }
            // Convert strings to lowercase for case-insensitive testing
            if (is_string($value)) {
                $lookupArray[$i] = StringHelper::strToLower($value);
            }
            if (
                ($value === null) &&
                (($matchType == self::MATCHTYPE_LARGEST_VALUE) || ($matchType == self::MATCHTYPE_SMALLEST_VALUE))
            ) {
                unset($lookupArray[$i]);
            }
        }

        return $lookupArray;
    }
}