Monday, July 04, 2011

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

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 @

@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.

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)

WritableWorkbook workbook1 = Workbook.createWorkbook(new File("d:\\output.xls"))
WritableSheet sheet1 = workbook1.createSheet("Worksheet Number 1", 0)
Label label = new Label(0, 2, "Text input in Excel");

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".


  1. where should i place this jxl.jar file?

  2. Hi Pradeep, How can we read write excel spreadsheet without using datasource for free trial version of soapui pro ? I tried your code given above but it gives error on trial version.Where can we get jxl file and where should we keep it.Can you please explain? I am new to soapui and groovy and more into qtp for web services testing.finding it very hard to learn groovy and specially for data driven testing.
    Please help me.

  3. Hi Pradeep,
    This is Prashanth and this was a very helpful blog. I would like to know as to where should I place this jxl. jar file.

  4. Hi,

    1. I need you help on transferring data from .xls file to properties file.

    2. I don't know how to do data driven using groovy it possible to help me on this...


    i have one request with 2 inputs...i need to do data driven for this using xls or xlsx file with multiple sets of data.

  5. good blog .

    when I am using the workbook to read the data from excel sheet , I am getting the error message "Unable to resolve class Workbook". Can you please help me in this

  6. Hello,

    I have copied the above mentioned code in Groovy script and executed.

    I am getting following error.

    org.codehaus.groovy.control.MultipleCompilationErrorsException: startup failed: Script3.groovy: 4: unable to resolve class Workbook @ line 4, column 10. Workbook workbook = Workbook.getWorkbook(new File("D:/Project/ReadExcel/input.xlsx")); ^ org.codehaus.groovy.syntax.SyntaxException: unable to resolve class Workbook @ line 4, column 10. at org.codehaus.groovy.ast.ClassCodeVisitorSupport.addError( at org.codehaus.groovy.control.ResolveVisitor.resolveOrFail( at org.codehaus.groovy.control.ResolveVisitor.resolveOrFail( at

    1. Hi Satyajit,

      Workbook.getWorkbook(new File("D://Project//ReadExcel//input.xlsx"));

      Try now

  7. Hi,

    Please refer the another blog site & read comments. This is happening because characters are not recognized by Groovy Script panel if code is copy pasted from blog.

    I guess this code block need to be replaced with SyntaxHighlighter.

    /Pradeep Bishnoi

    1. Hey!
      Are You sure that you gave the right link??

  8. Hi Pradeep,

    Thanks for the blog. I'm new to groovy scripting and need your guidance in terms from where i should get started in learning grrovy scripting for soapUI automation.
    Can you please help me with the groovy script for:
    1. How to export the test result using the Groovy into the Excel file
    2. Groovy script to email the test results excel file

    Thanks and Regards,

  9. Hi,

    I download the zip file you said, but i got the error: "unable to resolve class WritetableSheet ". Am i missing any library? Thanks.


  10. Hi
    I am writing a groovy code in soapui.
    how can i rread and edit excel file through groovy in soapui?

  11. Grееtings! Very useful аdvіcе within
    this post! It is the little changes that produce the bіggest changes.
    Thanks fοr shaгing!

    Feel free to ѕurf to my wеbsite - How to lose arm fat

  12. It's not my first time to pay a visit this site, i am visiting this web page dailly and take nice information from here daily.

    Here is my web blog ronco pocket fisherman

  13. Hi! I'm at work surfing around your blog from my new apple iphone! Just wanted to say I love reading through your blog and look forward to all your posts! Carry on the outstanding work!

    Also visit my blog - snoring mask

  14. I love what you guys tend to be up too. This type of clever work and exposure!
    Keep up the amazing works guys I've incorporated you guys to my own blogroll.

    Feel free to visit my web site

  15. For hottest information you have to pay a quick visit world-wide-web
    and on the web I found this website as a most excellent
    web page for latest updates.

    my homepage - Garcinai Cambogia Review

  16. each time i used to read smaller articles or reviews that as
    well clear their motive, and that is also happening with this post which
    I am reading here.

    Also visit my site; Internet Money Path Reviews

  17. I blog frequently and I seriously appreciate your content.

    The article has truly peaked my interest. I will
    take a note of your website and keep checking for new details about once per week.

    I subscribed to your Feed as well.

    My page

  18. It's awesome in support of me to have a web site, which is beneficial for my know-how. thanks admin

    Also visit my weblog juice fasting recipes

  19. Link exchange is nothing else except it is only placing the other person's website link on your page at appropriate place and other person will also do same for you.

    Also visit my blog post: omega juicer

  20. I think this is among the most important info
    for me. And i am glad reading your article. But want to remark on
    some general things, The site style is great, the articles is really excellent :
    D. Good job, cheers

    Here is my web-site :: hottest gift ideas

  21. Whats up this is kind of of off topic but I was wanting to know if blogs use WYSIWYG editors or
    if you have to manually code with HTML. I'm starting a blog soon but have no coding know-how so I wanted to get guidance from someone with experience. Any help would be enormously appreciated!

    Here is my web-site

  22. This is very interesting, You are a very skilled blogger. I've joined your rss feed and look forward to seeking more of your magnificent post. Also, I have shared your site in my social networks!

    Feel free to visit my web blog ...

  23. Hola! I've been reading your website for a while now and finally got the courage to go ahead and give you a shout out from Houston Tx! Just wanted to say keep up the good work!

    Also visit my page: juicing benefits

  24. Definitely believe that which you stated. Your favorite justification seemed to
    be on the web the simplest thing to be aware of. I say to you, I definitely get irked while people think about
    worries that they plainly don't know about. You managed to hit the nail upon the top and defined out the whole thing without having side effect , people can take a signal. Will likely be back to get more. Thanks

    Take a look at my page :: goodwill online store

  25. My family members every time say that I am killing my time here
    at net, except I know I am getting experience daily by reading such nice content.

    Also visit my web page - tv products

  26. constantly i used to read smaller articles or reviews that
    also clear their motive, and that is also happening
    with this post which I am reading now.

    my page :: best workout routines to Lose Weight

  27. What's up, yup this article is truly fastidious and I have learned
    lot of things from it concerning blogging. thanks.

    Here is my homepage - legitimate work at home jobs

  28. Wonderful items from you, man. I've bear in mind
    your stuff prior to and you're just too fantastic. I really like what
    you have acquired right here, certainly like what you're stating and
    the way during which you say it. You are making it entertaining
    and you continue to care for to stay it smart.
    I can not wait to learn much more from you. This is really a tremendous website.

    Look at my site :: looking for a job

  29. Thank you for another wonderful post. The place else may anyone get that
    type of information in such an ideal method of writing?
    I've a presentation subsequent week, and I'm at the search for such info.

    Here is my page; top eleven cheats ()

  30. If some one wants expert view on the topic of running a blog
    after that i recommend him/her to pay a visit this web site, Keep up the nice

    Feel free to surf to my web site :: belgium including;,

  31. bookmarkеd!!, I love your website!

    Feel free to surf to my blog -

  32. Eҳϲellent postt hօwever I was wanting to knolw if you could write a litte more on this subject?

    I'd be very thankful іf you could elaborate a little bitt more.
    Many tɦanks!

    Visit my blog; spanish listening comprehension family ()

  33. In addition it has a little bar and a hot tub to relaxing for
    occupants. The unit with several deals are producing waves all over UK and several affordable deals are to the offering.

    Also visit my site: cydia download ()

  34. The Moulay Yacoub Spa outside Fez, Morocco, was the location for
    one of the most unpleasant treatments I've ever tried.
    Once you have decided on a password, you can check the "remember me on this computer" if you want Gmail to automatically
    insert your password once you have entered your login name.
    If you use Windows and have a newer computer,
    consider getting Microsoft Security Essentials.

    my blog: How to hack A mail

  35. We are a group of volunteers and opening a new scheme in our community.

    Your website provided us with valuable info to work on. You've done a formidable job and our entire community will be grateful to

    Look at my weblog demenagement martin

  36. Heya! I'm at work browsing your blog from my new apple iphone!
    Just wanted to say I love reading your blog and look forward to
    all your posts! Keep up the superb work!

    My web site west palm mover

  37. Spot on wіth this write-սp, I reаlly feel
    tgis amazing site neеds a lot more attention. ӏ'll pгobably Ье back
    again to read more, tɦanks fߋr the advice!

    my homepage; criminal case hack without survey

  38. Thursday, June 19, 2014 - Contra Dance.

    Feel free to visit my website :: legal highs

  39. org.codehaus.groovy.control.MultipleCompilationErrorsException: startup failed: Script7.groovy: 16: unable to resolve class Sheet @ line 16, column 15. Sheet sheet = workbook.getSheetAt(0); ^

    getting this errror

  40. Yeah even i get the same error for the below code

    Title : Reading data from Excel and writing to XML using Groovy Soap UI

    import java.util.Date
    import jxl.*
    import groovy.xml.MarkupBuilder
    import org.custommonkey.xmlunit.*

    import org.codehaus.groovy.scriptom.*

    def excelPath = "F:\\SOAPUI Practice\\Practice\\New Practice\\GetCities.xls"
    def dataSheetName = "Sheet1"

    def oExcel = new ActiveXObject('Excel.Application')

    assert oExcel != null, "Excel object not initalized"

    def openWb = oExcel.Workbooks.Open(excelPath) //get access to the workbook
    def dtUsedRange = openWb.Sheets(dataSheetName).UsedRange //get the usedRange of the sheet
    int rCount = dtUsedRange.Rows.Count
    int cCount = dtUsedRange.Columns.Count

    def strXml = ""

    //add property names to xlMapSheet under col d or col# 4
    for(int r = 1;r<=rCount;r++){
    strXml = strXml + ""
    for(int c = 1;c" + cValue + ""
    strXml = strXml + ""
    strXml = strXml + "" strXml


  41. I have downloaded dll 'jacob-1.14.3-x86.dll' and put the same in bin folder still i get the same error. Request some one to help me

  42. Error Displayed as :org.codehaus.groovy.control.MultipleCompilationErrorsException: startup failed:

  43. It's been good to see your blog when I always look for such type of blogs. Thanks for your sharing. I have been really impressed by going through this awesome info. Recently i have read a very good online review about Fast Weight Loss Program. I hope that it will equally help you people in loosing weight.

  44. hi,we provide online training & video tutorial for soapui
    for free videos refer