iOfficeAI/OfficeCLI · error · ArgumentException

validation formula '{value}' is not valid for this validatio

Error message

validation formula '{value}' is not valid for this validation type: expected a number, date, cell reference, or formula (a bare value with spaces is not valid formula syntax).

What it means

For data-validation types other than 'list' and 'custom', formula1/formula2 must be a number, date/time, cell/range reference, or formula. A bare value containing whitespace (e.g. 'hello world') is not valid OOXML formula syntax and makes real Excel refuse the file with 0x800A03EC. The library rejects such values so the saved workbook stays openable.

Source

Thrown at src/officecli/Handlers/Excel/ExcelHandler.Helpers.Validation.cs:440

                var epoch = new System.DateTime(1899, 12, 30);
                return ((int)(dt - epoch).TotalDays).ToString(System.Globalization.CultureInfo.InvariantCulture);
            }
        }
        if (type == DataValidationValues.Custom)
        {
            if (value.StartsWith("="))
                return value.Substring(1);
        }
        // For non-list numeric/date/text types, formula1/formula2 must be a
        // number, date/time (handled above), cell/range ref, or a formula — a
        // bare value containing whitespace (e.g. "hello world") is invalid
        // OOXML formula syntax and makes real Excel refuse the file
        // (0x800A03EC). Reject it up front. (Quoted literals and refs pass.)
        if (type != DataValidationValues.Custom
            && value.Any(char.IsWhiteSpace)
            && !value.StartsWith("\"") && !value.StartsWith("=")
            && !value.Contains('!') && !value.Contains('('))
            throw new ArgumentException(
                $"validation formula '{value}' is not valid for this validation type: " +
                "expected a number, date, cell reference, or formula (a bare value with spaces is not valid formula syntax).");
        return value;
    }

    // CONSISTENCY(merge-overlap): centralize the "insert one MergeCell"
    // policy. Excel rejects overlapping <mergeCell> entries with a
    // "found a problem" repair dialog, but the OOXML SDK happily
    // appends them. Mirrors the T4 overlap-throws pattern used by
    // tables and AutoFilter+table.
    // - Exact-match ref: no-op (idempotent re-Add stays consistent
    //   with prior dedup behavior).
    // - Geometric overlap with a non-identical range: throw.
    // - Otherwise: append.
    private static readonly System.Text.RegularExpressions.Regex SingleMergeRefPattern =
        new(@"^[A-Z]+[0-9]+(:[A-Z]+[0-9]+)?$",
            System.Text.RegularExpressions.RegexOptions.Compiled);

View on GitHub (pinned to 1ced45e900)

Solutions

  1. Switch the validation type to 'list' and let the list normalizer quote the values.
  2. Prefix the value with '=' so it is treated as a formula body.
  3. Remove the whitespace so the value is a single number/date/ref token.
  4. Quote the literal with double-quotes if it is a string.

Example fix

// before
sheet.AddDataValidation("A1:A10", type: DataValidationValues.Text, formula1: "hello world");

// after (option A: list type)
sheet.AddDataValidation("A1:A10", type: DataValidationValues.List, formula1: "hello world");
// after (option B: formula)
sheet.AddDataValidation("A1:A10", type: DataValidationValues.Text, formula1: "=LEN(A1)>5");
Defensive patterns

Strategy: validation

Validate before calling

static string NormalizeNonListValidationFormula(string value, DataValidationValues type) {
    if (type == DataValidationValues.Custom || type == DataValidationValues.List) return value;
    if (!value.Any(char.IsWhiteSpace)) return value;
    if (value.StartsWith("\"") || value.StartsWith("=") || value.Contains('!') || value.Contains('(')) return value;
    return "=" + value;   // promote to a formula body
}

Try / catch

try { sheet.AddDataValidation(range, type, formula1: v); }
catch (ArgumentException ex) when (ex.Message.Contains("not valid for this validation type")) {
    sheet.AddDataValidation(range, DataValidationValues.List, formula1: v);
}

Prevention

When it happens

Trigger: Calling a data-validation setter on a non-custom, non-list type with a value that: contains whitespace AND does not start with '"' or '=' AND does not contain '!' or '('. All four conditions must hold to trip the guard.

Common situations: Passing a list of allowed values ('red, blue') to a numeric/text validation type instead of type=list; forgetting to prefix a formula with '='; quoting mistakes on string literals.

Related errors

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