Empty cells issue when extracting excel data using POI in JAVA?

Question

I have just Extracted the cells from the excel sheet using Apache POI, everything is working fine. But whenever there is an empty cell, the very next right cell data is what I get as a output. But, if exists a value in the latter, the desired output is coming.

This is the logic I've written.

    Iterator<Row> rowIterator=sheet.rowIterator();
    while(rowIterator.hasNext())
    {

        ExtractedRowsString extRows=new ExtractedRowsString();
        ArrayList<HSSFCell> list=new ArrayList<HSSFCell>();
        HSSFRow row=(HSSFRow) rowIterator.next();

        Iterator<Cell> cellIterator=row.cellIterator();
        while(cellIterator.hasNext())
        {
            HSSFCell cell=(HSSFCell)cellIterator.next();
            list.add(cell); 
        }
        if(check)
        {
            addBean(list,extRows);
            print(extRows);
        }

        check=true;

    }

What may be the problem?

EDITED :

public static void addBean(ArrayList list,ExtractedRowsString extRows)
{
    for(int i=0;i<list.size();i++)
    {
        switch(i)
        {
            case 0:
                extRows.setSchool_success_id((list.get(i)).toString());
                break;
            case 1:
                extRows.setPem_id( (list.get(i)).toString() );
                break;  
            case 2:
                extRows.setDistrict_code((list.get(i)).toString());
                break;
            case 3:
                extRows.setDistrict((list.get(i)).toString());
                break;

        }
    }
}

score 3 · Accepted Answer · answered Mar 18 '10 at 12:33

From the docs:

cellIterator

public java.util.Iterator cellIterator()

Specified by: cellIterator in interface Row

Returns: cell iterator of the physically defined cells. Note that the 4th element might well not be cell 4, as the iterator will not return un-defined (null) cells. Call getCellNum() on the returned cells to know which cell they are. As this only ever works on physically defined cells, the Row.MissingCellPolicy has no effect.

In short, empty cells do not show up in the iterator so you always have to check which cell you got.

score 0 · Answer 2 · answered Mar 18 '10 at 12:32

0

ArrayList does permit adding nulls. Also what is the addBean method doing (skipping nulls or empty strings perhaps?) Can you post a small working code that one can run ?

answered Mar 18 '10 at 12:32

Kannan Ekanath

16,759
22
75
101

Calm, i've added my add bean method. – i2ijeya Mar 18 '10 at 13:16

Empty cells issue when extracting excel data using POI in JAVA?

2 Answers2