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

If you have a problem with the path just send me details of the file format you are trying to process - or even better post an example of the XML to the midrange code area.

On 2014-05-19, at 6:45 AM, aec <cfuture@xxxxxxxxxxx> wrote:

Thanks millions, Jon!

Thing is, we're on v7r1 but without the TR's that allow for complete free-form (sigh) (for "political" reasons) which I'll be trying to take care of (after this month end the "politics" will change, but we'll see). I "studied" the RPG reference and guide both on XML-INTO to get as far as I did but I'll apply your points.

As to the Excel, I'm not at work yet till later this morning, I'll check the Excel version...

You say the path= is "way too deep". I first tried just doing it from "row" but I'll do that again.

Thanks again,
Alan

On 5/16/14 8:06 PM, Jon Paris wrote:

Turns out the Excel 2004 XML matched what you had better than I thought. This program works and processes all of the XML document I gave it.

A few points:

1) No allowmissing or allowextra.

allowextra is probably needed - particularly if you don't care about the Type fields. allowmissing was not needed because I supplied a count variable (countprefix) wherever it was needed (rows and cells) which as you will see also simplifies the processing.

By using both you basically guaranteed that any valid XML document would process without error no matter what it contained.

2) ns=remove option needed as namespaces are present and need to be stripped out.

3) datasubf was needed because data and attributes occur together in the <Data> element.

4) your path= went way too deep. You needed to target the first of the repeating elements - which (at least in 2004 Excel XML) appears to me to be row - which is what I would have expected. Because your path was wrong no data was found.

Think that's about it. Except to say that if you are on V5R4 you have a bit of a problem as you are missing a number of needed features. You could live without but it won't work as well. If you need V5R4 let me know.

Here's the code (Free-form 'cos fixed feels like hard work now):

dcl-ds *n PSDS;
RowCount int(20) pos(372);
End-Ds;

dcl-s r int(3);
dcl-s c int(3);

dcl-ds Row Dim(100) Qualified Inz;
Cell LikeDs(Cell_T) Dim(10);
CountCell int(5);
End-Ds;

dcl-ds Cell_T Template Qualified;
Data LikeDs(Data_T);
End-Ds;

dcl-ds Data_T Template;
Type char(20);
CountType int(3);
Data char(40);
End-Ds;

// Much as I hate CtData I couldn't be bothered to copy the XML to the IFS
// so the spreadsheet XML was all dumped at the end of the source
dcl-ds xmlData;
xml Char(80) Dim(200) CTData;
end-ds;

