Detect or remove auto filter

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Detect or remove auto filter

Samuel Stein
Hello folks,

i have a question regarding Auto Filter. As far as i can see POI
supports right now only setting an auto filter.

Is there a way to detect if the spreadsheet has an active auto filter
set and/or remove auto filter?

Thanks in advance


Samuel

--
Diese E-Mail könnte vertrauliche und/oder rechtlich geschützte Informationen enthalten.
Wenn Sie nicht der richtige Adressat sind oder diese E-Mail irrtümlich erhalten haben,
informieren Sie bitte sofort den Absender und vernichten Sie diese Mail
Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser Mail sind nicht gestattet.

This e-mail may contain confidential and/or privileged information
If you are not the intended recipient (or have received this e-mail in error)
please notify the sender immediately and destroy this e-mail
Any unauthorised copying, disclosure or distribution of the material in this
e-mail is strictly forbidden.


GA Gebäude Automatisierung GmbH
Technisches Büro Berlin
Bundesallee 89
12161 Berlin

Amtsgericht Dortmund, HRB 9327
Geschäftsführer Rüdiger Plett
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Detect or remove auto filter

Greg Woolsey
You have to go down to the CT* classes for that info, POI doesn't have a
high-level API for it yet.  Note this is a per-sheet setting, not
per-workbook.

XSSFWorksheet xw = getTheWorksheet();
if (xw.getCTWorksheet().isSetAutoFilter()) {
  CTAutoFilter af = xw.getCTWorksheet().getAutoFilter();
}

You can then use it's ref property directly and parse it as a cell range
reference, or go look for the built-in named range that matches it (or
could be for a different advanced filter, see the JavaDoc):

org.apache.poi.xssf.usermodel.XSSFName.BUILTIN_FILTER_DB

All this can be inferred by following the source code in
XSSFSheet.setAutoFilter(CellRangeAddress)


On Thu, Jul 20, 2017 at 6:05 AM Samuel Stein <[hidden email]> wrote:

> Hello folks,
>
> i have a question regarding Auto Filter. As far as i can see POI
> supports right now only setting an auto filter.
>
> Is there a way to detect if the spreadsheet has an active auto filter
> set and/or remove auto filter?
>
> Thanks in advance
>
>
> Samuel
>
> --
> Diese E-Mail könnte vertrauliche und/oder rechtlich geschützte
> Informationen enthalten.
> Wenn Sie nicht der richtige Adressat sind oder diese E-Mail irrtümlich
> erhalten haben,
> informieren Sie bitte sofort den Absender und vernichten Sie diese Mail
> Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser Mail sind
> nicht gestattet.
>
> This e-mail may contain confidential and/or privileged information
> If you are not the intended recipient (or have received this e-mail in
> error)
> please notify the sender immediately and destroy this e-mail
> Any unauthorised copying, disclosure or distribution of the material in
> this
> e-mail is strictly forbidden.
>
>
> GA Gebäude Automatisierung GmbH
> Technisches Büro Berlin
> Bundesallee 89
> 12161 Berlin
>
> Amtsgericht Dortmund, HRB 9327
> Geschäftsführer Rüdiger Plett
>
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Detect or remove auto filter

Samuel Stein
Hello Greg,

thanks for the fast answer and the good example.

I forgot to mention that i have an .xls file and not an .xlsx.
Is there also an internal api for auto filter?


Diese E-Mail könnte vertrauliche und/oder rechtlich geschützte Informationen enthalten.
Wenn Sie nicht der richtige Adressat sind oder diese E-Mail irrtümlich erhalten haben,
informieren Sie bitte sofort den Absender und vernichten Sie diese Mail
Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser Mail sind nicht gestattet.

This e-mail may contain confidential and/or privileged information
If you are not the intended recipient (or have received this e-mail in error)
please notify the sender immediately and destroy this e-mail
Any unauthorised copying, disclosure or distribution of the material in this
e-mail is strictly forbidden.

On 20.07.2017 23:51, Greg Woolsey wrote:

> You have to go down to the CT* classes for that info, POI doesn't have a
> high-level API for it yet.  Note this is a per-sheet setting, not
> per-workbook.
>
> XSSFWorksheet xw = getTheWorksheet();
> if (xw.getCTWorksheet().isSetAutoFilter()) {
>    CTAutoFilter af = xw.getCTWorksheet().getAutoFilter();
> }
>
> You can then use it's ref property directly and parse it as a cell range
> reference, or go look for the built-in named range that matches it (or
> could be for a different advanced filter, see the JavaDoc):
>
> org.apache.poi.xssf.usermodel.XSSFName.BUILTIN_FILTER_DB
>
> All this can be inferred by following the source code in
> XSSFSheet.setAutoFilter(CellRangeAddress)
>
>
> On Thu, Jul 20, 2017 at 6:05 AM Samuel Stein <[hidden email]> wrote:
>
>> Hello folks,
>>
>> i have a question regarding Auto Filter. As far as i can see POI
>> supports right now only setting an auto filter.
>>
>> Is there a way to detect if the spreadsheet has an active auto filter
>> set and/or remove auto filter?
>>
>> Thanks in advance
>>
>>
>> Samuel
>>
>> --
>> Diese E-Mail könnte vertrauliche und/oder rechtlich geschützte
>> Informationen enthalten.
>> Wenn Sie nicht der richtige Adressat sind oder diese E-Mail irrtümlich
>> erhalten haben,
>> informieren Sie bitte sofort den Absender und vernichten Sie diese Mail
>> Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser Mail sind
>> nicht gestattet.
>>
>> This e-mail may contain confidential and/or privileged information
>> If you are not the intended recipient (or have received this e-mail in
>> error)
>> please notify the sender immediately and destroy this e-mail
>> Any unauthorised copying, disclosure or distribution of the material in
>> this
>> e-mail is strictly forbidden.
>>
>>
>> GA Gebäude Automatisierung GmbH
>> Technisches Büro Berlin
>> Bundesallee 89
>> 12161 Berlin
>>
>> Amtsgericht Dortmund, HRB 9327
>> Geschäftsführer Rüdiger Plett
>>


Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Detect or remove auto filter

pj.fanning
Hi Samuel,
getCTWorksheet only works for xlsx files as it relies on the fact that underlying xlsx data is in XML files in ooxml format.
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Detect or remove auto filter

Dominik Stadler
Hi,

HSSF/xls are a binary format, you probably need to take a look at the code
in HSSFSheet.setAutoFilter() to see how these are stored in this binary
format.

The Spec [MS-XLS] at
https://msdn.microsoft.com/en-us/library/cc313105%28v=office.12%29.aspx
will contain the canonical description of how these are supposed to be
stored, albeit in a somewhat convoluted and hard to read form.

Dominik.


On Fri, Jul 21, 2017 at 2:59 PM, pj.fanning <[hidden email]> wrote:

> Hi Samuel,
> getCTWorksheet only works for xlsx files as it relies on the fact that
> underlying xlsx data is in XML files in ooxml format.
>
>
>
> --
> View this message in context: http://apache-poi.1045710.n5.
> nabble.com/Detect-or-remove-auto-filter-tp5728276p5728279.html
> Sent from the POI - User mailing list archive at Nabble.com.
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]
>
>
Loading...