How to write Output result and message to Output Excel File?

Hi, I am try to implement selenium with refer to your Hybrid Automation Framework

My goals are input data store in testData Excel file, output data store in testOutput Excel file.

My question are
1. How to implement a test case which can test many row of data

(e.g. 1st row to test positive data

2nd row to test negative data

3rd row to test blank data

4th row to test boundary data)

2. How to write the overall test result and log message into output excel file

if my input test row have 4 row, then my output also results in 4 row
Sample output excel file
How to write Output result and message to Output Excel File?

Add Comment
1 Answer(s)

Hi

You have to write read and write method for excel, Please find below steps,

1. Add all jar files of Apache POI in your project

2. Create new package and write below class in one package for reading and writing into excel file.

package utility;

import java.io.FileInputStream;
import java.io.FileOutputStream;

import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class icExcel {

public static XSSFSheet ExcelWSheet;

public static XSSFWorkbook ExcelWBook;

public static XSSFCell Cell;

public static XSSFRow Row;

//This method is to set the File path and to open the Excel file, Pass Excel Path and Sheetname as Arguments to this method

public static void setExcelFile(String Path,String SheetName) throws Exception {

try {

// Open the Excel file

FileInputStream ExcelFile = new FileInputStream(Path);

//Access the required test data sheet

ExcelWBook = new XSSFWorkbook(ExcelFile);

ExcelWSheet = ExcelWBook.getSheet(SheetName);

} catch (Exception e){

throw (e);

}

}

//This method is to read the test data from the Excel cell, in this we are passing parameters as Row num and Col num

public static String getCellData(int RowNum, int ColNum) throws Exception{

try{

Cell = ExcelWSheet.getRow(RowNum).getCell(ColNum);

String CellData = Cell.getStringCellValue();

return CellData;

}catch (Exception e){

return””;

}

}
public static int getCellData1(int RowNum, int ColNum) throws Exception{

try {

Cell = ExcelWSheet.getRow(RowNum).getCell(ColNum);

int CellData = (int) Cell.getNumericCellValue();

return CellData;

} catch (Exception e){
return 0;
}

}
//This method is to write in the Excel cell, Row num and Col num are the parameters

public static void setCellData(String Result,  int RowNum, int ColNum) throws Exception    {

try{

Row  = ExcelWSheet.getRow(RowNum);

Cell = Row.getCell(ColNum, Row.RETURN_BLANK_AS_NULL);

if (Cell == null) {

Cell = Row.createCell(ColNum);

Cell.setCellValue(Result);

} else {

Cell.setCellValue(Result);

}

// Constant variables Test Data path and Test Data file name

FileOutputStream fileOut = new FileOutputStream(icconstant.Path_TestData);
//  FileOutputStream fileOut = new FileOutputStream(Constant.Path_TestData + Constant.File_TestData);

ExcelWBook.write(fileOut);

fileOut.flush();

fileOut.close();

}catch(Exception e){

throw (e);

}

}

}

3. Use above methods to read and write your data from excel.

siddheshlabdhe Train Answered on January 9, 2015.
Add Comment

Your Answer

By posting your answer, you agree to the privacy policy and terms of service.