I’ve been working pretty flat out recently trying to resurrect the effects of a large API change on our Excel Add-in. We’ve changed the signature, return types and behaviour of API in a more intuitive way. This means that I’ve had to refactor all client code that expected the old way of things. This in itself actually was not the hard part, the hard part was actually fixing our tests.

One of the ways we ensure that our functionality is doing what we expect it to do, is that we test it by assessing before and after states of certain pieces of code. This ensures that that functionality hasn’t changed those expectations. We use Moq to do this. So basically we’ve got test coverage for most of our functionality, and these things have an absolute cadenza when these types of API changes happen.

The most interesting problem I had recently was around the mocking nature of these tests. A basic testing principle described above is further complicated when your functionality that you are testing does something behind the scenes that relies on being ‘online’ or ‘in production’ or connecting to a database somewhere. These are all things that you don’t want to have when you’re testing some piece of functionality(which has this dependency). So we can ‘mock out’ that piece of work, and continue to test everything else.

Mocking will allow one to have a say in what happens when that dependency(say fetching data from an API or database) is reached. In most cases you’ll tell it not to go and fetch the data, but rather you’ll instruct it to think its going to fetch the data and give it ‘mock’ data and it’ll use that moving forward, thinking that it had happily gone and fetch the data live from the database/internet.

The data that you faked is used to it is just to get by that external dependency(say to call out to the internet/database), to get past that point in the functionality, then the mocked data is used throughout the functionality under test. We provide this fake data before the test and ensure that our expectations about the test and perhaps how its affected that test data are still in place after the functionality run.

So the particular problem I’ve recently had was to resolve an issue whereyby I’d need to Mock out an API request but its a API request thats quite odd. It contains a function as a parameter. I need not to entirely mock out the entire function, but to do so but still have the function parameter run and me mock that. Huh? – how bizarre. Have a look:

This is the call to mock, or at least its signature - see the Action<IMeta> call as a parameter. I dont want to mock that out completely...

public delegate void GetPortfoliocallSignature(
Scope scope,
Code portfolioId, 
DateTimeOffset? effectiveDate, 
AsAtPredicate asAt, 
Action<IMeta> getMeta);

And this is how it gets called internally, within the test:

IMeta portfolioMeta = null;

var results = PortfoliosHttpClient.ListPortfolios(
scope,
ParamUtils.GetDate(effectiveDate).ToDateTimeOffSet(), ParamUtils.GetDate(asAt).ToAsAtPredicate(),
getMeta: meta => portfolioMeta = meta);

This basically is a call that goes a fetches some data from the API(ListPortfolios), now i dont want that to go and do that when I’m testing, so I’ll mock this call to return/fetch some fake data. Problem is that there a Action<data> parameter which is responsible for setting a variable, prior to this call. I still need that variable to be set by this call but I’m mocking the entire function out, effectively removing the effects of this call entirely during test(even the Action paramter which would normally be run) – and replacing it with a mock call.

This unfortunately causes that parameter never to be evaluated and my variable(portfolioMeta) is never set – its always null. I’d like to set it to fake data too so it sets my variable and also the effects of the entire function (ListPortfolio) need to be mocked also. This is how it can be done:

 var mockPortfoliosClient = new Mock<IPortfoliosClient>(MockBehavior.Strict);
mockPortfoliosClient.Setup(x => x.GetPortfolio(
TestScope.Scope,
portfolio.Id.Code,
null,
AsAtPredicate.Latest,
It.IsAny<Action<IMeta>>()))
.Callback(new GetPortfoliocallSignature((scope, id, date, at, meta) => meta(TestData.Meta.DummyMeta)))
.Returns(ResponseHelper.CreatePortfolioResponse(portfolio, details, new PortfolioPropertiesDto{Properties = properties}));

Mock out the main ListPortfolios function, but get a call when you do, and then you can influence the mocking of that call, including setting the Action parameter to provide always mocked data via the call to:

=> meta(TestData.Meta.DummyMeta))

Which basically says, I'll provide a mocked version of the action parameter function, so anyone calling the overall function (our test) will have a my mock Action function run and in this case, that mock function will  provide mocked data to the outside world instead of not running at all. Its a fairly complicated scenario to explain...but I'll try.

This means that when the full mocked call is about to be mocked, it allows a call back to be called which I subscribe to, and this allows me to intercept the mocking out of the call and more importantly influence the invocation, including mocking the parameter with a version of Action<data>. I can do this and  always returns mock data and so always sets the variable to this mock data over and above mocking this entire API call.

