-1

I'm using 

bindingObj.setFormatsAsync()

but I can't mark rows with index above 32768. I've got big amounts of data (even up to 1 milion) to validate, and I need to mark invalid cells/rows by changing background color or other formating options.

There's no effect on rows above that unfortunately "smallint".

MoMi
  • 1
  • 1

1 Answers1

0

I was not aware of the 32768 limitation; I'm following up on that.

What version of Office are you targeting? If it's Office 2016 / Office Online / iOS, you can use our new 2016 wave of APIs, which would almost certainly not have this limitation. It would be something like:

Excel.run(function(ctx) {
    var bindingRange = ctx.workbook.bindings.getItem("MyBinding").getRange();
    bindingRange.load("values");
    bindingRange.format.fill.clear();
    return ctx.sync()
        .then(function() {
            for (var i = 0; i < bindingRange.values.length; i++) {
                if (bindingRange.values[i][0] < 10) {
                    bindingRange.getRow(i).format.fill.color = "red";
                }
            }
        })
        .then(ctx.sync);
});

There's a small chance you may run into a slowdown, depending on the number of rows you are formatting (essentially, how many getRow() calls you're making), if it's in the many-thousands. If you do, let me know, and I should be able to give you a workaround for that.

~ Michael Zlatkovsky, developer on Office Extensibility team, MSFT

Michael Zlatkovsky - Microsoft
  • 8,424
  • 1
  • 33
  • 46
  • Unfortunately We're targeting Office 2013, so Excel.js is not an option. – MoMi May 12 '16 at 08:56
  • I know this is old but I am curious if there is an intended fix. I use both `document.setSelectedDataAsync()` and `Office.select().setFormatsAsync()` and if I pass in a cell format with a row index of 32,768 or greater "The row index value is out of the allowed range. Use a value (0 or higher) that's less than the number of rows." These methods allow you to build up the formats and pass them in. It is not a data or data/index issue because you can write a table larger than 32,768 without a problem. You just can't specify a row format above that, even if it's the only format you pass. – Jim Barrett Jul 28 '17 at 23:07