Excel Office JS筛选器数据 [英] Excel Office JS filter data

问题描述

在Excel中，用户可以选择一个范围并按 Ctrl + Shift + L 以显示过滤器.我正在尝试从Office.js加载项获得同等的行为.

In Excel a user can select a range and hit Ctrl+Shift+L to show filters. I am trying to get equivalent behavior from an Office.js Add-in.

我最接近的是在我要过滤的范围内添加一个表，然后向该表添加一个过滤器.但是，这似乎有几个重大问题.

The closest I have come to that is adding a table over the range I want to filter and then adding a filter to the table. There seem to be a couple of significant problems with that however.

首先，以这种方式为30000+行添加一个表非常慢，并且我经常使用比该表大得多的表.如果我在该大小的范围内执行 Ctrl + Shift + L ，则它是瞬时的.

First, adding a table this way for 30000+ rows is very slow and I am frequently using tables much larger than that. If I do Ctrl+Shift+L over a range that size it is instantaneous.

此外，当我添加表时，Office.js风格化了范围.我不希望该范围有任何新样式，我只想添加一个过滤器.

Additionally, when I add the table, Office.js stylizes the range. I do not want any new styling for the range I just want a filter added.

我当前的代码如下:

await Excel.run(async ctx => {
    const table = await getOrCreateDataTable(ctx, "CostData", new ExcelRange(this.stateService.headerRow)); //see below
    const validationColumn: Excel.TableColumn = table.columns.getItemOrNullObject("Validation");
    validationColumn.filter.applyCustomFilter(`*${searchString}*`)
    await ctx.sync();
});

export const getOrCreateDataTable = async(ctx: Excel.RequestContext, tableName: string, headerRow: ExcelRange): Promise < Excel.Table > => {

    let table: Excel.Table = ctx.workbook.tables.getItemOrNullObject(tableName)
    await ctx.sync();
    if (!table.isNullObject)
        console.log(`Table: ${tableName} found`)
    else {
        const sheet = await getSheet(ctx, headerRow.sheet)
        const headerRange = sheet.getRange(headerRow.getRange()).getEntireRow().getUsedRange()
        const usedRange: Excel.Range = sheet.getUsedRange()
        const tableRange = headerRange.getBoundingRect(usedRange.getLastCell())
        table = ctx.workbook.tables.add(tableRange, true)
        table.name = tableName
        await ctx.sync();

    }
    return table;
}

推荐答案

您现在可以从Excel Javascript API 1.9开始将过滤器添加到范围:

You can now add filters to ranges as of Excel Javascript API 1.9: https://docs.microsoft.com/en-us/office/dev/add-ins/reference/requirement-sets/excel-api-1-9-requirement-set

下面是一个应用自定义过滤器的示例，该过滤器将查找带有"test"子字符串的单元格:

Here's an example to apply a custom filter which will find cells with the substring "test":

Excel.run((ctx) => {
    var sheet = ctx.workbook.worksheets.getActiveWorksheet();
    var range = sheet.getUsedRange();
    var columnIndex = 3; //zero based index
    var condition = { criterion1: "=*test*", filterOn: Excel.FilterOn.custom }        

    sheet.autoFilter.apply(range, columnIndex, condition);

    return ctx.sync();
}).catch(errorHandlerFunction);

使用以下方法删除过滤器:

Remove filters with:

sheet.autoFilter.remove();

在此处找到更多详细信息:

Find more details here:

https://docs.microsoft.com/zh-cn/office/dev/add-ins/excel/excel-add-ins-worksheets#filter-data

或在脚本实验室中: https://www.microsoft .com/en-us/garage/profiles/script-lab/

这篇关于Excel Office JS筛选器数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！