cancel
Showing results for 
Search instead for 
Did you mean: 

Getting corrupted excel file created through groovy script

Highlighted
Contributor

Getting corrupted excel file created through groovy script

Hi ,

 

I'm using below script to create a report of my test execution. Since some of my response body has huge size , which cannot be accommodated into an excel cell I have used a logic to just write that "reponse is too big" instead of writing  my original response. Whenever my response is huge my report is getting corrupted and some of the data is not getting written to the output file. Please suggest me on how to get this issue resolved. Attached the screenshot of error I'm getting.error.png

 

import static java.nio.charset.StandardCharsets.*;
import java.lang.*;
import com.eviware.soapui.model.testsuite.Assertable.AssertionStatus
import com.eviware.soapui.impl.wsdl.teststeps.WsdlTestRequestStep
import com.eviware.soapui.support.XmlHolder
import jxl.*
import jxl.write.*
import org.skyscreamer.jsonassert.JSONAssert
import org.skyscreamer.jsonassert.JSONCompareMode
import groovy.json.JsonSlurper
def count=0,i=1,j=1,k=1,pass_count=0,fail_count=0
def inputFileName,outputFileName
def inputFile = testRunner.testCase.testSuite.getPropertyValue("inputFileName")
def outputFile = testRunner.testCase.testSuite.getPropertyValue("outputFileName")
def testStep = testRunner.testCase.testSteps["Properties"]

 

Workbook inputWB = Workbook.getWorkbook(new File(inputFile)) 
Sheet inputSheet = inputWB.getSheet(0)
no_of_rows= inputSheet.getRows().toInteger() 

 

Workbook existingInputWB=Workbook.getWorkbook(new File(inputFile))
WritableWorkbook outputWB=Workbook.createWorkbook(new File(outputFile),existingInputWB)

 

testCaseName=testRunner.testCase.name
outputWB.createSheet(testCaseName,2)
WritableSheet sheet_writable = outputWB.getSheet(testCaseName)
(0..context.testCase.testStepCount-1).each{
    def step = context.testCase.testStepList[it]
    if ( step instanceof WsdlTestRequestStep) {
        jxl.write.Label stepName =  new jxl.write.Label(0,i ,step.name)
        sheet_writable.addCell(stepName)
        def tr=testRunner.testCase.getTestStepByName(step.name)
          def String endPointUrl= tr.getHttpRequest().getResponse().getURL()
        jxl.write.Label reqUrl =  new jxl.write.Label(2,i ,endPointUrl)
        sheet_writable.addCell(reqUrl)
        def payload = context.expand(step.getPropertyValue('Request'))
        jxl.write.Label reqPayload =  new jxl.write.Label(3,i ,payload)
        sheet_writable.addCell(reqPayload)
          def response = context.expand(step.getPropertyValue('Response'))
        if(response.length()<1048576)
		{
        	jxl.write.Label reqResponse =  new jxl.write.Label(4,i ,response)
		sheet_writable.addCell(reqResponse)
		}
		else
		{
			log.info "response too big"
			jxl.write.Label reqResponse =  new jxl.write.Label(4,i ,"Response is too big")
		     sheet_writable.addCell(reqResponse)
		}
        count=count+1
        i=i+1
    }
}
def overall_status=testRunner.testCase.testSuite.getPropertyValue("overallStatus")
def TestCase = testRunner.getTestCase()
def StepList = TestCase.getTestStepList()

 

StepList.each{
    if(it.metaClass.hasProperty(it,'assertionStatus')){
        if(it.assertionStatus == AssertionStatus.FAILED){
          jxl.write.Label anotherWritableCell10 =  new jxl.write.Label(1,j ,"Failed")
          sheet_writable.addCell(anotherWritableCell10)
          fail_count=fail_count+1
        }else if(it.assertionStatus == AssertionStatus.VALID){
            jxl.write.Label anotherWritableCell10 =  new jxl.write.Label(1,j ,"Passed")
          sheet_writable.addCell(anotherWritableCell10)
          pass_count=pass_count+1
        }else if(it.assertionStatus == AssertionStatus.UNKNOWN){
            jxl.write.Label anotherWritableCell10 =  new jxl.write.Label(1,j ,"UNKNOWN (PROBABLY NOT ALREADY EXECUTED)")
          sheet_writable.addCell(anotherWritableCell10)
        }
            j=j+1
    }

 

}
if(overall_status=="Passed" && fail_count>0)
{
   testRunner.testCase.testSuite.setPropertyValue("overallStatus","Failed")
}
outputWB.write()
outputWB.close()
existingInputWB.close()

 

Appreciate your help in advance.

5 REPLIES 5
Highlighted
Community Hero

Re: Getting corrupted excel file created through groovy script

Not sure why the response is required in the report which any way gets saved by the tool if you run the tests from command line.

Like you said, response is too big, what is the use for it? I believe it doesn't even make any difference by writing even small XML.

Just think of what do you lose by not writing response. Instead consider having as many assertions possible to ensure test is passed.


Regards,
Rao.
Highlighted
Contributor

Re: Getting corrupted excel file created through groovy script

Hi @nmrao ,

 

Thanks for your reply. As per the requirements, we need to get the response in the report.

 

Highlighted
Community Leader

Re: Getting corrupted excel file created through groovy script

@roja1 :

 

What i would suggest is store the request and response into txt files and in your Excel sheet write the link of that excel file.

 

This way your excel file will look more neat 🙂


Click "Accept as Solution" if my answer has helped,
Remember to give "Kudos" 🙂 ↓↓↓↓↓



Thanks and Regards,
Himanshu Tayal
Highlighted
Contributor

Re: Getting corrupted excel file created through groovy script

Hi @HimanshuTayal ,

 

Thanks for your reply. I'm not saving my response manually. We need to do this through the groovy script

Highlighted
Community Leader

Re: Getting corrupted excel file created through groovy script

@roja1 ,

 

What i mean was not to store request and response directly into Excel Sheet cell, 

 

Store your request and response into txt file and in excel cell write the link of the txt file:

 

def extRequest = context.rawrequest;
def requestFile = "REQUEST File Path";

def extResponse = context.response;
def responseFile = "RESPONSE File Path";

//Storing Request
def rqfile = new File(requestFile);
rqfile.write(extRequest, "UTF-8");

//Storing Response
def rsfile = new File(responseFile);
rsfile.write(extResponse, "UTF-8");

 

By below command you can add hyperlink in your desired excel cell:

 

//Styling for hyperlink cell
CellStyle hlink_style = workbook.createCellStyle();
Font hlink_font = workbook.createFont();
hlink_font.setUnderline(Font.U_SINGLE);
hlink_font.setColor(Font.COLOR_RED);
hlink_style.setFont(hlink_font);

//adding hyperlink
Hyperlink link = createHelper.createHyperlink(Hyperlink.LINK_FILE);
Cell cell = null;     
cell=row.createCell((short) 1);
cell.setCellValue("Go to Result");
path_f="TXT FILE PATH";
link.setAddress(path_f);        
cell.setHyperlink(link);   

 

 


Click "Accept as Solution" if my answer has helped,
Remember to give "Kudos" 🙂 ↓↓↓↓↓



Thanks and Regards,
Himanshu Tayal
New Here?
Join us and watch the welcome video:
Announcements
Top Kudoed Authors