reading file from excel file
dear all,
I am new in soapUI and groovy scripts.
I am trying to retrieve data from excel files that I can insert into soapUI (not soapUI pro). I am trying some always get an error.
Right now, what I have is this:
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.Iterator;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class readExcelFile3 {
public void readXLSXFile(String fileName) {
InputStream XlsxFileToRead = null;
XSSFWorkbook workbook = null;
try {
XlsxFileToRead = new FileInputStream(fileName);
//Getting the workbook instance for xlsx file
workbook = new XSSFWorkbook(XlsxFileToRead);
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
//getting the first sheet from the workbook using sheet name.
// We can also pass the index of the sheet which starts from '0'.
XSSFSheet sheet = workbook.getSheet("Sheet1");
XSSFRow row;
XSSFCell cell;
//Iterating all the rows in the sheet
Iterator rows = sheet.rowIterator();
while (rows.hasNext()) {
row = (XSSFRow) rows.next();
//Iterating all the cells of the current row
Iterator cells = row.cellIterator();
while (cells.hasNext()) {
cell = (XSSFCell) cells.next();
if (cell.getCellType() == XSSFCell.CELL_TYPE_STRING) {
System.out.print(cell.getStringCellValue() + " ");
} else if (cell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC) {
System.out.print(cell.getNumericCellValue() + " ");
} else if (cell.getCellType() == XSSFCell.CELL_TYPE_BOOLEAN) {
System.out.print(cell.getBooleanCellValue() + " ");
} else { // //Here if require, we can also add below methods to
// read the cell content
// XSSFCell.CELL_TYPE_BLANK
// XSSFCell.CELL_TYPE_FORMULA
// XSSFCell.CELL_TYPE_ERROR
}
}
System.out.println();
try {
XlsxFileToRead.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
public static void main(String[] args) {
readExcelFile3 readXlsx = new readExcelFile3();
readXlsx.readXLSXFile("/Users/Documents/soapUIworkspace/skyvva/testExcelFile.xlsx");
}
}
----
this script is just basic script to tell me the basic concepts the works. When I run the code in eclipse, it works. But when I run it in soapUI, I have errors.
error messages:
org.codehaus.groovy.control.MultipleCompilationErrorsException: startup failed: Script4.groovy: 9: unable to resolve class org.apache.poi.xssf.usermodel.XSSFSheet @ line 9, column 1. import org.apache.poi.xssf.usermodel.XSSFSheet; ^ org.codehaus.groovy.syntax.SyntaxException: unable to resolve class org.apache.poi.xssf.usermodel.XSSFSheet @ line 9, column 1. at org.codehaus.groovy.ast.ClassCodeVisitorSupport.addError(ClassCodeVisitorSupport.java:149) at org.codehaus.groovy.control.ResolveVisitor.visitClass(ResolveVisitor.java:1225) at org.codehaus.groovy.control.ResolveVisitor.startResolving(ResolveVisitor.java:178) at org.codehaus.groovy.control.CompilationUnit$11.call(CompilationUnit.java:651) at org.codehaus.groovy.control.CompilationUnit.applyToSourceUnits(CompilationUnit.java:931) at org.codehaus.groovy.control.CompilationUnit.doPhaseOperation(CompilationUnit.java:593) at org.codehaus.groovy.control.CompilationUnit.compile(CompilationUnit.java:542) at groovy.lang.GroovyClassLoader.doParseClass(GroovyClassLoader.java:298) at groovy.lang.GroovyClassLoader.parseClass(GroovyClassLoader.java:268) at groovy.lang.GroovyShell.parseClass(GroovyShell.java:694) at groovy.lang.GroovyShell.parse(GroovyShell.java:706) at groovy.lang.GroovyShell.parse(GroovyShell.java:742) at groovy.lang.GroovyShell.parse(GroovyShell.java:733) at com.eviware.soapui.support.scripting.groovy.SoapUIGroovyScriptEngine.compile(SoapUIGroovyScriptEngine.java:136) at com.eviware.soapui.support.scripting.groovy.SoapUIGroovyScriptEngine.run(SoapUIGroovyScriptEngine.java:87) at com.eviware.soapui.impl.wsdl.teststeps.WsdlGroovyScriptTestStep.run(WsdlGroovyScriptTestStep.java:141) at com.eviware.soapui.impl.wsdl.panels.teststeps.GroovyScriptStepDesktopPanel$RunAction$1.run(GroovyScriptStepDesktopPanel.java:250) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) at java.lang.Thread.run(Thread.java:748) Script4.groovy: 8: unable to resolve class org.apache.poi.xssf.usermodel.XSSFRow @ line 8, column 1. import org.apache.poi.xssf.usermodel.XSSFRow; ^ org.codehaus.groovy.syntax.SyntaxException: unable to resolve class org.apache.poi.xssf.usermodel.XSSFRow @ line 8, column 1. at org.codehaus.groovy.ast.ClassCodeVisitorSupport.addError(ClassCodeVisitorSupport.java:149) at org.codehaus.groovy.control.ResolveVisitor.visitClass(ResolveVisitor.java:1225) at org.codehaus.groovy.control.ResolveVisitor.startResolving(ResolveVisitor.java:178) at org.codehaus.groovy.control.CompilationUnit$11.call(CompilationUnit.java:651) at org.codehaus.groovy.control.CompilationUnit.applyToSourceUnits(CompilationUnit.java:931) at org.codehaus.groovy.control.CompilationUnit.doPhaseOperation(CompilationUnit.java:593) at org.codehaus.groovy.control.CompilationUnit.compile(CompilationUnit.java:542) at groovy.lang.GroovyClassLoader.doParseClass(GroovyClassLoader.java:298) at groovy.lang.GroovyClassLoader.parseClass(GroovyClassLoader.java:268) at groovy.lang.GroovyShell.parseClass(GroovyShell.java:694) at groovy.lang.GroovyShell.parse(GroovyShell.java:706) at groovy.lang.GroovyShell.parse(GroovyShell.java:742) at groovy.lang.GroovyShell.parse(GroovyShell.java:733) at com.eviware.soapui.support.scripting.groovy.SoapUIGroovyScriptEngine.compile(SoapUIGroovyScriptEngine.java:136) at com.eviware.soapui.support.scripting.groovy.SoapUIGroovyScriptEngine.run(SoapUIGroovyScriptEngine.java:87) at com.eviware.soapui.impl.wsdl.teststeps.WsdlGroovyScriptTestStep.run(WsdlGroovyScriptTestStep.java:141) at com.eviware.soapui.impl.wsdl.panels.teststeps.GroovyScriptStepDesktopPanel$RunAction$1.run(GroovyScriptStepDesktopPanel.java:250) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) at java.lang.Thread.run(Thread.java:748) Script4.groovy: 7: unable to resolve class org.apache.poi.xssf.usermodel.XSSFCell @ line 7, column 1. import org.apache.poi.xssf.usermodel.XSSFCell; ^ org.codehaus.groovy.syntax.SyntaxException: unable to resolve class org.apache.poi.xssf.usermodel.XSSFCell @ line 7, column 1. at org.codehaus.groovy.ast.ClassCodeVisitorSupport.addError(ClassCodeVisitorSupport.java:149) at org.codehaus.groovy.control.ResolveVisitor.visitClass(ResolveVisitor.java:1225) at org.codehaus.groovy.control.ResolveVisitor.startResolving(ResolveVisitor.java:178) at org.codehaus.groovy.control.CompilationUnit$11.call(CompilationUnit.java:651) at org.codehaus.groovy.control.CompilationUnit.applyToSourceUnits(CompilationUnit.java:931) at org.codehaus.groovy.control.CompilationUnit.doPhaseOperation(CompilationUnit.java:593) at org.codehaus.groovy.control.CompilationUnit.compile(CompilationUnit.java:542) at groovy.lang.GroovyClassLoader.doParseClass(GroovyClassLoader.java:298) at groovy.lang.GroovyClassLoader.parseClass(GroovyClassLoader.java:268) at groovy.lang.GroovyShell.parseClass(GroovyShell.java:694) at groovy.lang.GroovyShell.parse(GroovyShell.java:706) at groovy.lang.GroovyShell.parse(GroovyShell.java:742) at groovy.lang.GroovyShell.parse(GroovyShell.java:733) at com.eviware.soapui.support.scripting.groovy.SoapUIGroovyScriptEngine.compile(SoapUIGroovyScriptEngine.java:136) at com.eviware.soapui.support.scripting.groovy.SoapUIGroovyScriptEngine.run(SoapUIGroovyScriptEngine.java:87) at com.eviware.soapui.impl.wsdl.teststeps.WsdlGroovyScriptTestStep.run(WsdlGroovyScriptTestStep.java:141) at com.eviware.soapui.impl.wsdl.panels.teststeps.GroovyScriptStepDesktopPanel$RunAction$1.run(GroovyScriptStepDesktopPanel.java:250) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) at java.lang.Thread.run(Thread.java:748) Script4.groovy: 10: unable to resolve class org.apache.poi.xssf.usermodel.XSSFWorkbook @ line 10, column 1. import org.apache.poi.xssf.usermodel.XSSFWorkbook; ^ org.codehaus.groovy.syntax.SyntaxException: unable to resolve class org.apache.poi.xssf.usermodel.XSSFWorkbook @ line 10, column 1. at org.codehaus.groovy.ast.ClassCodeVisitorSupport.addError(ClassCodeVisitorSupport.java:149) at org.codehaus.groovy.control.ResolveVisitor.visitClass(ResolveVisitor.java:1225) at org.codehaus.groovy.control.ResolveVisitor.startResolving(ResolveVisitor.java:178) at org.codehaus.groovy.control.CompilationUnit$11.call(CompilationUnit.java:651) at org.codehaus.groovy.control.CompilationUnit.applyToSourceUnits(CompilationUnit.java:931) at org.codehaus.groovy.control.CompilationUnit.doPhaseOperation(CompilationUnit.java:593) at org.codehaus.groovy.control.CompilationUnit.compile(CompilationUnit.java:542) at groovy.lang.GroovyClassLoader.doParseClass(GroovyClassLoader.java:298) at groovy.lang.GroovyClassLoader.parseClass(GroovyClassLoader.java:268) at groovy.lang.GroovyShell.parseClass(GroovyShell.java:694) at groovy.lang.GroovyShell.parse(GroovyShell.java:706) at groovy.lang.GroovyShell.parse(GroovyShell.java:742) at groovy.lang.GroovyShell.parse(GroovyShell.java:733) at com.eviware.soapui.support.scripting.groovy.SoapUIGroovyScriptEngine.compile(SoapUIGroovyScriptEngine.java:136) at com.eviware.soapui.support.scripting.groovy.SoapUIGroovyScriptEngine.run(SoapUIGroovyScriptEngine.java:87) at com.eviware.soapui.impl.wsdl.teststeps.WsdlGroovyScriptTestStep.run(WsdlGroovyScriptTestStep.java:141) at com.eviware.soapui.impl.wsdl.panels.teststeps.GroovyScriptStepDesktopPanel$RunAction$1.run(GroovyScriptStepDesktopPanel.java:250) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) at java.lang.Thread.run(Thread.java:748) 4 errors
please help. I want to retrieve data from excel files and insert in the xml in the soapUI via parameter and I do not know how to set parameter in soapUI.
thanks in advance.
