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

Hi John

At COMMON I give a couple presentations on using CGIDEV2 to create XLSX spreadsheets, including the packaging.

There is also a lab, I have materials for setting it up on your system.

performance is good, creating the templates can take time, esp. when someone wants things changed. Never happens, right? unless at Area 51!

I'll send it when I get to work.

Cheers
Vern

On 10/25/2019 7:26 AM, John Rusling via RPG400-L wrote:

Hey John

I thought there should be a way to do the pre-selection - so I looked at
the contents of the sheet XML that is in the XLSX file - I assume you
are creating the new format, not the Excel 95=98 format.

So an XLSX file is a ZIPped archive of upwards of 30 files, most are XML
content.

I remember hearing this awhile back, and thinking it'd be a neat way to do
spreadsheets.
After reading this i opened my generated report, put a single filter for
Facility '75' on the filter,
saved it, then copied it from file.xlsx to file.zip. Then doubleclicking
the file.zip revealed the structure you refer to via
By searching for '75' if eventually found the structure you pointed out
below...
Mine did not have the xr:uid string (whatever that is :-)

I made a simple XLSX from running a SELECT statement in the ACS Run SQL
SCripts action, then saved the results.

Then I opened the XLSX file, turned on filters, and saved it with a new
name.

Then I for value TX in column 5 and saved the Excel file again, with a
new name.

Then I opened each of the files with filter using 7-Zip - just
right-click the file and select to open the archive.

In each archive there will be an XL directory with a Worksheets
directory in that. In the latter is the sheet1.xml - and here are the
contents related to filters -

For just turning the filters on, this is near the end -

<autoFilter ref="A1:K13"
xr:uid="{5C638613-BD28-4959-90CB-6DA09179BBE6}"/>

I don't know what the xr:uid thing is about, Microsoft doesn't add
that. But the ref is the range that is filtered.

Now for the sheet when I selected something, here it "autoFilter" node -
<autoFilter ref="A1:K13"

xr:uid="{5C638613-BD28-4959-90CB-6DA09179BBE6}">

<filterColumn colId="5">
<filters>
<filter val="TX"/>
</filters>
</filterColumn>
</autoFilter>

So the values ARE there - maybe the function in the service program has
the ability to add this, but maybe not - POI tends to do exactly 1 thing
or level, nested stuff has to be done on its own. Every attribute of a
cell uses a separate method, for example.

Here a child of <autoFilter> would be <filterColumn>, a child of that
would be <filters>, and actual filters would be child of that.

Yes, nested stuff -- and that really gets into more prototyping (and
understanding) of the java stuff than i want to bite off right now.

I hope this can lead to success! Personally I do it all with CGIDEV2,
using MS' output as models for templates - there is almost no
functionality limit other than how hard I want to work at it. I still
have not worked at pivot tables! Far too many internal objects for

those.

Reverse engineering -- paying off. And why not, the govt does it with
alien tech. . . <g>

Regards
Vern

Vern, thanks for the good explanation.
If you're doing it all with cgidev2, i can understand that.
I'm guessing it might be faster for some things too and like you said...
'no limit other than how hard I want to work at it'.

I have used cgidev2 in the past.
It's the Assembling and Disassembling the .xlsx file programatically that
i have no experience or tool
to accomplish.

I'd be interested in how you, or others go about this?
Thanks,
John

(but the message looked so clear and grammatically correct when i sent it.
. . . :-)
<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.