iOfficeAI/OfficeCLI · error · ArgumentException

External workbook references are not supported in pivot sour

Error message

External workbook references are not supported in pivot source. Use a local sheet name (e.g. Sheet1!A1:D10)

What it means

Thrown by AddPivotTable's R8-3 guard when the source spec begins with '['. Bracket-prefixed specs like [other.xlsx]Sheet1!A1:D10 denote external workbook references, which OfficeCLI does not support for pivot sources. Without this guard the spec would fall through to FindWorksheet and surface as a misleading 'Source sheet not found: [other.xlsx]Sheet1' error.

Source

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

            ?? properties.GetValueOrDefault("src", "")
            ?? throw new ArgumentException("pivottable requires 'source' property (e.g. source=Sheet1!A1:D100)");
        if (string.IsNullOrEmpty(sourceSpec))
            throw new ArgumentException("pivottable requires 'source' property (e.g. source=Sheet1!A1:D100)");

        // R8-7: incidental whitespace around the source spec or its
        // components (" Sheet1 ! A1:D10 ") is a common paste-from-docs
        // artefact. Trim the whole string and both sides of the '!'
        // split so the downstream sheet/range lookup sees clean values.
        sourceSpec = sourceSpec.Trim();

        // R8-3: external workbook refs such as [other.xlsx]Sheet1!A1:D10
        // used to fall through to FindWorksheet and surface as the
        // misleading "Source sheet not found: [other.xlsx]Sheet1".
        // Detect the '[' prefix up front and throw a clear error so
        // users know the feature is not supported rather than blaming
        // a missing sheet.
        if (sourceSpec.StartsWith("["))
            throw new ArgumentException(
                "External workbook references are not supported in pivot source. "
                + "Use a local sheet name (e.g. Sheet1!A1:D10)");

        string sourceSheetName;
        string sourceRef;

        // B6 v2: try resolving structured-table refs (Table1[#All]) and
        // workbook/sheet-scoped defined names (SalesData, Sheet1!SalesData)
        // into an explicit (sheet, range) tuple BEFORE the literal-parse
        // path. Falls through to the literal parser for explicit
        // "Sheet1!A1:C5" specs and any form the resolver doesn't recognize.
        // See PivotTableHelper.Cache.cs ResolvePivotSourceSpec for coverage.
        var resolved = OfficeCli.Core.PivotTableHelper.ResolvePivotSourceSpec(
            _doc.WorkbookPart!, sourceSpec, defaultSheet: ptSheetName);
        if (resolved.HasValue)
        {
            sourceSheetName = resolved.Value.sheet;
            sourceRef = resolved.Value.rangeRef;

View on GitHub (pinned to 1ced45e900)

Solutions

1. Move or copy the source data into a local sheet in the same workbook and reference it directly, e.g. source=Sheet1!A1:D10.
2. If the external workbook is required, first import its data into the target workbook.
3. Confirm the spec uses a plain local sheet name with no leading bracket.

Example fix

// before
add /Report/pivottable --prop source=[data.xlsx]Sheet1!A1:D10
// after (data copied into same workbook)
add /Report/pivottable --prop source=Data!A1:D10

// Reject external (bracket-prefixed) pivot sources before Add.
var src = (props.GetValueOrDefault("source") ?? props.GetValueOrDefault("src") ?? "").Trim();
if (src.StartsWith("["))
    throw new InvalidOperationException("External workbook references are not supported in pivot source.");

static bool IsExternalRef(string src) => src.TrimStart().StartsWith("[");

try { handler.Add(parentPath, "pivottable", null, props); }
catch (ArgumentException ex) when (ex.Message.Contains("External workbook references are not supported"))
{ /* import the external data into the workbook, then reference it locally */ }

When it happens

Trigger: Calling Add('/Report/pivottable', ...) with source=[data.xlsx]Sheet1!A1:D10 or any spec starting with '['.

Common situations: Copying a reference from Excel's formula bar (which externalizes refs with brackets), or pointing the pivot at data in another workbook.

Related errors

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

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