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

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

WritableWorkbook workbook1 = Workbook.createWorkbook(new File("d:\\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()

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

43 comments:

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

    ReplyDelete
  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.
    Regards
    Vivek
    vivek.kumar2005@gmail.com

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

    ReplyDelete
  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 script.is it possible to help me on this...

    Example:

    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.

    ReplyDelete
  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

    ReplyDelete
  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(ClassCodeVisitorSupport.java:148) at org.codehaus.groovy.control.ResolveVisitor.resolveOrFail(ResolveVisitor.java:226) at org.codehaus.groovy.control.ResolveVisitor.resolveOrFail(ResolveVisitor.java:236) at

    ReplyDelete
    Replies
    1. Hi Satyajit,

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

      Try now

      Delete
  7. Hi,

    Please refer the another blog site http://learnsoapui.wordpress.com & 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

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

      http://learnsoapui.wordpress.com

      Delete
  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,
    Sandeep

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

    Regards,
    Peter

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

    ReplyDelete
  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

    ReplyDelete
  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

    ReplyDelete
  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

    ReplyDelete
  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 http://www.youtube.com/

    ReplyDelete
  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

    ReplyDelete
  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

    ReplyDelete
  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 http://rvtlskincare.com

    ReplyDelete
  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

    ReplyDelete
  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

    ReplyDelete
  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

    ReplyDelete
  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 wiki.directvelo.com

    ReplyDelete
  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 ... www.studieren-ka.de

    ReplyDelete
  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

    ReplyDelete
  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

    ReplyDelete
  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

    ReplyDelete
  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

    ReplyDelete
  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

    ReplyDelete
  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

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

    ReplyDelete
  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
    job.

    Feel free to surf to my web site :: belgium including; http://thelano.co.kr/,

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

    Feel free to surf to my blog - parking-pod-zyrafa.pl

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

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

    ReplyDelete
  34. 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
    you.

    Look at my weblog demenagement martin

    ReplyDelete
  35. 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

    ReplyDelete
  36. 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

    ReplyDelete
  37. 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.io.File
    import java.util.Date
    import jxl.*
    import groovy.xml.MarkupBuilder
    import org.custommonkey.xmlunit.*

    import org.codehaus.groovy.scriptom.*
    import org.codehaus.groovy.scriptom.util.office.ExcelHelper;

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

    def oExcel = new ActiveXObject('Excel.Application')
    Thread.sleep(1000)

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

    log.info strXml

    openWb.Close(false,null,false)
    oExcel.Quit()
    Scriptom.releaseApartment()

    ReplyDelete
  38. 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

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

    ReplyDelete