How to delete all the filtered rows using aspose cells for java

Aspose.Cells Product Family
1

Hi,

I am filtering 1st column in excel sheet using below code :

AutoFilter autoFilter = ws_sheet.getAutoFilter();
autoFilter.setRange(“A1:P1”);
autoFilter.addFilter(0, “0”);

Now I want to delete these filtered rows from the sheet. If I write below line :
ws_sheet.getCells().deleteRows(1,ws_sheet.getCells().getMaxDataRow(),true);

It is deleting all the rows along with filtered rows (deletes the hidden rows).

getMaxDataRow() considers the rows which is hidden also .

How can I delete only the filtered rows using aspose cells?

Thanks,
Sowmya
2

Hi,

Thanks for your posting and using Aspose.Cells.

Once you have applied the filter, then you will have to delete hidden rows one by one. You can check if a particular row is hidden or not using the Row.isHidden() method. If it returns true, then delete the row and if it returns false, then do not delete the row.

3

<span style=“font-size:12.0pt;font-family:“Times New Roman”,“serif”;
mso-fareast-font-family:“Times New Roman”;mso-ansi-language:EN-US;mso-fareast-language:
EN-US;mso-bidi-language:AR-SA”>Hi ,

<span style=“font-size:12.0pt;font-family:“Times New Roman”,“serif”;
mso-fareast-font-family:“Times New Roman”;mso-ansi-language:EN-US;mso-fareast-language:
EN-US;mso-bidi-language:AR-SA”>Thanks for the reply .
<span style=“font-size:12.0pt;font-family:“Times New Roman”,“serif”;
mso-fareast-font-family:“Times New Roman”;mso-ansi-language:EN-US;mso-fareast-language:
EN-US;mso-bidi-language:AR-SA”>
<span style=“font-size:12.0pt;font-family:“Times New Roman”,“serif”;
mso-fareast-font-family:“Times New Roman”;mso-ansi-language:EN-US;mso-fareast-language:
EN-US;mso-bidi-language:AR-SA”>I tried with that condition as below :
<span style=“font-size:12.0pt;font-family:“Times New Roman”,“serif”;
mso-fareast-font-family:“Times New Roman”;mso-ansi-language:EN-US;mso-fareast-language:
EN-US;mso-bidi-language:AR-SA”>
Workbook wb0 = new Workbook(“Input/Book2.xlsx”);
Worksheet ws0 = wb0.getWorksheets().get(0);
AutoFilter autoFilter = ws0.getAutoFilter();
autoFilter.setRange(“A1:P1”);
autoFilter.addFilter(0, “0”);
autoFilter.refresh();
for(int index = 1 ; index < ws0.getCells().getMaxDataRow() + 1 ; index++)
{
Row row = ws0.getCells().getRows().get(index);
if(!row.isHidden())
{
System.out.println(row.getIndex());
ws0.getCells().deleteRow(index);
}
}
autoFilter.refresh();
wb0.save(“Input/Book3.xlsx”);

But it is not deleting all the filtered rows.

Thanks,
Sowmya
4

Attached is the excel sheet .

5

Hi,

Thanks for your posting and using Aspose.Cells.

Please also provide us the Book2.xlsx used in your code so that we run your code at our end and investigate this issue. We will look into it and provide you a correct code. Thanks for your cooperation.

6

Hi,

Attached is the ‘Book2.xlsx’ .
7

Hi,

Thanks for your source excel file and using Aspose.Cells.

You need to delete rows from reverse as shown in this code. I have attached the output excel file generated with this code for your reference. The output excel file looks correct. If it is not, then let us know what is wrong with it so that we could fix it. Let us know your feedback.

Java

Workbook wb0 = new Workbook(“Book2.xlsx”);
Worksheet ws0 = wb0.getWorksheets().get(0);
AutoFilter autoFilter = ws0.getAutoFilter();
autoFilter.setRange(“A1:P1”);
autoFilter.addFilter(0, “0”);
autoFilter.refresh();

//The loop is now reverse
for(int i=ws0.getCells().getMaxDataRow(); i>=1; i–)
{

Row row = ws0.getCells().getRows().get(i);
if(row.isHidden())
{
System.out.println(row.getIndex());
ws0.getCells().deleteRow(i);
}
}

wb0.save(“output.xlsx”);

8

Hi,


It is working fine from the above mentioned logic . Thank you very much.


Thanks,
Sowmya
9

Hi,

Thanks for your feedback and using Aspose.Cells.

It is good to know that your issue is now resolved with the above code and logic. Let us know if you encounter any other issue, we will be glad to look into it and help you further.

10

Which dependency use for delete filter

11

@Ankit_J,

I am not entirely sure about your requirements. But if you want to remove autofilters dropdowns, you may set Range to null using Aspose.Cells APIs:
worksheet.getAutoFilter().setRange(null);

Let us know with details if you need something else. We will check and help you accordingly.

12

@Ankit_J
Please simply use Worksheet.RemoveAutoFilter() to remove filters.