Heck, i love outputting to spreadsheets. After having to worry about
field lengths and fitting things on DDS reports etc, it's so liberating to
NOT have to worry about such things.
Indeed. And most users prefer spreadsheets too, so it's a win all around.
Just thought i'd try the easy way first. :-)
Always worth a try!
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.
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.)
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.)
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.
midrange.com
