Bug 52660 - Unable to detect auto filter applied on excel sheet.
Summary: Unable to detect auto filter applied on excel sheet.
Alias: None
Product: POI
Classification: Unclassified
Component: HSSF (show other bugs)
Version: 3.7-FINAL
Hardware: PC All
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on:
Reported: 2012-02-14 06:27 UTC by Sushil Kumar
Modified: 2015-05-31 22:17 UTC (History)
0 users

test excel sheet with auto filter. (23.50 KB, application/vnd.ms-excel)
2012-02-14 06:30 UTC, Sushil Kumar
Testing excel sheet (23.50 KB, application/octet-stream)
2012-02-14 08:30 UTC, Sushil Kumar
Java sample code (3.56 KB, text/plain)
2012-02-14 08:32 UTC, Sushil Kumar

Note You need to log in before you can comment on or make changes to this bug.
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.