Office-JS中UsedRange无法调用MergedAreas方法的问题排查

环境 #

• 桌面版Excel
• Office JavaScript API

可正常运行的代码 #

console.log('START DEFINED RNG')
var ws = context.workbook.worksheets.getActiveWorksheet();
var rng = ws.getRange("A1:A2")
//var rng = ws.getUsedRange(true)
var mergedAreas = rng.getMergedAreasOrNullObject();
mergedAreas.load(["areas"]);
await context.sync()
console.log('SYCNED')

报错代码（原可运行，现失效） #

console.log('START USED_RNG')
var ws = context.workbook.worksheets.getActiveWorksheet();
//var rng = ws.getRange("A1:A2")
var rng = ws.getUsedRange(true)
var mergedAreas = rng.getMergedAreasOrNullObject();
mergedAreas.load(["areas"]);
await context.sync()
console.log('SYCNED')

报错信息 #

InvalidOperation: 不允许对当前对象执行此操作。

{
   "code": "InvalidOperation",
   "data": undefined,
   "debugInfo": {
      "code": "InvalidOperation",
      "errorLocation": "Range.getMergedAreasOrNullObject",
      "message": "不允许对当前对象执行此操作。",
      "statement": "var mergedAreasOrNullObject = usedRange.getMergedAreasOrNullObject();",
      "surroundingStatements": [
         "var workbook = context.workbook;",
         "var worksheets = workbook.worksheets;",
         "var activeWorksheet = worksheets.getActiveWorksheet();",
         "var usedRange = activeWorksheet.getUsedRange(...);",
         "// Instantiate {usedRange}",
         "// >>>>>>>",
         "var mergedAreasOrNullObject = usedRange.getMergedAreasOrNullObject();",
         "// <<<<<<",
         "// Instantiate {mergedAreasOrNullObject}",
         "mergedAreasOrNullObject.load([\"areas\"]);"
      ]
   },
   "description": "不允许对当前对象执行此操作。",
   "httpStatusCode": 400,
   "message": "不允许对当前对象执行此操作。",
   "name": "RichApi.Error",
   "stack": "InvalidOperation: 不允许对当前对象执行此操作。\n   at Anonymous function (https://appsforoffice.microsoft.com/lib/beta/hosted/excel-win32-16.01.js:26:321537)\n   at Anonymous function (http://localhost:3000/yo/dist/polyfill.js:1:76119)\n   at e (http://localhost:3000/yo/dist/polyfill.js:1:31843)"
}

分析与解决方案 #

原因 #

Office JS API更新后，getMergedAreasOrNullObject()不再支持直接作用于getUsedRange()返回的Range对象。这类Range可能包含多个非连续区域，API现在限制该方法仅能处理单个连续的单元格区域。

解决代码 #

遍历UsedRange下的所有子区域，逐个调用合并区域方法：

console.log('START USED_RNG FIX')
var ws = context.workbook.worksheets.getActiveWorksheet();
var usedRange = ws.getUsedRange(true);
usedRange.load("areas");
await context.sync();

let allMergedAreas = [];
for (let area of usedRange.areas) {
    let mergedAreas = area.getMergedAreasOrNullObject();
    mergedAreas.load("areas");
    await context.sync();
    if (mergedAreas.areas) {
        allMergedAreas.push(...mergedAreas.areas);
    }
}
console.log('ALL MERGED AREAS', allMergedAreas);

内容的提问来源于stack exchange，提问作者FreeSoftwareServers