cancel
Showing results for 
Search instead for 
Did you mean: 

Trying to process data from an Excel file

SOLVED
Highlighted
Contributor

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 be conditionally changed as the required test steps for the active row is run depending on the values in different columns of the row.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Leader

Re: Trying to process data from an Excel file

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())
	}
}

Click "Accept as Solution" if my answer has helped,
Remember to give "Kudos" 🙂 ↓↓↓↓↓



Thanks and Regards,
Himanshu Tayal

View solution in original post

3 REPLIES 3
Highlighted
Community Leader

Re: Trying to process data from an Excel file

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())
	}
}

Click "Accept as Solution" if my answer has helped,
Remember to give "Kudos" 🙂 ↓↓↓↓↓



Thanks and Regards,
Himanshu Tayal

View solution in original post

Highlighted
Contributor

Re: Trying to process data from an Excel file

Is that even going to be viable if I had like 1000 rows and 20 columns..??  So, there is no way to maintain an actual file pointer across the entire test case or pointer to the sheet..??

 

Also, eventually, I need to be able to output data to the same Excel (or if easier, a new Excel) file based on groups of rows from the input file.

Highlighted
Community Leader

Re: Trying to process data from an Excel file

@hazel_chua ,

 

I have tested this with 500+ rows and 9 columns and didn't find any problem, you can test this for 1000 rows hope it will work fine.

 


Click "Accept as Solution" if my answer has helped,
Remember to give "Kudos" 🙂 ↓↓↓↓↓



Thanks and Regards,
Himanshu Tayal
New Here?
Join us and watch the welcome video:
Announcements
Top Kudoed Authors