Dowload jexcelapi_2_6_12 and put it in ext folder of soap ui .. .restart SOAP UI.. create one sample xls and paste the below code in soap ui...
import jxl.*
import jxl.write.*
//to read from excel
Workbook excelFile= Workbook.getWorkbook(new File("c:\\Testing\\myfile.xls"));
Sheet sheet = excelFile.getSheet(0)
Cell a1 = sheet.getCell(0,0)
log.info a1 // 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)
String s1 = c2.getContents();
log.info s1
excelFile.close()
// To write in excel
WritableWorkbook workbook1 = Workbook.createWorkbook(new File("c:\\Testing\\myfile.xls"))
WritableSheet sheet1 = workbook1.createSheet("Worksheet Number 1", 0)
log.info(sheet1.isHidden())
Label label = new Label(0, 1, "some data to put");
sheet1.addCell(label);
workbook1.write()
workbook1.close()
Disclaimer-- code copied from various blogs :)
import jxl.*
import jxl.write.*
//to read from excel
Workbook excelFile= Workbook.getWorkbook(new File("c:\\Testing\\myfile.xls"));
Sheet sheet = excelFile.getSheet(0)
Cell a1 = sheet.getCell(0,0)
log.info a1 // 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)
String s1 = c2.getContents();
log.info s1
excelFile.close()
// To write in excel
WritableWorkbook workbook1 = Workbook.createWorkbook(new File("c:\\Testing\\myfile.xls"))
WritableSheet sheet1 = workbook1.createSheet("Worksheet Number 1", 0)
log.info(sheet1.isHidden())
Label label = new Label(0, 1, "some data to put");
sheet1.addCell(label);
workbook1.write()
workbook1.close()
Disclaimer-- code copied from various blogs :)
test
ReplyDeleteHi there... hoping you might be able to help... would you have any further details on how to ensure that the jexcelapi_2_6_12 download is installed correctly. I have downloaded it and extracted it which extracts to a bunch of folders... do I place the full folder stack inside the ext folder or is there further manipulation required,... For me the script is not recognizing the Workbook element which tells me it is not seeing the import?.. thanks a million Tony
ReplyDeleteCould you please provide an example of how to write in an existing excel file
ReplyDeleteWhile executing above code getting below error-
ReplyDeleteorg.codehaus.groovy.control.MultipleCompilationErrorsException: startup failed: Script2.groovy: 5: unable to resolve class Workbook @ line 5, column 10. Workbook excelFile= Workbook.getWorkbook(new File("D:\\SoapAutomation1.xls")); ^ org.codehau
Any suggestion to resolve it
The "read" section no longer works. It runs, but a1 returns a jxl.read.biff.Label msg instead of a value. When I converted it to a string (like you did below in your code), it worked:
ReplyDeleteCell a1 = sheet.getCell(0,0);
String z1 = a1.getContents();
log.info ("a1= "+z1);
Also, your comments read "// getCell(row,column) -- place some values in myfile.xls"
Actually, it reads column, row - but even the Java Docs aren't very clear (http://jexcelapi.sourceforge.net/resources/javadocs/2_6/docs/index.html). They show the parms as I mentioned, but the description switches the order:
getCell(int column, int row)
Returns the cell specified at this row and at this column.
Finally, you say "...place some values in myfile.xls" A Get won't place (write) a value, it reads a value.
Your code worked, which is very good and puts you in an elite group. The vast majority of examples don't work; won't even compile.
Here is an example of a working SoapUI script using Groovy doing data-driven testing reading from Excel .xls and populating a Request. It also reads the Response and compares it to the expected result from the same Excel sheet. Watch it at https://www.youtube.com/watch?v=v2aNSUZtFRY&t=1s
ReplyDeleteHello Everyone I am getting below error, can anyone know how to rectify this
ReplyDeleteorg.codehaus.groovy.control.MultipleCompilationErrorsException: startup failed: Script7.groovy: 4: unable to resolve class FIleInputStream @ line 4, column 9. def fa= new FIleInputStream("D:\\AgencyList.xls"); ^