Forum Discussion

mani1's avatar
mani1
Occasional Contributor
7 years ago

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 n...
nmrao's avatar
nmrao
Champion Level 3
If you search (either on internet or in this forum), am sure you get sample code snippets. Doesn't it work?
mani1's avatar
mani1
Occasional Contributor
7 years ago

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();

 

 

 

    • mani1's avatar
      mani1
      Occasional Contributor
      7 years ago

      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