Maybe this could help other people, so this the solution I came up with before this answer.
Take in consideration that I am not very good with Java so the following code can surely be optimized.
I implemented a filter by myself, to do so, I created 3 classes :
ExcelWorksheetFilterFilterRuleFilterRuleOperation
ExcelWorksheetFilter
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.util.ArrayList;
import java.util.List;
public class ExcelWorksheetFilter {
private List<FilterRule> ruleList = new ArrayList<>();
private CellRangeAddress cellRange;
private XSSFSheet worksheet;
private XSSFWorkbook workbook;
public ExcelWorksheetFilter(XSSFWorkbook workbook, int worksheetId) {
this.workbook = workbook;
this.worksheet = workbook.getSheetAt(worksheetId);
}
/**
* Apply rules of ruleList to the worksheet.
* The row is put in the result if at least one rule match.
*/
public void apply(){
for(int rowId = cellRange.getFirstRow(); rowId <= cellRange.getLastRow(); rowId++){
worksheet.getRow(rowId).getCTRow().setHidden(true);
for(FilterRule rule : ruleList){
if(rule.match(worksheet.getRow(rowId))){
worksheet.getRow(rowId).getCTRow().setHidden(false);
break;
}
}
}
}
/**
* Apply rules of ruleList to the worksheet.
* The row is put in the result if every rules match.
*/
public void applyStrict(){
for(int rowId = cellRange.getFirstRow(); rowId <= cellRange.getLastRow(); rowId++){
worksheet.getRow(rowId).getCTRow().setHidden(false);
for(FilterRule rule : ruleList){
if(!rule.match(worksheet.getRow(rowId))){
worksheet.getRow(rowId).getCTRow().setHidden(true);
break;
}
}
}
}
public List<Row> getRowList(){
List<Row> rowList = new ArrayList<>();
for(int rowId = cellRange.getFirstRow(); rowId <= cellRange.getLastRow(); rowId++){
if(!worksheet.getRow(rowId).getCTRow().getHidden()){
rowList.add(worksheet.getRow(rowId));
}
}
return rowList;
}
public void addRule(FilterRule rule) {
this.ruleList.add(rule);
}
// Getters and setters omitted...
}
FilterRule
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.xssf.usermodel.XSSFRow;
public class FilterRule {
private final static DataFormatter df = new DataFormatter();
private Integer columnId;
private String[] values;
private FilterRuleOperation operator;
public FilterRule(Integer columnId, FilterRuleOperation operator, String[] values){
this.columnId = columnId;
this.operator = operator;
this.values = values;
}
/**
* If at least one of the value matches return true.
* @param row The row to match
* @return a boolean
*/
public boolean match(XSSFRow row){
for(String value : values){
if(operator.match(df.formatCellValue(row.getCell(columnId)), value)){
return true;
};
}
return false;
}
}
FilterRuleOperation
public enum FilterRuleOperation {
DIFFERENT("!="){
@Override public boolean match(String x, String y){
return !x.equals(y);
}
},
EQUAL("=="){
@Override public boolean match(String x, String y){
return x.equals(y);
}
};
private final String text;
private FilterRuleOperation(String text) {
this.text = text;
}
public abstract boolean match(String x, String y);
@Override public String toString() {
return text;
}
}
Then you can use it almost like described the OP.
For example with this Excel file:
And this code:
public void parseExcelFile(XSSFWorkbook myExcelFile) {
XSSFSheet worksheet = myExcelFile.getSheetAt(1);
// Create the filter
ExcelWorksheetFilter excelWorksheetFilter = new ExcelWorksheetFilter(myExcelFile, 0);
excelWorksheetFilter.setCellRange(new CellRangeAddress(
1, // Exclude the row with columns titles
worksheet.getLastRowNum(),
0,
worksheet.getRow(0).getPhysicalNumberOfCells()-1
));
// Create rules for filtering
excelWorksheetFilter.addRule(new FilterRule(
1, // Last name column
FilterRuleOperation.EQUAL,
new String[]{"Doe"}
));
excelWorksheetFilter.addRule(new FilterRule(
0, // First name column
FilterRuleOperation.EQUAL,
new String[]{"Jhon"}
));
// Apply with applyStrict function puts a AND condition between rules
excelWorksheetFilter.applyStrict();
// You can also use apply function it puts a OR condition between rules
// excelWorksheetFilter.apply();
excelWorksheetFilter.getRowList().forEach(row -> {
for(int i = 0; i <3; i++) {
System.out.print(df.formatCellValue(row.getCell(i)) + '\t');
}
System.out.println();
});
// Save the file
FileOutputStream out = new FileOutputStream("filter_test.xlsx");
excelWorksheetFilter.getWorkbook().write(out);
out.close();
excelWorksheetFilter.getWorkbook().close();
}
This will print:
Jhon Doe 25
And if you use excelWorksheetFilter.apply() it will print:
Jhon Doe 25
Aaa Doe 22
Jhon Smith 30
The two main downside are:
- It doesn't use the Excel filter so it is harder to use the Excel file afterward.
- Not memory efficient as the
ExcelWorksheetFilter.getRowList() function return a list and not an Iterator.
Also it only works with strings but I suppose that it could be adapted to work with other type of data.
