通过POI设置Excel表格头的过滤器

I generate a sheet, pretty bog standard headers and columns of data.

我生成了一个表格，很糟的标准标题和数据列。

I want to turn on the "Filter" function for the sheet, so the user can easily sort and filter the data.

我想打开“Filter”函数，以便用户可以轻松地对数据进行排序和过滤。

Can I so this using POI?

我可以用POI吗?

3 个解决方案

#1

Save the first and last cell from filter area, and execute:

从过滤器区域保存第一个和最后一个单元格，并执行:

sheet.setAutoFilter(new CellRangeAddress(firstCell.getRow(), lastCell.getRow(), firstCell.getCol(), lastCell.getCol()));

For example, from the below sheet.

例如，从下面的表格。

>x         (x, y)
  0123456  
0|--hhh--|   h = header
1|--+++--|   + = values
2|--+++--|   - = empty fields
3|--+++--|
4|-------|

first cell will be the header above the first + (2,1) cell. The last will be the last + cell (5,3)

第一个单元格将是第一个+(2,1)单元格上面的标题。最后一个是最后一个+单元格(5,3)

#2

If you also want to set a filter programmatically, you could use the following:

如果您还想以编程方式设置过滤器，您可以使用以下方法:

void setAutoFilter(final XSSFSheet sheet, final int column, final String value) {
    sheet.setAutoFilter(CellRangeAddress.valueOf("A1:Z1"));

    final CTAutoFilter sheetFilter = sheet.getCTWorksheet().getAutoFilter();
    final CTFilterColumn filterColumn = sheetFilter.addNewFilterColumn();
    filterColumn.setColId(column);
    final CTFilter filter = filterColumn.addNewFilters().insertNewFilter(0);
    filter.setVal(value);

    // We have to apply the filter ourselves by hiding the rows: 
    for (final Row row : sheet) {
        for (final Cell c : row) {
            if (c.getColumnIndex() == column && !c.getStringCellValue().equals(value)) {
                final XSSFRow r1 = (XSSFRow) c.getRow();
                if (r1.getRowNum() != 0) { // skip header
                    r1.getCTRow().setHidden(true);
                }
            }
        }
    }
}

Relevant Gradle dependencies:

相关Gradle依赖性:

    // https://mvnrepository.com/artifact/org.apache.poi/poi
compile group: 'org.apache.poi', name: 'poi', version: '3.9'

// https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml
compile group: 'org.apache.poi', name: 'poi-ooxml', version: '3.9'

// https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml-schemas
compile group: 'org.apache.poi', name: 'poi-ooxml-schemas', version: '3.9'

// https://mvnrepository.com/artifact/org.apache.poi/ooxml-schemas
compile group: 'org.apache.poi', name: 'ooxml-schemas', version: '1.3'

#3

I figured out how to do this with NPOI.
You add a CT_AutoFilter to the CT_Table.

我知道如何用NPOI来做这个。在CT_Table中添加一个CT_AutoFilter。

I am guessing the it works the same for POI as NPOI.

我猜POI和NPOI是一样的。

    cttable.autoFilter = new CT_AutoFilter();
    cttable.autoFilter.@ref = "A1:C5";   // value is data and includes header.