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 number columns can be implemented:

  • Equals
  • Does Not Equal
  • Greater Than
  • Greater Than Or Equal To
  • Less Than
  • Less Than Or Equal To
  • Between
  • Top 10
  • Above Average
  • Below Average
excel custom filters on number 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

Now we will write Java code to implement all the above operations.

Related Posts:

Equals

Equals returns the results that match with the given value. The rows with the given value do not match get hidden.

boolean skipFirstRow = true;
for (Row r : sheet) {
	if (skipFirstRow) {
		skipFirstRow = false;
		continue;
	}
	for (Cell c : r) {
		if (columnNumber == c.getColumnIndex() && c.getNumericCellValue() != search) {
			XSSFRow row = (XSSFRow) c.getRow();
			row.getCTRow().setHidden(true);
		}
	}
}

Does Not Equal

If the given value does not match with rows, then those rows get hidden.

boolean skipFirstRow = true;
for (Row r : sheet) {
	if (skipFirstRow) {
		skipFirstRow = false;
		continue;
	}
	for (Cell c : r) {
		if (columnNumber == c.getColumnIndex() && c.getNumericCellValue() == search) {
			XSSFRow row = (XSSFRow) c.getRow();
			row.getCTRow().setHidden(true);
		}
	}
}

Greater Than

You want to show those rows which are having greater than a specific value.

boolean skipFirstRow = true;
for (Row r : sheet) {
	if (skipFirstRow) {
		skipFirstRow = false;
		continue;
	}
	for (Cell c : r) {
		if (columnNumber == c.getColumnIndex() && c.getNumericCellValue() <= search) {
			XSSFRow row = (XSSFRow) c.getRow();
			row.getCTRow().setHidden(true);
		}
	}
}

Greater Than Or Equal To

You want to show those rows which are having greater than or equal to a specific value.

boolean skipFirstRow = true;
for (Row r : sheet) {
	if (skipFirstRow) {
		skipFirstRow = false;
		continue;
	}
	for (Cell c : r) {
		if (columnNumber == c.getColumnIndex() && c.getNumericCellValue() < search) {
			XSSFRow row = (XSSFRow) c.getRow();
			row.getCTRow().setHidden(true);
		}
	}
}

Less Than

You want to show those rows which are having less than a specific value.

boolean skipFirstRow = true;
for (Row r : sheet) {
	if (skipFirstRow) {
		skipFirstRow = false;
		continue;
	}
	for (Cell c : r) {
		if (columnNumber == c.getColumnIndex() && c.getNumericCellValue() >= search) {
			XSSFRow row = (XSSFRow) c.getRow();
			row.getCTRow().setHidden(true);
		}
	}
}

Less Than Or Equal To

You want to show those rows which are having less than or equal to a specific value.

boolean skipFirstRow = true;
for (Row r : sheet) {
	if (skipFirstRow) {
		skipFirstRow = false;
		continue;
	}
	for (Cell c : r) {
		if (columnNumber == c.getColumnIndex() && c.getNumericCellValue() > search) {
			XSSFRow row = (XSSFRow) c.getRow();
			row.getCTRow().setHidden(true);
		}
	}
}

Between

You want to show those rows which are having values less than or greater than a specific value.

boolean skipFirstRow = true;
for (Row r : sheet) {
	if (skipFirstRow) {
		skipFirstRow = false;
		continue;
	}
	for (Cell c : r) {
		double val = c.getNumericCellValue();
		if (columnNumber == c.getColumnIndex() && (val < from || val > to)) {
			XSSFRow row = (XSSFRow) c.getRow();
			row.getCTRow().setHidden(true);
		}
	}
}

Top 10

You want to show those rows which are in top 10 in ascending order.

List<Integer> nums = new ArrayList<>();

boolean skipFirstRow = true;
for (Row r : sheet) {
	if (skipFirstRow) {
		skipFirstRow = false;
		continue;
	}
	for (Cell c : r) {
		if (columnNumber == c.getColumnIndex()) {
			nums.add((int) c.getNumericCellValue());
		}
	}
}

Integer[] numbers = nums.toArray(new Integer[nums.size()]);

Arrays.sort(numbers, Collections.reverseOrder());

int[] arr = new int[10];
for (int i = 0; i < 10; i++) {
	arr[i] = numbers[i];
}

int arrSize = arr.length;
skipFirstRow = true;
for (Row r : sheet) {
	if (skipFirstRow) {
		skipFirstRow = false;
		continue;
	}
	for (Cell c : r) {
		if (columnNumber == c.getColumnIndex()) {
			boolean hide = true;
			int val = (int) c.getNumericCellValue();
			for (int i = 0; i < arrSize; i++) {
				if (arr[i] == val) {
					hide = false;
					break;
				}
			}
			if (hide) {
				XSSFRow row = (XSSFRow) c.getRow();
				row.getCTRow().setHidden(hide);
			}
		}
	}
}

Above Average

Above average means, you have some values on multiple rows and you calculate average of those values, then you show those rows which are having above average values.

List<Integer> nums = new ArrayList<>();

boolean skipFirstRow = true;
for (Row r : sheet) {
	if (skipFirstRow) {
		skipFirstRow = false;
		continue;
	}
	for (Cell c : r) {
		if (columnNumber == c.getColumnIndex()) {
			nums.add((int) c.getNumericCellValue());
		}
	}
}

int sum = 0;
for (Integer integer : nums) {
	sum += integer;
}

double avg = sum / nums.size();

int size = nums.size();
skipFirstRow = true;
for (Row r : sheet) {
	if (skipFirstRow) {
		skipFirstRow = false;
		continue;
	}
	for (Cell c : r) {
		if (columnNumber == c.getColumnIndex()) {
			boolean hide = true;
			double val = (int) c.getNumericCellValue();
			for (int i = 0; i < size; i++) {
				if (val > avg) {
					hide = false;
					break;
				}
			}
			if (hide) {
				XSSFRow row = (XSSFRow) c.getRow();
				row.getCTRow().setHidden(hide);
			}
		}
	}
}

Below Average

Below average means, you have some values on multiple rows and you calculate average of those values, then you show those rows which are having below average values.

List<Integer> nums = new ArrayList<>();

boolean skipFirstRow = true;
for (Row r : sheet) {
	if (skipFirstRow) {
		skipFirstRow = false;
		continue;
	}
	for (Cell c : r) {
		if (columnNumber == c.getColumnIndex()) {
			nums.add((int) c.getNumericCellValue());
		}
	}
}

int sum = 0;
for (Integer integer : nums) {
	sum += integer;
}

double avg = sum / nums.size();

int size = nums.size();
skipFirstRow = true;
for (Row r : sheet) {
	if (skipFirstRow) {
		skipFirstRow = false;
		continue;
	}
	for (Cell c : r) {
		if (columnNumber == c.getColumnIndex()) {
			boolean hide = true;
			double val = (int) c.getNumericCellValue();
			for (int i = 0; i < size; i++) {
				if (val < avg) {
					hide = false;
					break;
				}
			}
			if (hide) {
				XSSFRow row = (XSSFRow) c.getRow();
				row.getCTRow().setHidden(hide);
			}
		}
	}
}

Testing the Application

You will get different excel file output for each of the above operations. You can download the sample input and output files from the source code.

Source Code

Download

Thanks for reading.

Tags:

Leave a Reply

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