Apache poi cellIterator skips blank cells but not in first row

apache-poi

I am creating a java program to read an excel sheet and create a comma separated file. When I run my sample excel file, with blank columns, The first row works perfectly, but the rest of the rows skip the blank cells.
I have read about the code changes required to insert blank cells into the rows, but my question is why does the first row work ????

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 Input in .xls file (Sorry don't know how to insert an excel table here )

Name >>>>>>>>>> Country of Origin >>>>>>>>> State of origin >>>>>>> Grade>>>>>> No of months
Apple >>>>>>>> USA >>>>>>>>>>>>>>>>>>>>>> Washington >>>>>>>>>>>>>> A >>>>>>>>> 6
orange >>>>>> USA >>>>>>>>>>>>>>>>>>>>>> Florida >>>>>>>>>>>>>>>>> A >>>>>>>>> 9
pineapple>>>>> USA >>>>>>>>>>>>>>>>>>>>>> Hawaii >>>>>>>>>>>>>>>>>> B >>>>>>>>> 10
strawberry>>>> USA >>>>>>>>>>>>>>>>>>>>>> New Jersey>>>>>>>>>>>>>> C >>>>>>>>>> 3

my output text file

Name ,Country of Origin,State of origin,,,Grade,No 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 in the rest of the output.

I am using Apache Poi-3.9-20121203.jar

Best Answer

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

The CellIterator will only return cells that have been defined in the file, which largely means ones with either values or formatting. The excel file format is sparse, and doesn't bother storing cells which have neither values nor formatting.

For your case, you must have formatting applied to the first row, which causes them to show up.

You need to read through the documentation and switch to lookups by index. That will also allow you full control over how blank vs never used cells are handled in your code.

Related Topic