Forum Discussion

hazel_chua's avatar
hazel_chua
Contributor
5 years ago

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...
  • HimanshuTayal's avatar
    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())
    	}
    }