Groovy 8 – creating and manipulating Excel file using Groovy script in soapUI

Posted: July 4, 2011 in groovy, soapUI
Tags: , , , , , , , , , , , ,

Many of soapUI users wonder, if they can create and manipulate the data inside an Excel file using the soapUI’s groovy scripting. Well answer is YES and through this blog i am putting an end to their concerns / questions.

As i might have mentioned in my previous blogs that Groovy Script together with soapUI provides utilitarian, seamless & efficient functionalities with infinite possibilities. So, i would say “your thinking is the limit, rest almost everything can be achieved using Groovy in soapUI”.

From eviware forum, i found that soapUI is using the free Java Excel API to communicate or manipulate data in the Excel files. So, all this can be achieved using the JXL.jar file. All you need do is place the JXL api file in the “lib” folder of %SOAPUI_INSTALL_DIR%. This file can be downloaded @ http://sourceforge.net/projects/jexcelapi/files/jexcelapi/2.6.12/jexcelapi_2_6_12.zip/download

/*
@Author : Pradeep Bishnoi
@Description : Manipulate the data inside an Excel file using jxl api in groovy
@Ref : Thanks to Andy Khan for sharing the reference documents about JXL API.
*/

/* code lines first test step*/

import jxl.*
import jxl.write.*
WritableWorkbook workbook = Workbook.createWorkbook(new File("d:\\output.xls"))
WritableSheet sheet = workbook.createSheet("Worksheet 1", 0)
log.info(sheet1.isHidden())
Label label = new Label(0, 2, "Test data in Column A, Row 3"); //column=0=A,row=0=1
sheet.addCell(label);
Label label1 = new Label(2, 2, "Column C, Row 3");
sheet.addCell(label1);
workbook.write()
workbook.close()

/* code lines second test step*/


import jxl.*
import jxl.write.*
Workbook workbook1 = Workbook.getWorkbook(new File("d:\\output.xls"))
Sheet sheet1 = workbook1.getSheet(0)
Cell a1 = sheet1.getCell(0,2) // getCell(row,column) -- place some values in myfile.xls
Cell b2 = sheet1.getCell(2,2)  // then those values will be acessed using a1, b2 & c3 Cell.
Cell c2 = sheet1.getCell(2,1)
log.info a1.getContents()
log.info b2.getContents()
log.info c2.getContents()
workbook1.close()

