Forum Discussion

rnxn's avatar
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
    3 years ago



    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... 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 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"Number of rows is ${size}.");
    // We will use these in our loop.
    def found = false;
    def row = 0;"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();
"${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;
    	// 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();"DOB is a Match!");
    } else {"Could not find ${sourceForename} ${sourceSurname} in the  Excel lookup sheet.");
    workbook1.close() //close the file