iOfficeAI/OfficeCLI · error · ArgumentException

Source sheet not found: {sourceSheetName}

Error message

Source sheet not found: {sourceSheetName}

What it means

Thrown by AddPivotTable when the sheet named in the source spec (the part before '!', or the host sheet when no '!' is present) does not resolve via FindWorksheet. The pivot cache needs a real source worksheet, so an unknown source sheet name is rejected before building the cache. Note structured-table refs (Table1[#All]) and defined names are resolved earlier and fall through to this literal parse only if unresolved.

Source

Thrown at src/officecli/Handlers/Excel/ExcelHandler.Add.Tables.cs:1716

        if (resolved.HasValue)
        {
            sourceSheetName = resolved.Value.sheet;
            sourceRef = resolved.Value.rangeRef;
        }
        else if (sourceSpec.Contains('!'))
        {
            var srcParts = sourceSpec.Split('!', 2);
            sourceSheetName = srcParts[0].Trim().Trim('\'', '"').Trim();
            sourceRef = srcParts[1].Trim();
        }
        else
        {
            sourceSheetName = ptSheetName;
            sourceRef = sourceSpec;
        }

        var sourceWorksheet = FindWorksheet(sourceSheetName)
            ?? throw new ArgumentException($"Source sheet not found: {sourceSheetName}");

        var ptPosition = (properties.GetValueOrDefault("position", "")
            ?? properties.GetValueOrDefault("pos", ""))
            ?.Replace("$", ""); // CONSISTENCY(dollar-strip): parity with source ref handling
        if (string.IsNullOrEmpty(ptPosition))
        {
            // Auto-position: place after the source data range
            var rangeEnd = sourceRef.Split(':').Last();
            var colEndMatch = System.Text.RegularExpressions.Regex.Match(rangeEnd, @"([A-Za-z]+)");
            var nextCol = colEndMatch.Success ? IndexToColumnName(ColumnNameToIndex(colEndMatch.Value.ToUpperInvariant()) + 2) : "H";
            ptPosition = $"{nextCol}1";
        }

        // R26-1: validate that the pivot output fits within sheet dimensions
        // before writing any cache/pivot parts. A position near the sheet edge
        // can produce an end-location beyond XFD1048576, which causes a
        // partial-write: cache parts are already saved when the render stage
        // discovers the overflow and throws, leaving a corrupt zip.

View on GitHub (pinned to 1ced45e900)

Solutions

1. Verify the source sheet exists by listing the workbook's sheets.
2. For same-sheet sources, ensure the host sheet (parent path) actually contains the data.
3. If the source is a table or defined name, confirm it resolves before the literal parse path.

Example fix

// before
add /Report/pivottable --prop source=Data2!A1:D100
// after (correct sheet name)
add /Report/pivottable --prop source=Data!A1:D100

// Resolve and verify the source sheet exists before Add.
var src = (props.GetValueOrDefault("source") ?? props.GetValueOrDefault("src")).Trim();
var sourceSheet = src.Contains('!') ? src.Split('!', 2)[0].Trim().Trim('\'', '"').Trim() : parentSheet;
if (handler.FindWorksheet(sourceSheet) is null)
    throw new InvalidOperationException($"Source sheet not found: {sourceSheet}");

try { handler.Add(parentPath, "pivottable", null, props); }
catch (ArgumentException ex) when (ex.Message.StartsWith("Source sheet not found"))
{ /* correct the source sheet name and retry */ }

When it happens

Trigger: Calling Add('/Report/pivottable', ...) with source=MissingSheet!A1:D10 where MissingSheet does not exist, or a same-sheet source where the host sheet itself is wrong.

Common situations: Source sheet renamed/deleted, typo in the sheet name, or pointing at a sheet in a different workbook after the external-ref guard.

Related errors

• Sheet not found: {ptSheetName}
• Pivot name '{explicitName}' already exists in workbook
• Property 'sqref' (or 'range'/'ref') is required for validati
• Sheet not found: {tblSheetName}
• pivottable requires 'source' property (e.g. source=Sheet1!A1

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