Keep watching this space… soon i will be posting about “how to export the test result & certain other things using the Groovy into the Excel file”.
NOTE : Off late it has been observed that on performing the copy paste of this code from wordpress to soapui, user are facing some issues. Well it is because soapui script editor doesn’t recognize certain character used by wordpress (like double quotes “). Workaround for this issue is, after pasting the code in editor replace all double quotes + special character manually.
Advertisements
Comments
  1. Karthik says:

    hi Pradeep,

    For report creation using groovy…
    I tried but it is asking some class files… for excel we need jxl ?
    like that for report, do we need any class file,Do you have any idea on that ?

    Regards,
    – Karthik

    • Hi Karthik,

      I didn’t use any specific files for custom report generation using scripts. It’s only that you can use the “report” parameter to manipulate the report data (looks/feel) or you can import the package “com.eviware.soapui.report” which will call all the available reporting methods used by soapUI. Hope this will help.

      Regards,
      /Pradeep Bishnoi

  2. Krishna says:

    Can u please tell me in which soapUI version can I use the above code?

    I am using SoapUI 2.5 version (Not Pro version) and I need to use XLS as the data source and need to compare the reponse with another XLS. Will it be possible using a groovy script?

  3. Karthik says:

    “how to export the test result & certain other things using the Groovy into the Excel ?

    when you will be posting this ?

    i’m expecting it soon…

  4. Lovely just what I was looking for.Thanks to the author for taking his clock time on this one.

  5. manit says:

    I want to append the last row of the excel whenever i call the script.

    How can this be done

    • Hi,

      To append any existing file, you have open the file first and then perform the needed operation.
      Code lines for opening existing file (& will create new if it doesn’t exist) :
      Workbook workbook = Workbook.getWorkbook(new File(“d:\\myExistingFile.xls”));

      Now perform a count operation on the selected sheet & column for non-Null values [i.e., for existing values], that will give you number of existing rows.
      Or
      Maintain another sheet and update a specific cell value [i.e., row counter] with the number of rows added in first run. And for every next run just increment the existing value of the cell before closing the xls file.
      Haven’t written code lines written for this & would be sharing in near future. Hope this will help.

      Best Regards,
      /Pradeep Bishnoi

  6. spaudel says:

    I am getting following error.

    groovy.lang.MissingMethodException: No signature of method: jxl.read.biff.WorkbookParser.write() is applicable for argument types: () values: [] Possible solutions: wait(), wait(long), with(groovy.lang.Closure), wait(long, int), print(java.io.PrintWriter), print(java.lang.Object)

    • Karthik says:

      can we have your code?

    • Hi,

      i guess you must have written different code lines here.. coz above code lines worked fine for me 😉
      btw, looking at the error message i can interpret that you are calling write() method which doesn’t belong the calling object.
      if possible share your code, u might get some input from some one 🙂

      regards,
      /pradeep bishnoi

    • Hi, this error occurs because of first call to workbook.write() .
      Just comment that and it will work.

  7. subhash says:

    how to export the result fom a script in to an excel sheet.
    can anybody help me on this

  8. subhash says:

    how to store data in excel sheet with ‘\’ as seperator through groovy script from SoapUI
    if any one knows how to do this kindly help me.

  9. Ramkumar says:

    Hi,

    I executed above code in Groovy test step, However i could see ArrayIndexOutofbound exception.
    where i am doing mistake.

    Thanks
    -Ram

    • Hi,

      You might be facing the issue with the code lines because on doing copy/paste from the wordpress to soapui.. certain characters are not recognized properly.

      Perform following :
      – Split the above code into 2 different section / test steps. Co
      – Comment first method call workbook.write() //wokrbook.write()
      – Replace all the double quotes (“) by deleting & typing the new one.

      Regards,
      /Pradeep Bishnoi

  10. ramsnp says:

    Hi pradeep,

    I did the same as you explained above,When i execute the step bellow error has been returned to me. I am importing .xls and have jxl api at the right path. I think have problem with array index of the sheet while retrieving. Please help me

    org.codehaus.groovy.control.MultipleCompilationErrorsException: startup failed: Script11.groovy: 4: unexpected token: Sheet @ line 4, column 2. Sheet sheet = workbook.getSheet(0) ^ org.codehaus.groovy.syntax.SyntaxException: unexpected token: Sheet @ line 4, column 2. at org.codehaus.groovy.antlr.AntlrParserPlugin.transformCSTIntoAST(AntlrParserPlugin.java:102) at org.codehaus.groovy.antlr.AntlrParserPlugin.parseCST(AntlrParserPlugin.java:71) at org.codehaus.groovy.control.SourceUnit.parse(SourceUnit.java:236) at org.codehaus.groovy.control.CompilationUnit$1.call(CompilationUnit.java:158) at org.codehaus.groovy.control.CompilationUnit.applyToSourceUnits(CompilationUnit.java:814) at org.codehaus.groovy.control.CompilationUnit.doPhaseOperation(CompilationUnit.java:511) at org.codehaus.groovy.control.CompilationUnit.processPhaseOperations(CompilationUnit.java:487) at org.codehaus.groovy.control.CompilationUnit.compile(CompilationUnit.java:464) at groovy.lang.GroovyClassLoader.doParseClass(GroovyClassLoader.java:306) at groovy.lang.GroovyClassLoader.parseClass(GroovyClassLoader.java:287) at groovy.lang.GroovyShell.parseClass(GroovyShell.java:727) at groovy.lang.GroovyShell.parse(GroovyShell.java:739) at groovy.lang.GroovyShell.parse(GroovyShell.java:766) at groovy.lang.GroovyShell.parse(GroovyShell.java:757) at

    Thanks
    -Ram

  11. RamKumar says:

    Hi,

    I am using below code to import my test data file. Actually iam working for domain registrar company.

    1. I need to import domain name, extension from the input file,
    2. Need to send these two inputs to the request.
    3. Finally i need to store the results from xml nodes in to excel.

    import jxl.*
    import jxl.write.*
    Workbook workbook = Workbook.getWorkbook(new File(“c:\\myfile.xls”)
    Sheet sheet = workbook.getSheet(0)
    Cell a1=sheet.getCell(0,0)
    Cell b2=sheet.getCell(1,1)
    Cell c2=sheet.getCell(2,1)
    workbook.write()
    workbook.close()

    Thanks
    -Ram

    • Hi,
      if the shared code is the one you are using. then u need to add a closing bracket ) at the end of 3rd line.
      Workbook workbook = Workbook.getWorkbook(new File(“c:\\myfile.xls”))
      comment 8th line “workbook.write()
      rest everything looks perfect. maybe you can add ‘log.info’ before workbook.close()
      log.info a1.getContents() //or something similar to this..

      regards,
      /Pradeep Bishnoi

  12. RamKumar says:

    Thanks it is worked. Hi pradeep may i know your mail id ? i need some help

  13. RamKumar says:

    Hi Pradeep,

    How can i use for loop inside above code? i want to read values up to some rows from the sheet

    • Hi,

      how about going with this Algo/Logic ?

      open worksheet & get sheet handle
      for(col=0, col<3; col++) //loop to iterate columns
      {
      for (row=0, row<5; row++) //loop to iterate rows
      {
      value = sheet.getCell( row, col ) // row,column
      log.info value
      }
      row=0 /reset the rows again.
      }

      best regards,
      /Pradeep Bishnoi

  14. RamKumar says:

    Thanks, let me try this

  15. import com.eviware.soapui.model.*
    import com.eviware.soapui.model.testsuite.Assertable
    import com.eviware.soapui.support.XmlHolder
    import java.io.File;
    import java.util.*;
    import jxl.write.*
    import jxl.*
    def regLogger = org.apache.log4j.Logger.getLogger(“RegressionTestLoger”);
    def groovyUtils = new com.eviware.soapui.support.GroovyUtils( context )
    def properties = new java.util.Properties();
    //context.expand(‘${Properties#propertyname}’)
    def s2
    def s3=(testRunner.testCase.getPropertyValue(“RUN”))
    regLogger.info(s3);

    if (s3 != ‘1’ && s3 != ‘2’ && s3 != ‘3’)
    { testRunner.testCase.setPropertyValue(“RUN”, ‘1’ );
    s3=(testRunner.testCase.getPropertyValue(“RUN”));
    }

    //if (s2==’0′)

    //{

    //{

    Workbook workbook = Workbook.getWorkbook(new File(“D:\\myfile.xls”))
    for (count in 2..< 10)
    {
    Sheet sheet = workbook.getSheet(0)

    Cell a1 = sheet.getCell(0,count) // getCell(row,column) — place some values in myfile.xls

    Cell b2 = sheet.getCell(s3.toInteger(),count) // then those values will be acessed using a1, b2 & c3 Cell.
    String s1 = a1.getContents();
    s2 = b2.getContents();
    //Cell c2 = sheet.getCell(2,1)
    testRunner.testCase.setPropertyValue(s1,s2);

    }

    def flag = (testRunner.testCase.getPropertyValue("RUN")).toInteger()+1;
    testRunner.testCase.setPropertyValue("RUN",flag.toString());

    workbook.close()

    Script for the above is to answer the question "How can i use for loop inside above code? i want to read values up to some rows from the sheet" Thanks Pradeep for the help

  16. RamKumar says:

    Hi,

    Here my intention is to pick each value from an array list and send it to soap request command. later it is to be iterated until condition fails..here my script. Here i used forloop..but i was unable to send each value in to my request. please help me..how to solve this.

    def groovyUtils = new com.eviware.soapui.support.GroovyUtils( context )
    def stringList = [“test1″,”test2″,”test3″,”test4″,”test5″]
    stringList.eachWithIndex() { obj, i -> println ” ${i}: ${obj}” };

    Here is my commented code i tried different ways but it is not solved for me.

    //context.setProperty(“testval”,${obj})

    //log.info(list.size())

    //context.setPropertyValue(“i”,0)
    //for ( list[i] = 0; i>=list.size(); i++) {

    // log.info(list[i])

    //}
    //context.setProperty(“index”,0)

    //context.setProperty(“values”,list)

    -Ram

    • raj says:

      Hi Ram,
      Even I want to pick up each value from an excel sheet and send it to soap request command .Want to send each sheet data to my number of requests. please help me..how to solve this.

      Can you please share your code with me.

      Thanks a lot

  17. RG says:

    Hi,

    I want to write the properties to excel file using groovy in soapui. Please help.

    Regards,
    RG

    • Hi,

      can’t you just capture the property in a simple variable (temporary) & use that variable in sheet.

      tempVar = context.expand(‘${Properties#propertyname})
      Label label = new Label(0, 2, tempVar);
      sheet1.addCell(label);

      something on similar lines..

      regards,
      /pradeep bishnoi

  18. renren876 says:

    Great post, thanks.

  19. happyday67 says:

    Awsome site! shared it with my friends on myspace! You should get a Google+ 1 share button too!

  20. manoj says:

    import jxl.*

    import jxl.write.*

    Workbook workbook = Workbook.getWorkbook(new File(“C:\myfile.xls”))

    Sheet sheet = workbook.getSheet(0)

    Cell a1 = sheet.getCell(0,0) // 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)

    workbook.write()

    workbook.close()

    /* code lines second test step*/
    WritableWorkbook workbook1 = Workbook.createWorkbook(new File(“E:\\output.xls”))

    WritableSheet sheet1 = workbook1.createSheet(“Worksheet Number 1”, 0)

    log.info(sheet1.isHidden())

    Label label = new Label(0, 2, “Text input in Excel”);

    sheet1.addCell(label);

    workbook1.write()

    workbook1.close()

    I am getting below erro while exeuting above code.

    org.codehaus.groovy.control.MultipleCompilationErrorsException: startup failed: Script11.groovy: 5: unexpected char: ‘\’ @ line 5, column 54. kbook.getWorkbook(new File(“C:\myfile.xl ^ org.codehaus.groovy.syntax.SyntaxException: unexpected char: ‘\’ @ line 5, column 54. at org.codehaus.groovy.antlr.AntlrParserPlugin.transformCSTIntoAST(AntlrParserPlugin.java:134) at org.codehaus.groovy.antlr.AntlrParserPlugin.parseCST(AntlrParserPlugin.java:107) at org.codehaus.groovy.control.SourceUnit.parse(SourceUnit.java:236) at org.codehaus.groovy.control.CompilationUnit$1.call(CompilationUnit.java:163) at org.codehaus.groovy.control.CompilationUnit.applyToSourceUnits(CompilationUnit.java:839) at org.codehaus.groovy.control.CompilationUnit.doPhaseOperation(CompilationUnit.java:544) at org.codehaus.groovy.control.CompilationUnit.processPhaseOperations(CompilationUnit.java:520) at org.codehaus.groovy.control.CompilationUnit.compile(CompilationUnit.java:497) at groovy.lang.GroovyClassLoader.doParseClass(GroovyClassLoader.java:306) at groovy.lang.GroovyClassLoader.parseClass(GroovyClassLoader.java:287) at groovy.lang.GroovyShell.parseClass(GroovyShell.java:731) at groovy.lang.GroovyShell.parse(GroovyShell.java:743) at groovy.lang.GroovyShell.parse(GroovyShell.java:770) at groovy.lang.GroovyShell.parse(GroovyShell.java:761) at com.eviware.soapui.support.scripting.groovy.SoapUIGroovyScriptEngine.compile(SoapUIGroovyScriptEngine.java:148) at com.eviware.soapui.support.scripting.groovy.SoapUIGroovyScriptEngine.run(SoapUIGroovyScriptEngine.java:93) at com.eviware.soapui.impl.wsdl.teststeps.WsdlGroovyScriptTestStep.run(WsdlGroovyScriptTestStep.java:148) at com.eviware.soapui.impl.wsdl.panels.teststeps.GroovyScriptStepDesktopPanel$RunAction$1.run(GroovyScriptStepDesktopPanel.java:274) at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(Unknown Source) at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source) at java.lang.Thread.run(Unknown Source) Caused by: Script11.groovy:5:54: unexpected char: ‘\’ at org.codehaus.groovy.antlr.parser.GroovyLexer.mESC(GroovyLexer.java:2180) at org.codehaus.groovy.antlr.parser.GroovyLexer.mSTRING_CTOR_END(GroovyLexer.java:2226) at org.codehaus.groovy.antlr.parser.GroovyLexer.mSTRING_LITERAL(GroovyLexer.java:1985) at org.codehaus.groovy.antlr.parser.GroovyLexer.nextToken(GroovyLexer.java:468) at org.codehaus.groovy.antlr.parser.GroovyLexer$1.nextToken(GroovyLexer.java:258) at groovyjarjarantlr.TokenBuffer.fill(TokenBuffer.java:69) at groovyjarjarantlr.TokenBuffer.LA(TokenBuffer.java:80) at groovyjarjarantlr.LLkParser.LA(LLkParser.java:52) at org.codehaus.groovy.antlr.parser.GroovyRecognizer.nls(GroovyRecognizer.java:796) at org.codehaus.groovy.antlr.parser.GroovyRecognizer.newExpression(GroovyRecognizer.java:13368) at org.codehaus.groovy.antlr.parser.GroovyRecognizer.primaryExpression(GroovyRecognizer.java:10836) at org.codehaus.groovy.antlr.parser.GroovyRecognizer.pathExpression(GroovyRecognizer.java:11438) at org.codehaus.groovy.antlr.parser.GroovyRecognizer.postfixExpression(GroovyRecognizer.java:13175) at org.codehaus.groovy.antlr.parser.GroovyRecognizer.unaryExpressionNotPlusMinus(GroovyRecognizer.java:13144) at org.codehaus.groovy.antlr.parser.GroovyRecognizer.powerExpressionNotPlusMinus(GroovyRecognizer.java:12848) at org.codehaus.groovy.antlr.parser.GroovyRecognizer.multiplicativeExpression(GroovyRecognizer.java:12780) at org.codehaus.groovy.antlr.parser.GroovyRecognizer.additiveExpression(GroovyRecognizer.java:12450) at org.codehaus.groovy.antlr.parser.GroovyRecognizer.shiftExpression(GroovyRecognizer.java:9664) at org.codehaus.groovy.antlr.parser.GroovyRecognizer.relationalExpression(GroovyRecognizer.java:12355) at org.codehaus.groovy.antlr.parser.GroovyRecognizer.equalityExpression(GroovyRecognizer.java:12279) at org.codehaus.groovy.antlr.parser.GroovyRecognizer.regexExpression(GroovyRecognizer.java:12227) at org.codehaus.groovy.antlr.parser.GroovyRecognizer.andExpression(GroovyRecognizer.java:12195) at org.codehaus.groovy.antlr.parser.GroovyRecognizer.exclusiveOrExpression(GroovyRecognizer.java:12163) at org.codehaus.groovy.antlr.parser.GroovyRecognizer.inclusiveOrExpression(GroovyRecognizer.java:12131) at org.codehaus.groovy.antlr.parser.GroovyRecognizer.logicalAndExpression(GroovyRecognizer.java:12099) at org.codehaus.groovy.antlr.parser.GroovyRecognizer.logicalOrExpression(GroovyRecognizer.java:12067) at org.codehaus.groovy.antlr.parser.GroovyRecognizer.conditionalExpression(GroovyRecognizer.java:4842) at org.codehaus.groovy.antlr.parser.GroovyRecognizer.assignmentExpression(GroovyRecognizer.java:7988) at org.codehaus.groovy.antlr.parser.GroovyRecognizer.expression(GroovyRecognizer.java:9841) at org.codehaus.groovy.antlr.parser.GroovyRecognizer.strictContextExpression(GroovyRecognizer.java:9079) at org.codehaus.groovy.antlr.parser.GroovyRecognizer.argument(GroovyRecognizer.java:13771) at org.codehaus.groovy.antlr.parser.GroovyRecognizer.argList(GroovyRecognizer.java:6712) at org.codehaus.groovy.antlr.parser.GroovyRecognizer.methodCallArgs(GroovyRecognizer.java:11753) at org.codehaus.groovy.antlr.parser.GroovyRecognizer.pathElement(GroovyRecognizer.java:11334) at org.codehaus.groovy.antlr.parser.GroovyRecognizer.pathExpression(GroovyRecognizer.java:11464) at org.codehaus.groovy.antlr.parser.GroovyRecognizer.postfixExpression(GroovyRecognizer.java:13175) at org.codehaus.groovy.antlr.parser.GroovyRecognizer.unaryExpressionNotPlusMinus(GroovyRecognizer.java:13144) at org.codehaus.groovy.antlr.parser.GroovyRecognizer.powerExpressionNotPlusMinus(GroovyRecognizer.java:12848) at org.codehaus.groovy.antlr.parser.GroovyRecognizer.multiplicativeExpression(GroovyRecognizer.java:12780) at org.codehaus.groovy.antlr.parser.GroovyRecognizer.additiveExpression(GroovyRecognizer.java:12450) at org.codehaus.groovy.antlr.parser.GroovyRecognizer.shiftExpression(GroovyRecognizer.java:9664) at org.codehaus.groovy.antlr.parser.GroovyRecognizer.relationalExpression(GroovyRecognizer.java:12355) at org.codehaus.groovy.antlr.parser.GroovyRecognizer.equalityExpression(GroovyRecognizer.java:12279) at org.codehaus.groovy.antlr.parser.GroovyRecognizer.regexExpression(GroovyRecognizer.java:12227) at org.codehaus.groovy.antlr.parser.GroovyRecognizer.andExpression(GroovyRecognizer.java:12195) at org.codehaus.groovy.antlr.parser.GroovyRecognizer.exclusiveOrExpression(GroovyRecognizer.java:12163) at org.codehaus.groovy.antlr.parser.GroovyRecognizer.inclusiveOrExpression(GroovyRecognizer.java:12131) at org.codehaus.groovy.antlr.parser.GroovyRecognizer.logicalAndExpression(GroovyRecognizer.java:12099) at org.codehaus.groovy.antlr.parser.GroovyRecognizer.logicalOrExpression(GroovyRecognizer.java:12067) at org.codehaus.groovy.antlr.parser.GroovyRecognizer.conditionalExpression(GroovyRecognizer.java:4842) at org.codehaus.groovy.antlr.parser.GroovyRecognizer.assignmentExpression(GroovyRecognizer.java:7988) at org.codehaus.groovy.antlr.parser.GroovyRecognizer.expression(GroovyRecognizer.java:9841) at org.codehaus.groovy.antlr.parser.GroovyRecognizer.expressionStatementNoCheck(GroovyRecognizer.java:8314) at org.codehaus.groovy.antlr.parser.GroovyRecognizer.varInitializer(GroovyRecognizer.java:2682) at org.codehaus.groovy.antlr.parser.GroovyRecognizer.variableDeclarator(GroovyRecognizer.java:7889) at org.codehaus.groovy.antlr.parser.GroovyRecognizer.listOfVariables(GroovyRecognizer.java:7843) at org.codehaus.groovy.antlr.parser.GroovyRecognizer.variableDefinitions(GroovyRecognizer.java:2278) at org.codehaus.groovy.antlr.parser.GroovyRecognizer.declaration(GroovyRecognizer.java:2165) at org.codehaus.groovy.antlr.parser.GroovyRecognizer.statement(GroovyRecognizer.java:1208) at org.codehaus.groovy.antlr.parser.GroovyRecognizer.compilationUnit(GroovyRecognizer.java:757) at org.codehaus.groovy.antlr.AntlrParserPlugin.transformCSTIntoAST(AntlrParserPlugin.java:130) … 20 more 1 error

  21. manoj says:

    Still getting same errors

  22. manoj says:

    Can you please give post or send to email of sample scritps to do data driven testing of with groovy scripts for SOAPUI

  23. manoj says:

    Hi Pradeep~

    Can you please give me reply to above items and I am middle of the project to implement the same.

    Thanks,
    Narasimha

    • Hi Manoj,

      Even i am big time busy with my projects 😦
      I still feel your issue has something to do with the copy paste of the code from wordpress to groovy script window. Content within double quotes/single quotes should appear in PINK color, if not delete those quotes & type again.

      Drop me a mail, i might be able to share the code in txt file.

      regards,
      /pradeep bishnoi

  24. Fantastic post, I actually benefited from studying it, keep doing the good work.

  25. Hashim says:

    i am new to groovy and java. i have read above code and try to implement. it is working great.
    but my issue is to append an existing Excel file. i can read it and write to a new XLS but can not update existing Excel file

    can you please send me code of this

    thank you

  26. Subha says:

    Hi Pradeep,
    I read UR Blogs…Really a great work..
    I need one help..
    This is my request parameters.(i just mentioned that main part of the request)

    In Groovy I want to call this request and pass values for these parameters from database.
    From the response I’m getting I want to check remaining other fields in the database are matching with the response I got.
    How to proceed for this in automation..Please help me..

    Regards,
    Subha

    • Hi Subha,
      Didn’t found your request in the comments. However, will go by my generic understanding to your question.
      – Write groovy script to read/setup database connection & read values one at a time and update the same in a custom property teststep.
      – Use the variable from property test step as an input in your request.
      – Put a script assertion on the test response. Use xPath to navigate the node to be matched. Get the value of node. Compare that with the value stored in custom property variable. Assert based on result match or failure.

      I hope by end of December will have a post on similar lines 😉

      Regards,
      /Pradeep Bishnoi

  27. Subha says:


    My request missed in previous one …Please note nd help me

  28. Subha says:

    Thanks Pradeep for ur valuable comments…

  29. arun Kumar says:

    Hi pardeep,
    please share the code to read the data from an external excel sheet to soapui test.i am using SOAPUI 3.5…Please share the code

  30. Tiina says:

    “All you need do is place the JXL api file in the “lib” folder of %SOAPUI_INSTALL_DIR%.”

    I’m using SoapUI 4.0.1 and in that one the JXL.JAR file should be added to folder “soapUI\bin\ext”-folder, not to “soapUI\lib”-folder.

  31. Munireddy says:

    Hi Pradeep,

    your blog is very good, thanks for sharing the knowledge,

    I have soapui 3.6.1 installed, i am trying the create the excel file.
    i have placed the jxl.jar in lib folder but getting the following error
    ( Buththe same code working fine in soapui 4.0.1)

    Fri Apr 06 15:58:52 IST 2012:ERROR:An error occured [startup failed:
    Script3.groovy: 25: unable to resolve class WritableWorkbook
    @ line 25, column 19.
    WritableWorkbook workbook = Workbook.createWorkbook(new File(fileName))
    ^
    org.codehaus.groovy.syntax.SyntaxException: unable to resolve class WritableWorkbook
    @ line 25, column 19.
    at org.codehaus.groovy.ast.ClassCodeVisitorSupport.addError(ClassCodeVisitorSupport.java:148)
    at org.codehaus.groovy.control.ResolveVisitor.resolveOrFail(ResolveVisitor.java:226)
    at org.codehaus.groovy.control.ResolveVisitor.resolveOrFail(ResolveVisitor.java:236)
    at org.codehaus.groovy.control.ResolveVisitor.transformVariableExpression(ResolveVisitor.java:999)
    at org.codehaus.groovy.control.ResolveVisitor.transform(ResolveVisitor.java:756)
    at org.codehaus.groovy.control.ResolveVisitor.transformDeclarationExpression(ResolveVisitor.java:1127)
    at org.codehaus.groovy.control.ResolveVisitor.transform(ResolveVisitor.java:760)
    at org.codehaus.groovy.ast.ClassCodeExpressionTransformer.visitExpressionStatement(ClassCodeExpressionTransformer.java:139)
    at org.codehaus.groovy.ast.stmt.ExpressionStatement.visit(ExpressionStatement.java:40)
    at org.codehaus.groovy.ast.CodeVisitorSupport.visitBlockStatement(CodeVisitorSupport.java:35)
    at org.codehaus.groovy.ast.ClassCodeVisitorSupport.visitBlockStatement(ClassCodeVisitorSupport.java:165)
    at org.codehaus.groovy.control.ResolveVisitor.visitBlockStatement(ResolveVisitor.java:1345)
    at org.codehaus.groovy.ast.stmt.BlockStatement.visit(BlockStatement.java:51)
    at org.codehaus.groovy.ast.ClassCodeVisitorSupport.visitClassCodeContainer(ClassCodeVisitorSupport.java:101)
    at org.codehaus.groovy.ast.ClassCodeVisitorSupport.visitConstructorOrMethod(ClassCodeVisitorSupport.java:112)
    at org.codehaus.groovy.ast.ClassCodeExpressionTransformer.visitConstructorOrMethod(ClassCodeExpressionTransformer.java:50)
    at org.codehaus.groovy.control.ResolveVisitor.visitConstructorOrMethod(ResolveVisitor.java:171)
    at org.codehaus.groovy.ast.ClassCodeVisitorSupport.visitMethod(ClassCodeVisitorSupport.java:123)
    at org.codehaus.groovy.ast.ClassNode.visitContents(ClassNode.java:1039)
    at org.codehaus.groovy.ast.ClassCodeVisitorSupport.visitClass(ClassCodeVisitorSupport.java:50)
    at org.codehaus.groovy.control.ResolveVisitor.visitClass(ResolveVisitor.java:1288)
    at org.codehaus.groovy.control.ResolveVisitor.startResolving(ResolveVisitor.java:148)
    at org.codehaus.groovy.control.CompilationUnit$6.call(CompilationUnit.java:574)
    at org.codehaus.groovy.control.CompilationUnit.applyToSourceUnits(CompilationUnit.java:814)
    at org.codehaus.groovy.control.CompilationUnit.doPhaseOperation(CompilationUnit.java:511)
    at org.codehaus.groovy.control.CompilationUnit.processPhaseOperations(CompilationUnit.java:487)
    at org.codehaus.groovy.control.CompilationUnit.compile(CompilationUnit.java:464)
    at groovy.lang.GroovyClassLoader.doParseClass(GroovyClassLoader.java:306)
    at groovy.lang.GroovyClassLoader.parseClass(GroovyClassLoader.java:287)
    at groovy.lang.GroovyShell.parseClass(GroovyShell.java:727)
    at groovy.lang.GroovyShell.parse(GroovyShell.java:739)
    at groovy.lang.GroovyShell.parse(GroovyShell.java:766)
    at groovy.lang.GroovyShell.parse(GroovyShell.java:757)
    at com.eviware.soapui.support.scripting.groovy.SoapUIGroovyScriptEngine.compile(SoapUIGroovyScriptEngine.java:148)
    at com.eviware.soapui.support.scripting.groovy.SoapUIGroovyScriptEngine.run(SoapUIGroovyScriptEngine.java:93)
    at com.eviware.soapui.impl.wsdl.WsdlTestSuite.runTearDownScript(WsdlTestSuite.java:515)
    at com.eviware.soapui.impl.wsdl.testcase.WsdlTestSuiteRunner.internalFinally(WsdlTestSuiteRunner.java:184)
    at com.eviware.soapui.impl.wsdl.testcase.WsdlTestSuiteRunner.internalFinally(WsdlTestSuiteRunner.java:42)
    at com.eviware.soapui.impl.wsdl.support.AbstractTestRunner.run(AbstractTestRunner.java:163)
    at java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source)
    at java.util.concurrent.FutureTask$Sync.innerRun(Unknown Source)
    at java.util.concurrent.FutureTask.run(Unknown Source)
    at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(Unknown Source)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
    at java.lang.Thread.run(Unknown Source)

    Script3.groovy: 28: unable to resolve class WritableSheet
    @ line 28, column 16.
    WritableSheet sheet = workbook.createSheet(suiteName, 0)
    ^
    org.codehaus.groovy.syntax.SyntaxException: unable to resolve class WritableSheet
    @ line 28, column 16.
    at org.codehaus.groovy.ast.ClassCodeVisitorSupport.addError(ClassCodeVisitorSupport.java:148)
    at org.codehaus.groovy.control.ResolveVisitor.resolveOrFail(ResolveVisitor.java:226)
    at org.codehaus.groovy.control.ResolveVisitor.resolveOrFail(ResolveVisitor.java:236)
    at org.codehaus.groovy.control.ResolveVisitor.transformVariableExpression(ResolveVisitor.java:999)
    at org.codehaus.groovy.control.ResolveVisitor.transform(ResolveVisitor.java:756)
    at org.codehaus.groovy.control.ResolveVisitor.transformDeclarationExpression(ResolveVisitor.java:1127)
    at org.codehaus.groovy.control.ResolveVisitor.transform(ResolveVisitor.java:760)
    at org.codehaus.groovy.ast.ClassCodeExpressionTransformer.visitExpressionStatement(ClassCodeExpressionTransformer.java:139)
    at org.codehaus.groovy.ast.stmt.ExpressionStatement.visit(ExpressionStatement.java:40)
    at org.codehaus.groovy.ast.CodeVisitorSupport.visitBlockStatement(CodeVisitorSupport.java:35)
    at org.codehaus.groovy.ast.ClassCodeVisitorSupport.visitBlockStatement(ClassCodeVisitorSupport.java:165)
    at org.codehaus.groovy.control.ResolveVisitor.visitBlockStatement(ResolveVisitor.java:1345)
    at org.codehaus.groovy.ast.stmt.BlockStatement.visit(BlockStatement.java:51)
    at org.codehaus.groovy.ast.ClassCodeVisitorSupport.visitClassCodeContainer(ClassCodeVisitorSupport.java:101)
    at org.codehaus.groovy.ast.ClassCodeVisitorSupport.visitConstructorOrMethod(ClassCodeVisitorSupport.java:112)
    at

    • Hi,

      The reason for this error is pretty strange.
      I guess it might have something to do with the placement of the .jar file.
      Try with either /Lib folder or /Bin folder or search internet.

      Regards,
      {Pradeep Bishnoi}

  32. Pavan says:

    I created 2 sheets using the above code.And filled the data.
    I want to open the existing file and add 3rd sheet into it retaining the first 2 sheet with their contents

  33. shruti says:

    Hi Pradeep,

    Very nice share..
    I got how to read the data from a excel sheet using groovy, but is there anyway I can put that result data as an endpoint or request for the next test step. I am using Soap UI open source.

    Thanks in advance

    • Hi Shruti,

      Property transfer is the approach which can be used.
      Or in simpler terms, create a groovy assertion in your teststep and use it to update a testcase level property with the needed values.
      Use those values as input in next teststep. I hope this will help.

      Best Regards,
      {Pradeep Bishnoi}

  34. shruti says:

    Hi Pradeep,

    I used property transfer, but I am not able to access all values read from the excel sheet. Here is my script from which I am reading 3 values from the excel sheet:

    import jxl.write.*
    Workbook workbook1 = Workbook.getWorkbook(new File(‘D:/AutomationInput.xls’))
    Sheet sheet1 = workbook1.getSheet(0)
    Cell a1 = sheet1.getCell(0,0)
    Cell b1 = sheet1.getCell(1,0)
    Cell c1 = sheet1.getCell(2,0)
    def endpoint = a1.getContents()
    def submitxml = b1.getContents()
    def jobfolderpath = c1.getContents()
    log.info(endpoint)
    log.info(submitxml)
    log.info(jobfolderpath)
    workbook1.close()
    return endpoint

    In Property Transfer, I can transfer only 1 value by (example:”return endpoint”) this line which will be stored in the “result” property of source(excel sheet).I am unable to create property, since there is no option to create it.

    How can I access and transfer all 3 values in 1 property transfer step?

    It might be the dumbest question to ask…But i need help…

    Thanks in advance 🙂

    • Well, this mean that you didn’t read the other blogs 😉
      Refer the blog
      https://learnsoapui.wordpress.com/2011/07/17/10-groovy-scripts-on-your-finger-tips-soapui/

      And instead of using the return in your code you can actually update the property using following code lines.

      log.info “TestCase level property value : ” + context.testCase.testSuite.project.testSuites[‘myTestSuiteName’].testCases[‘myTestCaseName’].getProperty(“testCaseProp”).value

      //get the Proptery variable handle
      propVar = context.testCase.testSuite.project.testSuites[‘myTestSuiteName’].testCases[‘myTestCaseName’].getProperty(“testCaseProp”)

      //update the value of the prop variable
      propVar.value = “New Value”

      Regards,
      {Pradeep Bishnoi}

  35. shruti says:

    yeah…I just skipped reading other blogs before i post that, but then when I started to read… I resolved the issue, I added these steps in my code:

    testRunner.testCase.testSuite.project.setPropertyValue(‘endpoint1’,endpoint)
    testRunner.testCase.testSuite.project.setPropertyValue(‘submitxml1’,submitxml)
    testRunner.testCase.testSuite.project.setPropertyValue(‘jobfolder1’,jobfolder)

    and accessed them using property transfer.

    Thanks a lot for ur reply 🙂

  36. shri says:

    Hi Pradip,
    I want parameterised my scripts ie I want to get test data from excel sheet and want to generate multiple scripts using that parameterised script. means if 5 rows are present in excel sheet then, 5 scripts will generate.
    Will this possible with groovy script?

  37. shri says:

    Hi Pradip, I am using soap 4.0.1 version, I have kept jxl.jar file in to lib folder of soap ui but still getting error that ,”org.codehaus.groovy.control.MultipleCompilationErrorsException: startup failed: Script3.groovy: 3: unable to resolve class WritableWorkbook @ line 3, column 18. WritableWorkbook workbook = Workbook.createWorkbook(new File(“d:\\output.xls”)) ^”

    Can you please suggest on this.

  38. HCL says:

    Hi Pardeep,

    I want to write data into XLS but my code is overwriting the values i.e. it is not appending the XLS.
    Can you review below code as I have to write each record in XLS that is being captured in the loop:

    //code to write to xls
    import jxl.*
    import jxl.write.*
    import jxl.format.Colour;

    WritableWorkbook workbook = Workbook.createWorkbook(new File(“C:/Users/rajbir_kaur/Desktop/EM2.xls”)) //replace the file location & file name
    WritableSheet sheet = workbook.createSheet(“Actual”, 0)//replace the name of sheet with desired name

    //Setting Background colour for Cells
    Colour bckcolor = Colour.DARK_GREEN;
    WritableCellFormat cellFormat = new WritableCellFormat();
    cellFormat.setBackground(bckcolor);

    //Setting Colour & Font for the Text
    WritableFont font = new WritableFont(WritableFont.ARIAL);
    font.setColour(Colour.GOLD);
    cellFormat.setFont(font);

    //Variable Declaration for xls
    int counter = 1;

    //xls sheet header formatting
    Label label1= new Label(0, 0, “SITE ID”);
    sheet.addCell(label1);
    WritableCell cell1 = sheet.getWritableCell(0, 0);
    cell1.setCellFormat(cellFormat);
    Label label2= new Label(1, 0, “NAME”);
    sheet.addCell(label2);
    WritableCell cell2 = sheet.getWritableCell(1, 0);
    cell2.setCellFormat(cellFormat);
    Label label3= new Label(2, 0, “PATHS”);
    sheet.addCell(label3);
    WritableCell cell3 = sheet.getWritableCell(2, 0);
    cell3.setCellFormat(cellFormat);

    //Variable declaration for XML response handling
    def project = testRunner.testCase.testSuite.project ;
    def tcase = project.testSuites[“login TestSuite”].testCases[“getPremisesByIds TestCase”] ; //replace the names of testsuite & testcase
    def tstep = tcase.getTestStepByName(‘Premises IDs’); //replace the teststepname with name of REST request in testcase
    def response_test_suite_1 = tstep.getPropertyValue(“ResponseAsXml”);
    def groovyUtils = new com.eviware.soapui.support.GroovyUtils( context );
    def holder1 = groovyUtils.getXmlHolder(“${response_test_suite_1}”);
    holder1.namespaces [‘ns1’]= “https://ems.gridpoint.com/publicApi/services/user/getPremisesByIds” //replace the namespace as required
    def nodeCount = holder1.getDomNodes(“//ns1:Response/ns1:result/ns1:e/ns1:paths/ns1:e”).length //replace the xpath as required

    //get node values from the mentioned xpath and add to worksheet
    for (def nodeIndex = 1; nodeIndex <= nodeCount; nodeIndex++) {
    def node = holder1.getDomNodes("//ns1:Response/ns1:result/ns1:e/ns1:paths/ns1:e["+nodeIndex+"]") //replace the xpath refer the sample script below
    node.each {
    Label label4 = new Label(0,counter, holder1.getNodeValue("//ns1:Response[1]/ns1:result[1]/ns1:e[1]/ns1:id[1]")); //replace the xpath with the sample given below
    sheet.addCell(label4);
    Label label5 = new Label(1,counter, holder1.getNodeValue("//ns1:Response[1]/ns1:result[1]/ns1:e[1]/ns1:name[1]")); //replace the xpath with the sample given below
    sheet.addCell(label5);
    Label label6 = new Label(2,counter, it.firstChild.nodeValue.toString());
    sheet.addCell(label6);
    counter ++
    }
    }
    //writing to and clsoing the worksheet
    workbook.write()
    workbook.close()

    Thanks

  39. Neha says:

    Hi Pardeep,

    I am getting following error : jxl.read.biff.BiffExceptopn:unable to recognize ole stream

    code:
    import jxl.*
    import jxl.write.*

    Workbook workbook1 = Workbook.getWorkbook(new File(“C:\\Users\\neha26\\Desktop\\AddBenf.xlsx”))

    i have put jxl-2.6.12.jar in lib as well as bin .

    Please help.

    Thanks in advance!

  40. hye says:

    how can i edit exel cell ?

  41. yamini says:

    Hi pradeep,
    I can able to read the data from excel but i can’t able to fetch the data into test steps. Here i have mentioned the code.i have created a properties and added the properties value as user id.
    does i need to add any other steps.Could you please suggest me how to use the imported xl values in to test step.

    import jxl.*
    import jxl.write.*
    Workbook workbook1 = Workbook.getWorkbook(new File(“d:\\data sheet1.xls”))
    Sheet sheet1 = workbook1.getSheet(“Sheet1”)
    int n=sheet1.getRows()
    log.info n
    int n1=sheet1.getColumns()
    log.info n1

    for(int i=0;i<n;i++)
    {
    for(int j=0;j<n1;j++)
    {
    p = sheet1.getCell(j,i).getContents()
    log.info p

    }

    }

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s