Re: How to apply filter to a sheet in RPG. (HSSFR4, POI, Spreadsheet)

Now, as someone who doesn't use POI at all, but who does work with
Excel spreadsheets quite a lot, I'd like to mention that while turning
on autofilter is easy, prepopulating a filter with specific values may
or may not be.

I'm getting that feeling. :-)

The reason is that selecting values from the filter and hiding rows
based on those selections are two different and independent things. It
is conceivable that POI does some kind of magic to hide the rows for
you, based on your selections, but that would be pretty impressive,
because it would be a TON of work to implement that for the general
case. (Remember, in the general case, the cells you are filtering on
could contain arbitrarily complex formulas, and the library would have
to evaluate those formulas programmatically first, to know which rows
to hide, and thus it would need to essentially replicate Excel's
entire calculation engine, just to *fully* implement filtering. In the
general case. Does POI do this? I don't know. It is a gigantic piece
of software, so maybe. But I kind of doubt it.)

Yep.
I examined a file.xlsx after copying it to file.zip and doubleclicking it,
opening
it in win explorer. Based on the filter i had applied before copying it i
saw that
the optional hidden attribute for the row element -
is NOT there for rows matching the filter
is there - ie: hidden="1" - for rows NOT matching the filter.

So if you're set on making specific filter selections for your users,
most likely you will either need to put it on them to "accept" the
choices (i.e. when they open the workbook, the filter selections will
be checked off, but all the rows will be visible until they take
manual action), or you will have to hide rows in your program as you
go. (Your program of course does NOT need the full power of Excel's
calculation engine if you know the exact values that you're writing as
you're writing them.)

Well put.

And now, my standard plug: For people who are NOT already using POI
(so, not OP, but maybe lurkers who are also reading along), the "easy
way" is to use something other than POI. Specifically, the various
Excel packages for the open source languages on PASE tend to be much
easier to use than POI, especially POI through RPG. (If you already
know Java, then your first option is probably to just use POI directly
from Java.)

I'm a big fan of XlsxWriter for Python. Its documentation has a pretty
clear explanation of filtering and thorough examples to illustrate the
concepts in action. I've also got this short demo program:

https://gist.github.com/jkyeung/3ca2fe882d4997365460f622e4fa17d0

John Y.

Thanks John, and thanks for the autofilter_demo_001.py example program.

I did attend the Hands On Python Workshop at RPG DB2 Summit in Minneapolis
last week.

This included a (mostly pre-built) simple python example, that used
xlsxwriter to make a spreadsheet.

I'm in the process of getting our DEV system setup (by our admin), similar
to the Summit, then hope to do
some python.

John R.
<br />
The information in this email is confidential and may be legally privileged.
It is intended solely for the addressee. Access to this email by anyone else is
unauthorized. If you are not the intended recipient, any disclosure, copying,
distribution or any action taken or omitted to be taken in reliance on it, is
prohibited and may be unlawful.