Skip to content

Instantly share code, notes, and snippets.

@d3ep4k
Last active August 29, 2015 14:24
Show Gist options
  • Save d3ep4k/8114f6c5962696bda15d to your computer and use it in GitHub Desktop.
Save d3ep4k/8114f6c5962696bda15d to your computer and use it in GitHub Desktop.
@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
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment