Monday, January 8, 2018

Apache POI Read Excel For use of Selenium TestNG DataProvider

Apache POI Read Excel For use of Selenium TestNG DataProvider


Here is the Class designed for reading Excel file...
You can download the JAR files needed to work with Apache POI here.

package Excel;

import java.io.File;
import java.io.FileInputStream;
import org.apache.poi.ss.usermodel.*;
import java.io.IOException;
import java.io.InputStream;
import java.util.Enumeration;
import java.util.Hashtable;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.dom4j.DocumentException;

public class POI {
int rowIndex = 0, columnIndex = 0;
Workbook wb;
Sheet ws;
Row wr;
String fileName, sheetName;
Hashtable <String, String> [] data = null;

public  void open() throws IOException  {
if (fileName.indexOf("xlsx") < 0) {
wb = new HSSFWorkbook(new FileInputStream(new File(fileName)));
ws = wb.getSheet(sheetName);
} else {
wb = new XSSFWorkbook(fileName);
ws = (XSSFSheet) wb.getSheet(sheetName);
}
}

@SuppressWarnings("unchecked")
public Object[][] getData() throws IOException  {
data = new Hashtable[ws.getPhysicalNumberOfRows()];                            
wr = ws.getRow(0);

for(rowIndex = 1; rowIndex < ws.getPhysicalNumberOfRows(); rowIndex++) {
data[rowIndex - 1] = new Hashtable <String, String>();

for (columnIndex = 0; columnIndex < ws.getRow(rowIndex).getPhysicalNumberOfCells(); columnIndex++) {
data[rowIndex - 1].put(wr.getCell(columnIndex).toString(), ws.getRow(rowIndex).getCell(columnIndex).toString());
}
}

Object[][] obj = new Object[data.length - 1][1];
for(int i = 0; i < data.length - 1; i++) {
obj[i][0] = data[i];
}
return obj;
}


       public  void close() throws IOException  {
            wb = null
            ws = null
            wr = null
            data = null
}


}



visit link download

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.