iOfficeAI/OfficeCLI · error · ArgumentException

Unknown displayEmptyCellsAs value

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.

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
Defensive patterns

Strategy: type-guard

Validate before calling

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

Type guard

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

Try / catch

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

Prevention

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

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

Appendix: 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)