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

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 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.

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.

Regards
Vern

On 10/24/2019 8:51 AM, John Rusling via RPG400-L wrote:

Vern, thanks.

Right. I have some additional things i've aggregated/found-on-list/etc
thru the years but have not incorporated them into his srvpgm.
When needed, i copy them into the program.

The filter Don pointed out is one of them. (thanks to original? poster) I
love putting the filters on columns/ranges. . . I'd just like to be able
to tweak their contents.
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.
(kinda like going all free code)

It'd be a nice to have (someday). My starting point being this list and.
. if you don't ask, you'll never know.

One of the usual gurus on the list pointed out the steps to do what you
indicate below, (in a reply a few years back). I still have them, maybe
i'll dig them out when this reaches critical mass with me.
(if i do i'll share)

Just thought i'd try the easy way first. :-)

John


<snip>
Hi John

Since you're using Scott's POI service program, right? It can do only
the things from POI that Scott elected to include.

POI has many other functions that are not yet in the SRVPGM, and you can
certainly add what you need to it. Once added, you could share it, to
the benefit of all.

It isn't too complicated to add things there - as I recall, all the
calls to Java methods are similar in design.

Good luck!
Vern
</snip>
<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.