PHPOffice/PhpSpreadsheet · error · PhpOffice\PhpSpreadsheet\Exception

Invalid rule value for column AutoFilter Rule.

Error message

Invalid rule value for column AutoFilter Rule.

What it means

When a rule value is set as an array (a dateGroupItem), every key must be one of year, month, day, hour, minute, second. Invalid keys are silently stripped; if none survive, the value array is empty and the rule is rejected as invalid. Key matching is case-sensitive.

Source

Thrown at src/PhpSpreadsheet/Worksheet/AutoFilter/Column/Rule.php:274

     * @return $this
     */
    public function setValue($value): static
    {
        $this->setEvaluatedFalse();
        if (is_array($value)) {
            $grouping = -1;
            foreach ($value as $key => $v) {
                //    Validate array entries
                if (!in_array($key, self::DATE_TIME_GROUPS)) {
                    //    Remove any invalid entries from the value array
                    unset($value[$key]);
                } else {
                    //    Work out what the dateTime grouping will be
                    $grouping = max($grouping, array_search($key, self::DATE_TIME_GROUPS));
                }
            }
            if (count($value) == 0) {
                throw new PhpSpreadsheetException('Invalid rule value for column AutoFilter Rule.');
            }
            //    Set the dateTime grouping that we've anticipated
            $this->setGrouping(self::DATE_TIME_GROUPS[$grouping]); // @phpstan-ignore offsetAccess.notFound (no idea what phpstan is complaining about)
        }
        $this->value = $value;

        return $this;
    }

    /**
     * Get AutoFilter Rule Operator.
     */
    public function getOperator(): string
    {
        return $this->operator;
    }

    /**

View on GitHub (pinned to 65b080eef4)

Solutions

  1. Use the exact key set year/month/day/hour/minute/second with integer components, e.g. ['year' => 2024, 'month' => 1, 'day' => 15]
  2. Validate array_keys($value) against the allowed list before setValue()
  3. For plain value filters, pass a scalar instead of an array

Example fix

// before
$rule->setValue(['date' => '2024-01-15']);

// after
$rule->setValue(['year' => 2024, 'month' => 1, 'day' => 15]);
Defensive patterns

Strategy: validation

Validate before calling

$allowedKeys = ['year', 'month', 'day', 'hour', 'minute', 'second'];
if (is_array($value) && array_diff(array_keys($value), $allowedKeys) !== []) {
    throw new InvalidArgumentException('dateGroup keys must be year/month/day/hour/minute/second');
}
$rule->setValue($value);

Type guard

function isDateGroupValue(array $value): bool
{
    $allowed = ['year', 'month', 'day', 'hour', 'minute', 'second'];

    return array_diff(array_keys($value), $allowed) === [] && $value !== [];
}

Try / catch

try {
    $rule->setValue($value);
} catch (\PhpOffice\PhpSpreadsheet\Exception $e) {
    // not a valid dateGroup: fall back to a scalar equality filter
    $rule->setValue(implode('-', $value));
}

Prevention

When it happens

Trigger: ->setValue(['Year' => 2024]) (wrong casing), ->setValue(['date' => '2024-01-01']) or ->setValue([]) — all leave zero valid keys after stripping.

Common situations: Passing complete dates/timestamps instead of date components; wrong key casing from array keys generated by array_change_key_case-sensitive code; empty arrays from failed date parsing.

Related errors

AI-assisted analysis of PHPOffice/PhpSpreadsheet@65b080eef4 (2026-08-17). Data as JSON: /api/errors/d69b22136c0e97d4. Report an issue: GitHub.