Apache poi cellIterator skips empty cells, but not in the first line

Question

I am creating a java program to read an excel sheet and create a comma delimited file. When I run my sample excel file with empty columns, the first line works fine, but the rest of the lines skip empty cells. I read about the code changes needed to insert empty cells in rows, but my question is why the first row works ????

---

public ArrayList OpenAndReadExcel(){ FileInputStream file = null; HSSFWorkbook workBook = null; ArrayList <String> rows = new ArrayList(); //open the file try { file = new FileInputStream(new File("Fruity.xls")); } catch (FileNotFoundException e) { // TODO Auto-generated catch block System.out.println("Could not open Input File"); e.printStackTrace(); } // open the input stream as a workbook try { workBook = new HSSFWorkbook(file); } catch (IOException e) { // TODO Auto-generated catch block System.out.println("Can't Open HSSF workbook"); e.printStackTrace(); } // get the sheet HSSFSheet sheet = workBook.getSheetAt(0); // add an iterator for every row and column Iterator<Row> rowIter = sheet.rowIterator(); while (rowIter.hasNext()) { String rowHolder = ""; HSSFRow row = (HSSFRow) rowIter.next(); Iterator<Cell> cellIter = row.cellIterator(); Boolean first =true; while ( cellIter.hasNext()) { if (!first) rowHolder = rowHolder + ","; HSSFCell cell = (HSSFCell) cellIter.next(); rowHolder = rowHolder + cell.toString() ; first = false; } rows.add(rowHolder); } return rows; } public void WriteOutput(ArrayList<String> rows) { // TODO Auto-generated method stub PrintStream outFile ; try { outFile = new PrintStream("fruity.txt"); for(String row : rows) { outFile.println(row); } outFile.close(); } catch (FileNotFoundException e) { // TODO Auto-generated catch block e.printStackTrace(); } } 

}
-----
my entry in the .xls file (Sorry, I do not know how to insert an excel table here)

Name → → → → → Country of origin → → → →> Country of origin → → →> Rating → → → No months
Apple → → → → USA → → → → → → → → → → → Washington → → → → → → → A → → → →> 6
orange → → → USA → → → → → → → → → → → → → → → → → → → → → → → → → 9
pineapple → →> USA → → → → → → → → → → → → → → → → → →> 10
strawberries → → USA → → → → → → → → → → → New Jersey → → → → → → → C → → → → → 3

my output text file
Name, country of origin, country of origin, class, number of months
Apple, USA, Washington, A, 6.0
Orange, USA, Florida, A, 9.0
Pineapple, USA, Hawaii, B, 10.0
Strawberry, USA, New Jersey, C, 3.0

---

 Notice the two extra commas before the Grade column... This is because I have two blank columns there.<br/> 

These extra commas are missing from the rest of the output.

I am using Apache Poi-3.9-20121203.jar

Answer 1

You should read the Iterating Over Rows and Cells documentation on the Apache POI website.

CellIterator will return only those cells that were defined in the file, which pretty much means those that have either values ​​or formatting. The excel file format is rare and does not require the storage of cells that have neither values ​​nor formatting.

In your case, you should have the formatting applied to the first line, which makes them appear.

You need to read the documentation and switch to index search. It will also allow you full control over how empty vs never used cells are processed in your code.