Re: Update a Cell in a MS EXCEL Worksheet

Thanks Dennis.
That solved the problem with getting the sheet name.
I am now having another problem that I hope you can help with.
In the worksheet I am working with, column 0 is formatted as a General type field.
I am attempting to use the following code to retreive the contents of that column:
Item  = %Trim(String_getBytes(HSSFCell_getStringCellValue(Cell)));
Where Item is a character field.
I am receiving the following error:
Message . . . . :   Java exception received when calling Java method.       
                                                                            
Cause . . . . . :   RPG procedure PROCESSSHE in program QGPL/XL_UPDTEST     
  received Java exception "java.lang.NumberFormatException: You cannot get a
  string value from a numeric cell" when calling method "getStringCellValue"
  with signature "()Ljava.lang.String;" in class                            
  "org.apache.poi.hssf.usermodel.HSSFCell".                                 
Since this is defined as General, and has both numeric and character data in it, I do not understand why I am receiving this error.

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: Fri, June 11, 2010 1:43:14 PM
Subject: RE: Update a Cell in a MS EXCEL Worksheet

Sorry.  http://code.midrange.com/9b7be1eab2.html

Dennis Lovelady
http://www.linkedin.com/in/dennislovelady
--
My intuition nearly makes up for my lack of good judgment.

That is a wrapper.  See http://code.midrange.com/9b7be1eab2.htm

Regards,
Dennis Lovelady
http://www.linkedin.com/in/dennislovelady
--
"Suppose you were an idiot and suppose you were a member of Congress.
But I repeat myself."
        -- Mark Twain

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

do

this?

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