Apply Advanced Filter of Microsoft Excel to Display Records Meeting Complex Criteria

Possible Usage Scenarios

Microsoft Excel allows you to apply Advanced Filter on worksheet data to display records that meet complex criteria. You can apply Advanced Filter with Microsoft Excel via its Data > Advanced command as shown in this screenshot.

Aspose.Cells also allows you to apply the Advanced Filter using the Worksheet.advancedFilter() method. Just like Microsoft Excel, it accepts the following parameters.

isFilter

Indicates whether filtering the list in place.

listRange

The list range.

criteriaRange

The criteria range.

copyTo

The range where copying data to.

uniqueRecordOnly

Only displaying or copying unique rows.

Apply Advanced Filter of Microsoft Excel to Display Records Meeting Complex Criteria

The following sample code applies the advanced filter on the Sample Excel File and generates the Output Excel File. The screenshot shows both files for comparison. As you can see inside the screenshot, data has been filtered inside the output Excel file according to complex criteria.

Sample Code

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java

// Load your source workbook
Workbook wb = new Workbook(srcDir + "sampleAdvancedFilter.xlsx");

// Access first worksheet
Worksheet ws = wb.getWorksheets().get(0);

// Apply advanced filter on range A5:D19 and criteria range is A1:D2
// Besides, we want to filter in place
// And, we want all filtered records not just unique records
ws.advancedFilter(true, "A5:D19", "A1:D2", "", false);

// Save the workbook in xlsx format
wb.save(outDir + "outputAdvancedFilter.xlsx", SaveFormat.XLSX);