How to work with filtered results

Aspose.Cells Product Family
1
Hi, In my work i am using cells-2.3.1.jar. I want to filter columns and work with those filtered values, without save to file and reload it, just working in memory

This is my code:

Worksheet worksheet = workbook.getWorksheets().getSheet(0);
AutoFilter autoFilter = worksheet.getAutoFilter();
autoFilter.filter(2, nameValue);
autoFilter.filter(4, countryValue);

// At this point if I insert workbook.save("file.xls"), this file contains the right values
//But I don't need and don´t want to write to disk


//However shows all the values

Cells cells = worksheet.getCells();
for (int fila = 1; fila <= cells.getMaxRow(); fila++) {
System.out.println("Name: " + ((String) cells.getCell(fila,2).getValue()).trim());
System.out.println("Country: " + ((String) cells.getCell(fila, 4).getValue()).trim());
}


Sorry for my english.
Thanx in advance.
2

Hi Lucas,

Thanks for your posting and using Aspose.Cells.

After refreshing the AutoFilter, you should check if the row is hidden while iterating the rows. If the rows are hidden, then do not print the values and if they are visible, then print their values.

You can check if the row is hidden or not using Cells.isRowHidden() method.

Please see the following code and its console output for your reference. I have also attached the source Excel file used in this code for you to view. I have tested this code on the latest version: Aspose.Cells for Java (Latest Version).

Java


String filePath = “F:\Shak-Data-RW\Downloads\source.xlsx”;


Workbook workbook = new Workbook(filePath);


Worksheet worksheet = workbook.getWorksheets().get(0);


AutoFilter af = worksheet.getAutoFilter();


af.filter(1, “China”);

af.refresh();


Cells cells = worksheet.getCells();


for (int fila = 1; fila <= cells.getMaxRow(); fila++) {


if (cells.isRowHidden(fila) == false) {

System.out.println("Name: " + cells.get(fila, 0).getStringValue());

System.out.println("Country: " + cells.get(fila, 1).getStringValue());

}//if


}//for


Console Output:
Name: Li
Country: China
Name: Azx
Country: China

3

Thank you Shakeel


I add the hidden check.
I think that the issue is the version i use does not contain the method refresh on Autofilter class.
Then seems do not apply the filter. My updated code is:

Worksheets ws = workbook.getWorksheets();
Worksheet worksheet = ws.getSheet(0);

Cells cells = worksheet.getCells();

AutoFilter autoFilter = ws.getSheet(0).getAutoFilter();
autoFilter.filter(2, nameValue);
autoFilter.filter(4, countryValue);

for (fila = 1; fila <= cells.getMaxDataRow(); fila++) {
if (cells.checkRow(fila).isHidden() == false) {
String contrib = ((String) cells.getCell(fila, 2).getValue()).trim();
String conce = ((String) cells.getCell(fila, 4).getValue()).trim();
}
}

Do you know some workaround?

Thx
4

Hi Lucas,

Thanks for your posting and using Aspose.Cells.

Please use AutoFilter.applyFilter() method which will apply or refresh the filter. I have tested the following code with the older version and it worked fine.

I used the same source.xlsx file as used in the previous code.

Java - Old Version


String filePath = “F:\Shak-Data-RW\Downloads\source.xlsx”;


Workbook workbook = new Workbook();

workbook.open(filePath);


Worksheet worksheet = workbook.getWorksheets().getSheet(0);

Cells cells = worksheet.getCells();


AutoFilter autoFilter = worksheet.getAutoFilter();


autoFilter.filter(1, “China”);

autoFilter.applyFilter();


for (int fila = 1; fila <= cells.getMaxRow(); fila++) {


if (cells.isRowHidden(fila) == false) {

System.out.println("Name: " + cells.getCell(fila, 0).getStringValue());

System.out.println("Country: " + cells.getCell(fila, 1).getStringValue());

}//if


}//for


Console Output:
Name: Li
Country: China
Name: Azx
Country: China