Excel AutoFilter in Java
GemBox.Spreadsheet for Java has been retired
Following example shows how to filter rows in a specific cell range of an Excel worksheet with Java.
Filtered rows are hidden in all output file formats. The active ExcelWorksheet.getFilter
is written only to XLSX file format.

import com.gembox.spreadsheet.*;
import java.time.LocalDateTime;
import java.util.Random;
class Program {
public static void main(String[] args) throws java.io.IOException {
// If using the Professional version, put your serial key below.
SpreadsheetInfo.setLicense("FREE-LIMITED-KEY");
ExcelFile workbook = new ExcelFile();
ExcelWorksheet worksheet = workbook.addWorksheet("Filtering");
int rowCount = %RowCount%;
// Specify sheet formatting.
worksheet.getRow(0).getStyle().getFont().setWeight(ExcelFont.BOLD_WEIGHT);
worksheet.getColumn(0).setWidth(3, LengthUnit.CENTIMETER);
worksheet.getColumn(1).setWidth(3, LengthUnit.CENTIMETER);
worksheet.getColumn(2).setWidth(3, LengthUnit.CENTIMETER);
worksheet.getColumn(2).getStyle().setNumberFormat("[$$-409]#,##0.00");
worksheet.getColumn(3).setWidth(3, LengthUnit.CENTIMETER);
worksheet.getColumn(3).getStyle().setNumberFormat("yyyy-mm-dd");
CellRange cells = worksheet.getCells();
// Specify header row.
cells.get(0, 0).setValue("Departments");
cells.get(0, 1).setValue("Names");
cells.get(0, 2).setValue("Salaries");
cells.get(0, 3).setValue("Deadlines");
// Insert random data to sheet.
Random random = new Random();
String[] departments = new String[] { "Legal", "Marketing", "Finance", "Planning", "Purchasing" };
String[] names = new String[] { "John Doe", "Fred Nurk", "Hans Meier", "Ivan Horvat" };
for (int i = 0; i < rowCount; ++i) {
cells.get(i + 1, 0).setValue(departments[random.nextInt(departments.length)]);
cells.get(i + 1, 1).setValue(names[random.nextInt(names.length)] + ' ' + (i + 1));
cells.get(i + 1, 2).setValue((random.nextInt(91) + 10) * 100);
cells.get(i + 1, 3).setValue(LocalDateTime.now().plusDays(random.nextInt(3) - 1));
}
// Specify range which will be filtered.
CellRange filterRange = worksheet.getCells().getSubrangeAbsolute(0, 0, rowCount, 3);
// Show only rows which satisfy following conditions:
// - 'Departments' value is either "Legal" or "Marketing" or "Finance" and
// - 'Salaries' value is in the top 20 percent of all 'Salaries' values and
// - 'Deadlines' value is today's date.
// Shown rows are then sorted by 'Salaries' values in the descending order.
filterRange.filter().
byValues(0, "Legal", "Marketing", "Finance").
byTop10(2, true, true, 20).
byDynamic(3, DynamicFilterType.TODAY).
sortBy(2, true).
apply();
workbook.save("Filtering.%OutputFileType%");
}
}
See also
Next steps
Published: December 13, 2018 | Modified: April 28, 2023 | Author: Marek Turis