Bug 52660

Summary: Unable to detect auto filter applied on excel sheet.
Product: POI Reporter: Sushil Kumar <sushil.is.online>
Component: HSSFAssignee: POI Developers List <dev>
Severity: normal    
Priority: P2    
Version: 3.7-FINAL   
Target Milestone: ---   
Hardware: PC   
OS: All   
Attachments: test excel sheet with auto filter.
Testing excel sheet
Java sample code

Description Sushil Kumar 2012-02-14 06:27:25 UTC

Comment 1 Sushil Kumar 2012-02-14 06:30:58 UTC
Created attachment 28326 [details]
test excel sheet with auto filter.

I have filtered some rows.
Comment 2 Yegor Kozlov 2012-02-14 06:56:11 UTC
Please explain what is wrong with POI: post sample code you are using, what you expect and what is the actual result. 

Also, please try with the latest POI 3.8-beta5. Many bugs  have been fixed since POI-3.7, involving support for auto-filters. 

Comment 3 Sushil Kumar 2012-02-14 08:30:42 UTC
Created attachment 28327 [details]
Testing excel sheet
Comment 4 Sushil Kumar 2012-02-14 08:31:38 UTC
I am trying to read an excel file (attached) with filter. This excel contains only one workbook. Without implementing any filter, following records will be visible - 
Age	Name	Class
1	qq	fgh
2	qq	fgh
3	as	a
4	fr	a
5	gf	a

When we apply filter on workbook on Name column, and select "qq" as filter, then following records will be visible-
Age	Name	Class
1	qq	fgh
2	qq	fgh

Now, when I am trying to read filtered excel sheet using Apache POI (version 3.7), then my code is able to read all 6 rows (including header). While my understading was because sheet is filtered, only 3 recodrs should be read by POI.

The sample code, I am usinf to read attached excel sheet is also attached.

Comment 5 Sushil Kumar 2012-02-14 08:32:36 UTC
Created attachment 28328 [details]
Java sample code
Comment 6 Dominik Stadler 2015-05-31 22:17:43 UTC
As far as I understood how POI handles auto-filter-settings is that it will allow to read/write the settings, i.e. enable or disable auto-filters, but it will not apply those auto-filters on the data when you are using POI as you still will need to work on all data-items usually. 

The filtering is only applied in Excel when visualizing the content, things like formulas et.al. still use all rows, so POI also needs to.