[Summary view]

Differences Between: [Versions 400 and 401] [Versions 400 and 402] [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\Statistical;
   8  
   9  class Subtotal
  10  {
  11      /**
  12       * @param mixed $cellReference
  13       * @param mixed $args
  14       */
  15      protected static function filterHiddenArgs($cellReference, $args): array
  16      {
  17          return array_filter(
  18              $args,
  19              function ($index) use ($cellReference) {
  20                  [, $row, ] = explode('.', $index);
  21  
  22                  return $cellReference->getWorksheet()->getRowDimension($row)->getVisible();
  23              },
  24              ARRAY_FILTER_USE_KEY
  25          );
  26      }
  27  
  28      /**
  29       * @param mixed $cellReference
  30       * @param mixed $args
  31       */
  32      protected static function filterFormulaArgs($cellReference, $args): array
  33      {
  34          return array_filter(
  35              $args,
  36              function ($index) use ($cellReference) {
  37                  [, $row, $column] = explode('.', $index);
  38                  $retVal = true;
  39                  if ($cellReference->getWorksheet()->cellExists($column . $row)) {
  40                      //take this cell out if it contains the SUBTOTAL or AGGREGATE functions in a formula
  41                      $isFormula = $cellReference->getWorksheet()->getCell($column . $row)->isFormula();
  42                      $cellFormula = !preg_match('/^=.*\b(SUBTOTAL|AGGREGATE)\s*\(/i', $cellReference->getWorksheet()->getCell($column . $row)->getValue());
  43  
  44                      $retVal = !$isFormula || $cellFormula;
  45                  }
  46  
  47                  return $retVal;
  48              },
  49              ARRAY_FILTER_USE_KEY
  50          );
  51      }
  52  
  53      /** @var callable[] */
  54      private const CALL_FUNCTIONS = [
  55          1 => [Statistical\Averages::class, 'average'],
  56          [Statistical\Counts::class, 'COUNT'], // 2
  57          [Statistical\Counts::class, 'COUNTA'], // 3
  58          [Statistical\Maximum::class, 'max'], // 4
  59          [Statistical\Minimum::class, 'min'], // 5
  60          [Operations::class, 'product'], // 6
  61          [Statistical\StandardDeviations::class, 'STDEV'], // 7
  62          [Statistical\StandardDeviations::class, 'STDEVP'], // 8
  63          [Sum::class, 'sumIgnoringStrings'], // 9
  64          [Statistical\Variances::class, 'VAR'], // 10
  65          [Statistical\Variances::class, 'VARP'], // 11
  66      ];
  67  
  68      /**
  69       * SUBTOTAL.
  70       *
  71       * Returns a subtotal in a list or database.
  72       *
  73       * @param mixed $functionType
  74       *            A number 1 to 11 that specifies which function to
  75       *                    use in calculating subtotals within a range
  76       *                    list
  77       *            Numbers 101 to 111 shadow the functions of 1 to 11
  78       *                    but ignore any values in the range that are
  79       *                    in hidden rows
  80       * @param mixed[] $args A mixed data series of values
  81       *
  82       * @return float|string
  83       */
  84      public static function evaluate($functionType, ...$args)
  85      {
  86          $cellReference = array_pop($args);
  87          $aArgs = Functions::flattenArrayIndexed($args);
  88  
  89          try {
  90              $subtotal = (int) Helpers::validateNumericNullBool($functionType);
  91          } catch (Exception $e) {
  92              return $e->getMessage();
  93          }
  94  
  95          // Calculate
  96          if ($subtotal > 100) {
  97              $aArgs = self::filterHiddenArgs($cellReference, $aArgs);
  98              $subtotal -= 100;
  99          }
 100  
 101          $aArgs = self::filterFormulaArgs($cellReference, $aArgs);
 102          if (array_key_exists($subtotal, self::CALL_FUNCTIONS)) {
 103              /** @var callable */
 104              $call = self::CALL_FUNCTIONS[$subtotal];
 105  
 106              return call_user_func_array($call, $aArgs);
 107          }
 108  
 109          return Functions::VALUE();
 110      }
 111  }