{"record":{"id":"67366d31e01f436d","repo":"iOfficeAI/OfficeCLI","slug":"cannot-store-cell-cellvalue-text-as-boolean","errorCode":null,"errorMessage":"Cannot store '{cell.CellValue?.Text}' as boolean; value must be true/false, yes/no, or 1/0. Use type=string to keep the literal text.","messagePattern":"Cannot store '(.+?)' as boolean; value must be true/false, yes/no, or 1/0\\. Use type=string to keep the literal text\\.","errorType":"validation","errorClass":"ArgumentException","httpStatus":null,"severity":"error","filePath":"src/officecli/Handlers/Excel/ExcelHandler.Add.Cells.cs","lineNumber":673,"sourceCode":"            if (cellType.Equals(\"richtext\", StringComparison.OrdinalIgnoreCase) ||\n                cellType.Equals(\"rich\", StringComparison.OrdinalIgnoreCase))\n            {\n                ApplyRichTextToCell(cell, properties);\n            }\n            else\n            {\n                // Validate a boolean retype BEFORE mutating DataType. When the\n                // cell already holds text and type=boolean arrives with no new\n                // value, the switch below would stamp t=\"b\" onto that text and\n                // only the later check would throw — leaving a corrupt\n                // <c t=\"b\"><v>hello</v></c> Excel refuses (0x800A03EC). The\n                // R114 upfront guard only sees the incoming value=, not the\n                // existing cell text, so guard that here too.\n                if ((cellType.Equals(\"boolean\", StringComparison.OrdinalIgnoreCase)\n                        || cellType.Equals(\"bool\", StringComparison.OrdinalIgnoreCase))\n                    && cell.CellValue?.Text?.Trim().ToLowerInvariant() is { Length: > 0 } existingBool\n                    && existingBool is not (\"true\" or \"false\" or \"yes\" or \"no\" or \"1\" or \"0\"))\n                    throw new ArgumentException(\n                        $\"Cannot store '{cell.CellValue?.Text}' as boolean; value must be true/false, yes/no, or 1/0. \" +\n                        \"Use type=string to keep the literal text.\");\n                cell.DataType = cellType.ToLowerInvariant() switch\n                {\n                    \"string\" or \"str\" => new EnumValue<CellValues>(CellValues.String),\n                    \"number\" or \"num\" => null,\n                    \"boolean\" or \"bool\" => new EnumValue<CellValues>(CellValues.Boolean),\n                    // CONSISTENCY(cell-type-parity): Bug #4 — Add must accept\n                    // the same type tokens as Set (ExcelHandler.Set.cs line 1105).\n                    // Dates are stored as numeric OADate, so DataType stays null;\n                    // the date-shaped cell value serialization and default\n                    // numberformat are applied right after this switch.\n                    \"date\" => null,\n                    // CE16 — accept `type=error value=\"#N/A\"|\"#DIV/0!\"|...` →\n                    // emits <x:c t=\"e\"><x:v>#N/A</x:v></x:c>. Standard\n                    // Excel error tokens: #N/A, #DIV/0!, #REF!, #NAME?,\n                    // #NULL!, #NUM!, #VALUE!, #GETTING_DATA.\n                    \"error\" or \"err\" => new EnumValue<CellValues>(CellValues.Error),","sourceCodeStart":655,"sourceCodeEnd":691,"githubUrl":"https://github.com/iOfficeAI/OfficeCLI/blob/1ced45e900782c5083ed550ddf328ee974e425e7/src/officecli/Handlers/Excel/ExcelHandler.Add.Cells.cs#L655-L691","documentation":"A defense-in-depth boolean guard inside the type switch: when type=boolean arrives but the cell already holds text (no new value= supplied) that is not bool-convertible, this throws BEFORE DataType is mutated. Without it, the switch would stamp t=\"b\" onto the existing text and only the later check would fire — leaving a corrupt <c t=\"b\"><v>hello</v></c> Excel refuses (0x800A03EC). The R114 upfront guard (error 467) only sees the incoming value=, not existing cell text, so this catches the retype-on-existing-content case.","triggerScenarios":"Add(\"/Sheet1/A1\",\"cell\",pos,{[\"type\"]=\"boolean\"}) on a cell that already contains \"hello\"; retype an existing numeric or text cell to boolean without supplying a new bool value.","commonSituations":"Retyping a column header cell to boolean by mistake; a 'set type only' Add that assumes the existing value is bool-shaped; replaying a type override onto cells imported from a CSV as text.","solutions":["Supply a new boolean value together with type=boolean so the existing text is overwritten: { type=\"boolean\", value=\"true\" }.","If the existing text is actually meant to be a boolean, first Set the value to a bool token, then retype.","If the existing content is genuinely text, use type=string to keep it."],"exampleFix":"// before — cell A1 already holds \"hello\"\nhandler.Add(\"/Sheet1/A1\", \"cell\", null, new() { [\"type\"] = \"boolean\" });\n// after — supply the boolean value explicitly\nhandler.Add(\"/Sheet1/A1\", \"cell\", null, new() { [\"type\"] = \"boolean\", [\"value\"] = \"true\" });","handlingStrategy":"try-catch","validationCode":"// This depends on existing cell content; pre-read the cell and validate.\nvar existing = h.Get(parentPath);\nif (props.GetValueOrDefault(\"type\") is \"boolean\" or \"bool\"\n    && !props.ContainsKey(\"value\") && !props.ContainsKey(\"text\"))\n{\n    var t = /* existing cell text */ \"\";\n    if (!string.IsNullOrEmpty(t) && t.ToLowerInvariant() is not (\"true\" or \"false\" or \"yes\" or \"no\" or \"1\" or \"0\"))\n        throw new ArgumentException(\"existing cell text is not bool-convertible\");\n}","typeGuard":"static bool IsBoolConvertibleText(string? t) =>\n    string.IsNullOrEmpty(t) || t.Trim().ToLowerInvariant() is \"true\" or \"false\" or \"yes\" or \"no\" or \"1\" or \"0\";","tryCatchPattern":"try { h.Add(parentPath, \"cell\", pos, props); }\ncatch (ArgumentException ex) when (ex.Message.Contains(\"as boolean\"))\n{ /* supply an explicit boolean value=, or use type=string */ }","preventionTips":["Always pass a boolean value= when retyping an existing cell to boolean.","Inspect existing cell text before a type-only retype.","Use type=string to preserve non-boolean text."],"tags":["excel","xlsx","cell","boolean","atomicity","retype"],"backgroundTag":null,"analyzedSha":"1ced45e900782c5083ed550ddf328ee974e425e7","analyzedAt":"2026-08-13T13:01:07.193Z","schemaVersion":2},"datasetVersion":"2026-08-13T14:17:21.547Z"}