CODEWITHSUNDEEP
office365office-jsexcel-addins
bryanbraun
bryanbraun

Reputation: 3355

Best way to loop through all worksheets in the Excel Javascript API?

I want to loop through each of the worksheets in a workbook, and perform an action on each one, (for example, running calculate()). For performance reasons, I want to do this all in one sync() request.

The problem is, prior to syncing, I can only get worksheets in a few limited ways like via the active worksheet, or by worksheet name, which makes looping hard.

The best approach I can come up with uses getFirst() and getNext():

Excel.run(function(ctx) {
  var sheets = ctx.workbook.worksheets;
  var sheetCount = sheets.getCount();
  var currentSheet = sheets.getFirst();
  currentSheet.calculate()

  if (sheetCount > 1) {
    for (var i = 2; i <= sheetCount; i++) {
      currentSheet = currentSheet.getNext();
      currentSheet.calculate();
    }
  }
  return ctx.sync();
});

This loop is pretty awkward, and I feel like there has to be a better way. Is there?

Upvotes: 2

Views: 2800

Answers (1)

Michael Zlatkovsky
Michael Zlatkovsky

Reputation: 8670

Does your code work? It looks suspect -- you are doing a sheets.getCount(), and yet you are not syncing before proceeding. As such, I'm not sure how the sheetCount > 1 or i <= sheetCount would work.

So, unless you happen to know the sheet count in advance somehow, I think you have to resign yourself to having two context.sync -- one to find some preliminary information (e.g., count, or maybe something else), and another to flush the results of your actual operations.

Though you could get the collection count using getCount(), using it alone is quite awkward. Fortunately, if you just load the sheets collection, you will end up with sheets.items, which will be an array of all of the Worksheet proxy objects.

You can load any property on the sheets, such as an ID or name or whatever pleases you. In your particular scenario, it doesn't sounds like you need anything at all -- but if you don't specify a property name to load, the pipeline will load all scalar properties, which isn't great for performance reasons (at least in the general case -- worksheet in particular has so few scalar properties that it probably doesn't matter, but it's still wasteful, and it's not a good pattern to have creep up into your code). So if you want just the structure but not any given property, you can give it any bogus property name -- my favorite being $none, which is nicely distinct. Or you can instead load the worksheet name or ID, up to you.

Resulting code, using TypeScript (or rather, async/await, which TypeScript will compile for you to ES5-compatible JavaScript):

Excel.run(function(ctx) {
    var sheets = ctx.workbook.worksheets;
    sheets.load("$none");
    await context.sync();

    sheets.items.forEach(sheet => sheet.calculate());
    await context.sync();
});

Upvotes: 4

Related Questions