RE: Update a Cell in a MS EXCEL Worksheet

A few clarifications, mostly because I know you're going to look at that
inner procedure and still wonder why two variables are being passed when the
prototype clearly shows that only one is requested.

First, I guessed, apparently correctly, that you were not using XLPARSE, but
rather the HSSF service program, also provided by Scott. I understand why
one might get confused, but the difference is critical. XLPARSE will
process spreadsheets on its own, passing the data it finds in the
spreadsheet to procedures whose address you pass to it initially. HSSF is a
library of functions that allow you to read/process update that same
spreadsheet according to your own methods, not necessarily in the same
sequence/manner as XLPARSE.

I do not pass the workbook name to hssf_getSheetName. I pass an object that
happens to have the same class as an
org.apache.poi.hssf.usermodel.HSSFWorkbook object.

When you call a *JAVA function that is not a constructor, you always have to
pass the object against which the procedure is to operate. In java, you
might do:
myClass.print() ;

The class myObject contains a function named print, which expects no
arguments in this case. But if I were to call this from RPG, the Prototype
would still indicate no arguments, but the execution would still require an
argument: the object containing the class that contains the function. So in
RPG, the above would be something like:
D myClass_print...
D PR
D ExtProc(*JAVA
D :'my.class.path.myClass'
D :'print')

D objectHolder D O Class(*JAVA
D : 'my.class.path.myClass')
D otherObject D Like(objectHolder)
/Free

myClass_escape(objectHolder) ;

The developers of RPG and its compiler chose (wisely, I think) to maintain
consistency with the JAVA prototype. But there remains the need to tell RPG
which object of that class is to be used, when the class function is
invoked. For example, let's say that objectHolder and otherObject both
represent spreadsheets, and the print function prints each cell in some
fashion. We need a way to tell RPG that we want to execute the
objectHolder.print function rather than the otherObject.escape. The "extra"
first parameter is how we differentiate those.

Similarly, all calls to *JAVA functions that are not *CONSTRUCTOR require an
extra first parameter, and that is the object that contains the function.

This message has been reviewed by our Department of Redundancy Department,
and no repetitions were found to be repeated.

Dennis Lovelady
http://www.linkedin.com/in/dennislovelady
--
"Thrusting my nose firmly between his teeth, I threw him heavily to the
ground on top of me."
-- Mark Twain

Dennis,
Can you send me the prototype for the the hssf_getSheetName?
I have found some references to it, but am confused as the
prototype seems to only require the sheet number, but you are passing
the workbook name as well as the sheet number.

Thanks,

Jeff Young
Sr. Programmer Analyst
IBM -e(logo) server Certified Systems Exper - iSeries Technical
Solutions V5R2
IBM  Certified Specialist- e(logo) server i5Series Technical Solutions
Designer V5R3
IBM  Certified Specialist- e(logo)server i5Series Technical
Solutions Implementer V5R3








________________________________
From: Dennis Lovelady <iseries@xxxxxxxxxxxx>
To: RPG programming on the IBM i / System i <rpg400-l@xxxxxxxxxxxx>
Sent: Wed, June 9, 2010 10:03:43 AM
Subject: RE: Update a Cell in a MS EXCEL Worksheet

Unfortunately I cannot post this entire program, but I can post some of
the
pieces you might need.

Here's a start; tell me what else you need.

D Workbook_getNumberOfSheets...
D                PR            10I 0
D                                    ExtProc(*JAVA:
D                                    'org.apache.poi.ss.-
D                                    usermodel.Workbook':
D                                    'getNumberOfSheets')

  If book <> *NULL ;
    nbrSheets = Workbook_getNumberOfSheets(book) ;
    For SN = 0 to nbrSheets - 1 ;
        sheetName = hssf_getSheetName(book: SN) ;
        workSheet = hssf_getSheet(book: sheetName) ;
        If workSheet <> *NULL ;
          processSheetData(book: workSheet: sheetName) ;
        EndIF ;
    EndFOR ;
  EndIF ;


P processSheetData...
P                B
D processSheetData...
D                PI
D  book                              like(HSSFWorkbook)
D  Sheet                            Like(HSSFSheet)
D  sheetName                  32    Varying Value

  GatherStats('Processing sheet' + sheetName) ;
  RN = 0 ;
  row = HSSFSheet_getRow(sheet: RN) ;
  cell = HSSFRow_getCell(row: 0) ;

Dennis Lovelady
http://www.linkedin.com/in/dennislovelady
--
"When I was a boy of fourteen, my father was so ignorant I could hardly
stand to have the old man around.  But when I got to twenty-one, I was
astonished at how much he had learned in seven years."
        -- Mark Twain

I have used Scotts XLPARSER4 service program in the past to read a
single worksheet and process it.\
I now have a need to read multiple worksheets from a workbook and
update a specific value.
Can someone point me to an example or sample code on what I need to