SoapUI: Groovy Snippets for Apache POI

Excel

Requires Apache  POI: poi.apache.org/

This module provide an interface for Excel spreadsheet files

import org.apache.poi.hssf.usermodel.*;
sourceSheet = srcBook.getSheetAt(0)
sourceSheet = srcBook.getSheet("TestData2")

log.info( "Test Case Setup..." )

import org.apache.poi.hssf.usermodel.*;

//Create data formatter
dFormatter = new HSSFDataFormatter()

//Create a new workbook using POI API
sFileName = "C:\\PerTableData\\TestData-Mix.xls"
log.info( "TestCase DataSource is  : " + sFileName)
srcBook = new HSSFWorkbook(new FileInputStream(new File(sFileName)))

//Create formula evaluator to handle formula cells
fEval = new HSSFFormulaEvaluator(srcBook)

//Get first sheet of the workbook (assumes data is on first sheet)
//sourceSheet = srcBook.getSheetAt(0)
sourceSheet = srcBook.getSheet("TestData2")


//Sets row counter to 0 (first row)-- if your sheet has headers, you can set this to 1
context.rowCounter = 0

//Read in the contents of the first row
sourceRow = sourceSheet.getRow(0)

//Step through cells in the row and populate property values-- note the extra work for numbers
elNameCell = sourceRow.getCell(0)
testCase.setPropertyValue("ElName",dFormatter.formatCellValue(elNameCell,fEval))

atNumCell = sourceRow.getCell(1)
testCase.setPropertyValue("AtNum",dFormatter.formatCellValue(atNumCell,fEval))

symbolCell = sourceRow.getCell(2)
testCase.setPropertyValue("Symbol",dFormatter.formatCellValue(symbolCell,fEval))

atWtCell = sourceRow.getCell(3)
testCase.setPropertyValue("AtWeight",dFormatter.formatCellValue(atWtCell,fEval))

boilCell = sourceRow.getCell(4)
testCase.setPropertyValue("BoilPoint",dFormatter.formatCellValue(boilCell,fEval))


//Rename request test steps for readability in the log; append the element name to the test step names
//testCase.getTestStepAt(0).setName("GetAtomicNumber-" + testCase.getPropertyValue("AtNum"))
//testCase.getTestStepAt(1).setName("GetAtomicWeight-" + testCase.getPropertyValue("AtWeight"))
testCase.getTestStepAt(0).setName("GetElementSymbol-" + testCase.getPropertyValue("Symbol"))

//Add references to sheet to re-use it in ReadNextLine step
context.srcWkSheet = sourceSheet
log.info( "Test Case Setup Done..." )