Office-JS API: Fetching filtered data from table

问题:

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.

回答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

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