Example of using third-party Java libraries in Groovy script: reading from Excel file using Apache POI

This article explains how you can use the external jar file which has classes you need to access in ReadyAPI.

ReadyAPI allows you to use classes and methods from external libraries in custom scripts (you can learn more about scripting in ReadyAPI in this article https://support.smartbear.com/readyapi/docs/testing/scripts/about.html).

This simple example shows how to use the Apache POI libraries to open an Excel file in reading mode and return the amount of records in the file using the Groovy script. You can find more examples of usage of these libraries here: http://poi.apache.org/spreadsheet/quick-guide.html.
Please note that, normally, you can use Excel DataSource and DataSink TestSteps for executing reading/writing Excel file procedures. Scripting such operations should be useful when you are trying to perform some advanced file actions.

In order to reproduce this example, follow the steps below:
1. Download the latest release version of the Apache POI libraries from here: https://poi.apache.org/download.html.
2. Place the extracted libraries (all of the .jar files in the main extraction directory, \lib and \ooxml-lib directories) to the ReadyAPI installation directory\bin\ext directory and restart ReadyAPI.
3. Use this script:

import org.apache.poi.ss.usermodel.*
import org.apache.poi.hssf.usermodel.*
import org.apache.poi.xssf.usermodel.*
import org.apache.poi.ss.util.*

def fs = new FileInputStream ("Path to excel file")

 Workbook wb = WorkbookFactory.create(fs);
 def ws = wb.getSheet("Sheet1")
 def r = ws.getPhysicalNumberOfRows()
 log.info r

If you get the "java.lang.NoClassDefFoundError: org.openxmlformats.schemas.*" error (e.g. java.lang.ClassNotFoundException: org.openxmlformats.schemas.spreadsheetml.x2006.main.CTExtensionList), follow the suggestion from here: http://poi.apache.org/faq.html#faq-N10025.