Parsing Excel can be achieved as like similar to OOTB Functionality:
This Functionality can be used in Agent or some other place where to read the excel where its stored in server. This implementation can be used as alternative for File Listener where reading the Excel file.
Using poi jar its implemented:
Poi Jar
Screen shot and implementation is given:
1.First Seperate One Template Excel file which contains, for which property it should set.
2. Data sheet should be in the same format as Template excel.
3.Create a Page "TempEmployeeDetails" of class "Code-Pega-List".
4.Inside the java step for which class of pxResults need to set. "Data-" (this should be of the class name for which data should set).
Sample code is given below:
ClipboardPage Page = tools.findPage("TempEmployeeDetails");
ClipboardProperty PageListProperty = Page.getProperty(".pxResults");
try {
int TemplateColSize = 0;
int CurrentColCount=0;
java.io.FileInputStream file = new java.io.FileInputStream(new java.io.File("C:\\EmployeeDetailsData1.xls"));
java.io.FileInputStream Templatefile = new java.io.FileInputStream(new java.io.File("C:\\EmployeeDetails1.xls"));
java.util.List<String> TemplateColString = new java.util.ArrayList<String>();
//Reading Property Name from the Template File
org.apache.poi.hssf.usermodel.HSSFWorkbook Templateworkbook = new org.apache.poi.hssf.usermodel.HSSFWorkbook(Templatefile);
org.apache.poi.hssf.usermodel.HSSFSheet Templatesheet = Templateworkbook.getSheetAt(0);
//Iterate through each rows from first sheet
java.util.Iterator rowIterator = Templatesheet.iterator();
while(rowIterator.hasNext()) {
org.apache.poi.ss.usermodel.Row row = (org.apache.poi.ss.usermodel.Row) rowIterator.next();
System.out.println(row.getRowNum());
if(row.getRowNum()==1){
//For each row, iterate through each columns
java.util.Iterator cellIterator = row.cellIterator();
while(cellIterator.hasNext()) {
org.apache.poi.ss.usermodel.Cell cell = (org.apache.poi.ss.usermodel.Cell) cellIterator.next();
switch(cell.getCellType()) {
case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_STRING:
TemplateColString.add(cell.getStringCellValue());
//System.out.print(cell.getBooleanCellValue() + "\t\t");
break;
}
}
}
System.out.println("");
}
Templatefile.close();
System.out.println(TemplateColString);
TemplateColSize = TemplateColString.size();
//Iterator TemplateColStringItr = TemplateColString.iterator();
System.out.println("Size of Template Excel-->"+TemplateColString.size() +TemplateColString.get(0));
//********* Reading Template File Ends Here************//
int IntColValue=0;
String StringColValue="";
boolean BooleanColValue=false;
// Reading Data from the file
org.apache.poi.hssf.usermodel.HSSFWorkbook workbook = new org.apache.poi.hssf.usermodel.HSSFWorkbook(file);
org.apache.poi.hssf.usermodel.HSSFSheet sheet = workbook.getSheetAt(0);
//Iterate through each rows from first sheet
java.util.Iterator rowIterator1 = sheet.iterator();
while(rowIterator1.hasNext()) {
org.apache.poi.ss.usermodel.Row row = (org.apache.poi.ss.usermodel.Row) rowIterator1.next();
CurrentColCount=0;
//Create a New Page
if(row.getRowNum()>0){
//Replace Data- with Class name for which pxResults Should Form
ClipboardPage PageListProp= tools.createPage("Data-", "EmployeeDetail" );
//For each row, iterate through each columns
Iterator cellIterator = row.cellIterator();
while(cellIterator.hasNext()) {
if(CurrentColCount<TemplateColSize){
org.apache.poi.ss.usermodel.Cell cell = (org.apache.poi.ss.usermodel.Cell) cellIterator.next();
String NameSet=TemplateColString.get(CurrentColCount);
switch(cell.getCellType()) {
case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_BOOLEAN:
//System.out.print("NameSet "+NameSet+" "+cell.getBooleanCellValue() + "\t\t");
BooleanColValue = cell.getBooleanCellValue();
StringColValue = Boolean.toString(BooleanColValue);
PageListProp.putString(NameSet, StringColValue);
break;
case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_NUMERIC:
//System.out.print("NameSet "+NameSet+" "+cell.getNumericCellValue() + "\t\t");
IntColValue = (int) cell.getNumericCellValue();
StringColValue = Integer.toString(IntColValue);
PageListProp.putString(NameSet, StringColValue);
break;
case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_STRING:
//System.out.print("NameSet "+NameSet+" "+cell.getStringCellValue() + "\t\t");
StringColValue = cell.getStringCellValue();
PageListProp.putString(NameSet, StringColValue);
break;
}
//System.out.print(NameSet+cell.getStringCellValue());
CurrentColCount++;
}
}
PageListProperty.add(PageListProp);
}
//Add the Newly Added Page to some Page List
System.out.println("");
}
file.close();
java.io.FileOutputStream out =
new java.io.FileOutputStream(new java.io.File("C:\\test.xls"));
workbook.write(out);
out.close();
} catch (java.io.FileNotFoundException e) {
e.printStackTrace();
} catch (java.io.IOException e) {
e.printStackTrace();
}
