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

Alan,

As I read this saga of you and Jon going deeper and deeper down the
XML rabbit hole, I have to say: Are you sure you really want to go
through this learning exercise right now?

Please let me be clear: I think it is great that you want to learn
RPG's XML features. I have absolutely nothing against learning them
(or learning pretty much anything, for that matter). So please
understand that I am not trying to discourage you, I'm not trying to
undermine your decision, and I am not trying to be difficult.

But it all just sounds like it would be way, way easier and quicker
(for you and your users and your bosses) if you instead invested your
time learning a bona fide Excel-handling package. Especially once Jon
said "could you ask your users to save the specific worksheets you
need in XML format?".

Putting more onus on the user is only going to introduce another
source of user error, making both their experience and your experience
worse.

Now, admittedly I can only speak to using Python, because that's my
expertise. It's free, and easy enough to install that I can install
it in just a few minutes (and I basically have no admin skills at all)
but as far as I can tell, it's not officially supported or endorsed by
IBM, so if that means it's out of the question, then fine, but there
are other choices, like PHP.

For now though, I think it may be helpful to at least show you a few
Python snippets so you can see the flavor of how working with Excel
files typically goes in a modern, dynamic language. It should be
quite similar with PHP or a number of other languages.

First, you have to load an Excel-reading module (there are two major
ones for Python; I use xlrd):

import xlrd

To open a workbook (can be either .xls or .xlsx; the user doesn't have
to do any extra "Save As..." step) is simply

wb = xlrd.open_workbook('/my_excel_dir/my_workbook.xlsx')

At that point, wb is a "workbook object". You can access any of the
sheets of the workbook as follows:

ws = wb.sheet_by_index(0)

or

ws = wb.sheet_by_name('Sheet1')

Assuming a "normal" workbook created by Excel, where the sheets have
not been renamed, either of the above lines of Python will get you the
first sheet (indices start at zero). But of course you can pick any
index or any name. At that point, ws is a "worksheet object".

To get the value of a particular cell (including a cell which contains
a formula) in that sheet:

x = ws.cell_value(3, 5)

The above sets x to the value of the cell at the 4th row, 6th column
(F4 in default Excel notation). Or, to get a bunch of values at once:

list1 = ws.row_values(3)
list2 = ws.col_values(5)

The above creates two Python lists (somewhat like arrays, but can
change size freely and contain objects of any type, including mixed
types). list1 will contain all values in the 4th row; list2 will
contain all values in the 6th column.

Suppose the values in column A are state abbreviations and column B
are sales dollars. You are interested in getting the sales for NJ,
NY, and CT. One way to do that is

total_sales = 0
for rx in range(ws.nrows):
if ws.cell_value(rx, 0) in ('NJ', 'NY', 'CT'):
total_sales += ws.cell_value(rx, 1)

The Python that I use on the i comes with easy ways to read and write
physical files (both RLA and SQL flavors), so the interoperability
with native i stuff is really first-rate.

By now, I'm sure I've babbled on way too long, especially as this is
the RPG list. But I felt it was worth trying to show how easy it can
be to work with Excel. Alan, if you are intrigued enough by this
taste to learn more, I'm happy to answer any questions. Whether you
try Python or something else or continue forging ahead with RPG, I
sincerely wish you success.

John Y.