Read Modify Excel Files in PeopleCode - Part 6

In Part 5 of this series, we modified the wrapper class method to include a method that returns a workbook object, which can be used to Modify a copy of the workbook. We also created a simple workbook ReadDocument.xls which we are aiming to modify by including the account lock status of users in Column B. In the concluding part of this series, we will present the PeopleCode snippet which modifies the Excel document. The complete code with comments is provided below.

/* This variable holds the complete path to the readable version of the worksheet */

Local string &St_path_l = "ReadDocument.xls";

/* This variable holds the complete path to the final worksheet. We will be writing into this version */

Local string &St_modifiedpath_l = "ModifiedDocument.xls";

/* Create a base object to the class file */

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

/* Create a Workbook object mapping to the readable version */

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

/* Create a WritableWorkbook object mapping to the copy of readable version. Note that we are passing the readable version object into this method*/

Local JavaObject &Obj_modifyworkbook_l = &Obj_class_l.CreateWorkbookFromExisting(&St_modifiedpath_l, &Obj_getworkbook_l);

/* Get First sheet in the writable copy for updating */

Local JavaObject &Obj_modifyworksheet_l = &Obj_modifyworkbook_l.getSheet(0);

Local string &St_returnvalue_l;

Local string &St_acctlockstatus_l;

For &i = 0 To 8;

  /* This string variable holds the operator ID from column A */

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

/* Query the account locked status from PeopleSoft */

   SQLExec("SELECT DECODE(ACCTLOCK,0,'Active',1,'InActive') FROM PSOPRDEFN WHERE OPRID=:1", &St_returnvalue_l, &St_acctlockstatus_l);

/* dump the output to column B, note that we are using 1, &i to signify column B */

   Local JavaObject &Obj_label_l = CreateJavaObject("jxl.write.Label", 1, &i, &St_acctlockstatus_l);

   &Obj_modifyworksheet_l.addCell(&Obj_label_l);

End-For;

/* Write the workbook and close both the workbooks */

&Obj_modifyworkbook_l.write();

&Obj_modifyworkbook_l.close();

&Obj_getworkbook_l.close();

If you run this code, you will find a new document ModifiedDocument.xls that will hold the login IDs with the account lock status. A sample is provided below:

UserID1	Active
UserID2	InActive
UserID3	InActive
UserID4	Active
UserID5	Active
UserID6	Active
UserID7	InActive
UserID8	InActive
UserID9	Active

The code is self explanatory with the comments; we have used all the methods for reading and writing workbooks that we discussed in our previous parts. Give a try and if you encounter any issues, post it..We will have a look.

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