You can do this with Moq's .Callback() function which as explained allows me to influence the call to Action<IMeta> meta and at the same time, then mock out the call to GetPortfolioFolio entirely and mock its response.

That’s pretty nifty.

I think I know what it must be like being a contractor. It’s a bit like having a baby and ten giving it away. Well, that’s what it feels like when you work on a project and then have to abandon it. I’ve been working on an ‘innovation’ project around driving excel through its JavaScript API. More specifically I was tasked to write a Add-in using the new ‘Universal App’ approach which basically says, write once and run anywhere, be that Mac, Windows, Linux or Office 365,

You basically start off writing a website, then you load the website into Excel and then you tell your website to load a JavaScript library which will talk to Excel. Quite interesting, Its been tough though, seeing that I’m not a JavaScript developer but I am a developer who when he puts his mind to it, can do most things. Anyway, the idea behind this idea of using a JavaScript plugin was to explore firstly how its done, and then what it can do and what it might afford us. Currently, we have a native c# excel add-in which basically created user-defined functions and allows you to upload/download data from the Lusid API. This is fine and it works but what you don’t get is a UI. The user is expected to know or read up on how the functions work, what parameters need to be passed in and how to format the data results – for example, if like me, you don’t know how an array of results appears only on one cell and then needs to manually be expanded – then you’re missing something. A UI might better direct you as to what should be done…

So I decided to build an Angular4 web application to represent the add-in and then call/load the JavaScript excel library to interact with it.

My job was to explore how to interpret and represent information coming from Lusid and see how it could be represented in excel and more importantly how the UI might work and how we could use it.

The way I decided to do things was quite different to the default Excel mentality as represented by our C# plugin, I decided to fetch information from Lusid and present it in the plugin and allow the user to ‘synchonise’ or send that information over to the excel sheet, effectively dumping it as a table. A nicely formatted table. You could browse through the website, selecting and drilling down into the data and when you want to use that data simply send it to the spreadsheet and I’d convert the data to a table. This was a far throw from typing in formulas and expanding array results. The data just appeared on the sheet.

To create new entries, simply adding new rows to the existing table and ‘synchronising’  would automatically create those entities in Lusid. Quite interesting approach. I showed this to the CTO and basically he rubbed his hands together and said perfect, stop there and shelve it – now I know what can be done, thanks very much.

In one respect, that's great but in another, its like I’m closing down shop, moving on and leaving something behind. I know its not lost, I’d checked it into the repository and it will be resurrected in the future apparently but that is that – no more work on it.  I kinda want to work on it more. Why? I love doing working and doing different stuff with technology. I want to do it differently now, perhaps come up with a new novel way of interacting with excel and Lusid.

So that’s why I think I know what being a contractor is like. Not sure I’d want to be a contractor. For one, I’ve put my heart and soul into this project, I’ve written it on the train to work, on the train from work, just before bed, on the weekends – its been a labour of love… But there is one thing that is worth noting, I’ve gained valuable experience and that is something that isn’t left behind. I’ve written an Angular 4, Boostrap enabled application that uses typescript and webpack to deliver a web application that is embeddable into Excel. I’ve worked with the API, I know how it works and its little quirks – like its Promise-Like structures. I’ve learned about Okta authentication(which was tricky in a hash-location based angular app – required by excel api) and I’ve practised using the Lusid API using raw POSTs/GETs and I’ve been able to visualise how to consume the data and then try define it in a UI. Not bad for leaving it all behind and shelving the project!

I made some progress on the excel add-in I'd been tasked with to develop. Up until this time, I'd been so absorbed in learning the main code base at at my new company that I had not time to spend with this. Actually, its a totally diffirent beast being a JavaScript centric project. I'd been assigned countless bugs and tasks for the main code base so all my time was spent doing that. 

During the last week of my illness, I basically was bed-ridden but I could code, so I did and I made some really great progress. I took a lot of my ideas from my Angular4 investment project and incorporated them into the plugin. i also started getting my head around the excel api. This code uses Promises which I' learned about a while back so i finally could use that. Promises lead to code that looks a like this(notice the multiple returns which return new Promises):

