× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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.


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.