xml-into Row %XML( xmlData: 'countPrefix=Count datasubf=Data +
ns=remove case=any path=Workbook/Worksheet/Table/Row' );

dsply ( 'Processed ' + %Char(RowCount) + ' Rows' );

For r = 1 to RowCount;
For c = 1 to Row(r).CountCell;
dsply ( 'Row ' + %Char(r) + ' Cell ' + %Char(c) + ' Data =' );
dsply ( Row(r).Cell(c).Data.Data );
EndFor;
EndFor;

*InLR = *On;
**
<?xml version="1.0"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet">
<Worksheet ss:Name="System i Developer">
<Table ss:ExpandedColumnCount="10" ss:ExpandedRowCount="18"
x:FullColumns="1"
x:FullRows="1" ss:StyleID="s16" ss:DefaultColumnWidth="61"
ss:DefaultRowHeight="15">
<Row>
<Cell ss:StyleID="s17"><Data ss:Type="String">
System i Developer Payment History</Data></Cell>
</Row>
<Row>
<Cell ss:StyleID="s17"><Data ss:Type="String">
Assets Available for MVP</Data></Cell>
</Row>
<Row ss:Index="4">
<Cell ss:StyleID="s17"><Data ss:Type="String">
eLearning terms: 30% revenue + $50/student over 20</Data></Cell>
</Row>
<Row ss:StyleID="s17">
<Cell><Data ss:Type="String">Course Title</Data></Cell>
<Cell ss:StyleID="s20"><Data ss:Type="String">
Instructor</Data></Cell>
<Cell ss:StyleID="s21"><Data ss:Type="String">
2010 Payment</Data></Cell>
<Cell ss:StyleID="s21"><Data ss:Type="String">
2011 Payment</Data></Cell>
<Cell ss:StyleID="s21"><Data ss:Type="String">
2012 Payment</Data></Cell>
<Cell ss:StyleID="s21"><Data ss:Type="String">
2013 Payment</Data></Cell>
<Cell ss:StyleID="s21"><Data ss:Type="String">
Total Payment</Data></Cell>
<Cell ss:StyleID="s20"><Data ss:Type="String">
Course Offered Live</Data></Cell>
<Cell ss:StyleID="s20"><Data ss:Type="String">
Course Re-recorded</Data></Cell>
<Cell><Data ss:Type="String">Notes</Data></Cell>
</Row>
<Row>
<Cell><Data ss:Type="String">An Introduction to SQL</Data></Cell>
<Cell><Data ss:Type="String">Paul</Data></Cell>
<Cell><Data ss:Type="String">N/A</Data></Cell>
<Cell><Data ss:Type="Number">2740</Data></Cell>
<Cell><Data ss:Type="Number">4657</Data></Cell>
<Cell><Data ss:Type="Number">3456</Data></Cell>
<Cell ss:Formula="=SUM(RC[-3]:RC[-1])"><Data
ss:Type="Number">10853</Data></Cell>
<Cell><Data ss:Type="String">Yes</Data></Cell>
<Cell><Data ss:Type="String">No</Data></Cell>
</Row>
<Row>
<Cell><Data ss:Type="String">SQL Stored Procedures</Data></Cell>
<Cell><Data ss:Type="String">Paul</Data></Cell>
<Cell><Data ss:Type="String">N/A</Data></Cell>
<Cell><Data ss:Type="Number">1969</Data></Cell>
<Cell><Data ss:Type="Number">1209.78</Data></Cell>
<Cell><Data ss:Type="String">N/A</Data></Cell>
<Cell ss:Formula="=SUM(RC[-3]:RC[-1])"><Data
ss:Type="Number">3178.7799999999997</Data></Cell>
<Cell><Data ss:Type="String">No</Data></Cell>
<Cell><Data ss:Type="String">No</Data></Cell>
</Row>
<Row>
<Cell><Data ss:Type="String">Modernize RPG Applications</Data></Cell>
<Cell><Data ss:Type="String">Paul</Data></Cell>
<Cell><Data ss:Type="String">N/A</Data></Cell>
<Cell ss:Formula="=2931+1531"><Data ss:Type="Number">4462</Data></Cell>
<Cell><Data ss:Type="String">N/A</Data></Cell>
<Cell><Data ss:Type="String">N/A</Data></Cell>
<Cell ss:Formula="=SUM(RC[-3]:RC[-1])">
<Data ss:Type="Number">4462</Data></Cell>
<Cell><Data ss:Type="String">No</Data></Cell>
<Cell><Data ss:Type="String">No</Data></Cell>
</Row>
<Row>
<Cell><Data ss:Type="String">
Foundations in Modular RPG Applications</Data></Cell>
<Cell><Data ss:Type="String">Susan</Data></Cell>
<Cell><Data ss:Type="Number">2170</Data></Cell>
<Cell><Data ss:Type="Number">3390</Data></Cell>
<Cell><Data ss:Type="Number">1276.57</Data></Cell>
<Cell><Data ss:Type="String">N/A</Data></Cell>
<Cell ss:Formula="=SUM(RC[-3]:RC[-1])">
<Data ss:Type="Number">4666.57</Data></Cell>
<Cell><Data ss:Type="String">No</Data></Cell>
<Cell><Data ss:Type="String">No</Data></Cell>
</Row>
<Row>
<Cell><Data ss:Type="String">
Simplify Complex RPG Functions</Data></Cell>
<Cell><Data ss:Type="String">Jon</Data></Cell>
<Cell><Data ss:Type="Number">2000</Data></Cell>
<Cell><Data ss:Type="Number">2291</Data></Cell>
<Cell><Data ss:Type="Number">1711.71</Data></Cell>
<Cell><Data ss:Type="String">N/A</Data></Cell>
<Cell ss:Formula="=SUM(RC[-3]:RC[-1])">
<Data ss:Type="Number">4002.71</Data></Cell>
<Cell><Data ss:Type="String">No</Data></Cell>
<Cell><Data ss:Type="String">No</Data></Cell>
</Row>
<Row>
<Cell><Data ss:Type="String">RPG Meets the Web</Data></Cell>
<Cell><Data ss:Type="String">Paul</Data></Cell>
<Cell><Data ss:Type="Number">2000</Data></Cell>
<Cell ss:Formula="=3492+1466">
<Data ss:Type="Number">4958</Data></Cell>
<Cell ss:Formula="=2839.83+1737.45">
<Data ss:Type="Number">4577.28</Data></Cell>
<Cell><Data ss:Type="String">N/A</Data></Cell>
<Cell ss:Formula="=SUM(RC[-3]:RC[-1])">
<Data ss:Type="Number">9535.2799999999988</Data></Cell>
<Cell><Data ss:Type="String">No</Data></Cell>
<Cell><Data ss:Type="String">No</Data></Cell>
</Row>
<Row>
<Cell><Data ss:Type="String">XML for RPG Programmers</Data></Cell>
<Cell><Data ss:Type="String">Jon</Data></Cell>
<Cell><Data ss:Type="String">N/A</Data></Cell>
<Cell ss:Formula="=4152+1673">
<Data ss:Type="Number">5825</Data></Cell>
<Cell ss:Formula="=2229.41+3070.01">
<Data ss:Type="Number">5299.42</Data></Cell>
<Cell><Data ss:Type="Number">2430</Data></Cell>
<Cell ss:Formula="=SUM(RC[-3]:RC[-1])">
<Data ss:Type="Number">13554.42</Data></Cell>
<Cell><Data ss:Type="String">Yes</Data></Cell>
<Cell><Data ss:Type="String">No</Data></Cell>
</Row>
<Row>
<Cell><Data ss:Type="String">Embedding SQL in RPG Programs</Data></Cell>
<Cell><Data ss:Type="String">Susan</Data></Cell>
<Cell><Data ss:Type="String">N/A</Data></Cell>
<Cell ss:Formula="=4385+1287"><Data ss:Type="Number">5672</Data></Cell>
<Cell ss:Formula="=3653.84+1935.87">
<Data ss:Type="Number">5589.71</Data></Cell>
<Cell><Data ss:Type="Number">803.4</Data></Cell>
<Cell ss:Formula="=SUM(RC[-3]:RC[-1])">
<Data ss:Type="Number">12065.109999999999</Data></Cell>
<Cell><Data ss:Type="String">Yes</Data></Cell>
<Cell><Data ss:Type="String">No</Data></Cell>
<Cell><Data ss:Type="String">New Assignments/Handouts 2013</Data></Cell>
</Row>
<Row>
<Cell><Data ss:Type="String">RSE Jump Start Workshop</Data></Cell>
<Cell><Data ss:Type="String">Susan</Data></Cell>
<Cell><Data ss:Type="Number">2939</Data></Cell>
<Cell ss:Formula="=4730+1879"><Data ss:Type="Number">6609</Data></Cell>
<Cell ss:Formula="=5193.63+3087.84">
<Data ss:Type="Number">8281.4700000000012</Data></Cell>
<Cell><Data ss:Type="Number">5636</Data></Cell>
<Cell ss:Formula="=SUM(RC[-3]:RC[-1])"><Data ss:Type="Number">20526.47
</Data></Cell>
<Cell><Data ss:Type="String">Yes</Data></Cell>
<Cell><Data ss:Type="String">Yes</Data></Cell>
<Cell><Data ss:Type="String">
New recording 2013; Old version offered with MVP</Data></Cell>
</Row>
<Row ss:Index="17">
<Cell ss:StyleID="s17"><Data ss:Type="String">
eBooks - 20% royalty</Data></Cell>
<Cell ss:Index="3" ss:StyleID="s21">
<Data ss:Type="String">2010 Royalty</Data></Cell>
<Cell ss:StyleID="s21">
<Data ss:Type="String">2011 Royalty</Data></Cell>
<Cell ss:StyleID="s21">
<Data ss:Type="String">2012 Royalty</Data></Cell>
<Cell ss:StyleID="s21">
<Data ss:Type="String">2013 Royalty</Data></Cell>
<Cell ss:StyleID="s21">
<Data ss:Type="String">Total Royalty</Data></Cell>
</Row>
<Row>
<Cell><Data ss:Type="String">
PHP From an RPG Perspective</Data></Cell>
<Cell><Data ss:Type="String">Jon</Data></Cell>
<Cell><Data ss:Type="String">N/A</Data></Cell>
<Cell><Data ss:Type="Number">290.42</Data></Cell>
<Cell><Data ss:Type="Number">263.47000000000003</Data></Cell>
<Cell><Data ss:Type="Number">89.07</Data></Cell>
<Cell ss:Formula="=SUM(RC[-3]:RC[-1])">
<Data ss:Type="Number">642.96</Data></Cell>
</Row>
</Table>
</Worksheet>
</Workbook>
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

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