hazel_chua
5 years agoContributor
Trying to process data from an Excel file
I'm trying to process the rows in an Excel file. Is there a way to do that by just reading in the worksheet once and then iterate through the rows.?? Each row will cause the flow of the test case to...
- 5 years ago
Hi hazel_chua :
You can use below code to get data from excel sheet and store all the values in properties file and then use it in your Test Step accordingly.
import java.io.* import java.text.SimpleDateFormat; import java.util.Calendar; import org.apache.poi.ss.usermodel.* import org.apache.poi.hssf.usermodel.* import org.apache.poi.xssf.usermodel.* import org.apache.poi.ss.util.* import groovy.xml.MarkupBuilder import groovy.xml.StreamingMarkupBuilder def fs = new FileInputStream("F:\\temp.xlsx") Workbook wb = WorkbookFactory.create(fs); def ws = wb.getSheet("Sheet1"); int r = ws.getLastRowNum(); testRunner.testCase.getTestStepByName("Properties").setPropertyValue("DataRowCount", r.toString()); for(def i = 1 ; i <= r ; i++ ){ def row = ws.getRow(i) def headerRow = ws.getRow(0) def noOfCell = row.getLastCellNum(); for (def j=0;j<noOfCell;j++){ def headertext = ws.getRow(0).getCell(j).getStringCellValue() Cell cell = row.getCell(j) if (cell == null || cell.getCellType() == Cell.CELL_TYPE_BLANK) { value = ""; } else if(cell.getCellType() == Cell.CELL_TYPE_BOOLEAN){ value = (cell.getBooleanValue()); } else if(cell.getCellType() == Cell.CELL_TYPE_NUMERIC){ value = (cell.getNumericCellValue()); } else if(cell.getCellType() == Cell.CELL_TYPE_STRING){ value = (cell.getStringCellValue()); } else if(cell.getCellType() == Cell.CELL_TYPE_ERROR){ } else if(cell.getCellType() == Cell.CELL_TYPE_FORMULA){ value = String.valueOf(cell.getStringCellValue()) } testRunner.testCase.getTestStepByName("Properties").setPropertyValue(headertext.toString()+""+i.toString(), value.toString()) } }