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.