Ok, until this post we know how to create a new XLS document from PeopleSoft, write some contents into it and save it as a new workbook. We also know how to read an existing spreadsheet contents and do some processing in PeopleCode based on the values that are read.
In the final part of this series, we will see how to modify existing Excel documents in PeopleSoft using JExcel API. To set an objective for us, we will have a spreadsheet with a list of user IDs. We will read each User ID from the workbook, and write into a different column the "account locked" status of this user. The account locked status can be inferred from PSOPRDEFN.ACCTLOCK column. A value of '0' in ACCTLOCK field indicates that the user account is 'Active' and a value of '1' indicates that the user account is locked.
To Modify an Excel document in JExcel, it is required to create a copy of the document, by passing the document that is being read as a "workbook" object. Once we are able to do this, it is a matter of time we write some PeopleCode and dump the output in a modified Excel file. We will begin with expanding our wrapper class to include a method to return a "WritableWorkbook" object by accepting a "Workbook" object. More precisely, we will be using the method as shown below
public static WritableWorkbook createWorkbook(java.io.File file, Workbook in) throws java.io.IOException
Creates a writable workbook with the given filename as a copy of the workbook passed in. Once created, the contents of the writable workbook may be modified
Modify the wrapper class code to include the method as shown below;
public WritableWorkbook CreateWorkbookFromExisting(String Filename,jxl.Workbook ReadExcel){
try {
WritableWorkbook ModifyExcel = Workbook.createWorkbook(new File(Filename),ReadExcel);
return ModifyExcel;
}
catch (Exception i)
{
System.out.println(i);
return null;
}
}
Compile the entire java file and load it into the application server classes folder. This method "CreateWorkbookFromExisting" will create a copy of the readable workbook object, so that we can do updates into it. It uses the "createWorkbook" method and takes two arguments:- the name of the new worbook that will be created and the object that has the readable version in it.
Create an Excel document ReadDocument.xls and from Cells A1 to A9, load User IDs that exist in your PeopleSoft Environment. A sample is shown below;
UserID1 |
UserID2 |
UserID3 |
UserID4 |
UserID5 |
UserID6 |
UserID7 |
UserID8 |
UserID9 |
Move this XLS document into your application server classes folder or anywhere in the application server box which PeopleCode can access. We will be reading this worksheet, create a copy of this for updating, and update the account lock status for these users in columns B1 .. B9.
The final part of this series (Part 6) explains how to do this..
No comments:
Post a Comment