iOfficeAI/OfficeCLI · error · ArgumentException

Invalid subtotals '{s}'. Valid: on, off (default on)

Error message

Invalid subtotals '{s}'. Valid: on, off (default on)

What it means

Thrown by PushSubtotalsOptions when the 'subtotals' (or 'Subtotals') property value, after trimming and lowercasing, does not match any accepted token. Accepted on-values: on, true, 1, yes, show. Accepted off-values: off, false, 0, no, hide, none. Previously (pre-R35-2) unknown values silently fell through to the default 'on', masking typos; now they are rejected explicitly.

Source

Thrown at src/officecli/Core/PivotTableHelper.cs:544

    /// </summary>
    private static IDisposable PushSubtotalsOptions(Dictionary<string, string> properties)
    {
        var prev = _defaultSubtotal;

        if (properties.TryGetValue("subtotals", out var s)
            || properties.TryGetValue("Subtotals", out s))
        {
            switch ((s ?? "").Trim().ToLowerInvariant())
            {
                case "on": case "true": case "1": case "yes": case "show":
                    _defaultSubtotal = true; break;
                case "off": case "false": case "0": case "no": case "hide": case "none":
                    _defaultSubtotal = false; break;
                // R35-2: previously unknown values silently fell through to the
                // default ("on"). Reject explicitly so typos like
                // "subtotals=auto" surface as errors instead of being misread.
                default:
                    throw new ArgumentException(
                        $"Invalid subtotals '{s}'. Valid: on, off (default on)");
            }
        }

        if (TryParseBoolProp(properties, "defaultSubtotal", out var ds))
            _defaultSubtotal = ds;

        return new SubtotalsScope(prev);
    }

    private sealed class SubtotalsScope : IDisposable
    {
        private readonly bool? _prev;
        public SubtotalsScope(bool? prev) { _prev = prev; }
        public void Dispose() { _defaultSubtotal = _prev; }
    }

    // ==================== Layout mode options ====================

View on GitHub (pinned to 1ced45e900)

Solutions

1. Use 'on' or 'off' (or their synonyms: true/false, 1/0, yes/no, show/hide, none for off).
2. If unsure, omit the subtotals property to accept the default (on).
3. Check the exact spelling — it is case-insensitive but must match a known token.

Example fix

// before
Set pivot subtotals='auto'
// after
Set pivot subtotals='on'

var validOn = new HashSet<string>(StringComparer.OrdinalIgnoreCase) { "on", "true", "1", "yes", "show" };
var validOff = new HashSet<string>(StringComparer.OrdinalIgnoreCase) { "off", "false", "0", "no", "hide", "none" };
var v = subtotalsValue.Trim().ToLowerInvariant();
if (!validOn.Contains(v) && !validOff.Contains(v))
    throw new ArgumentException($"Invalid subtotals '{subtotalsValue}'. Valid: on, off.");

static readonly HashSet<string> SubtotalTokens =
    new(StringComparer.OrdinalIgnoreCase)
    { "on","true","1","yes","show","off","false","0","no","hide","none" };
static bool IsValidSubtotals(string val) =>
    string.IsNullOrWhiteSpace(val) || SubtotalTokens.Contains(val.Trim().ToLowerInvariant());

When it happens

Trigger: Calling Add/Set pivot subtotals='auto' (not recognized); subtotals='enable'; subtotals='default'; or any value not in the accepted on/off token lists.

Common situations: Typo like subtotals='auto' instead of 'on'/'off'; using a synonym not in the accepted list (e.g. 'enable'/'disable'); assuming 'auto' is supported because Excel's UI uses that term.

Related errors

• invalid sort: '{mode}'. Valid: asc, desc, locale, locale-des
• invalid layout: '{mode}'. Valid: compact, outline, tabular
• labelFilter type must be one of contains/doesNotContain/begi
• pivot name must not be empty
• labelFilter must be 'field:type:value', got: '{spec}'

AI-assisted analysis of iOfficeAI/OfficeCLI@1ced45e900 (2026-08-13). Data as JSON: /api/errors/3cff227466285260. Report an issue: GitHub.