{"record":{"id":"d277df79e25d3e0d","repo":"iOfficeAI/OfficeCLI","slug":"property-formula1-is-empty-for-validation-type-l","errorCode":null,"errorMessage":"Property 'formula1' is empty for validation type=list; supply options like formula1=\"1,2,3\" or a range reference.","messagePattern":"Property 'formula1' is empty for validation type=list; supply options like formula1=\"1,2,3\" or a range reference\\.","errorType":"validation","errorClass":"ArgumentException","httpStatus":null,"severity":"error","filePath":"src/officecli/Handlers/Excel/ExcelHandler.Add.Tables.cs","lineNumber":569,"sourceCode":"                \"notbetween\" => DataValidationOperatorValues.NotBetween,\n                \"equal\" => DataValidationOperatorValues.Equal,\n                \"notequal\" => DataValidationOperatorValues.NotEqual,\n                \"greaterthan\" => DataValidationOperatorValues.GreaterThan,\n                \"lessthan\" => DataValidationOperatorValues.LessThan,\n                \"greaterthanorequal\" => DataValidationOperatorValues.GreaterThanOrEqual,\n                \"lessthanorequal\" => DataValidationOperatorValues.LessThanOrEqual,\n                _ => throw new ArgumentException($\"Unknown operator: {dvOp}\")\n            };\n        }\n\n        if (properties.TryGetValue(\"formula1\", out var dvFormula1))\n        {\n            // R28-A1 — reject empty formula1 for type=list. Excel renders an empty\n            // dropdown (or rejects the file outright depending on form), and the\n            // user almost certainly meant to provide options like \"1,2,3\".\n            if (dv.Type?.Value == DataValidationValues.List\n                && string.IsNullOrWhiteSpace(dvFormula1.Trim('\"')))\n                throw new ArgumentException(\n                    \"Property 'formula1' is empty for validation type=list; supply options like formula1=\\\"1,2,3\\\" or a range reference.\");\n            // Excel caps data-validation formulas at 255 chars; longer ones\n            // pass schema validation but the file is refused (0x800A03EC).\n            if (dvFormula1.Length > 255)\n                throw new ArgumentException(\n                    $\"validation formula1 is {dvFormula1.Length} chars; Excel's limit is 255. Put the list in a range and reference it instead.\");\n            // Embedded double quotes inside a literal list (not a range ref)\n            // produce list literals Excel refuses to open — empirically\n            // verified; reject rather than write a corrupt file.\n            if (dv.Type?.Value == DataValidationValues.List)\n            {\n                var inner = dvFormula1.Trim();\n                if (inner.StartsWith('\"') && inner.EndsWith('\"') && inner.Length >= 2)\n                    inner = inner[1..^1];\n                if (inner.Contains('\"'))\n                    throw new ArgumentException(\n                        \"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.\");\n            }","sourceCodeStart":551,"sourceCodeEnd":587,"githubUrl":"https://github.com/iOfficeAI/OfficeCLI/blob/1ced45e900782c5083ed550ddf328ee974e425e7/src/officecli/Handlers/Excel/ExcelHandler.Add.Tables.cs#L551-L587","documentation":"Thrown by AddValidation when type is list and a formula1 property is supplied but, after trimming surrounding double quotes, is empty/whitespace. An empty list formula renders an empty dropdown or makes Excel refuse the file, so it is rejected up front.","triggerScenarios":"Call Add type \"validation\" with properties[\"type\"]=\"list\" and formula1=\"\", formula1=\"\\\"\\\"\", or formula1=\"   \".","commonSituations":"Building formula1 from an empty source list; passing a placeholder; stripping quotes earlier in a pipeline and ending with an empty string.","solutions":["Supply non-empty options: formula1=\"1,2,3\".","Or supply a range reference the list should pull from.","Guard against empty inputs before calling and skip / error with your own message."],"exampleFix":"// before\nhandler.Add(\"/Sheet1\", \"validation\", null,\n    new() { [\"sqref\"] = \"A1:A5\", [\"type\"] = \"list\", [\"formula1\"] = \"\" });\n// after\nhandler.Add(\"/Sheet1\", \"validation\", null,\n    new() { [\"sqref\"] = \"A1:A5\", [\"type\"] = \"list\", [\"formula1\"] = \"Yes,No\" });","handlingStrategy":"validation","validationCode":"if (properties.GetValueOrDefault(\"type\").Equals(\"list\", StringComparison.OrdinalIgnoreCase)\n    && properties.TryGetValue(\"formula1\", out var f1)\n    && string.IsNullOrWhiteSpace(f1.Trim('\"')))\n    throw new InvalidOperationException(\"formula1 is empty for type=list\");","typeGuard":null,"tryCatchPattern":null,"preventionTips":["Skip empty source lists instead of passing an empty formula1.","Validate list options are non-empty after stripping outer quotes.","Prefer a range reference when the option set is dynamic."],"tags":["excel","validation","list","required-property"],"backgroundTag":null,"analyzedSha":"1ced45e900782c5083ed550ddf328ee974e425e7","analyzedAt":"2026-08-13T13:01:07.193Z","schemaVersion":2},"datasetVersion":"2026-08-13T14:17:21.547Z"}