× 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 Darren

We use a method I was able to put together here a few years ago that uses functions from CGIDEV2 in RPGLE to do almost anything you have the courage, sometimes, to do - it's based on an original workbook created in Excel, as Jon described in one of his posts.

I've given presentations on this and had open labs at COMMON to help get going in the methodology.

The documentation for the various Excel formats exist - the 2003 XML Spreadsheet format is out there, search on that, although it is old and open to confusion.

The XLSX format is described in documentation for the Office Open XML specification - it is official - and huge! You CAN roll your own - SEQUEL does, albeit limited to data dump with appropriate cell formats based on IBM i data types, I think.

I just ran across a Powerpoint introduction to the SpreadsheetML markup language used for XLSX -

http://download.microsoft.com/download/3/E/3/3E3435BD-AA68-4B32-B84D-B633F0D0F90D/SpreadsheetMLBasics.ppt

Regards
Vern

On 12/16/2020 8:55 AM, Darren Strong wrote:
Thank you Vern,

I anticipated that the XML extension was going to be an issue, and I hadn't found a resolution for that yet. Also, knowing that it's an older format means I'll probably reject that method.

So, here is my current view of the state of Excel generation:

- There are lots of tools to take a file and create a spreadsheet, but, there isn't much flexibility in file dumps.
- There are languages like C, Python and Java that have pseudo native access to Excel generation through "libraries", but, I'm programming in RPG, so, I have to use inefficient interfaces to access those tools.

In conclusion, what I want does not exist, and that is a way to generate flexible Excel output from RPG without using an inefficient interface to another language. There isn't much documentation around the Excel XML format, even if I was to generate my own methods, because it's complicated, and most people would say, why re-invent the wheel when C, Python, and Java have tools already.

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Vernon Hamberg
Sent: Wednesday, December 16, 2020 9:23 AM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: Excel by XML generation

CAUTION: This email originated from outside of the organization. Do not click links or open attachments unless you recognize the sender and know the content is safe.


Hi Darren

I don't know which Scott went with but can speak to the difference - the XML-only version is the 2003 XML Spreadsheet format that is pretty much not in use anymore - several of Jon and Susan's early examples used this, since it's easy to work with just that one XML file. It has several limitations, like, so far as I know, not allowing for images.
You CAN do multiple sheets, though.

It does usually have the XML extension, which, we found, was confusing to our users. I need tried changing the extension to XLS - or maybe I did - the result might have been a message about content not matching the file type - I am not sure, though - memory leaks regularly around here.

Regards
Vern

On 12/16/2020 7:49 AM, Darren Strong wrote:
Scott,

Looks like there are two methods to generate XML that Excel will open. One is to generate one XML which is opened directly, or to generate the xlsx format, which is basically a bunch of XML files zipped into a container. Do you recall which method you chose and if you/they looked at both options?

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of
Scott Klement
Sent: Tuesday, December 15, 2020 4:55 PM
To: midrange-l@xxxxxxxxxxxxxxxxxx
Subject: Re: Excel by XML generation

CAUTION: This email originated from outside of the organization. Do not click links or open attachments unless you recognize the sender and know the content is safe.


Hi,

Yes, I did that at my previous employer (it was about 9ish years ago).
It was much, much, much faster than POI, used less memory, etc. We had a big Excel project, and POI took hours to generate the spreadsheet, whereas writing XML took only about 30 seconds.

It might even be easier nowadays since we have tools like DATA-GEN.

Unfortunately, since I no longer work there, I don't have access to the code. And, I don't do anything with spreadsheets these days, so haven't tried to write any equivalent in RPG.

You might consider using the tools in other languages.. just about any open source language (not just Java) has tools for making Excel documents. I've used ExcelJs for Node.js -- this works really nicely.
In fact, if I were to write my own RPG Excel tool today, i'd probably model it on ExcelJs, much nicer to work with than POI.

-SK

On 12/15/2020 2:34 PM, Darren Strong wrote:
We've gotten pretty good at generating spreadsheets with POI HSSF and the JNI interface, but, the JNI interface is not efficient and the POI HSSF versions march on while we've frozen our updates due to the move to enums in that tool. Now, IBM has come a long way in helping us generate XML and writing it to the IFS, which Excel is based on. Has anyone had success generating the .xlsx file structure directly as XML files and then zipping it up in the format that Excel likes? Is there some documentation on the web I'm not able to find that might give some detail and examples on this? I would guess that this method would be very fast compared to what we're using now.


--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To
subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate link: https://amazon.midrange.com


As an Amazon Associate we earn from qualifying purchases.

This thread ...

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.