Forum Discussion

biikej's avatar
biikej
Occasional Visitor
3 years ago

Reading Excel Data with groovy and execute request

Hi Everyone

 

I want to use Excel as a Datasource (Testdata) to execute an request where it creates me a Person with information about him.

I want to be able to execute one or five testdatas at once. 

I saw some videos regarding this issue but somehow I am not able to do it since my data is different then theirs and im not very good in groovy script. 

 

My dataset looks like this: 

 

AttributeDataset1Dataset2Dataset3
Languagedefren
NameQWERTZ
SurnameASDFGH
DegreeFalseTrueFalse
GenderMaleFemaleMale
Birthdate1988-01-011989-01-011990-01-01

 

Could someone help me with the groovy script? 

Vallalarasu_P I tried your version with csv and that would work but the birthdate is changing for me from "yyyy-mm-dd" to "dd-mm-yyyy" when I save the file.  

  • ChrisAdams's avatar
    ChrisAdams
    Champion Level 3

    Hi, 

     

    If you are using the licensed version, then you do not need Groovy script to implement data-driven testing.

    If you're using the open source SoapUI, then you will and it is a little complicated.  If you Google "soapui data driven testing groovy", you get lots of results where others provide the scripts to implement.

  • bqualters's avatar
    bqualters
    Occasional Contributor

    Saw this on another post - thought it might be helpful...it's something I'm about to try myself - looks pretty straightforward - 1)  define the workbook where the Excel sheet is stored

    2) define the sheet name In the workbook (file) as there can be many sheets to a file

    3) get the number of rows and columns dynamically and store in arrays to allow to use in loop creation

    4) loop through the data first by row and then within each row loop through columns

    5) write out the contents of each cell based on the  row/cell identifier

       -instead of writing out the value - assign the value of the cell to a variable and use it to drive your test

       -if you have this in an Excel file the date format is set by the file itself...

     

    import jxl.*

    Workbook workbook = Workbook.getWorkbook(new File("D:/SOAPUI/ConversionRate.xls"))
    Sheet sheet1 = workbook.getSheet("DataDriven")
    def rows = sheet1.getRows()
    def cols = sheet1.getColumns()
    log.info "Row Count =" + rows
    log.info "Column Count =" + cols
    for(i=1;i<rows;i++) {
    for(j=0;j<cols;j++) {
    Cell cell = sheet1.getCell(j,i)
    log.info cell.getContents()
    }
    }


    Hope this helped...as I said - I'm new too so ...not sure if there is a formatDate command or such you could use...anyone...?

    import jxl.*
    
    Workbook workbook = Workbook.getWorkbook(new File("D:/SOAPUI/ConversionRate.xls"))
    Sheet sheet1 = workbook.getSheet("DataDriven")
    def rows = sheet1.getRows()
    def cols = sheet1.getColumns()
    log.info "Row Count =" + rows 
    log.info "Column Count =" + cols 
    for(i=1;i<rows;i++) { 
        for(j=0;j<cols;j++) { 
           Cell cell = sheet1.getCell(j,i) 
           log.info cell.getContents() 
        } 
    }