Solved
URGENT-Data driven- TestStep-Could't pass different test data from excel
Hi All,
I am using soapui 5.7.2, from .xlsx sheet, I need to get different testdata from excel and pass it in to my different testSteps.
problem : while using apache poi jar excel value is converting in to float, In my excel I have float, Integer, String and Empty Value
| addId1 | addId2 | addId3 | addId4 | addId5 | addId6 | addId7 | addId8 | addId9 | addId10 |
| test | 2 | test | 2 | test | 2 | test | 2 | 9 | 10 |
| 21 | 22 | 21 | 22 | 21 | 22 | 21 | 22 | 19 | 20 |
| ^%^&%% | 32 | ^%^&%% | 32 | ^%^&%% | 32 | ^%^&%% | 32 | 19 | 20 |
| 41 | 41 | 41 | 41 | 19 | 20 | ||||
| 51 | 1.0 | 51 | 1.0 | 51 | 1.0 | 51 | 1.0 | 19 | 20 |
I my excel sheet I can't set every single excel name as set PropertyValue ,
so that, I am creating a property request dynamically
// Set properties for API request dynamically
testRunner.testCase.setPropertyValue("Param${j + 1}", paramValue)
problem is while retrieving a data from excel, all the Integer value converting a float value
Sat Dec 30 05:26:31 IST 2023:INFO:Param1: test
Sat Dec 30 05:26:31 IST 2023:INFO:Param2: 2.0
Sat Dec 30 05:26:31 IST 2023:INFO:Param3: test
Sat Dec 30 05:26:31 IST 2023:INFO:Param4: 2.0
Sat Dec 30 05:26:31 IST 2023:INFO:Param5: test
Sat Dec 30 05:26:31 IST 2023:INFO:Param6: 2.0
Sat Dec 30 05:26:31 IST 2023:INFO:Param7: test
Sat Dec 30 05:26:31 IST 2023:INFO:Param8: 2.0
Sat Dec 30 05:26:31 IST 2023:INFO:Param9: 9.0
Sat Dec 30 05:26:31 IST 2023:INFO:Param10: 10.0
Sat Dec 30 05:26:32 IST 2023:INFO:Param1: 21.0
Sat Dec 30 05:26:32 IST 2023:INFO:Param2: 22.0
Sat Dec 30 05:26:32 IST 2023:INFO:Param3: 21.0
Sat Dec 30 05:26:32 IST 2023:INFO:Param4: 22.0
Sat Dec 30 05:26:32 IST 2023:INFO:Param5: 21.0
Sat Dec 30 05:26:32 IST 2023:INFO:Param6: 22.0
Sat Dec 30 05:26:32 IST 2023:INFO:Param7: 21.0
Sat Dec 30 05:26:32 IST 2023:INFO:Param8: 22.0
Sat Dec 30 05:26:32 IST 2023:INFO:Param9: 19.0
Sat Dec 30 05:26:32 IST 2023:INFO:Param10: 20.0
Sat Dec 30 05:26:32 IST 2023:INFO:Param1: ^%^&%%
Sat Dec 30 05:26:32 IST 2023:INFO:Param2: 32.0
Sat Dec 30 05:26:32 IST 2023:INFO:Param3: ^%^&%%
Sat Dec 30 05:26:32 IST 2023:INFO:Param4: 32.0
Sat Dec 30 05:26:32 IST 2023:INFO:Param5: ^%^&%%
Sat Dec 30 05:26:32 IST 2023:INFO:Param6: 32.0
Sat Dec 30 05:26:32 IST 2023:INFO:Param7: ^%^&%%
Sat Dec 30 05:26:32 IST 2023:INFO:Param8: 32.0
Sat Dec 30 05:26:32 IST 2023:INFO:Param9: 19.0
Sat Dec 30 05:26:32 IST 2023:INFO:Param10: 20.0
Sat Dec 30 05:26:32 IST 2023:INFO:Param1: 41.0
Sat Dec 30 05:26:32 IST 2023:INFO:Param2:
Sat Dec 30 05:26:32 IST 2023:INFO:Param3: 41.0
Sat Dec 30 05:26:32 IST 2023:INFO:Param4:
Sat Dec 30 05:26:32 IST 2023:INFO:Param5: 41.0
Sat Dec 30 05:26:32 IST 2023:INFO:Param6:
Sat Dec 30 05:26:32 IST 2023:INFO:Param7: 41.0
Sat Dec 30 05:26:32 IST 2023:INFO:Param8:
Sat Dec 30 05:26:32 IST 2023:INFO:Param9: 19.0
Sat Dec 30 05:26:32 IST 2023:INFO:Param10: 20.0
Sat Dec 30 05:26:32 IST 2023:INFO:Param1: 51.0
Sat Dec 30 05:26:32 IST 2023:INFO:Param2: 1.0
Sat Dec 30 05:26:32 IST 2023:INFO:Param3: 51.0
Sat Dec 30 05:26:32 IST 2023:INFO:Param4: 1.0
Sat Dec 30 05:26:32 IST 2023:INFO:Param5: 51.0
Sat Dec 30 05:26:32 IST 2023:INFO:Param6: 1.0
Sat Dec 30 05:26:32 IST 2023:INFO:Param7: 51.0
Sat Dec 30 05:26:32 IST 2023:INFO:Param8: 1.0
Sat Dec 30 05:26:32 IST 2023:INFO:Param9: 19.0
Sat Dec 30 05:26:32 IST 2023:INFO:Param10: 20.0
in my teststep we can't pass the float value toFloat().toInteger() or toFloat().toString ()
for individual excel parameter name we can't convert as
// Extract data from each column
def param1 = row.getCell(0).toString()
// Convert the string to a float and then to an integer
def param1AsInteger = param1.toFloat().toInteger()
my next TestStep I have 40 parameter, this approach is not working
WHAT IS THE BEST WAY TO RESLOVE THE ISSUE
Code:
// Specify the path to your Excel file
def excelFilePath = "D:/gitrepo/datadriven.xlsx"
// Load the Excel workbook
def workbook = new org.apache.poi.xssf.usermodel.XSSFWorkbook(new java.io.FileInputStream(excelFilePath))
// Get the sheet (assuming data is in the first sheet)
def sheet = workbook.getSheetAt(0)
// Define a map to store test step names
def testStepMap = [
1: "Add",
2: "Multiply",
3: "Subtract",
4: "Divide",
5: "Decimal"
]
// Loop through rows (start from 1 to skip header)
for (int i = 1; i <= sheet.lastRowNum; i++) {
def row = sheet.getRow(i)
// Loop through cells in the row
for (int j = 0; j < row.getLastCellNum(); j++) {
// Get the cell value and handle conversion
def cell = row.getCell(j)
def paramValue
if (cell.cellType == org.apache.poi.ss.usermodel.CellType.NUMERIC) {
// Check if the cell value is an integer
if (cell.numericCellValue == cell.numericCellValue.toInteger()) {
paramValue = cell.numericCellValue.toInteger()
} else {
paramValue = cell.numericCellValue
}
} else {
// Handle other types (string, date, etc.)
paramValue = cell.toString()
}
// Log the current parameter value
log.info("Param${j + 1}: ${paramValue}")
// Set properties for API request dynamically
testRunner.testCase.setPropertyValue("Param${j + 1}", paramValue)
}
// Construct the test step name dynamically
def testStepName = testStepMap[i]
if (testStepName) {
// Run the test step
def testStep = testRunner.testCase.getTestStepByName(testStepName)
if (testStep) {
testStep.run(testRunner, context)
} else {
log.warn("Test step not found: ${testStepName}")
}
} else {
log.warn("No test step defined for row ${i}")
}
}
// Close the workbook
workbook.close()
