[Summary view]

Differences Between: [Versions 400 and 403]

   1  <?php
   2  
   3  namespace PhpOffice\PhpSpreadsheet\Calculation\MathTrig;
   4  
   5  use PhpOffice\PhpSpreadsheet\Calculation\Exception;
   6  use PhpOffice\PhpSpreadsheet\Calculation\Functions;
   7  use PhpOffice\PhpSpreadsheet\Calculation\Information\ExcelError;
   8  use PhpOffice\PhpSpreadsheet\Calculation\Statistical;
   9  
  10  class Subtotal
  11  {
  12      /**
  13       * @param mixed $cellReference
  14       * @param mixed $args
  15       */
  16      protected static function filterHiddenArgs($cellReference, $args): array
  17      {
  18          return array_filter(
  19              $args,
  20              function ($index) use ($cellReference) {
  21                  $explodeArray = explode('.', $index);
  22                  $row = $explodeArray[1] ?? '';
  23                  if (!is_numeric($row)) {
  24                      return true;
  25                  }
  26  
  27                  return $cellReference->getWorksheet()->getRowDimension($row)->getVisible();
  28              },
  29              ARRAY_FILTER_USE_KEY
  30          );
  31      }
  32  
  33      /**
  34       * @param mixed $cellReference
  35       * @param mixed $args
  36       */
  37      protected static function filterFormulaArgs($cellReference, $args): array
  38      {
  39          return array_filter(
  40              $args,
  41              function ($index) use ($cellReference) {
  42                  $explodeArray = explode('.', $index);
  43                  $row = $explodeArray[1] ?? '';
  44                  $column = $explodeArray[2] ?? '';
  45                  $retVal = true;
  46                  if ($cellReference->getWorksheet()->cellExists($column . $row)) {
  47                      //take this cell out if it contains the SUBTOTAL or AGGREGATE functions in a formula
  48                      $isFormula = $cellReference->getWorksheet()->getCell($column . $row)->isFormula();
  49                      $cellFormula = !preg_match(
  50                          '/^=.*\b(SUBTOTAL|AGGREGATE)\s*\(/i',
  51                          $cellReference->getWorksheet()->getCell($column . $row)->getValue() ?? ''
  52                      );
  53  
  54                      $retVal = !$isFormula || $cellFormula;
  55                  }
  56  
  57                  return $retVal;
  58              },
  59              ARRAY_FILTER_USE_KEY
  60          );
  61      }
  62  
  63      private const CALL_FUNCTIONS = [
  64          1 => [Statistical\Averages::class, 'average'], // 1 and 101
  65          [Statistical\Counts::class, 'COUNT'], // 2 and 102
  66          [Statistical\Counts::class, 'COUNTA'], // 3 and 103
  67          [Statistical\Maximum::class, 'max'], // 4 and 104
  68          [Statistical\Minimum::class, 'min'], // 5 and 105
  69          [Operations::class, 'product'], // 6 and 106
  70          [Statistical\StandardDeviations::class, 'STDEV'], // 7 and 107
  71          [Statistical\StandardDeviations::class, 'STDEVP'], // 8 and 108
  72          [Sum::class, 'sumIgnoringStrings'], // 9 and 109
  73          [Statistical\Variances::class, 'VAR'], // 10 and 110
  74          [Statistical\Variances::class, 'VARP'], // 111 and 111
  75      ];
  76  
  77      /**
  78       * SUBTOTAL.
  79       *
  80       * Returns a subtotal in a list or database.
  81       *
  82       * @param mixed $functionType
  83       *            A number 1 to 11 that specifies which function to
  84       *                    use in calculating subtotals within a range
  85       *                    list
  86       *            Numbers 101 to 111 shadow the functions of 1 to 11
  87       *                    but ignore any values in the range that are
  88       *                    in hidden rows
  89       * @param mixed[] $args A mixed data series of values
  90       *
  91       * @return float|string
  92       */
  93      public static function evaluate($functionType, ...$args)
  94      {
  95          $cellReference = array_pop($args);
  96          $bArgs = Functions::flattenArrayIndexed($args);
  97          $aArgs = [];
  98          // int keys must come before string keys for PHP 8.0+
  99          // Otherwise, PHP thinks positional args follow keyword
 100          //    in the subsequent call to call_user_func_array.
 101          // Fortunately, order of args is unimportant to Subtotal.
 102          foreach ($bArgs as $key => $value) {
 103              if (is_int($key)) {
 104                  $aArgs[$key] = $value;
 105              }
 106          }
 107          foreach ($bArgs as $key => $value) {
 108              if (!is_int($key)) {
 109                  $aArgs[$key] = $value;
 110              }
 111          }
 112  
 113          try {
 114              $subtotal = (int) Helpers::validateNumericNullBool($functionType);
 115          } catch (Exception $e) {
 116              return $e->getMessage();
 117          }
 118  
 119          // Calculate
 120          if ($subtotal > 100) {
 121              $aArgs = self::filterHiddenArgs($cellReference, $aArgs);
 122              $subtotal -= 100;
 123          }
 124  
 125          $aArgs = self::filterFormulaArgs($cellReference, $aArgs);
 126          if (array_key_exists($subtotal, self::CALL_FUNCTIONS)) {
 127              /** @var callable */
 128              $call = self::CALL_FUNCTIONS[$subtotal];
 129  
 130              return call_user_func_array($call, $aArgs);
 131          }
 132  
 133          return ExcelError::VALUE();
 134      }
 135  }