{"record":{"id":"64d80f76351880de","repo":"iOfficeAI/OfficeCLI","slug":"sparkline-location-spkcell-targets-sheet-she","errorCode":null,"errorMessage":"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).","messagePattern":"Sparkline location '(.+?)' targets sheet '(.+?)' but sparkline lives on '(.+?)'\\. OOXML requires sparkline location to be on the same worksheet \\(sheet prefix is implicit\\)\\.","errorType":"exception","errorClass":"ArgumentException","httpStatus":null,"severity":"error","filePath":"src/officecli/Handlers/Excel/ExcelHandler.Helpers.ConditionalFormat.cs","lineNumber":154,"sourceCode":"        if (index < 1 || index > groups.Count) return null;\n        return groups[index - 1];\n    }\n\n    /// <summary>\n    /// Build a DocumentNode for a sparkline group.\n    /// </summary>\n    // Strip the parent-sheet prefix from a user-supplied sparkline location.\n    // <xm:sqref> is ST_Sqref — a bare cell address, no sheet prefix allowed\n    // (sheet is implied by the parent worksheet). Accept lenient input so the\n    // canonical \"Sheet1!G2\" form many users naturally write still works; reject\n    // cross-sheet references because OOXML doesn't allow them.\n    internal static string NormalizeSparklineSqref(string spkCell, string parentSheetName)\n    {\n        var excl = spkCell.IndexOf('!');\n        if (excl < 0) return spkCell;\n        var sheetPart = spkCell[..excl].Trim('\\'');\n        if (!string.Equals(sheetPart, parentSheetName, StringComparison.Ordinal))\n            throw new ArgumentException(\n                $\"Sparkline location '{spkCell}' targets sheet '{sheetPart}' but sparkline lives on '{parentSheetName}'. \" +\n                \"OOXML requires sparkline location to be on the same worksheet (sheet prefix is implicit).\");\n        return spkCell[(excl + 1)..];\n    }\n\n    private static IconSetValues ParseIconSetValues(string name)\n    {\n        return name.ToLowerInvariant() switch\n        {\n            \"3arrows\" => IconSetValues.ThreeArrows,\n            \"3arrowsgray\" => IconSetValues.ThreeArrowsGray,\n            \"3flags\" => IconSetValues.ThreeFlags,\n            \"3trafficlights1\" => IconSetValues.ThreeTrafficLights1,\n            \"3trafficlights2\" => IconSetValues.ThreeTrafficLights2,\n            \"3signs\" => IconSetValues.ThreeSigns,\n            \"3symbols\" => IconSetValues.ThreeSymbols,\n            \"3symbols2\" => IconSetValues.ThreeSymbols2,\n            \"4arrows\" => IconSetValues.FourArrows,","sourceCodeStart":136,"sourceCodeEnd":172,"githubUrl":"https://github.com/iOfficeAI/OfficeCLI/blob/1ced45e900782c5083ed550ddf328ee974e425e7/src/officecli/Handlers/Excel/ExcelHandler.Helpers.ConditionalFormat.cs#L136-L172","documentation":"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).","triggerScenarios":"Sparkline on 'Sheet1' with location='OtherSheet!G2'; copying a fully-qualified ref from another context into the sparkline location.","commonSituations":"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.","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."],"exampleFix":"// before -- sparkline lives on 'Sheet1'\nprops[\"location\"] = \"Report!G2\";  // cross-sheet -> throw\n\n// after\nprops[\"location\"] = \"G2\";          // bare ref (preferred)\n// or\nprops[\"location\"] = \"Sheet1!G2\";   // parent-sheet prefix is stripped","handlingStrategy":"validation","validationCode":"static string NormalizeSparkLocation(string loc, string parentSheet)\n{\n    var i = loc.IndexOf('!');\n    if (i < 0) return loc;\n    var prefix = loc[..i].Trim('\\'');\n    if (!string.Equals(prefix, parentSheet, StringComparison.Ordinal))\n        throw new ArgumentException($\"Sparkline location must be on '{parentSheet}', got '{prefix}'\");\n    return loc[(i+1)..];\n}\n\nspkCell = NormalizeSparkLocation(spkCell, sparklineSheet);","typeGuard":"static bool IsValidSparkLocation(string loc, string parentSheet)\n{\n    var i = loc.IndexOf('!');\n    return i < 0 || loc[..i].Trim('\\'').Equals(parentSheet, StringComparison.Ordinal);\n}","tryCatchPattern":null,"preventionTips":["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."],"tags":["excel","ooxml","sparkline","validation"],"backgroundTag":null,"analyzedSha":"1ced45e900782c5083ed550ddf328ee974e425e7","analyzedAt":"2026-08-13T13:01:07.193Z","schemaVersion":2},"datasetVersion":"2026-08-13T14:17:21.547Z"}