Excel Custom Filters on Text Column using Java and Apache POI

Introduction

In this example I am going to show you how to work with filters in excel file on text column using Java and Apache POI library.

The following list of custom filters for text columns can be implemented:

  • Equals
  • Does Not Equal
  • Begins With
  • Ends With
  • Contains
  • Does Not Contain
excel custom filters on text column using java and apache poi

Prerequisites

Eclipse 2020-06, At least Java 8, Gradle 6.5.1, Maven 3.6.3, Apache POI 4.1.2

We will now implement filters on text column one by one.

Equals

Equals checks for equality of the given string and returns the result.

The key implementation of this operation is given below.

We add a new auto filter because the input excel does not have already filter applied on a column.

Next we add new filter on a particular column number. We set the string that has the value to be searched.

We iterate through each row and hide the rows which do not have value for the given string.

We do not hide the header row, so we are checking the row number with 0.

CTAutoFilter autoFilter = sheet.getCTWorksheet().addNewAutoFilter();

CTFilterColumn filterColumn = autoFilter.addNewFilterColumn();
filterColumn.setColId(columnNumber);

CTFilter ctFilter = filterColumn.addNewFilters().insertNewFilter(columnNumber);
ctFilter.setVal(search);

for (Row r : sheet) {
	for (Cell c : r) {
		if (columnNumber == c.getColumnIndex() && !c.getStringCellValue().equals(search)) {
			XSSFRow row = (XSSFRow) c.getRow();
			if (0 != row.getRowNum()) {
				row.getCTRow().setHidden(true);
			}
		}
	}
}

Does Not Equal

It returns the results which do not match the given string.

CTAutoFilter autoFilter = sheet.getCTWorksheet().addNewAutoFilter();

CTFilterColumn filterColumn = autoFilter.addNewFilterColumn();
filterColumn.setColId(columnNumber);

CTFilter ctFilter = filterColumn.addNewFilters().insertNewFilter(columnNumber);
ctFilter.setVal(search);

for (Row r : sheet) {
	for (Cell c : r) {
		if (columnNumber == c.getColumnIndex() && c.getStringCellValue().equals(search)) {
			XSSFRow row = (XSSFRow) c.getRow();
			if (0 != row.getRowNum()) {
				row.getCTRow().setHidden(true);
			}
		}
	}
}

Begins With

Begins with operation searches a value that begins with a particular string.

The key implementation of this operation is given below.

We add a new auto filter because the input excel does not have already filter applied on a column.

Next we add new filter on a particular column number. We set the string that has the value to be searched.

We iterate through each row and hide the rows which do not have value starts with the given string.

CTAutoFilter autoFilter = sheet.getCTWorksheet().addNewAutoFilter();

CTFilterColumn filterColumn = autoFilter.addNewFilterColumn();
filterColumn.setColId(columnNumber);

CTFilter ctFilter = filterColumn.addNewFilters().insertNewFilter(columnNumber);
ctFilter.setVal(search);

for (Row r : sheet) {
	for (Cell c : r) {
		if (columnNumber == c.getColumnIndex() && !c.getStringCellValue().startsWith(search)) {
			XSSFRow row = (XSSFRow) c.getRow();
			if (0 != row.getRowNum()) {
				row.getCTRow().setHidden(true);
			}
		}
	}
}

Ends With

A string value may end with the given string.

CTAutoFilter autoFilter = sheet.getCTWorksheet().addNewAutoFilter();

CTFilterColumn filterColumn = autoFilter.addNewFilterColumn();
filterColumn.setColId(columnNumber);

CTFilter ctFilter = filterColumn.addNewFilters().insertNewFilter(columnNumber);
ctFilter.setVal(search);

for (Row r : sheet) {
	for (Cell c : r) {
		if (columnNumber == c.getColumnIndex() && !c.getStringCellValue().endsWith(search)) {
			XSSFRow row = (XSSFRow) c.getRow();
			if (0 != row.getRowNum()) {
				row.getCTRow().setHidden(true);
			}
		}
	}
}

Contains

If a string contains a given string.

CTAutoFilter autoFilter = sheet.getCTWorksheet().addNewAutoFilter();

CTFilterColumn filterColumn = autoFilter.addNewFilterColumn();
filterColumn.setColId(columnNumber);

CTFilter ctFilter = filterColumn.addNewFilters().insertNewFilter(columnNumber);
ctFilter.setVal(search);

for (Row r : sheet) {
	for (Cell c : r) {
		if (columnNumber == c.getColumnIndex() && !c.getStringCellValue().contains(search)) {
			XSSFRow row = (XSSFRow) c.getRow();
			if (0 != row.getRowNum()) {
				row.getCTRow().setHidden(true);
			}
		}
	}
}

Does Not Contain

If the string to be searched does not contain the given string.

CTAutoFilter autoFilter = sheet.getCTWorksheet().addNewAutoFilter();

CTFilterColumn filterColumn = autoFilter.addNewFilterColumn();
filterColumn.setColId(columnNumber);

CTFilter ctFilter = filterColumn.addNewFilters().insertNewFilter(columnNumber);
ctFilter.setVal(search);

for (Row r : sheet) {
	for (Cell c : r) {
		if (columnNumber == c.getColumnIndex() && c.getStringCellValue().contains(search)) {
			XSSFRow row = (XSSFRow) c.getRow();
			if (0 != row.getRowNum()) {
				row.getCTRow().setHidden(true);
			}
		}
	}
}

You can download the complete code and sample input and corresponding output files from the source code section.

Testing the Application

Running the complete code will produce the expected output in the excel files.

Source Code

Download

Thanks for reading.

Leave a Reply

Your email address will not be published. Required fields are marked *