In our earlier posts, we saw how to read an Excel document inside a PeopleSoft application by using JExcel Java API. We wrote a Java wrapper class to return the Workbook object to PeopleSoft and then invoked relevant methods to read data from the worksheet /Cell. In this post, we will see how to create a new Excel document from PeopleSoft using JExcel.
To set an objective for us, we will list all operator IDs in PeopleSoft PSOPRDEFN table into an Excel document "WriteExcel.xls" using JExcel API. To do this, our first step would be to expand the wrapper class to return a "WritableWorkbook" object for us. We will pass the name of the Excel document from PeopleSoft application and then pass the integration elements to Java layer to create the document. A modified version of the wrapper class that returns a "WritableWorkbook" object is provided below
import java.io.*;
import jxl.*;
import jxl.write.*;
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;
}
}
public WritableWorkbook initiateWritableWorkbook(String Filename){
try {
WritableWorkbook ModifyExcel = Workbook.createWorkbook(new File(Filename));
return ModifyExcel;
}
catch (Exception i)
{
System.out.println(i);
return null;
}
}
}
The "createWorkbook" method when invoked from PeopleSoft, would return an object of "WritableWorkbook" to the calling program. With this object, we will be able to achieve the objective we are after. A modified PeopleCode example in this case is provided below
/* File name that will be created from PeopleSoft */
Local string &St_path_l = "CreatedFromPeopleSoft.xls";
Local JavaObject &Obj_class_l = CreateJavaObject("image");
/* Call new method to get writable object */
Local JavaObject &Obj_getwritableworkbook_l = &Obj_class_l.initiateWritableWorkbook(&St_path_l);
/* call createSheet method to create a new worksheet with label OperatorIDs */
Local JavaObject &Obj_writableworksheet_l = &Obj_getwritableworkbook_l.createSheet("OperatorIDs", 0);;
Local string &St_returnvalue_l;
/* SQL cursor to get 100 operator IDs from PSOPRDEFN */
Local SQL &Sql_oprdefn_l = CreateSQL("SELECT OPRID FROM PSOPRDEFN WHERE ROWNUM < 100");
Local number &i = 0;
While &Sql_oprdefn_l.Fetch(&St_returnvalue_l)
/* Create label object passing cell reference, and value */
Local JavaObject &Obj_label_l = CreateJavaObject("jxl.write.Label", 0, &i, &St_returnvalue_l);
/* addCell method adds the required data into the worksheet */
&Obj_writableworksheet_l.addCell(&Obj_label_l);
&i = &i + 1;
End-While;
&Sql_oprdefn_l.Close(); /* cursor close */
/* Call the write method to write the contents to the worksheet */
&Obj_getwritableworkbook_l.write();
/* close the workbook */
&Obj_getwritableworkbook_l.close();
If you run this code, you will find a workbook "CreatedFromPeopleSoft.xls" in your appserv domain directory. i.e. under PS_HOME/appserv. This file would contain a worksheet with the name "OperatorIDs" with 99 operator IDs picked from PSOPRDEFN. This basic example opens up a new dimension to create Excel documents from PeopleSoft. From PeopleSoft, you can harness every feature that JExcel has to offer to create presentable worksheets.
In our next part of this series, we will see how to modify an existing Excel Spreadsheet from PeopleSoft using JExcel.
No comments:
Post a Comment