Forum Discussion

mitsuko's avatar
mitsuko
New Member
4 years ago

Read excel data and extract value in groovy

Hi,

 

Need help on how I can read excel file and extract value with specific text like "am" and merge it with other values using groovy code.

 

Sample input in excel:

 

Firstname Lastname

Amalia Miranda

Jayson Kind

Henry Gomez

Miriam Ostola

 

Expected Output:

Amalia Miranda

Miriam Ostola

 

Right now here's my code for checking character 'am'. my ouput only returns am but expected is to print Amalia Miranda and Miriam Ostola

 

import java.util.regex.Matcher;
import java.util.regex.Pattern;

final String regex = "am";
final String string = "amalia";

final Pattern pattern = Pattern.compile(regex, Pattern.MULTILINE);
final Matcher matcher = pattern.matcher(string);

while (matcher.find()) {
    System.out.println("Full match: " + matcher.group(0));
    for (int i = 1; i < matcher.groupCount(); i++) {
        System.out.println("Group " + i + ": " + matcher.group(i));
    }
}

And my code for reading excel but still encountering error:

 

import org.apache.poi.ss.usermodel.*
import org.apache.poi.ss.usermodel.Sheet
import org.apache.poi.ss.usermodel.Workbook
import org.apache.poi.xssf.usermodel.XSSFWorkbook
def ReadCellData(int vRow, int vColumn)
{
    try
    {
        fis = new FileInputStream("GROOVY\\name.xlsx");
        wb = new XSSFWorkbook(fis);
        print FileInputStream();
    }
    catch(FileNotFoundException e) { e.printStackTrace(); } catch(IOException e1) { e1.printStackTrace(); }
    sheet = wb.getSheetAt(0); 
    row = sheet.getRow(vRow); 
    cell = row.getCell(vColumn); 
    return cell.getStringCellValue(); 
}

vOutput=ReadCellData(0, 0);
println(vOutput);
  

 Would be happy if this 2 code ideas will be merge. I am new to groovy coding. Thanks.

2 Replies

Sort By
    • HimanshuTayal's avatar
      HimanshuTayal
      Community Hero
      4 years ago

      mitsuko :

       

      You can refer below code, pre-requisite you need to add properties step into test case.

       

      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.*

//fetching data from excel sheet
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){
			if (DateUtil.isCellDateFormatted(cell)) {
				Date date = cell.getDateCellValue();
				SimpleDateFormat format1 = new SimpleDateFormat("dd-MM-yyyy");
				String formatted = format1.format(date);
				value = formatted 
		    } else {
				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())
	}
}


//am based condition
for(int i = 1 ; i <= 4; i++){
	if(context.expand('${Properties#Firstname'+i.toString()+'}').toLowerCase().contains("am")){
		log.info context.expand('${Properties#Firstname'+i.toString()+'}') +" "+context.expand('${Properties#Lastname'+i.toString()+'}')
	}
}