I need to fetch the Test data from the excel by passing the test case id. If i pass the Test case id as "TC005" - i need to get all the column values like Lab name, Lab Address, City, State , Pincode, Collection center Name, CC Address, City, State & Pincode.
Can anyone tel me how to do it ??
I have worked on it, but I can get only one field.
package com.utils;
import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Properties;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Row;
public class ReadExcel {
static int colTestcase;
static int rowTestcase;
static int rownumber ;
static int colnumber;
static InputStream input = null;
static HSSFWorkbook workbook;
static FileInputStream file ;
static HSSFSheet sheet;
static Properties prop = new Properties();
public static boolean openFile(String fileName) {
try {
file = new FileInputStream(fileName);
workbook = new HSSFWorkbook(file);
return true;
}
catch(Exception e) {
e.printStackTrace();
return false;
}
}
public static void main(String args[])
{
long startTime = System.currentTimeMillis();
System.out.println("startTime: "+startTime);
int rowvalue=readTestCase("TC03");
int columnvalue=readHeader("Service Title");
String value=getValuefromExcel(rowvalue,columnvalue);
System.out.println("Row Value: "+rowvalue);
System.out.println("Col Value: "+columnvalue);
System.out.println("Text: "+value);
long endTime = System.currentTimeMillis();
System.out.println("endTime"+endTime);
System.out.println("Took "+(endTime - startTime) + " milliseconds");
}
public static String getInputData(String Testcase,String header)
{
int rowvalue=readTestCase(Testcase);
int columnvalue=readHeader(header);
String value = getValuefromExcel(rowvalue, columnvalue);
return value;
}
public static String getValuefromExcel(int row, int col)
{
String value =null;
String field="Wrongvalue";
if(!(row==-1||col==-1))
{
try {
System.out.println("Entered");
input = new FileInputStream("src\\com\\config\\config.properties");
// load a properties file
prop.load(input);
openFile(prop.getProperty("excelTestData_FilePath"));
//Get first sheet from the workbook
sheet = workbook.getSheet(prop.getProperty("excelTestData_Sheet"));
HSSFCell cell = null;
//Update the value of cell
cell = sheet.getRow(row).getCell(col);
value = cell.toString();
file.close();
}
catch(Exception e){
e.printStackTrace();
}
System.out.println("value Done");
return value;
}
else {
System.out.println("Given data was wrong");
}
return field;
}
public static int readTestCase(String Testcase)
{
try {
FileInputStream file = new FileInputStream(new File("C:\\Thyagu\\Workspace\\PMModule\\test-input\\PM_ModuleTestData.xls"));
//Get the workbook instance for XLS file
HSSFWorkbook workbook = new HSSFWorkbook(file);
//Get first sheet from the workbook
HSSFSheet sheet = workbook.getSheet("Data");
int rowNum = sheet.getLastRowNum()+1;
int colNum = sheet.getRow(0).getLastCellNum();
for (int i=1; i<rowNum; i++){
HSSFRow row = sheet.getRow(i);
int j=0;
HSSFCell cell = row.getCell(j);
String value = cell.toString();
if(value.equalsIgnoreCase(Testcase))
{
rowTestcase=row.getRowNum();
colTestcase=cell.getCellNum();
return rowTestcase;
}
}
file.close();
}
catch(Exception e){
e.printStackTrace();
}
System.out.println("rownumber Done: " +rowTestcase);
return -1;
}
public static int readHeader(String header)
{
try {
FileInputStream file = new FileInputStream(new File("C:\\Thyagu\\Workspace\\PMModule\\test-input\\PM_ModuleTestData.xls"));
//Get the workbook instance for XLS file
HSSFWorkbook workbook = new HSSFWorkbook(file);
//Get first sheet from the workbook
HSSFSheet sheet = workbook.getSheet("Data");
int rowNum = sheet.getLastRowNum()+1;
int colNum = sheet.getRow(0).getLastCellNum();
int i=0;
HSSFRow row = sheet.getRow(i);
rownumber =row.getRowNum();
for (int j=0; j<colNum; j++){
HSSFCell cell = row.getCell(j);
String value = cell.toString();
if(value.equalsIgnoreCase(header))
{
rownumber=row.getRowNum();
colnumber=cell.getCellNum();
return colnumber;
}
}
file.close();
}
catch(Exception e){
e.printStackTrace();
}
System.out.println("colnumber Done");
return -1;
}
}
Thanks in Advance
