Spire.XLS is a professional Excel API that enables developers to create, manage, manipulate, convert and print Excel worksheets. Get free and professional technical support for Spire.XLS for .NET, Java, Android, C++, Python.

Thu Jun 10, 2021 8:13 am

Hi,

In Java,
Once I have the filtered rows using the command filters.filter() instead of saving the result to a workbook
I need to iterate over each of the filtered rows and then print to console or get all the CELL values for each row

How can I do that?

piyush4360
 
Posts: 3
Joined: Thu Jun 10, 2021 8:04 am

Thu Jun 10, 2021 10:46 am

Hello,

Please refer to the following code to meet your requirement.
Code: Select all
        Workbook workbook = new Workbook();
        workbook.loadFromFile("data/CreateFilter.xlsx");
        Worksheet sheet = workbook.getWorksheets().get(0);
        AutoFiltersCollection filters = sheet.getAutoFilters();
        sheet.getAutoFilters().filterTop10(0,true,true,10);
        filters.filter();

        for(int i=0;i<sheet.getRows().length;i++){
            if(sheet.isRowVisible(i+1) == true){
                for(int j=0;j<sheet.getColumns().length;j++){
                    if(sheet.isColumnVisible(j+1) == true){
                        System.out.print(sheet.get(i+1,j+1).getValue()+";");
                    }
                }
                System.out.println();
            }
        }
        workbook.saveToFile("output/CreateFilter2.xlsx", ExcelVersion.Version2010);


If there are any other issues related to our products, please feel free to contact us.

Sincerely,
Brian
E-iceblue support team
User avatar

Brian.Li
 
Posts: 1271
Joined: Mon Oct 19, 2020 3:04 am

Thu Jun 10, 2021 11:59 am

Thank you for thesolution

I had one more question.

Suppose my row has a cell value "a,b" . Now either I want to filter on "a" or "b", it should bring the same row after applying filter. Is this possible?

piyush4360
 
Posts: 3
Joined: Thu Jun 10, 2021 8:04 am

Fri Jun 11, 2021 1:49 am

Hello,

Thanks for your feedback.
Our Spire.XLS is based on Microsoft Excel. Even in Excel, it will filter based on the value of the entire cell. Sorry that our Spire.XLS does not support the feature as you described. Hope you can understand.
If there are any other issues related to our products, please feel free to contact us.

Sincerely,
Brian
E-iceblue support team
User avatar

Brian.Li
 
Posts: 1271
Joined: Mon Oct 19, 2020 3:04 am

Fri Jun 11, 2021 4:11 am

Hello,

I see it is present in MS excel. Let me show you what I intend

My excel column tag has value "quick,smoke".

Code: Select all
Sno     tag                   locale
1     quick,smoke   en-AU


Now if I click on Filters on column Tag and click Text filters -> Contains -> Within the custom autofilter popup you either select "quick" or "smoke", it will now yield the same row result.


I want the similar behavior in Java using spire.xls

piyush4360
 
Posts: 3
Joined: Thu Jun 10, 2021 8:04 am

Fri Jun 11, 2021 10:37 am

Hello,

Thanks for your clarification.
Sorry to tell you that our Spire.XLS does not support this feature currently. But we have added it as a new feature into our upgrade list. If it is implemented in the future, we will inform you immediately.

Sincerely,
Brian
E-iceblue support team
User avatar

Brian.Li
 
Posts: 1271
Joined: Mon Oct 19, 2020 3:04 am

Fri Jul 16, 2021 6:26 am

Hello,

Thanks for your patience.
After further investigation, we found that you can use the following code to meet your requirement.
Code: Select all
        sheet.getAutoFilters().customFilter(1, FilterOperatorType.Equal,"*quick*");
        sheet.getAutoFilters().customFilter(1, FilterOperatorType.Equal,"*smoke*");

If this is not you want, Please provide your input file and you desired output for further investigation. You can send them to us (support@e-iceblue.com) via email, thanks in advance.

Sincerely,
Brian
E-iceblue support team
Last edited by Brian.Li on Fri Sep 17, 2021 7:42 am, edited 1 time in total.
User avatar

Brian.Li
 
Posts: 1271
Joined: Mon Oct 19, 2020 3:04 am

Tue Jul 20, 2021 9:55 am

Hello,

Greetings from E-iceblue support.
Has your issue been resolved? Did the code we provided work for you? Any feedback will be greatly appreciated.

Sincerely,
Brian
E-iceblue support team
User avatar

Brian.Li
 
Posts: 1271
Joined: Mon Oct 19, 2020 3:04 am

Return to Spire.XLS