Forum Discussion

rnxn's avatar
rnxn
Occasional Contributor
3 years ago

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

Hello,    I'm running SOAP requests using customer information from a test customer database.  Each customer's DOB changes on a daily basis so that they remain the exact same age down to the day -...
  • ChrisAdams's avatar
    ChrisAdams
    3 years ago

    Hi,

     

    Re iterating over a spreadsheet to find a row.

     

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

    CustomerId Customer Name Surname DOB
    1 John Lennon 9/10/1940
    2 Paul McCartney 18/6/1942
    3 George Harrison 25/2/1943
    4 Ringo Starkey 7/7/1940

     

    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