Well, we know that PeopleSoft has the concept of File Layouts which helps you to create Excel documents. On top, if you are using the process scheduler on Windows, you can create Excel documents from PeopleSoft by using COM objects native to Microsoft. This post is for those, who are not very keen to use File Layouts due to its inflexibility (and neither have their process scheduler running on MS Windows) and are looking for some better alternatives. We will explain how to create a simple Excel document through PeopleCode, without using File Layout but by using native Java APIs. I'm assuming that you have some preliminary knowledge on Java to get us going. I would also suggest the readers to go through out initial post on creating excel documents in Java as this post will be linked with that one.
To start with, download the JExcel (Search in Google for JExcel) and copy it to your appserver classes directory. The file that I would like to be present in the application server class folder is jxl.jar. We will be creating a standalone Java class (for those who are keen on using reflection techniques, refer to our PeopleCode Reflection Techniques series first; we will cover Reflection techniques also), which will use the methods in jxl.jar file and invoke this standalone class from PeopleCode. We will come to know that it is very easy to use this Java API and perform the following:
1) Read an XLS document from PeopleCode
2) Read an XLS document and Modify the Excel document from PeopleCode
3) Harness the benefits of JExcel directly from PeopleSoft.
Let us start with the basics in this tutorial. We will first see how to read a simple XLS document using PeopleCode. To access a CELL value for an Excel document, we have to go through the following objects
Workbook(getWorkbook) -> Worksheet (getSheet) -> Cell (getCell) -> Data (getContents)
Create a simple XLS document NOW and name it "ReadExcel.xls". Copy this document into your application server class folder. [ This can be any location, to make things simple I have specified application server class folder ]. This file should contain 5 rows of data in column A, named from 1…5.We will be reading these values from PeopleSoft PeopleCode as we move on.
For those, who are still wondering the class folder location, it would be something on the lines as shown below
$PS_HOME/appserv/classes
If you have done well till this point, you can move to the next part of this tutorial -> Reading the XLS document Using JavaObject.
Thank you very much for this example, I will give it a try.
ReplyDeleteI will also need to be able to read in .xlsx files from PeopleCode, can you please provide an example of how to do this using POI called from PeopleCode (making a wrapper would be fine to help simplify things).
Thanks very much again.