Ask a Question

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

mani1
Occasional Contributor

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

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

 

 

 

 

 

4 REPLIES 4
nmrao
Community Hero

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


Regards,
Rao.
mani1
Occasional Contributor

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
Community Hero

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.


Regards,
Rao.
mani1
Occasional Contributor

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

cancel
Showing results for 
Search instead for 
Did you mean: