Parse Excel Using java - POI jar - for xls

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();

}