Forum Discussion

Samrayen's avatar
Samrayen
Occasional Contributor
4 months ago
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
 
addId1addId2addId3addId4addId5addId6addId7addId8addId9addId10
test2test2test2test2910
21222122212221221920
^%^&%%32^%^&%%32^%^&%%32^%^&%%321920
41 41 41 41 1920
511.0511.0511.0511.01920
 
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()
 
 
  • nmrao's avatar
    nmrao
    4 months ago

    Samrayen 

    What you can do is set the right data type in the spread sheet and save it as .csv.

    Hope it will resolve it without needing any changes.