1

I am trying to figure out a way to fetch only the filtered values from a table if a filter is active in Office-JS API.

Right now the only way I have figured to fetch all the table data is from the table range values property:

var table = tables.getItemAt(0);
var tableRange = table.getRange();
tableRange.load("values");
ctx.sync().then(function () {
    // This returns all the values from the table, and not only the visible data
    var values = tableRange.values;
});

Any ideas on how I can proceed to fetch only the visible values from the table if a filter is active?

From previous experience with Office Interop I have achieved the same by looping through the different Areas of the table range, but I am unable to find the equivalent to Areas in Office-JS.

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Mats Oftedal
  • 65
  • 4

2 Answers2

2

The upcoming next wave of features as part of Excel JS APIs 1.3 will include a new object "RangeView" that allows you to read only the visible values off the Range object. Here's a link to the open spec on GitHub - https://github.com/OfficeDev/office-js-docs/tree/ExcelJs_1.3_OpenSpec/excel. Note that this isn't available just yet, but will be in the near future.

Usage for your case off a table would look like this:

var table = tables.getItemAt(0);
var visibleView = table.getRange().getVisibleView();
ctx.load(visibleView);
ctx.sync().then(function () {
    var values = visibleView.values;
});
Philip Rueker
  • 948
  • 5
  • 15
  • Hi Philip. Brilliant! This is exactly what I need. Hopefully they will release this soon as our client is pushing us to complete this add-in as soon as possible. Meanwhile I guess I will have to do with no filter support. – Mats Oftedal Jul 08 '16 at 07:15
  • I tried the workaround from Michael and it works to fetch filtered results from a bound table and will use that until 1.3 comes out. Thanks for the great info! – Mats Oftedal Jul 08 '16 at 09:39
  • @MatsOftedal, the APIs that Philip mentioned are going to go to Beta fairly soon... you'll see the new changes once they're up on http://dev.office.com/changelog – Michael Zlatkovsky - Microsoft Jul 09 '16 at 00:49
  • @MichaelZlatkovsky Thanks! I will keep an eye on the changelog :-) – Mats Oftedal Jul 11 '16 at 07:50
1

One way to get only filtered data is through the Binding.getDataAsync method, which takes a filterType parameter.

Office.select("bindings#myTableBinding1").getDataAsync({
    coercionType: "table",
    filterType: "onlyVisible"
},function(asyncResult){
    var values = (asyncResult.value.rows);
});

This code assumes you have already created a binding to the table. If not, you can run the following code first, which uses the table name to call Bindings.addFromNamedItemAsync:

Office.context.document.bindings.addFromNamedItemAsync("Table1","table",{
    id: "myTableBinding1"
},function(asyncResult){
    // handle errors and call code sample #1
});

Note that the solution above is supported as far back as Excel 2013 because it uses the shared APIs. The Excel-specific API set doesn't yet have the capability to return only unfiltered data.

-Michael Saunders, PM for Office add-ins

Michael Saunders
  • 2,662
  • 1
  • 12
  • 21
  • Hi Michael. Thank you so much for the workaround! It works great! As a developer coming from VSTO Interop development I realize one has to think very differently when using the new Javascript API. – Mats Oftedal Jul 08 '16 at 09:40
  • Michael, The function provided works excellent with fetching the filtered data, but as it seems to return a pure 2d array of rows where the filtered rows are removed, do you know of any way to retain the original unfiltered row number such that I can find back to the original cells after processing the filtered results? – Mats Oftedal Jul 08 '16 at 10:53