iOfficeAI/OfficeCLI · error · ArgumentException

Unknown displayEmptyCellsAs value '{deca}'. Valid: gap, span

Error message

Unknown displayEmptyCellsAs value '{deca}'. Valid: gap, span, zero.

What it means

Thrown when the sparkline group's `displayEmptyCellsAs=` property is present but not one of gap/span/zero. These map directly to X14 DisplayBlanksAsValues. The switch is case-insensitive (Trim().ToLowerInvariant()) but only accepts the three canonical tokens — no aliases. Any other value hits the default arm and throws.

Source

Thrown at src/officecli/Handlers/Excel/ExcelHandler.Add.Drawings.cs:1063

            spkGroup.FirstMarkerColor = new X14.FirstMarkerColor { Rgb = ParseHelpers.NormalizeArgbColor(firstMC) };
        if (properties.TryGetValue("lastmarkercolor", out var lastMC))
            spkGroup.LastMarkerColor = new X14.LastMarkerColor { Rgb = ParseHelpers.NormalizeArgbColor(lastMC) };
        if (properties.TryGetValue("markerscolor", out var markersMC))
            spkGroup.MarkersColor = new X14.MarkersColor { Rgb = ParseHelpers.NormalizeArgbColor(markersMC) };

        // Line weight
        if (properties.TryGetValue("lineweight", out var lwVal) && double.TryParse(lwVal, out var lw))
            spkGroup.LineWeight = lw;

        // Group-level axis / empty-cell / RTL attributes.
        if (properties.TryGetValue("displayemptycellsas", out var deca))
        {
            spkGroup.DisplayEmptyCellsAs = deca.Trim().ToLowerInvariant() switch
            {
                "span" => X14.DisplayBlanksAsValues.Span,
                "zero" => X14.DisplayBlanksAsValues.Zero,
                "gap" => X14.DisplayBlanksAsValues.Gap,
                _ => throw new ArgumentException(
                    $"Unknown displayEmptyCellsAs value '{deca}'. Valid: gap, span, zero."),
            };
        }
        if (properties.TryGetValue("displayxaxis", out var dxa) && ParseHelpers.IsTruthy(dxa))
            spkGroup.DisplayXAxis = true;
        if (properties.TryGetValue("righttoleft", out var rtl) && ParseHelpers.IsTruthy(rtl))
            spkGroup.RightToLeft = true;
        if (properties.TryGetValue("dateaxis", out var dax) && ParseHelpers.IsTruthy(dax))
            spkGroup.DateAxis = true;

        // Build the Sparkline element
        // Ensure range includes sheet reference. Validate the range shape
        // first: an arbitrary string ("NOTAREF!!!") landed verbatim in
        // <xne:f> and real Excel refused the file (0x800A03EC) while schema
        // validation stayed green.
        ValidateSparklineRange(spkRange);
        var spkFormulaRef = spkRange.Contains('!') ? spkRange : $"{spkSheetName}!{spkRange}";
        var sparkline = new X14.Sparkline

View on GitHub (pinned to 1ced45e900)

Solutions

1. Use gap (default Excel behavior), span, or zero.
2. Omit the property if the default (gap) is acceptable.

Example fix

// before
add ./book.xlsx /Sheet1 sparkline --prop location=F1 --prop dataRange=A1:E1 --prop displayEmptyCellsAs=blank
// after
add ./book.xlsx /Sheet1 sparkline --prop location=F1 --prop dataRange=A1:E1 --prop displayEmptyCellsAs=zero

var validDeca = new[]{"gap","span","zero"};
if (!validDeca.Contains((deca ?? "").Trim().ToLowerInvariant()))
    throw new InvalidOperationException($"Invalid displayEmptyCellsAs '{deca}'");

static readonly HashSet<string> DecaTokens = new(StringComparer.OrdinalIgnoreCase)
    {"gap","span","zero"};
static bool IsValidDisplayBlanks(string? v) => v is null || DecaTokens.Contains(v?.Trim());

try { handler.AddSparkline(...); }
catch (ArgumentException ex) when (ex.Message.Contains("displayEmptyCellsAs"))
{ /* default to gap or reprompt */ }

When it happens

Trigger: Passing `--prop displayEmptyCellsAs=blank`, `=skip`, `=interpolate`, or `=none`. The property key itself is matched case-insensitively against `displayemptycellsas`, so `displayEmptyCellsAs=` and `DisplayEmptyCellsAs=` both resolve; only the VALUE is constrained.

Common situations: Carrying vocabulary from chart blank-cell handling in other tools; assuming `blank` is accepted as a synonym for `gap`.

Related errors

• Invalid sparkline type: '{spkTypeStr}'. Valid values: line,
• Invalid valign value: '{shpValign}'. Valid: top, center, bot
• Unknown underline value '{shpUnder}'. Valid: single, double,
• Unknown align value '{shpAlign}'. Valid: left, center, right
• Sheet not found: {spkSheetName}

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