Trying to process data from an Excel file

Hi hazel_chua :

 

You can use below code to get data from excel sheet and store all the values in properties file and then use it in your Test Step accordingly.

 

import java.io.*
import java.text.SimpleDateFormat;  
import java.util.Calendar;  
import org.apache.poi.ss.usermodel.*
import org.apache.poi.hssf.usermodel.*
import org.apache.poi.xssf.usermodel.*
import org.apache.poi.ss.util.*
import groovy.xml.MarkupBuilder
import groovy.xml.StreamingMarkupBuilder

def fs = new FileInputStream("F:\\temp.xlsx")

Workbook wb = WorkbookFactory.create(fs);
def ws = wb.getSheet("Sheet1");
int r = ws.getLastRowNum();
testRunner.testCase.getTestStepByName("Properties").setPropertyValue("DataRowCount", r.toString());
for(def i = 1 ; i <= r ; i++ ){
	def row = ws.getRow(i)
	def headerRow = ws.getRow(0)
	def noOfCell = row.getLastCellNum();
	for (def j=0;j<noOfCell;j++){
		def headertext = ws.getRow(0).getCell(j).getStringCellValue()
		Cell cell = row.getCell(j)
		if (cell == null || cell.getCellType() == Cell.CELL_TYPE_BLANK) {
			value = "";
		}
		else if(cell.getCellType() == Cell.CELL_TYPE_BOOLEAN){
			value = (cell.getBooleanValue());
		}
		else if(cell.getCellType() == Cell.CELL_TYPE_NUMERIC){
			value = (cell.getNumericCellValue());
		}
		else if(cell.getCellType() == Cell.CELL_TYPE_STRING){
			value = (cell.getStringCellValue());
		}
		else if(cell.getCellType() == Cell.CELL_TYPE_ERROR){
		}
		else if(cell.getCellType() == Cell.CELL_TYPE_FORMULA){
			value = String.valueOf(cell.getStringCellValue())
		}
 		testRunner.testCase.getTestStepByName("Properties").setPropertyValue(headertext.toString()+""+i.toString(), value.toString())
	}
}