如何在Angular+Jest中用Office-Addin-Mock模拟Excel的NamedItemCollection

我正尝试在Angular环境中用Jest模拟Excel的命名范围数组，测试自定义范围的增删操作。按照文档模拟Host对象并赋值给global.Excel后，遇到了以下问题：

• 调用workbook.names.load()时，报错Cannot convert undefined or null to object
• 改用context.load(workbook.names, 'items')时，报错Property _properties needs to be present in object model before load is called
• 尝试workbook.names.load('items')时无报错，但workbook.names.items始终为undefined

注：这三种调用方式在正常加载项环境中均可正常运行。

测试代码 #

it('should delete the first item', async () => {

  const workbookWithNamedRanges = {
    context: {
      workbook: {
        names: {
          items: [
            {
              name: 'Test 1',
              type: 'Error'
            },
            {
              name: 'Test 2',
              type: 'Error'
            },
          ]
        }
      }
    },
    run: async function (callback: any) {
      await callback(this.context);
    }
  };


  const excelMock = new OfficeMockObject(workbookWithNamedRanges) as any;

  // eslint-disable-next-line @typescript-eslint/ban-ts-comment
  // @ts-ignore
  global.Excel = excelMock;

  await service.service.removeFirstItem();

  expect(excelMock.context.workbook.names.items.length).toBe(1);
});
});

服务代码 #

async removeFirstItem(): Promise<void> {

  
  await Excel.run<void>(async context => {
    workbook = context.workbook;

    const namedRanges: Excel.NamedItemCollection = workbook.names.load() // <- Error here at .load;
    await context.sync();

    namedRanges.items.at(0)?.delete();

    await context.sync();


  });
}

错误信息 #

1. 调用workbook.names.load()时：

Cannot convert undefined or null to object
TypeError: Cannot convert undefined or null to object
    at Function.keys (<anonymous>)

2. 改用context.load(workbook.names, 'items')时：

Error: Property _properties needs to be present in object model before load is called.

    at C:\Users\User\Addins\addins\node_modules\office-addin-mock\src\officeMockObject.ts:168:15
    at Array.forEach (<anonymous>)
    at OfficeMockObject.parseObjectPropertyIntoArray (C:\Users\User\Addins\addins\node_modules\office-addin-mock\src\officeMockObject.ts:148:29)
    at OfficeMockObject.load (C:\Users\User\Addins\addins\node_modules\office-addin-mock\src\officeMockObject.ts:41:25)

1. 修正Mock对象结构，适配OfficeMockObject要求 #

OfficeMockObject需要模拟对象包含_properties属性来处理load方法，集合类（如NamedItemCollection）也需要正确的Mock实现。调整Mock数据结构：

const workbookWithNamedRanges = {
  context: {
    workbook: {
      names: {
        items: [
          { name: 'Test 1', type: 'Error', delete: jest.fn() },
          { name: 'Test 2', type: 'Error', delete: jest.fn() },
        ],
        // Mock load方法，支持链式调用
        load: jest.fn((properties?: string) => this),
        _properties: ['items'] // 添加属性满足Mock库的加载校验
      }
    },
    // Mock sync方法，模拟实际同步行为
    sync: jest.fn().mockResolvedValue(undefined)
  },
  run: async function (callback: any) {
    await callback(this.context);
  }
};

2. 调整服务中的load调用方式 #

改为明确指定加载items属性，匹配Mock的逻辑：

const namedRanges: Excel.NamedItemCollection = workbook.names.load('items');

3. 手动Mock核心方法逻辑 #

如果OfficeMockObject对集合类支持有限，可完全手动Mock关键方法：

// 单独Mock命名范围集合
const namesMock = {
  items: [
    { name: 'Test 1', type: 'Error', delete: jest.fn() },
    { name: 'Test 2', type: 'Error', delete: jest.fn() },
  ],
  load: jest.fn(() => namesMock),
  _properties: []
};

// Mock上下文对象
const contextMock = {
  workbook: { names: namesMock },
  sync: jest.fn().mockResolvedValue(undefined)
};

// 最终的Excel Mock对象
const workbookWithNamedRanges = {
  context: contextMock,
  run: async (callback: (ctx: any) => Promise<void>) => {
    await callback(contextMock);
  }
};

4. 完善测试断言 #

除了校验数组长度，还可验证删除方法的调用情况：

expect(namesMock.items[0].delete).toHaveBeenCalled();

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