ExcelProcessing.groovy

@Grab(group='org.apache.poi', module='poi', version='3.9')
@Grab(group='org.apache.poi', module='poi-ooxml', version='3.9')

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFCellUtil;
import org.apache.poi.ss.util.CellRangeAddress;

file = new FileInputStream(new File("abc.xls"));
workbook = new HSSFWorkbook(file);
HSSFSheet sheet = workbook.getSheetAt(0);

// Branch 
println getRowValues(sheet, "1:2")
//Doc No
println getRowValues(sheet, "3:6")

//Doc Date
println getRowValues(sheet, 7);

//Overseas Party Name
println getRowValues(sheet, 8);

//Invoice No
println getRowValues(sheet, 9);

//cur
println getRowValues(sheet, "11:12")

//DHL No
println getRowValues(sheet, 10);

//Bill Amount
println getRowValues(sheet, "13:18")


def getMergedCellValue(sheet, int regionNo){

	CellRangeAddress region = sheet.getMergedRegion(regionNo);
	int rowNum = region.getFirstRow();
	int colIndex = region.getFirstColumn();
	cell = sheet.getRow(rowNum).getCell(colIndex);
	System.out.println(cell.getStringCellValue());
}


def getRowValues(sheet, String columnRange){
	list = []
	String[] tokens = columnRange.split(':');
	c1 = Integer.parseInt(tokens[0]);
	c2 = Integer.parseInt(tokens[1]);
	for(int i = 0; i < sheet.getNumMergedRegions(); i++) {
		CellRangeAddress region = sheet.getMergedRegion(i);
		
		int colIndex = region.getFirstColumn();
		
		//the colums are in range as needed
		if(colIndex == c1 && region.getLastColumn() == c2){
			
			int rowNum = region.getFirstRow();
			//skip if row number less than this value
			if( rowNum < 18 ){
				continue;
			}

			val = getCellValue(sheet.getRow(rowNum).getCell(colIndex));
			if(val != null)
			list.add(val);
		}
	}
	return list
}


def getRowValues(sheet, columnNo){
	list = []
	rowIterator = sheet.iterator()
	while(rowIterator.hasNext()){
		HSSFRow row = rowIterator.next()
		//skip if row number less than this value
		if( row.getRowNum() < 18 ){
			continue;
		}

		val = getCellValue(HSSFCellUtil.getCell(row,columnNo));
		if(val != null)
		list.add(val);
	}
	return list
}

def getCellValue(HSSFCell cell){

	if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING){
		return cell.getStringCellValue();
	}else if(cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC){
		return cell.getNumericCellValue();
	}else if(cell.getCellType() == HSSFCell.CELL_TYPE_BLANK){
		return null
	}
}