Bug 52220 - unable to read blank cells from excel.
Summary: unable to read blank cells from excel.
Alias: None
Product: POI
Classification: Unclassified
Component: XSSF (show other bugs)
Version: 3.7-FINAL
Hardware: PC All
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on:
Reported: 2011-11-21 07:34 UTC by chaitali
Modified: 2011-11-21 10:42 UTC (History)
0 users


Note You need to log in before you can comment on or make changes to this bug.
Description chaitali 2011-11-21 07:34:49 UTC
does not read blank cells from excel file 
XSSFWorkbook workbook = new XSSFWorkbook(fis)

XSSFSheet sheet = workbook.getSheetAt(0);
			Iterator rows = sheet.rowIterator();
			while (rows.hasNext()) {
			XSSFRow row = (XSSFRow) rows.next();
			Iterator cells = row.cellIterator();
			List data = new ArrayList();
			while (cells.hasNext()) {
			XSSFCell cell = (XSSFCell) cells.next();
		catch (IOException e) {
			if (fis != null) {
Comment 1 Yegor Kozlov 2011-11-21 10:09:10 UTC
As the javadoc states, both Sheet#rowIterator() and Cell#cellIterator() iterate over the physical data, i.e. they iterate over the rows and cells that are physically defined in the file. For optimization purposes Excel typically does not write blank cells and it explains why you are getting only the actual data cells and not blanks.

For the indexed cell access via Row.getCell(int columnIndex) there is a way to control the missing cell policy for the case of null and blank cells:

        // return a blank if a cell is null or missing

        Cell cell = sheet.getRow(0).getCell(0); // never null

Note that MissingCellPolicy works only for indexed access and not for iterators, that it, no matter what you pass to workbook.setMissingCellPolicy the cell iterator will always return only physical cells.

Comment 2 chaitali 2011-11-21 10:37:00 UTC
how can we read excel file without using iterator???
Comment 3 Yegor Kozlov 2011-11-21 10:42:13 UTC
(In reply to comment #2)
> how can we read excel file without using iterator???

        for(int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++){
            Row row = sheet.getRow(i);
            for(int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++){
                Cell cell = row.getCell(j);