We defined our objective in Part 1 of this series. In this post, we will explain how to read the Excel document "ReadExcel.xls" in PeopleCode. To start with, we will create a java object for jxl.workbook from PeopleCode.
Local JavaObject &obj_readxls_l = GetJavaClass("jxl.Workbook").getWorkbook(CreateJavaObject("java.io.File", "ReadExcelUsingJava.xls"));
This will invoke the famous error again;
Calling Java jxl.Workbook.getWorkbook: more than one overload matches.
While calling the noted Java method, the PeopleCode interface found more than one method signature that could match the given parameter types.
Alert! We will have to start using the reflection techniques here..Or write a wrapper. I would go with reflection now, cover wrapper ones later. The PeopleCode used to invoke the "getWorkbook" is provided below;
Local string &St_path_l = “Sample.xls";
Local JavaObject &Obj_class_l = GetJavaClass("java.lang.Class");
Local JavaObject &Obj_classloader_l = GetJavaClass("java.lang.ClassLoader");
Local JavaObject &Obj_getcoreclass_l = &Obj_class_l.forName("jxl.Workbook", False, &Obj_classloader_l.getSystemClassLoader());
Local JavaObject &Obj_filetype_l = &Obj_class_l.forName("java.io.File");
Local JavaObject &Obj_argtypes_l = CreateJavaObject("java.lang.Class[]", &Obj_filetype_l);
Local JavaObject &Obj_getcorrectmethod_l = &Obj_getcoreclass_l.getDeclaredMethod("getWorkbook", &Obj_argtypes_l);
Local JavaObject &Obj_consargarray_l = CreateJavaObject("java.lang.Object[]", CreateJavaObject("java.io.File", &St_path_l));
Local JavaObject &Obj_finalinvoke_l = &Obj_getcorrectmethod_l.invoke(&Obj_getcoreclass_l, &Obj_consargarray_l);
We have got a scope for Workbook object, by invoking "getWorkbook" method and passing the input file "Sample.xls". At this stage, if you try to print the value of &Obj_finalinvoke_l.getClass().getName(), you will get the following;
jxl.read.biff.WorkbookParser
Now, to get into the first worksheet of the workbook, we cannot directly give
Local JavaObject &obj_getsheet_l = &Obj_finalinvoke_l.getSheet(0); /* does not work */
If you attempt to give this, you will get an error as shown below
Java method getSheet not found for class java.lang.Object.
The noted method is not present in the given class. Verify the spelling and capitalization of the method name.
Eventhough we invoked the "getWorkbook" method, the return object is of type java.lang.Object only and we should continue to reflection technique to get into the worksheet and eventually to the cell and contents..Looks ugly, but I don't see any other magical way out, except writing a clean wrapper..(we will cover this also). So, to get the "Sheet" object we write the following code as shown below
Local JavaObject &Obj_integertype_l = GetJavaClass("java.lang.Integer").TYPE;
Local JavaObject &obj_getsheet_l = &Obj_finalinvoke_l.getClass().getDeclaredMethod("getSheet", CreateJavaObject("java.lang.Class[]", &Obj_integertype_l)).invoke(&Obj_finalinvoke_l, CreateJavaObject("java.lang.Object[]", CreateJavaObject("java.lang.Integer", "0")));
This is exactly same in the way we use this as described earlier, we have just removed creating an object for 'java.lang.reflect.Method' and directly triggered the "invoke" method by passing the "workbook" class and the input value of 0 of type java.lang.Integer.
ok, how do we know if we got the right worksheet object? To verify this, we can use some test code as shown below
Local JavaObject &obj_test_l = &obj_getsheet_l.getClass().getDeclaredMethod("getName", Null).invoke(&obj_getsheet_l, Null);
Error &obj_test_l.toString();
This would give you the name of the worksheet..In my case, it came as "Sheet1". Further, if you give "&obj_getsheet_l.getClass().getName()" you should get the following output;
jxl.read.biff.SheetImpl
This shows we are on track.. Now, to read the contents from A0 to A4, we use a simple for loop, get the "Cell" object using the same techniques and then use the "getContents" method of the cell object to read the contents;
For &i = 0 To 4;
Local JavaObject &obj_getcell_l = &obj_getsheet_l.getClass().getDeclaredMethod("getCell", CreateJavaObject("java.lang.Class[]", &Obj_integertype_l, &Obj_integertype_l)).invoke(&obj_getsheet_l, CreateJavaObject("java.lang.Object[]", CreateJavaObject("java.lang.Integer", "0"), CreateJavaObject("java.lang.Integer", &i))); /* Dynamically pass the cell value using the index variable */
Local JavaObject &obj_cell_value_l = &obj_getcell_l.getClass().getDeclaredMethod("getContents", Null);
rem jxl.read.biff.RKRecord; /* Cell Object */
Local JavaObject &ssdf = &obj_cell_value_l.invoke(&obj_getcell_l, Null); /* The value of the Cell; object.ToString() will print the value */
End-For;
This completes a round about way to read excel document data using PeopleCode and JExcel API. In our next part, we will discuss how to write a wrapper class in Java to achive the same objective. The wrapper would be a neat approach for us, as you will appreciate..it also minimizes the complexity involved when updating the workbook. I initially got the following error in Solaris version..but this is not occuring now
Java Exception: java.lang.InternalError: during call of java.lang.reflect.Method.invoke.
The noted Java error was thrown during a call of the given method.
Give a try , and refer to the related posts below for Part 3 of this series..
No comments:
Post a Comment