iOfficeAI/OfficeCLI · error · ArgumentException

Cannot store '{cell.CellValue?.Text}' as boolean; value must

Error message

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.

What it means

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.

Source

Thrown at src/officecli/Handlers/Excel/ExcelHandler.Add.Cells.cs:673

            if (cellType.Equals("richtext", StringComparison.OrdinalIgnoreCase) ||
                cellType.Equals("rich", StringComparison.OrdinalIgnoreCase))
            {
                ApplyRichTextToCell(cell, properties);
            }
            else
            {
                // Validate a boolean retype BEFORE mutating DataType. When the
                // cell already holds text and type=boolean arrives with no new
                // value, the switch below would stamp t="b" onto that text and
                // only the later check would throw — leaving a corrupt
                // <c t="b"><v>hello</v></c> Excel refuses (0x800A03EC). The
                // R114 upfront guard only sees the incoming value=, not the
                // existing cell text, so guard that here too.
                if ((cellType.Equals("boolean", StringComparison.OrdinalIgnoreCase)
                        || cellType.Equals("bool", StringComparison.OrdinalIgnoreCase))
                    && cell.CellValue?.Text?.Trim().ToLowerInvariant() is { Length: > 0 } existingBool
                    && existingBool is not ("true" or "false" or "yes" or "no" or "1" or "0"))
                    throw new ArgumentException(
                        $"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.");
                cell.DataType = cellType.ToLowerInvariant() switch
                {
                    "string" or "str" => new EnumValue<CellValues>(CellValues.String),
                    "number" or "num" => null,
                    "boolean" or "bool" => new EnumValue<CellValues>(CellValues.Boolean),
                    // CONSISTENCY(cell-type-parity): Bug #4 — Add must accept
                    // the same type tokens as Set (ExcelHandler.Set.cs line 1105).
                    // Dates are stored as numeric OADate, so DataType stays null;
                    // the date-shaped cell value serialization and default
                    // numberformat are applied right after this switch.
                    "date" => null,
                    // CE16 — accept `type=error value="#N/A"|"#DIV/0!"|...` →
                    // emits <x:c t="e"><x:v>#N/A</x:v></x:c>. Standard
                    // Excel error tokens: #N/A, #DIV/0!, #REF!, #NAME?,
                    // #NULL!, #NUM!, #VALUE!, #GETTING_DATA.
                    "error" or "err" => new EnumValue<CellValues>(CellValues.Error),

View on GitHub (pinned to 1ced45e900)

Solutions

1. Supply a new boolean value together with type=boolean so the existing text is overwritten: { type="boolean", value="true" }.
2. If the existing text is actually meant to be a boolean, first Set the value to a bool token, then retype.
3. If the existing content is genuinely text, use type=string to keep it.

Example fix

// before — cell A1 already holds "hello"
handler.Add("/Sheet1/A1", "cell", null, new() { ["type"] = "boolean" });
// after — supply the boolean value explicitly
handler.Add("/Sheet1/A1", "cell", null, new() { ["type"] = "boolean", ["value"] = "true" });

// This depends on existing cell content; pre-read the cell and validate.
var existing = h.Get(parentPath);
if (props.GetValueOrDefault("type") is "boolean" or "bool"
    && !props.ContainsKey("value") && !props.ContainsKey("text"))
{
    var t = /* existing cell text */ "";
    if (!string.IsNullOrEmpty(t) && t.ToLowerInvariant() is not ("true" or "false" or "yes" or "no" or "1" or "0"))
        throw new ArgumentException("existing cell text is not bool-convertible");
}

static bool IsBoolConvertibleText(string? t) =>
    string.IsNullOrEmpty(t) || t.Trim().ToLowerInvariant() is "true" or "false" or "yes" or "no" or "1" or "0";

try { h.Add(parentPath, "cell", pos, props); }
catch (ArgumentException ex) when (ex.Message.Contains("as boolean"))
{ /* supply an explicit boolean value=, or use type=string */ }

When it happens

Trigger: 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.

Common situations: 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.

Related errors

• Cannot store '{properties.GetValueOrDefault("value") ?? prop
• Sheet not found: {cellSheetName}
• Unrecognized cell parent path segment '{cellSegments[1]}'. E
• Invalid cell reference: '{cellRef}'
• --prop shift={shiftVal} not valid for add cell. Use 'right'

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