iOfficeAI/OfficeCLI · error · ArgumentException

validation list options must not contain double quotes; Exce

Error message

validation list options must not contain double quotes; Excel refuses files with quoted-literal escapes inside a list formula. Put the options in cells and reference the range instead.

What it means

Thrown by AddValidation when type is list and formula1, after stripping an outer pair of double quotes, still contains a double quote. Embedded quotes produce list literals Excel refuses to open (empirically verified), so they are rejected rather than writing a corrupt file.

Source

Thrown at src/officecli/Handlers/Excel/ExcelHandler.Add.Tables.cs:585

            if (dv.Type?.Value == DataValidationValues.List
                && string.IsNullOrWhiteSpace(dvFormula1.Trim('"')))
                throw new ArgumentException(
                    "Property 'formula1' is empty for validation type=list; supply options like formula1=\"1,2,3\" or a range reference.");
            // Excel caps data-validation formulas at 255 chars; longer ones
            // pass schema validation but the file is refused (0x800A03EC).
            if (dvFormula1.Length > 255)
                throw new ArgumentException(
                    $"validation formula1 is {dvFormula1.Length} chars; Excel's limit is 255. Put the list in a range and reference it instead.");
            // Embedded double quotes inside a literal list (not a range ref)
            // produce list literals Excel refuses to open — empirically
            // verified; reject rather than write a corrupt file.
            if (dv.Type?.Value == DataValidationValues.List)
            {
                var inner = dvFormula1.Trim();
                if (inner.StartsWith('"') && inner.EndsWith('"') && inner.Length >= 2)
                    inner = inner[1..^1];
                if (inner.Contains('"'))
                    throw new ArgumentException(
                        "validation list options must not contain double quotes; Excel refuses files with quoted-literal escapes inside a list formula. Put the options in cells and reference the range instead.");
            }
            // Non-list formulas land in the A1-only <x:formula1> element —
            // an R1C1-style ref makes real Excel refuse the file (0x800A03EC)
            // while schema validation stays green. (For type=list the text is
            // a literal option list, not a formula, so it's left alone.)
            if (dv.Type?.Value != DataValidationValues.List)
                ValidateNoR1C1Reference(dvFormula1);
            dv.Formula1 = new Formula1(NormalizeValidationFormula(dvFormula1, dv.Type?.Value));
        }
        else if (dv.Type?.Value == DataValidationValues.List)
        {
            // R28-A1 — type=list with no formula1 at all is also nonsense.
            throw new ArgumentException(
                "Property 'formula1' is required for validation type=list; supply options like formula1=\"1,2,3\" or a range reference.");
        }

        if (properties.TryGetValue("formula2", out var dvFormula2))

View on GitHub (pinned to 1ced45e900)

Solutions

1. Put the options in worksheet cells and reference the range in formula1 (cells accept quotes freely).
2. Remove the double quotes from the option labels.
3. Replace embedded quotes with another delimiter before building the literal.

Example fix

// before
handler.Add("/Sheet1", "validation", null,
    new() { ["sqref"] = "A1:A5", ["type"] = "list", ["formula1"] = "say \"hi\",bye" });
// after (options in cells, referenced by range)
handler.Add("/Sheet1", "validation", null,
    new() { ["sqref"] = "A1:A5", ["type"] = "list", ["formula1"] = "=$Z$1:$Z$2" });

if (properties.GetValueOrDefault("type").Equals("list", StringComparison.OrdinalIgnoreCase)
    && properties.TryGetValue("formula1", out var f1))
{
    var inner = f1.Trim();
    if (inner.StartsWith('"') && inner.EndsWith('"') && inner.Length >= 2) inner = inner[1..^1];
    if (inner.Contains('"')) throw new InvalidOperationException("list options contain a double quote");
}

When it happens

Trigger: Call Add type "validation" with formula1 such as "a\"b,c" or "\"say \"hi\"\"" where an inner quote survives unwrapping.

Common situations: Escaping quotes inside option labels; options that themselves contain quotation marks; double-encoding quotes from a CSV importer.

Related errors

• Invalid array constant: '{badElem}'. Inline arrays {...} may
• Literal braces '{...}' around a formula create an Excel-reje
• Property 'sqref' (or 'range'/'ref') is required for validati
• Property 'formula1' is empty for validation type=list; suppl
• validation formula1 is {dvFormula1.Length} chars; Excel's li

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