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(); data.add(cell); } //System.out.print(rowCount); sheetData.add(data); } } catch (IOException e) { e.printStackTrace(); } { if (fis != null) { fis.close(); } }
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 workbook.setMissingCellPolicy(Row.RETURN_BLANK_AS_NULL); 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. Yegor
how can we read excel file without using iterator???
(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); } }