How to read and write data into Excel using SoapUI and Apache POI
Source code: ReadFromExcel: //Import Excel API dependency import org.apache.poi.ss.usermodel.* import org.apache.poi.hssf.usermodel.* def testCase = context.testCase def filePath = "C:\\Users\\username\\How to read and write data into excel using SoapUI and Apache POI\\dataSet.xls" def counter FileInputStream inFile = new FileInputStream(new File(filePath)) //Create Workbook instance holding reference to .xls file HSSFWorkbook workbook = new HSSFWorkbook(inFile) HSSFSheet sheet = workbook.getSheetAt(0) //First part of if statement is needed when there is no TotalRow property. Second part of if statement is needed when there is already the property but it's empty. if(!testCase.hasProperty("TotalRow") || testCase.getPropertyValue("TotalRow").toString()=="") { testCase.setPropertyValue("Counter", "0") } counter = testCase.getPropertyValue("Counter").toInteger() //First part of if statement is needed when there is no TotalRow property. Second part of if statement is needed when there is already the property but it's empty. if (!testCase.hasProperty("TotalRow") || testCase.getPropertyValue("TotalRow").toString()=="") { testCase.setPropertyValue("TotalRow", sheet.getPhysicalNumberOfRows().toString()) } //Read Excel data //Cell cell = sheet.getCell(0, counter) //In JXL: getCell(columnNum, rowNum) //Apache POI is row based. Row row = sheet.getRow(counter) Cell cell = row.getCell(0) //getCell(columnNum) counter++ inFile.close() //Copy the Excel test data to properties in Properties test step testCase.setPropertyValue("Country", cell.getStringCellValue()) testCase.setPropertyValue("Counter", counter.toString()) if (counter == testCase.getPropertyValue("TotalRow").toInteger()) testCase.setPropertyValue("isEnd", "True") else testCase.setPropertyValue("isEnd", "False") GetCapitalCityFromResponse: def testCase = context.testCase def groovyUtils = new com.eviware.soapui.support.GroovyUtils(context) def xml = groovyUtils.getXmlHolder('GetCapitalCityByCountryISOCode#response') def token = xml.getNodeValue("//*:CapitalCityResult") testCase.setPropertyValue("CapitalCity", token) WriteIntoExcel: //Import Excel API dependency import org.apache.poi.ss.usermodel.* import org.apache.poi.hssf.usermodel.* def testCase = context.testCase def filePath = "C:\\Users\\matei\\Google Drive\\DEFECTLESS\\How to read and write data into excel using SoapUI and Apache POI\\dataSet.xls" FileInputStream inFile = new FileInputStream(new File(filePath)) //Create workbook instance holding reference to .xls file HSSFWorkbook workbook = new HSSFWorkbook(inFile) HSSFSheet sheet = workbook.getSheetAt(0) //Apache POI is row based and not column based, in jxl it is easier to handle columns and rows, my video link Row row = sheet.getRow(Integer.parseInt(testCase.getPropertyValue("Counter"))-1) Cell cell = row.getCell(1) //Write data into Excel with Apache POI cell.setCellValue(testCase.getPropertyValue("CapitalCity")) //Write data into Excel with JXL //sheet.addCell(new Label(columnNum: 1, rowNum: Integer.parseInt(testCase.getPropertyValue("Counter"))-1, testCase.getPropertyValue("CapitalCity"))) FileOutputStream outFile = new FileOutputStream(new File(filePath)) workbook.write(outFile) outFile.close() inFile.close() /*Closes this file input stream and releases any system resources associated with the stream. Its necessary to close all underline resources of FileInputStream. If you don't close your input streams, it might forbid the JVM from opening any more resource. Its will cause a potential resource leak, So once you are done with your FileInputStream just close it.*/ RunTestCaseAgain: def testCase = context.testCase Stop = testCase.getPropertyValue("isEnd").toString() if (Stop=="True"){ testCase.setPropertyValue("Country","") testCase.setPropertyValue("Counter", "0") testCase.setPropertyValue("TotalRow", "") testCase.setPropertyValue("isEnd", "False") } else testRunner.gotoStepByName("ReadFromExcel")
Source code: ReadFromExcel: //Import Excel API dependency import org.apache.poi.ss.usermodel.* import org.apache.poi.hssf.usermodel.* def testCase = context.testCase def filePath = "C:\\Users\\username\\How to read and write data into excel using SoapUI and Apache POI\\dataSet.xls" def counter FileInputStream inFile = new FileInputStream(new File(filePath)) //Create Workbook instance holding reference to .xls file HSSFWorkbook workbook = new HSSFWorkbook(inFile) HSSFSheet sheet = workbook.getSheetAt(0) //First part of if statement is needed when there is no TotalRow property. Second part of if statement is needed when there is already the property but it's empty. if(!testCase.hasProperty("TotalRow") || testCase.getPropertyValue("TotalRow").toString()=="") { testCase.setPropertyValue("Counter", "0") } counter = testCase.getPropertyValue("Counter").toInteger() //First part of if statement is needed when there is no TotalRow property. Second part of if statement is needed when there is already the property but it's empty. if (!testCase.hasProperty("TotalRow") || testCase.getPropertyValue("TotalRow").toString()=="") { testCase.setPropertyValue("TotalRow", sheet.getPhysicalNumberOfRows().toString()) } //Read Excel data //Cell cell = sheet.getCell(0, counter) //In JXL: getCell(columnNum, rowNum) //Apache POI is row based. Row row = sheet.getRow(counter) Cell cell = row.getCell(0) //getCell(columnNum) counter++ inFile.close() //Copy the Excel test data to properties in Properties test step testCase.setPropertyValue("Country", cell.getStringCellValue()) testCase.setPropertyValue("Counter", counter.toString()) if (counter == testCase.getPropertyValue("TotalRow").toInteger()) testCase.setPropertyValue("isEnd", "True") else testCase.setPropertyValue("isEnd", "False") GetCapitalCityFromResponse: def testCase = context.testCase def groovyUtils = new com.eviware.soapui.support.GroovyUtils(context) def xml = groovyUtils.getXmlHolder('GetCapitalCityByCountryISOCode#response') def token = xml.getNodeValue("//*:CapitalCityResult") testCase.setPropertyValue("CapitalCity", token) WriteIntoExcel: //Import Excel API dependency import org.apache.poi.ss.usermodel.* import org.apache.poi.hssf.usermodel.* def testCase = context.testCase def filePath = "C:\\Users\\matei\\Google Drive\\DEFECTLESS\\How to read and write data into excel using SoapUI and Apache POI\\dataSet.xls" FileInputStream inFile = new FileInputStream(new File(filePath)) //Create workbook instance holding reference to .xls file HSSFWorkbook workbook = new HSSFWorkbook(inFile) HSSFSheet sheet = workbook.getSheetAt(0) //Apache POI is row based and not column based, in jxl it is easier to handle columns and rows, my video link Row row = sheet.getRow(Integer.parseInt(testCase.getPropertyValue("Counter"))-1) Cell cell = row.getCell(1) //Write data into Excel with Apache POI cell.setCellValue(testCase.getPropertyValue("CapitalCity")) //Write data into Excel with JXL //sheet.addCell(new Label(columnNum: 1, rowNum: Integer.parseInt(testCase.getPropertyValue("Counter"))-1, testCase.getPropertyValue("CapitalCity"))) FileOutputStream outFile = new FileOutputStream(new File(filePath)) workbook.write(outFile) outFile.close() inFile.close() /*Closes this file input stream and releases any system resources associated with the stream. Its necessary to close all underline resources of FileInputStream. If you don't close your input streams, it might forbid the JVM from opening any more resource. Its will cause a potential resource leak, So once you are done with your FileInputStream just close it.*/ RunTestCaseAgain: def testCase = context.testCase Stop = testCase.getPropertyValue("isEnd").toString() if (Stop=="True"){ testCase.setPropertyValue("Country","") testCase.setPropertyValue("Counter", "0") testCase.setPropertyValue("TotalRow", "") testCase.setPropertyValue("isEnd", "False") } else testRunner.gotoStepByName("ReadFromExcel")