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 :)

7 comments:

  1. 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
  2. Could you please provide an example of how to write in an existing excel file

    ReplyDelete
  3. 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
  4. The "read" section no longer works. It runs, but a1 returns a jxl.read.biff.Label msg instead of a value. When I converted it to a string (like you did below in your code), it worked:

    Cell a1 = sheet.getCell(0,0);
    String z1 = a1.getContents();
    log.info ("a1= "+z1);

    Also, your comments read "// getCell(row,column) -- place some values in myfile.xls"
    Actually, it reads column, row - but even the Java Docs aren't very clear (http://jexcelapi.sourceforge.net/resources/javadocs/2_6/docs/index.html). They show the parms as I mentioned, but the description switches the order:
    getCell(int column, int row)
    Returns the cell specified at this row and at this column.
    Finally, you say "...place some values in myfile.xls" A Get won't place (write) a value, it reads a value.
    Your code worked, which is very good and puts you in an elite group. The vast majority of examples don't work; won't even compile.

    ReplyDelete
  5. Here is an example of a working SoapUI script using Groovy doing data-driven testing reading from Excel .xls and populating a Request. It also reads the Response and compares it to the expected result from the same Excel sheet. Watch it at https://www.youtube.com/watch?v=v2aNSUZtFRY&t=1s

    ReplyDelete
  6. Hello Everyone I am getting below error, can anyone know how to rectify this

    org.codehaus.groovy.control.MultipleCompilationErrorsException: startup failed: Script7.groovy: 4: unable to resolve class FIleInputStream @ line 4, column 9. def fa= new FIleInputStream("D:\\AgencyList.xls"); ^

    ReplyDelete