iOfficeAI/OfficeCLI · error · ArgumentException

Sparkline location '{spkCell}' targets sheet '{sheetPart}' b

Error message

Sparkline location '{spkCell}' targets sheet '{sheetPart}' but sparkline lives on '{parentSheetName}'. OOXML requires sparkline location to be on the same worksheet (sheet prefix is implicit).

What it means

Sparkline <xm:sqref> is ST_Sqref -- a bare cell address with NO sheet prefix, because the parent worksheet is implicit. NormalizeSparklineSqref leniently strips the parent-sheet prefix if the caller wrote 'Sheet1!G2', but rejects a prefix naming a DIFFERENT sheet because cross-sheet sparkline locations are invalid OOXML (Excel silently drops the whole extLst on load). Called from Add sparkline (Add.Drawings.cs:987) and Set sparkline (Set.Drawings.cs:110).

Source

Thrown at src/officecli/Handlers/Excel/ExcelHandler.Helpers.ConditionalFormat.cs:154

        if (index < 1 || index > groups.Count) return null;
        return groups[index - 1];
    }

    /// <summary>
    /// Build a DocumentNode for a sparkline group.
    /// </summary>
    // Strip the parent-sheet prefix from a user-supplied sparkline location.
    // <xm:sqref> is ST_Sqref — a bare cell address, no sheet prefix allowed
    // (sheet is implied by the parent worksheet). Accept lenient input so the
    // canonical "Sheet1!G2" form many users naturally write still works; reject
    // cross-sheet references because OOXML doesn't allow them.
    internal static string NormalizeSparklineSqref(string spkCell, string parentSheetName)
    {
        var excl = spkCell.IndexOf('!');
        if (excl < 0) return spkCell;
        var sheetPart = spkCell[..excl].Trim('\'');
        if (!string.Equals(sheetPart, parentSheetName, StringComparison.Ordinal))
            throw new ArgumentException(
                $"Sparkline location '{spkCell}' targets sheet '{sheetPart}' but sparkline lives on '{parentSheetName}'. " +
                "OOXML requires sparkline location to be on the same worksheet (sheet prefix is implicit).");
        return spkCell[(excl + 1)..];
    }

    private static IconSetValues ParseIconSetValues(string name)
    {
        return name.ToLowerInvariant() switch
        {
            "3arrows" => IconSetValues.ThreeArrows,
            "3arrowsgray" => IconSetValues.ThreeArrowsGray,
            "3flags" => IconSetValues.ThreeFlags,
            "3trafficlights1" => IconSetValues.ThreeTrafficLights1,
            "3trafficlights2" => IconSetValues.ThreeTrafficLights2,
            "3signs" => IconSetValues.ThreeSigns,
            "3symbols" => IconSetValues.ThreeSymbols,
            "3symbols2" => IconSetValues.ThreeSymbols2,
            "4arrows" => IconSetValues.FourArrows,

View on GitHub (pinned to 1ced45e900)

Solutions

Pass a bare cell ref 'G2' (preferred form).
If you must qualify, use the parent sheet's name so the prefix is stripped, e.g. 'Sheet1!G2'.
Never reference a different worksheet from a sparkline location.
Validate that any '!' prefix equals the sparkline's parent sheet before calling.

Example fix

// before -- sparkline lives on 'Sheet1'
props["location"] = "Report!G2";  // cross-sheet -> throw

// after
props["location"] = "G2";          // bare ref (preferred)
// or
props["location"] = "Sheet1!G2";   // parent-sheet prefix is stripped

Defensive patterns

Strategy: validation

Validate before calling

static string NormalizeSparkLocation(string loc, string parentSheet)
{
    var i = loc.IndexOf('!');
    if (i < 0) return loc;
    var prefix = loc[..i].Trim('\'');
    if (!string.Equals(prefix, parentSheet, StringComparison.Ordinal))
        throw new ArgumentException($"Sparkline location must be on '{parentSheet}', got '{prefix}'");
    return loc[(i+1)..];
}

spkCell = NormalizeSparkLocation(spkCell, sparklineSheet);

Type guard

static bool IsValidSparkLocation(string loc, string parentSheet)
{
    var i = loc.IndexOf('!');
    return i < 0 || loc[..i].Trim('\'').Equals(parentSheet, StringComparison.Ordinal);
}

Prevention

Pass a bare cell ref (e.g. 'G2') for sparkline locations -- never a cross-sheet ref.
If you qualify, use only the parent sheet's name so the prefix is stripped.
Validate any '!' prefix equals the parent sheet before Add/Set sparkline.
Remember sqref is ST_Sqref: no cross-sheet references are permitted.

When it happens

Trigger: Sparkline on 'Sheet1' with location='OtherSheet!G2'; copying a fully-qualified ref from another context into the sparkline location.

Common situations: User pastes a full 'Sheet!Cell' ref from a formula bar; UI displays qualified refs; assuming sqref is cross-sheet capable like a normal formula.

Related errors

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