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

John Yeung's points are valid - but even Python's xlsxwriter might not do everything you want - they have to select which Excel functionality they will support, just as the POI folks have, and as Scott has further done with his service program.

There is a huge set of docs that give all the stuff available with the Office Open spec - what a certain "renderer" uses is up to them, and they are "supposed" to document that they support. The Numbers app for apple devices is an example - very limited on what it handles.

That is why I started out with what Microsoft itself puts in the various XML files - lots of stuff not REALLY needed, of course, MS is wont to do.

BTW, there is and Excel tool on easy400.net - the CGIDEV2 site. It uses Scott's service program, I believe, which goes back to POI - it uses a description language, I believe. I think that approach is intrinsically slow, due to all the Java method calls from RPG - my CGIDEV2 approach does offer much faster performance, at the cost of maybe more programmer effort up front. Isn't that a trade-off we often accept?

Cheers
Vern

On 10/25/2019 8:05 AM, John Rusling via RPG400-L wrote:

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.