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
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
Thanks for reading.