Dec 20, 2013

PeopleSoft : Reading excel file using jxl in peoplesoft

Reading Excel using Java:
Step1: Create a java class to read excel and compile and place the class file in /app/psoft/psadmin/appserv/classes  .

/* Java class to read excel  file */
import java.io.*;
import jxl.*;
public class ReadExcelFile {
public static void main(String[] args){
System.out.println("Hi");
}
public Workbook Readworkbook(String Filename){
try {
Workbook ReadExcel = Workbook.getWorkbook(new File(Filename));
return ReadExcel;
}
catch (Exception i)
        {
            System.out.println(i);
            return null;
        }
}
}

Step 2: Use the below code to read data

Local string &strGetEnv = GetEnv("PS_HOME");
Local string &St_path_l = &strGetEnv | "/user/" | %DbName | "/ABC.xls";
Local JavaObject &Obj_class_l = CreateJavaObject("nExcel");
Local JavaObject &Obj_getworkbook_l = &Obj_class_l.readWorkbook(&St_path_l);
&rows = &Obj_getworkbook_l.getSheet(0).getRows();
&cols = &Obj_getworkbook_l.getSheet(0).getColumns();
WinMessage("No of rows:" | &rows | " No of columns:" | &cols, 0);
Local string &St_returnvalue_l;
For &i = 1 To &rows - 1
   For &j = 0 To &cols - 1;
      &St_returnvalue_l = &Obj_getworkbook_l.getSheet(0).getCell(&j, &i).getContents();
      MessageBox(0, "", 0, 0, "" | &St_returnvalue_l);
   End-For;
End-For;

&Obj_getworkbook_l.close();