Setting filter on headers of an Excel sheet via POI

Question

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.

Can I so this using POI?

Possible duplicate of [POI Auto Filter](http://stackoverflow.com/questions/3114220/poi-auto-filter) — beldaz, May 16 '16 at 00:25

score 78 · Accepted Answer · edited Jul 19 '17 at 23:25

78

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)

edited Jul 19 '17 at 23:25

Sastrija

3,284
6
47
64

answered Dec 04 '12 at 13:01

Victor

8,309
14
80
129

7

Nice. It seems that I can get away with the following though: s.setAutoFilter(CellRangeAddress.valueOf("A1:E1")); This range is the header cells only, and does not include the data cells. Still, result is whast I want. – Anders Johansen Dec 04 '12 at 13:15
1

s.setAutoFilter(CellRangeAddress.valueOf("A1:N1")); by this we can filter data also. – swamy Dec 06 '12 at 07:33
7

did you mean that the last + cell is (4, 3) ? – amphibient Apr 01 '15 at 18:12
4

I think the API for cell may have changed. I had to use this range: `new CellRangeAddress( firstCell.getRowIndex(), lastCell.getRowIndex(), firstCell.getColumnIndex(), lastCell.getColumnIndex() )`. getRow now returns a row instead of an index and getCol no longer exists. – Matt Pennington Jun 14 '17 at 18:11
@Victor by (5,3) do you mean (3,4) ? – Prabhat Gaur Nov 30 '20 at 14:05
@PrabhatGaur, I wrote this answer 8 years ago. ‍♂️ – Victor Nov 30 '20 at 14:08
The method is now sheet.SetAutoFilter(), with capital S. – Chegon Jul 07 '21 at 15:17

score 27 · Answer 2 · answered Dec 28 '18 at 10:45

27

easiest way of adding filter on header :

sheet.setAutoFilter(new CellRangeAddress(0, 0, 0, numColumns));
sheet.createFreezePane(0, 1);

answered Dec 28 '18 at 10:45

Tiago Medici

1,944
22
22

11

Small correction: that would be `numColumns - 1` (otherwise you get an extra filter column at the end). – Priidu Neemre Sep 02 '20 at 07:20
since numColumns is a var, why would you subtract a value? since you can just define it , example; if you expect to have 9, you don't need to put it as 10 and subtract 1 ... – Tiago Medici Sep 02 '20 at 13:38
2

This was more a conceptual remark rather than verbatim code :). The gist is that the constructors for `CellRangeAddress` are zero-indexed and bound-inclusive. So if you are calculating `numColumns` based off of something, you need to take that into consideration. – Priidu Neemre Sep 02 '20 at 15:58
2

I agree with @PriiduNeemre here. I think that `lastIndex` would be more appropiate than `numColumns` (Or I would use `numColumns - 1`) – Julio Dec 22 '20 at 15:21

score 4 · Answer 3 · answered Apr 27 '17 at 13:09

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:

    // 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'

score 2 · Answer 4 · answered Oct 26 '17 at 21:22

2

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

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

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

answered Oct 26 '17 at 21:22

Mark Holden

21
1

Thank you, this is the only way that worked for me. Excel kept blowing away my table using the other answers. – Nathan Champion Jan 04 '22 at 15:53
This effectively does the job: it sets the auto filter in the table (and it goes straight to the table entity). Calling `SetAutoFilter` on the sheet, adds some data to the sheet, but in my case, it also causes excel to complain and repair the document by taking out the table – Yennefer Aug 12 '23 at 16:44

score 1 · Answer 5 · answered Feb 25 '19 at 11:08

Use sheet.setAutoFilter(CellRangeAddress.valueOf("B1:H1"));

We have to specify only the header cells of the tabular data. Here in my example header starts from cell B1 and ends at cell H1.
Excel will automatically find the data below it and show it in the filter options.

Setting filter on headers of an Excel sheet via POI

5 Answers5

Linked