CODEWITHSUNDEEP
javaapache-poi
ritesh kumar poddar
ritesh kumar poddar

Reputation: 31

why apache poi reading less number of cell in a row of excel

I am trying to fetch the cell using named range.But After trying the below code,not able to get consistent cell in a row of the sheet that's getting null exception while using r.getCell().

String cname = "TestName";
Workbook wb = getMyWorkbook(); // retrieve workbook

// retrieve the named range
int namedCellIdx = wb.getNameIndex(cellName);
Name aNamedCell = wb.getNameAt(namedCellIdx);

// retrieve the cell at the named range and test its contents
AreaReference aref = new AreaReference(aNamedCell.getRefersToFormula());
CellReference[] crefs = aref.getAllReferencedCells();
for (int i = 0; i < crefs.length; i++) {
    Sheet s = wb.getSheet(crefs[i].getSheetName());
    Row r = sheet.getRow(crefs[i].getRow());
    Cell c = r.getCell(crefs[i].getCol());
    // extract the cell contents based on cell type etc.
}

Upvotes: 1

Views: 177

Answers (1)

Axel Richter
Axel Richter

Reputation: 61852

For the sake of memory consuming, totally empty rows are not stored on the sheet. Also totally empty cells are not stored in rows of the sheet.

Sheet.getRow returns null if the row is not defined on the sheet. Also Row.getCell returns null if the cell is undefined in that row.

So we always need check:

...
Row r = sheet.getRow(crefs[i].getRow());
if (r == null) {
 //row is empty
} else {
 Cell c = r.getCell(crefs[i].getCol());
 if (c == null) {
  //cell is empty
 } else {
  //do something with c
 }
}
...

Upvotes: 1

Related Questions