Return DOB for specified customer from Excel workbook (similar to Excel VLOOKUP)

Hi,

Re iterating over a spreadsheet to find a row.

I'm using this table in my Excel lookup spreadsheet.

To do the lookup, I wrote some Groovy script.  I used this page to help me read the Excel spreadsheet... https://www.anilvij.com/how-to-read-data-from-excel-using-groovy-soapui/ in order to write my Groovy script.

Here's the Groovy script you can use as an example.  Note : This works for xls files, not xlsx.

import com.eviware.soapui.support.XmlHolder;
import jxl.*;
import jxl.write.*;

// For the sake of example, these are the values returned from your db and these are what we will look for in Excel.
def forename = "George";
def surname = "Harrison";
def dob = "25/2/1943";


Workbook workbook1 = Workbook.getWorkbook(new File("C:\\temp\\CustomerData.xls")) //file containing the data

Sheet sheet1 = workbook1.getSheet(0) //Index 0 will read the first sheet from the workbook, you can also specify the sheet name with "Sheet1"
//Sheet sheet1 = workbook1.getSheet("Sheet1")

size= sheet1.getRows().toInteger() //get the number of rows, each row is a data set

log.info("Number of rows is ${size}.");

// We will use these in our loop.
def found = false;
def row = 0;

log.info("Looking for ${forename} ${surname}");

for (int i = 1; i < size; i++) {

	def id = sheet1.getCell(0,i).getContents(); // getCell(column,row)  getContents() teases out the value.
	def sourceForename = sheet1.getCell(1,i).getContents();
	def sourceSurname = sheet1.getCell(2,i) .getContents();

	log.info("${id} - ${sourceForename} ${sourceSurname}");

	if((forename.equals(sourceForename)) && (surname.equals(sourceSurname))) {
		// We've found a match.  Let's note the row and break out of loop to save time.
		found = true;
		row = i;
		break;
	}
}

if(found){
	// Compare DOB
	// You don't have to use assert, you could use a boolean check or whatever suits.
	assert dob == sheet1.getCell(3,row).getContents();
	log.info("DOB is a Match!");
} else {
	log.info("Could not find ${sourceForename} ${sourceSurname} in the  Excel lookup sheet.");
}

workbook1.close() //close the file