How to mock a NamedItemCollection in office-js using Office-Addin-Mock-CodePudding

I am trying to mock Named ranges array of excel so i can run some tests on adding and removing custom ranges, but it appears that the mock object is not equipped to handle arrays?

Using Jest and in Angular.

As per the documentation i am mocking the Host object and assigning it to the global.Excel

 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);
  });
});

Then in my service

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();


    });
}

But there is the following error on .load

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

I have also tried changing from workbook.names.load() to context.load(workbook.names, 'items') this produced a different error:

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)

Then i also tried workbook.names.load('items') but this has produced an unexpected behaviour, it works but workbook.names.items is undefined even though it is clearly defined.

It is worth noting the code works with all 3 above approaches as it should in the normal add-in enviroment.

Any suggestions?

CodePudding user response：

Try changing your service code to call .load() on the workbook object instead of the workbook.names collection:

  await Excel.run<void>(async context => {
    context.workbook.load('names');
    await context.sync();

    context.workbook.names.items.at(0)?.delete();
  });

It looks like OfficeMockObject doesn't yet have support for collections which means that a load method is not added to the names array that you provide. The suggested service code works around that limitation.