Read Modify Excel Files in PeopleCode - Part 3

In our previous part of this post, we tried using reflection techniques  to read data from an Excel document in PeopleCode. At the end of that post, we said we would explain the same approach by using a wrapper class in Java. We wrote lines and lines of code to get scope of the methods we need to invoke and finally ended up reading an Excel document. For all those Reflection enthusiasts, refer to part2.

In this post,we will try to keep things simple for a Java beginner and still end up reading an Excel document Using JExcel in PeopleCode. To do this, we will first create a simple Java file, image.java, with the code as shown below; (The main method was for my testing only).

import java.io.*;

import jxl.*;

public class image {

public static void main(String[] args){

System.out.println("Hi");

}

public Workbook initiateworkbook(String Filename){

try {

Workbook ReadExcel = Workbook.getWorkbook(new File(Filename));

return ReadExcel;

}

catch (Exception i)

        {

            System.out.println(i);

                                                return null;

        }

}

}

Very simple code; contains a method initiateworkbook that accepts the name of the file as an input..creates a Workbook object by calling the "getWorkbook" method and returns the object back to the calling code.. Compile this Java file and place the resulting class file in your application server Class file folder. The PeopleCode snippet now reduces greatly to;

Local string &St_path_l = "Sample.xls";

Local JavaObject &Obj_class_l = CreateJavaObject("image");

Local JavaObject &Obj_getworkbook_l = &Obj_class_l.initiateworkbook(&St_path_l);

Local string &St_returnvalue_l;

For &i = 0 To 4;

   &St_returnvalue_l = &Obj_getworkbook_l.getSheet(0).getCell(0, &i).getContents();

   MessageBox(0, "", 0, 0, "" | &St_returnvalue_l);

End-For;

&Obj_getworkbook_l.close();

And that is it!. Nothing more..The whole complexity of using Reflection in PeopleCode has been greatly reduced by the image java file. This PeopleCode snippet creates a Java Object for the "image" java file and invokes the "initiateworkbook" method. The object &Obj_getworkbook_l now has a scope for the following class jxl.read.biff.WorkbookParser.. (as returned from the wrapper layer).

With this input, we write a FOR loop running from 0 to 4, and use getSheet(0) to access the worksheet and getCell(0,&i) to get into a particular Cell. We then use getContents() to read the contents of the CELL into a string variable in PeopleSoft.  The MessageBox statement is just to output the contents of the CELL to UI in PeopleSoft. So simple..and very straightforward indeed.. Note that we have to close the workbook using Close method, as illustrated in our initial Java Excel Code snippet..

That completes Part 3 of our tutorial section on reading Excel documents from PeopleCode using JExcel API. We will now move on to Part 4, where we will explain how to write a new Excel document from PeopleCode..

Part -1
Part -2
Part -3
Part -4
Part -5
Part -6