Hi how can i save web service results (JSON) first 100 lines into an excel sheet using groovy script

Hi

In my project i am saving the web service request and result into an excel sheet. But when my result size is too large it doesnt get fit into single cell and excel gets crashed and didnt recover the data in excel.

how can i save web service results (JSON) only first 100 lines into an excel sheet using groovy script.

I am using SOAUPUI open source. can anyone pls send me the code to save only first few lines.

My code below.

//*******************************Verify that WorkBook File Exists or not. *******************************//
//*******************************if Not Present, Create New Excel File.*******************************//
//*******************************if Present, Do nothing*******************************//
Path path = Paths.get(filePathString);
if (Files.exists(path))
{
log.info "WorkBook is Already Present."
}
else
{
log.info "WorkBook is Not Present."
log.info "Creating and Saving NewWorkBook"
workbookW = Workbook.createWorkbook(new File(filePathString))
workbookW.createSheet(sheetName, 0)
sheet = workbookW.getSheet(sheetName)
sheet.addCell(new Label(0,0,"API Name"));
sheet.addCell(new Label(1,0,"Request Header"));
sheet.addCell(new Label(2,0,"Request Method"));
sheet.addCell(new Label(3,0,"Request Media Type"));
sheet.addCell(new Label(4,0,"Request Body"));
sheet.addCell(new Label(5,0,"Request Endpoint"));
sheet.addCell(new Label(6,0,"Response Header"));
sheet.addCell(new Label(7,0,"Response Body"));
sheet.addCell(new Label(8,0,"API Execution Status"));
workbookW.write()
workbookW.close()
}

//*******************************Verify that Sheet Exists or not. *******************************//
//*******************************If Present, Delete and then Create Same Sheet.*******************************//
//*******************************if Not Present, Create New Sheets.*******************************//
workbookR = Workbook.getWorkbook(new File(filePathString))
workbookW = Workbook.createWorkbook(new File(filePathString), workbookR);

if (workbookW.getSheet(sheetName) == null)
{
log.info "Current Test Suite sheet for Partner is Not Present ==> " + sheetName
log.info "Creating New Sheet for Same"
workbookW.createSheet(sheetName, 0)
}
else
{
log.info "Current Test Suite sheet for Partner is Present ==> " + sheetName
log.info "Modifying Existing Sheet"
sheet = workbookW.getSheet(sheetName)
sheet.setName("AbcdTempAbcd");
log.info "Creating New Sheet for Same"
workbookW.createSheet(sheetName,0)
log.info "Deleting Renamed Sheet"
for(i=0; i< workbookW.getNumberOfSheets();i++)
{
sheet = workbookW.getSheet(i)
if (sheet.getName()=="AbcdTempAbcd")
{ workbookW.removeSheet(i); break; }
}
}

sheet = workbookW.getSheet(sheetName)
sheet.addCell(new Label(0,0,"API Name"));
sheet.addCell(new Label(1,0,"Request Header"));
sheet.addCell(new Label(2,0,"Request Method"));
sheet.addCell(new Label(3,0,"Service_name"));
sheet.addCell(new Label(4,0,"Request Body"));
sheet.addCell(new Label(5,0,"Request Endpoint"));
sheet.addCell(new Label(6,0,"Response Header"));
sheet.addCell(new Label(7,0,"Response Body"));
sheet.addCell(new Label(8,0,"API Execution Status"));

workbookW.write()
workbookW.close()
workbookR.close()

//*******************************Add Appropiate Data.*******************************//
//log.info new Date("Tue Mar 21 19:51:27 EDT 2017")
//log.info new Date("Tue Mar 21 19:51:27 EDT 2017").format( 'MM/dd/yyyy' )
//log.info new Date("Tue Mar 21 19:51:27 EDT 2017").format( 'hh.mm.ss aa' )
log.info "Saving Test Step and Its Status in Excel Sheet"
workbookR = Workbook.getWorkbook(new File(filePathString))
workbookW = Workbook.createWorkbook(new File(filePathString), workbookR);
sheet = workbookW.getSheet(sheetName)
def i = 1
testRunner.testCase.testSuite.testCaseList.each
{it.testStepList.each{
def nameStep = it.name.toString()
def configStep = it.config.type
if ((!it.disabled) && configStep.contains("restrequest"))
{
assertionsList = it.getAssertionList()
CountT=0;CountP=0;CountF=0;CountU=0;
for (assertion in assertionsList)
{
CountT=CountT+1
if(assertion.getStatus().toString() == "VALID")
{ CountP=CountP+1; }
else if(assertion.getStatus().toString() == "FAILED")
{ CountF=CountF+1; }
else if(assertion.getStatus().toString() == "UNKNOWN")
{ CountU=CountU+1; }
}
status=""
if (CountT == 0)
{ status = "UNKNOWN"; }
else
{
if(CountT==CountP) {status="PASSED";}
if(CountT==CountF) {status="FAILED";}
if(CountT==CountU) {status="UNKNOWN";}
if(CountU != 0) {status="UNKNOWN";}
if(CountF != 0) {status="FAILED";}
}

sheet.addCell(new Label(0,i,nameStep));
sheet.addCell(new Label(1,i,it.getHttpRequest().getRequestHeaders().toString()));
sheet.addCell(new Label(2,i,"${it.getRestMethod().getMethod()}"));
//sheet.addCell(new Label(3,i,it.getHttpRequest().getMediaType().toString()));
//sheet.addCell(new Label(3,i,testRunner.testCase.getPropertyValue("Service_name").toString()));
sheet.addCell(new Label(3,i,it.testCase.getPropertyValue("Service_name").toString()));

sheet.addCell(new Label(4,i,it.getPropertyValue("Request").toString()));
sheet.addCell(new Label(5,i,it.getHttpRequest().getEndpoint().toString()));
if (it.testRequest.response != null)
{
sheet.addCell(new Label(6,i,it.testRequest.response.getResponseHeaders().toString()));
sheet.addCell(new Label(7,i,it.getPropertyValue("Response").toString()));
}
sheet.addCell(new Label(8,i,status));
i=i+1;
}
}}
workbookW.write()
workbookW.close()
workbookR.close()