Submit and vote on feature ideas.

Welcome to the new Parasoft forums! We hope you will enjoy the site and try out some of the new features, like sharing an idea you may have for one of our products or following a category.

Fetch data from a specific row in excel based on a value

Charanyasv
Charanyasv Posts: 11

I would like to fetch a row value from excel sheet (data source). Need to fetch one row value and use it to assert the response.

Scenario:
Create a data source of Excel type. Associate a file to it . The excel file has 30 rows(for eg). I need to fetch the row data corresponding tp one key value.

Eg.
I have an excel sheet like this
Sno Name Percentage
1 xxxx 50%
2 yyyy 80%
3 zzzz 50%

I need to fetch the values of the row having Name = yyyy.

So it will return me one row of data (2nd row) corresponding to name = yyyy.
2 yyyy 80%

In data source, I am able to fetch all rows or Range of row values. How do I fetch value based entries. Need to fetch the row value based on value condition.
How can I achieve this ?

Appreciate your help

Comments

  • jakubiak
    jakubiak Posts: 795 admin

    Using a data source tells SOAtest to iterate through the rows of the data source. You can tell SOAtest to use a specific row or range of rows, but you do that using specific numeric values and cannot do this based on the value in a particular column. So I'm not sure you want to do this using a data source. Instead, you should probably write a script that reads the excel file and finds the data that you want, possibly storing the relevant value(s) into test variables. You can use the Apache POI library to read Excel files: https://poi.apache.org/spreadsheet/how-to.html

  • Charanyasv
    Charanyasv Posts: 11

    Thank you for the response. I will try this approach using APACHE POI . I tried using an Extension tool to write script to manipulate the excel values. I created a method in groovy to access excel files.

    I followed this approach:
    Created a new test suite, Add New --Extension Tool inside the test suite and wrote a groovy script to access excel values.

    Groovy script :

    import org.codehaus.groovy.scriptom.*
    import org.codehaus.groovy.scriptom.tlb.office.excel.*
    def xls = new ActiveXObject("Excel.Application")
    def excelPath = "C:/Desktop/excelfile.xls"
    def dataSheetName = "Sheet1"
    Thread.sleep(1000)
    def openWb = xls.Workbooks.Open(excelPath)
    def dtUsedRange = openWb.Sheets(dataSheetName).UsedRange
    int rCount = dtUsedRange.Rows.Count
    int cCount = dtUsedRange.Columns.Count
    for(int r = 1;r<=rCount;r++){
    for(int c = 1;c<cCount;c++){
    def cValue = openWb.Sheets(dataSheetName).Cells(r,c).Value
    log.info cValue
    }
    }
    openWb.Close(false,null,false)
    xls.Quit()

    Clicking on Evaluate, throws the below error:
    org.codehaus.groovy.control.MultipleCompilationErrorsException: startup failed:, Script98.groovy: 4: unable to resolve class ActiveXObject , @ line 4, column 11., def xls = new ActiveXObject("Excel.Application"), ^, , 1 error,

    Appreciate your help to proceed further and resolve this issue.

  • Charanyasv
    Charanyasv Posts: 11

    Also, please suggest if it is a right approach to run a vbs file using Groovy script in Extension tool. If so, how can I achieve that.

  • Charanyasv
    Charanyasv Posts: 11

    I could also sense that we require appropriate jars and dlls to be added to the workspace for the scripts to run (unable to resolve class- error). Where can I find the files and get them associated?

  • benken_parasoft
    benken_parasoft Posts: 1,228 ✭✭✭
    edited December 2018

    org.codehaus.groovy.scriptom

    I haven't tried using Scriptom before. However, I know you can use the pure Java-based Apache POI library instead (it doesn't require Scriptom).