PeopleSoft Password Protect Excel WorkSheets

One common requirement for PeopleSoft applications is to generate an Excel document which is protected with a password. In our initial series of posts on JExcel, we saw how to integrate JExcel with PeopleSoft to generate, read and Modify Excel spreadsheets.

In this post, we will see how to password protect an Excel document from PeopleSoft. We will produce an example that creates and Excel document and protects it with a password. We will use the same operator ID code sample used earlier. The code snippet to password protect an Excel worksheet in PeopleSoft is provided below

Local string &St_path_l = "CreateProtectedWorkbook.xls";
Local JavaObject &Obj_class_l = CreateJavaObject("image");
Local JavaObject &Obj_getwritableworkbook_l = &Obj_class_l.initiateWritableWorkbook(&St_path_l);
Local JavaObject &Obj_writableworksheet_l = &Obj_getwritableworkbook_l.createSheet("OperatorIDs", 0);
Local string &St_returnvalue_l;
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)
   Local JavaObject &Obj_label_l = CreateJavaObject("jxl.write.Label", 0, &i, &St_returnvalue_l);
   &Obj_writableworksheet_l.addCell(&Obj_label_l);
   &i = &i + 1;
End-While;
&Sql_oprdefn_l.Close();
/* Set password for the Excel Worksheet */
Local JavaObject &obj_settings_l = &Obj_writableworksheet_l.getSettings();
&obj_settings_l.setPassword("password");
&obj_settings_l.setProtected( True);
/*Finish Protecting the Worksheet */
&Obj_getwritableworkbook_l.write();
&Obj_getwritableworkbook_l.close();

This example is an improved version of our write excel workbook example presented earlier. We use the "getSettings()" method of worksheet object. This returns an object back to PeopleSoft of type jxl.SheetSettings. We then use two methods of SheetSettings class viz setPassword to specify a password for the worksheet and SetProtected to set the worksheet read only.

This PeopleCode example would have given an insight on creating Excel documents with a password option. In the next post, we will see how to protect an entire Workbook with password in JExcel.