Tuesday, 17 September 2013

Reading/Writing from Excel in soap UI using groovy

Dowload jexcelapi_2_6_12 and put it in ext folder of soap ui .. .restart SOAP UI.. create one sample xls and paste the below code in soap ui...

import jxl.*
import jxl.write.*

//to read from excel
Workbook excelFile= Workbook.getWorkbook(new File("c:\\Testing\\myfile.xls"));
Sheet sheet = excelFile.getSheet(0)
Cell a1 = sheet.getCell(0,0)
log.info a1 // getCell(row,column) -- place some values in myfile.xls
Cell b2 = sheet.getCell(1,1)  // then those values will be acessed using a1, b2 & c3 Cell.
Cell c2 = sheet.getCell(2,1)
String s1 = c2.getContents();
log.info s1
excelFile.close()

// To write in excel
WritableWorkbook workbook1 = Workbook.createWorkbook(new File("c:\\Testing\\myfile.xls"))
WritableSheet sheet1 = workbook1.createSheet("Worksheet Number 1", 0)
log.info(sheet1.isHidden())
Label label = new Label(0, 1, "some data to put");
sheet1.addCell(label);
workbook1.write()
workbook1.close()


Disclaimer-- code copied from various blogs :)

5 comments:

  1. hi,we provide online training & video tutorial for soapui
    for free videos refer
    http://soapui-tutorial.com/soapui-tutorial/introduction-to-webservices/

    ReplyDelete
  2. Hi there... hoping you might be able to help... would you have any further details on how to ensure that the jexcelapi_2_6_12 download is installed correctly. I have downloaded it and extracted it which extracts to a bunch of folders... do I place the full folder stack inside the ext folder or is there further manipulation required,... For me the script is not recognizing the Workbook element which tells me it is not seeing the import?.. thanks a million Tony

    ReplyDelete
  3. Could you please provide an example of how to write in an existing excel file

    ReplyDelete
  4. While executing above code getting below error-

    org.codehaus.groovy.control.MultipleCompilationErrorsException: startup failed: Script2.groovy: 5: unable to resolve class Workbook @ line 5, column 10. Workbook excelFile= Workbook.getWorkbook(new File("D:\\SoapAutomation1.xls")); ^ org.codehau

    Any suggestion to resolve it

    ReplyDelete