Re: Parsing an XML-XLSX file using XML-INTO (was POI-HSSF classes) -- RPG400-L

At least in the initial stages you will be better off _without_ the allowmissing and allowextra options. They will mask any underlying errors in the structure of your DS etc.

What release are you writing to? The based() technique makes it appear as if you are on V5R4. If you are on V6 use Template instead and also you should be able to use the count prefix option which will remove the need to use allowmissing.

On 2014-05-16, at 5:34 PM, Alan Cassidy <ACassidy@xxxxxxxxxxxxxx> wrote:

Ladies and Gentlemen,

Thanks to all of you that provided suggestions for using the POI classes.

Finally I went the route of asking the users to do the "Save As" XML from an .xlsx file.

Now I'm working on parsing out the data. I could do a search on what I know will be there but decided to try the XML-INTO.

Got it to compile successfully, got it to run as if it was all peachy, but I'm getting solid blank spaces. I mean for example, ROW(1).CELL(1).DATA(1) = *ALL' '. All blanks. And I checked others too, just blanks.

Found a couple of things by trial and error as I expected. One is that the parser wants the whole path, everything, not just the atomic elements. I can't figure out why.

Here's the relevant code for the XML-INTO operation:

n Ladies and Gentlemen,

Thanks to all of you that provided suggestions for using the POI classes.

Finally I went the route of asking the users to do the "Save As" XML from an .xlsx file.

Now I'm working on parsing out the data. I could do a search on what I know will be there but decided to try the XML-INTO.

Got it to compile successfully, got it to run as if it was all peachy, but I'm getting solid blank spaces. I mean for example, ROW(1).CELL(1).DATA(1) = *ALL' '. All blanks. And I checked others too, just blanks.

Found a couple of things by trial and error as I expected. One is that the parser wants the whole path, everything, not just the atomic elements. I can't figure out why.

Here's the relevant code for the XML-INTO operation:

options = 'path=Workbook/Worksheet/Table/Row/Cell/Data +

doc=file +

case=any +

trim=all +

allowmissing=yes +

allowextra=yes ' ;

//

xml-into %handler( handler : shared )

%XML( %str(pfpath) : options );

(Where pfpath is the pointer to the null-terminated (trimmed) path of the XML document in the IFS.)

And the handler procedure:

* --------------------------------------------------------------------

P Handler B export

D Handler pi 10i 0

D share 1024a

D row likeds( rowdata ) dim( 25 )

D const

D rowcount 10u 0 value

* --------------------------------------------------------------------

* Data structures for Handler parameters and processing

D RowData ds dim( 25 ) based(p_rowdata)

D qualified

D Cell likeds( CellDS ) dim( 30 )

* ------------------

D CellDS ds based(p_CellDS)

D data 100a dim( 5 )

* --------------------------------------------------------------------

I'm first testing to make sure I'm getting what I should.

So in the code I'm using a short field to use with DSPLY for the first 25 bytes of a couple of elements in the code.

Like this:

msg = %subst( row(xa).cell(xb).data(xc): 1: 20 ) ;

dsply 'data_is' ' ' msg ;

Using xa=1 then 2 etc.

Blanks. Every time blanks.

What did I miss, guys?

I'll look at the responses over the weekend.

Alan

Alan Cassidy
Senior Developer
Hospital Physician Partners
954-693-0000 ext. 3433 - Direct phone
786-380-9236 - Mobile phone
acassidy@xxxxxxxxxxxxxx<mailto:acassidy@xxxxxxxxxxxxxx>
www.hppartners.com<http://www.hppartners.com>

[HPP Logo FINAL with name-tag-logo-TM]

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

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

Jon Paris

www.partner400.com
www.SystemiDeveloper.com