iOfficeAI/OfficeCLI · error · ArgumentException

Invalid merge ref '{newRangeRef}': range must read top-left

Error message

Invalid merge ref '{newRangeRef}': range must read top-left to bottom-right. Pass the canonical orientation (e.g. 'A1:B2', not 'B2:A1').

What it means

The merge range reads bottom-right to top-left (e.g. B2:A1, Z1:A1, A10:A1). Excel itself only writes the canonical top-left-to-bottom-right form, so a reversed pair is almost certainly a typo. The library parses both endpoints, converts columns via ColumnNameToIndex, and throws if the left column/row exceeds the right.

Source

Thrown at src/officecli/Handlers/Excel/ExcelHandler.Helpers.Validation.cs:493

        // form, so callers passing a reversed pair almost certainly typo'd.
        // Reject with a hint to swap, mirroring the orientation guard the
        // sheetShift normalizer applies after the fact (ExcelHandler.Set.cs
        // L1918) and matching how other range-bearing props (validation,
        // table, autofilter) demand canonical orientation up front.
        var colonIdx = refUpper.IndexOf(':');
        if (colonIdx > 0)
        {
            var lhs = refUpper.Substring(0, colonIdx);
            var rhs = refUpper.Substring(colonIdx + 1);
            try
            {
                var (lCol, lRow) = ParseCellReference(lhs);
                var (rCol, rRow) = ParseCellReference(rhs);
                int lColIdx = ColumnNameToIndex(lCol);
                int rColIdx = ColumnNameToIndex(rCol);
                if (lColIdx > rColIdx || lRow > rRow)
                {
                    throw new ArgumentException(
                        $"Invalid merge ref '{newRangeRef}': range must read top-left to bottom-right. " +
                        $"Pass the canonical orientation (e.g. 'A1:B2', not 'B2:A1').");
                }
            }
            catch (ArgumentException) { throw; }
            catch { /* parse failure already handled by SingleMergeRefPattern above */ }
        }
    }

    /// <summary>
    /// Scan a formula body for Sheet-qualified refs (bare `Sheet1!A1`
    /// or quoted `'My Data'!A1`) and return true if any referenced sheet
    /// name does not exist in the current workbook. Used to suppress the
    /// evaluator-based cachedValue fallback when cross-sheet refs point at
    /// a removed sheet — Real Excel shows `#REF!` there; we should not
    /// invent a "0".
    /// </summary>
    private bool FormulaReferencesMissingSheet(string formula)

View on GitHub (pinned to 1ced45e900)

Solutions

Swap the two endpoints so the smaller column and smaller row come first.
Run a normalizer that sorts (col,row) pairs before building the ref string.
Generate refs from min/max of the two corners.

Example fix

// before
sheet.Merge("B2:A1");

// after
sheet.Merge("A1:B2");

Defensive patterns

Strategy: validation

Validate before calling

static string CanonicalRange(string a, string b) {
    var (ac, ar) = ParseCell(a); var (bc, br) = ParseCell(b);
    var top = Math.Min(ColumnNameToIndex(ac), ColumnNameToIndex(bc));
    var bot = Math.Max(ColumnNameToIndex(ac), ColumnNameToIndex(bc));
    var left = Math.Min(ar, br); var right = Math.Max(ar, br);
    return $"{IndexToColumnName(top)}{left}:{IndexToColumnName(bot)}{right}";
}

Try / catch

try { sheet.Merge(range); }
catch (ArgumentException ex) when (ex.Message.Contains("top-left to bottom-right")) {
    var parts = range.Split(':');
    sheet.Merge($"{parts[1]}:{parts[0]}");
}

Prevention

Always build ranges as (min corner):(max corner).
Sort endpoint indices before formatting the A1 string.

When it happens

Trigger: Passing a range whose left cell is to the right of or below the right cell: lColIdx > rColIdx or lRow > rRow after ParseCellReference.

Common situations: UI selection made bottom-up; variables for start/end swapped; copy-paste from a tool that does not normalize orientation.

Related errors

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