CODEWITHSUNDEEP
excelgroovysoapuijxl
vihang shah
vihang shah

Reputation: 608

only first response is being written in excel file in saopui

I am new to saopUI and groovy.I want to read data from excel file and run into request and write data back to excel file. my reading and executing portion is working fine. but when I am writing response back to excel only first response is being written. but Whne I see log all three request were run successfully. Please help me out in this.

Here is my groovy script:

import jxl.*
import jxl.write.*

def dataFileLocation="D:/SOAP/input.xls"
//Datasheet read start
def workbook = Workbook.getWorkbook(new File(dataFileLocation))
def sheet = workbook.getSheet(0)
int count=workbook.getNumberOfSheets() 
def rowCount = sheet.getRows()
def colCount = sheet.getColumns()
def myTestCase = context.testCase
propTestStep = myTestCase.getTestStepByName("Properties");
def arr=[]
//Datasheet read end

//Content Write start
WritableWorkbook workbook1 = Workbook.createWorkbook(new File("D:/SOAP/output1.xls"))
WritableSheet sheet1 = workbook1.createSheet("Worksheet 1", 0)


//Content Write end
for(int i = 0;i < rowCount; i++){
    for(int j = 1;j < colCount+1; j++){
       arr[i]= sheet.getCell(j-1,i).getContents()
       def val=arr[i]
       propTestStep.setPropertyValue("zip",val)
       def project = testRunner.testCase.testSuite.project

       def aa=testRunner.runTestStep( project.testSuites['USZipSoap TestSuite'].testCases['GetInfoByZIP TestCase'].testSteps['GetInfoByZIP'] )
       def response = testRunner.testCase.testSteps["GetInfoByZIP"].testRequest.response.contentAsString
       Label label = new Label(j,i,response);
       log.info label
       sheet1.addCell(label);
       workbook1.write()

       log.info j+" " +i+" "+"  "+response 
    }
 }
 workbook1.close()

Upvotes: 1

Views: 257

Answers (1)

Emmanuel Rosa
Emmanuel Rosa

Reputation: 9885

JXL is weird about how it writes to an Excel workbook. Basically, write() doesn't actually write to the file, instead it writes to an in-memory structure. You can read a bit more about it here.

So moving the write() out of the loop, to just before the close() should fix it.

An alternative

I created a Groovy library named Frosted Sheets. It decorates Apache POI to make it super easy to work with Excel spreadsheets. Frosted Sheets can simply your code to something like this:

import org.apache.poi.hssf.usermodel.HSSFWorkbook
import com.emmanuelrosa.frostedsheets.*

def dataFileLocation='D:/SOAP/input.xls'
def workbook = new FrostedWorkbook(new HSSFWorkbook(new FileInputStream(dataFileLocation)))
def sheet = workbook[0] /* Index-based access to sheets */
def myTestCase = context.testCase
propTestStep = myTestCase.getTestStepByName("Properties")

def workbook1 = new FrostedWorkbook(new HSSFWorkbook())
def sheet1 = workbook1['Worksheet 1'] /* Map-like access to sheets */

sheet.eachWithIndex { row, rindex -> /* Iterate through each row in a sheet. */
    def output = []

    row.eachWithIndex { cell, cindex -> /* Iterate through the columns of a row. */
        propTestStep.setPropertyValue("zip", cell.cellValue)
        def project = testRunner.testCase.testSuite.project

        def aa=testRunner.runTestStep( project.testSuites['USZipSoap TestSuite'].testCases['GetInfoByZIP TestCase'].testSteps['GetInfoByZIP'] )
        def response = testRunner.testCase.testSteps["GetInfoByZIP"].testRequest.response.contentAsString
        output << response
        log.info "$cindex $rindex $response" 
    }

    sheet1 << output /* Appends a new row to the sheet. */
}

workbook1.write(new FileOutputStream('D:/SOAP/output1.xls'))

Upvotes: 1

Related Questions