How to write the Soap response in to an existing excel file in specific cell

[mani1]

Hi

Can any one help me,  how to write the Soap response in to an existing excel file in specific cell, I tried a lot, but not able to find solution yet.

I am able to save the response in to new excel file. I am new to soapUI and groovy scripting. Please let me know if any other details are required from my end.

Thanks & Regards,

Murugan Chelliah

[nmrao]

If you search (either on internet or in this forum), am sure you get sample code snippets. Doesn't it work?

[mani1]

Hi Rao,

Thanks for your quick response.

Yes, I tried, searched in intranet and in forum, but I didn't get the correct sample code.  Can you please help me on this..

I tried the below code, But it is giving the error  "jxl.read.biff.BiffException unable to recognize OLE stream"

import jxl.*
import jxl.write.*
import jxl.read.biff.BiffException;

def groovyUtils = new com.eviware.soapui.support.GroovyUtils( context )
def holder = groovyUtils.getXmlHolder("validateFile#Response")
//def request=groovyUtils.getXmlHolder("validateFile#Request")

log.info holder.getXml()

def inputDataFileName= "D:\\userdata\\mchellia\\Desktop\\SSDP\\SSDP Test Automation\\NSCMD\\InputValues\\tmp_input_copy1.xls"
def inputDataSheetName="Sheet1"
Workbook workbook = Workbook.getWorkbook(new File(inputDataFileName));
WritableWorkbook copy = Workbook.createWorkbook(new File(inputDataFileName),workbook);
WritableSheet sheetl = copy.getSheet(inputDataSheetName);

log.info(sheet1.isHidden())

xPath1 = "//*:uniqueid/text()"
log.info holder.getNodeValue(xPath1)

Cell cell2Update = sheet.getRow(3).getCell(19);
cell2Update.setCellValue(xPath1);

copy.write();
copy.close();
workbook.close();

[nmrao]

1. You can try other sample code, there are plenty such as
http://www.javabykiran.com/tutorials/how-to-set-data-into-excel-sheet-using.jxl/how-to-set-data-into...

http://www.quicklyjava.com/write-to-excel-in-java/

2. I am particularly not favour of saving response into excel (column ). Excel becomes clutter. Instead you can save it a file and have the file name in the column. It's just my opinion and upto you.

[mani1]

Hi,

Finally I find the solution. Find the below code

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.ss.usermodel.*;
import java.io.*;

def groovyUtils = new com.eviware.soapui.support.GroovyUtils( context )
def holder = groovyUtils.getXmlHolder("validateFile#Response")

log.info "soap xml >>>>>>>>>>>>>>>>:"+holder.getXml()

xPath1 = "//*:uniqueid/text()" 
  
log.info "uniqueid value >>>>>>>>>>>>>>>:"+holder.getNodeValue(xPath1)
iniqueidVal=holder.getNodeValue(xPath1)

 FileInputStream fIpStream= new FileInputStream(new File("D:\\tmp_input.xls"));
    
                    HSSFWorkbook wb = new HSSFWorkbook(fIpStream);
                      // accessing your existing sheet
                    HSSFSheet worksheet = wb.getSheetAt(0);
                      Row row = worksheet.getRow(2);
                 //Create a new cell in current row
                 Cell cell = row.createCell(18);
                 cell.setCellValue(iniqueidVal);
    //log.info(xPath1)
     fIpStream.close(); //Close the InputStream
     FileOutputStream output_file =new FileOutputStream(new File("D:\\tmp_input.xls")); 
                    wb.write(output_file);

                    output_file.close();

Thanks

Mani