JARs to be added

  • poi-3.8.jar
  • poi-examples-3.8.jar
  • poi-excelant-3.8.jar
  • poi-ooxml-3.8.jar
  • poi-ooxml-schemas-3.8.jar
  • poi-scratchpad-3.8.jar
  • dom4j-1.6.1.jar

Reading Excel Sheet

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 org.apache.poi.ss.usermodel.*;
import java.io.*;
 
class ExcelReader {
  
  def readData() {
        def path = "D:\\Test.xlsx";
        InputStream inputStream = new FileInputStream(path);
        Workbook workbook = WorkbookFactory.create(inputStream);
        Sheet sheet = workbook.getSheetAt(0);
                      
        Iterator rowIterator = sheet.rowIterator();
        rowIterator.next()
        Row row;                       
        def rowsData = []
        while(rowIterator.hasNext()) {
             row = rowIterator.next()
             def rowIndex = row.getRowNum()
             def colIndex;
             def rowData = []
             for (Cell cell : row) {
                 colIndex = cell.getColumnIndex()
                  rowData[colIndex] = cell.getRichStringCellValue().getString();
             }                    
             rowsData << rowData
         }
         rowsData
  }
 }
 
def groovyUtils = new com.eviware.soapui.support.GroovyUtils(context)
def myTestCase = context.testCase
 
ExcelReader excelReader = new ExcelReader();
List rows = excelReader.readData();
def d = []
Iterator i = rows.iterator();
while( i.hasNext()){
         d = i.next();
         myTestCase.setPropertyValue("From", d[0])
         myTestCase.setPropertyValue("To", d[1])       
         testRunner.runTestStepByName( "ConversionRate")
}