RE: Parsing an XML-XLSX file using XML-INTO

Thanks Henry!
PowerExt looks useful.

Alan


Alan Cassidy
Senior Developer
Hospital Physician Partners
954-693-0000 ext. 3433 – Direct phone
786-380-9236 - Mobile phone
acassidy@xxxxxxxxxxxxxx
www.hppartners.com




-----Original Message-----
From: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Henrik Rützou
Sent: Wednesday, May 21, 2014 10:00 AM
To: RPG programming on the IBM i (AS/400 and iSeries)
Subject: Re: Parsing an XML-XLSX file using XML-INTO

Well here is a RPG/XML approach where I position myself on the AsOfDate data element:

* Reading XML

/copy qsrc,pxapihdr General H-Spec's

* powerEXT API Connectors
/copy qsrc,pxapicgicn Basic HTTP connecter & Basic Services

* Declare Internal Variables
d dsp s 52a varying

/free
clearSrvPgm();
setContent('*none');

xmlFromStmf('xls.xml');
xmlReaderInz(xmladdr:xmlsize);
xmlReaderCase('L');

dow xmlReader = 0;

select;
// read only 'AsOfDate' data element in Row/Cell relation
when xmlGetNode = 'data' and xmlGetAttr = ''
and xmlGetXpath(1:2) = '/row/cell';
if xmlgetData = 'AsOfDate';
dsply 'now I''m positioned';
dsp = xmlGetData;
dsply dsp;
endif;
endsl;

enddo;


*inlr = *on;
/end-free



On Wed, May 21, 2014 at 3:52 PM, John Yeung <gallium.arsenide@xxxxxxxxx>wrote:

On Wed, May 21, 2014 at 9:00 AM, Jon Paris <jon.paris@xxxxxxxxxxxxxx>
wrote:

Unless I'm misremembering John, Alan reached this point of having
the users save off in XML format (his idea not mine) _after_ having
tried the POI classes to access the data directly. I would have
recommended that approach (or PHP or ...) had he not already gone
down that route.

Having the users save in XML was definitely his idea. And I never
meant to imply that your *first choice* would be for the users to save
multiple XML worksheets. I just felt that if you had gotten to the
point where you were even *considering* asking the users to do yet
another step, then it was at least worth taking one more look back at
other alternatives.

He did start on the POI path before deciding on XML, but it's not
clear how far he got. What I mean is, I don't know if he got to a
point where *POI* couldn't do the job, or to a point where *he*
couldn't get POI to do the job.

I suspect that XML then became more attractive because (1)
conceptually, processing XML from scratch is simpler than processing a
"native" Excel file from scratch (even the .xlsx variety); and (2)
when you receive an XML file, you can *see*, with your own eyeballs,
what's in it. The end result is tantalizingly close. If you receive
an Excel file, you can't just open it in a text editor or even a hex
editor and go "ah, I see, you just have to pick out these pieces and
Bob's your uncle". No, with an Excel file, you really need proper
tooling.

Now, I did bring up some questions regarding POI (because I don't have
personal experience with it, and was trying to establish whether it
was indeed up to the task), but they never got answered. They mainly
had to do with formulas. I specifically mentioned that the packages I
*am* personally aware of can retrieve values from formula cells
(provided that those formulas were evaluated by Excel, and that the
workbook was saved by Excel). No answer.

My fear was that Adam (and others on this list) didn't really
understand what I was talking about, and thus didn't give my
alternatives a fair shake. Even then, I was completely fine with
that. I only spoke up again when your exchange with Adam showed that
the XML approach isn't turning out to be as straightforward as hoped.
That's why I've attempted to revisit the direct Excel-reading path,
but this time with concrete examples.

John Y.
--
This is the RPG programming on the IBM i (AS/400 and iSeries)
(RPG400-L) mailing list To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at http://archive.midrange.com/rpg400-l.

--
Regards,
Henrik Rützou

http://powerEXT.com <http://powerext.com/>
--
This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L) mailing list To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at http://archive.midrange.com/rpg400-l.


--------------------------------------------------------------------------------
Confidentiality Notice: This email may contain confidential information or information covered under the Privacy Act, 5 USC 552(a), and/or the Health Insurance Portability and Accountability Act (PL 104-191) and its various implementing regulations and must be protected in accordance with those provisions. It contains information that is legally privileged, confidential or otherwise protected from use or disclosure. This e-mail message, including any attachments, is for the sole use of the intended recipient(s). Any unauthorized review, use, disclosure or distribution is prohibited. You, the recipient, are obligated to maintain it in a safe, secure and confidential manner. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message. Thank you.
--------------------------------------------------------------------------------