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

Charanyasv

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

jakubiak

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

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

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

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

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).