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