Office-JS API：从表中获取过滤的数据 [英] Office-JS API: Fetching filtered data from table

问题描述

现在只有一个方法可以从表格中只提取过滤值我已经想通过获取所有表数据是从表范围值属性：

$ pre $ var table = tables.getItemAt（0 ）;
var tableRange = table.getRange（）;
tableRange.load（values）;
ctx.sync（）。then（function（）{
//这将返回表中的所有值，而不仅仅是可见的数据
var values = tableRange.values;
}）;

如果过滤器处于活动状态，我可以继续从表中只提取可见值根据之前使用Office Interop的经验，我已经通过遍历表范围的不同区域实现了同样的效果，但是我无法找到相当于Office in Area -JS。

解决方案

获取过滤数据的一种方法是通过 Binding.getDataAsync方法，它需要一个 filterType参数。

  Office.select （bindings＃myTableBinding1）。getDataAsync（{
 coercionType：table，
 filterType：onlyVisible
}，function（asyncResult）{
 var values =（asyncResult .value.rows）; 
}）; 
  

这段代码假设您已经创建了一个绑定到表。如果不是，则可以先运行以下代码，该代码使用表名称来调用 Bindings.addFromNamedItemAsync ：
$ b $ pre $ Office $ .context.document.bindings.addFromNamedItemAsync（Table1，table ，{
id：myTableBinding1
}，function（asyncResult）{
//处理错误和调用代码示例＃1
}）;

请注意，上述解决方案早在Excel 2013中受到支持，因为它使用共享的API。特定于Excel的API集还没有能力只返回未经过滤的数据。

-Michael Saunders，PM for Office加载项

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.

解决方案

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

这篇关于Office-JS API：从表中获取过滤的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！