static SyncTable<T>(entities: T[], tableName: string, exclComplexTypes: boolean = true): OfficeExtension.IPromise<any> {
    // Get the named table.
    // Get the number of rows, if its diffirenct find difdirent rows from the bottom, create those entries
    let changes: TableChange<T>[] = [];
    //
    return Excel.run(context => {
      const currentWorksheet = context.workbook.worksheets.getActiveWorksheet();
      var table = currentWorksheet.tables.getItemOrNullObject(tableName);
      var bodyRange;
      return  context.sync().then(tableResult => {
        // now should or should not have table?
        if (table.isNullObject) {
          // empty table - normal entityToGrid
          console.log('Original row count is ' + entities.length);
          ExcelUtils.EntitiesToGrid(entities, tableName, exclComplexTypes);
        } else {
          // Existing table, get table and check the number of extra rows
          bodyRange = table.getDataBodyRange().load(['values', 'rowCount']); // cant use yet
          return context.sync().then(dataBodyResult => {
            console.log('RowCount is : ' + bodyRange.rowCount);
            if (bodyRange.rowCount > entities.length) {
              // get the added rows
              var diffCount = bodyRange.rowCount - entities.length;
              for (var i = 0; i < diffCount; i++) {
                var itemIndex = entities.length + i;
                var result = bodyRange.values[itemIndex];
                var obj: T = ReflectionUtils.toObject<T>(result, ReflectionUtils.getEntityProperties(entities[0], exclComplexTypes)[0].sort());
                var change: TableChange<T> = { change: 'a', value: <T>obj };
                changes.push(change);
                console.log('new row: ' + JSON.stringify(result));
              }
              //return new Promise<TableChange<T>[]>((resolve, reject) => { resolve(changes); });
              return context.sync();
            } else if (bodyRange.rowCount > entities.length) {
              // note which rows were removed and get them from the original entities
            }
            //return new Promise<TableChange<T>[]>((resolve, reject) => { resolve(changes); });
            return context.sync();
          });
        }
        
      })
    }).then(value => {
         return new OfficeExtension.Promise((resolve, reject) => resolve(changes));
      })
      .catch(error => {
      console.log(`Error: \({error}`);
      if (error instanceof OfficeExtension.Error) {
        console.log(`Debug info: \){JSON.stringify(error.debugInfo)}`);
      }
    });
    
    
  }
}

That code is turns a list JSON objects(entities) to an excel table and then if you edit that table and add extra rows, it detects the new rows and returns again those changes as a promise. I then act on those changes by 'subscribing' to that promise then creating them on the server. The returned promise contains the list of changed rows... its like madness.

 sync() {
    ExcelUtils.SyncTable<Trade>(this.trades, 'Trades', false).then((values) => {
      var syncResults = <TableChange<Trade>[]>values;
      if (syncResults && syncResults.length) {
        syncResults.forEach(tableChange => {
          var thinNewTrade: Trade = <Trade>{};
          var thinTradeRow = tableChange.value;
          for (let property in thinTradeRow) {
            (<any>thinNewTrade)[property] = (<any>thinTradeRow)[property];
          }
          this.zone.run(() => {
            this.readyTradeForUpsert(thinNewTrade);
          });
        });
      } else {
        this.message = 'No changes detected';

      }
    });
  }

Its all very rough but its got a lot of return context.sync().then...return context.sync()..then...etc. This is the chain of promises that I'm talking about that you need to do when working with promises.

Here is the code that will turn any JSON object into a table in excel:

static EntitiesToGrid<T>(entities: T[], tableName: string, exclComplexTypes: boolean = true): void {
    Excel.run(context => {
        const cols = ReflectionUtils.getEntityProperties<T>(entities[0], exclComplexTypes);
        cols[0].sort(); // sort columns alphabeticallly
        const rows = ReflectionUtils.getEntitiesPropertyValues<T>(entities, cols);

        // Turn camel case into display case ie. myNameIsEarl becomes My Name Is Earl
        cols[0] = cols[0].map(item => { return StringUtils1.Displayify(item); });
        const currentWorksheet = context.workbook.worksheets.getActiveWorksheet();

        // Work out the range we need based on the number of columns we have
        const range = 'A1:' + StringUtils1.xlsColumnDef[cols[0].length - 1] + '1';
        const expensesTable = currentWorksheet.tables.add(range, true /*hasHeaders*/);
        expensesTable.name = tableName;
        expensesTable.getHeaderRowRange().values = cols;
        expensesTable.rows.add(null /*add at the end*/, rows);
        expensesTable.getRange().format.autofitColumns();
        expensesTable.getRange().format.autofitRows();
        return context.sync();
      })
      .catch(error => {
        console.log(`Error: \({error}`);
        if (error instanceof OfficeExtension.Error) {
          console.log(`Debug info: \){JSON.stringify(error.debugInfo)}`);
        }
      });
  }

Most of this code can be found here.