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

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