How to read empty cell in excel file using poi and how to add this empty cell to array list?

Question

    public void readExcel(String fileName)
    try 
    {
          FileInputStream myInput = new FileInputStream(fileName);
          POIFSFileSystem myFileSystem = new POIFSFileSystem(myInput);
          HSSFWorkbook myWorkBook = new HSSFWorkbook(myFileSystem);
          HSSFSheet mySheet = myWorkBook.getSheetAt(0);
          Iterator rowIter = mySheet.rowIterator();
          while(rowIter.hasNext())
          {
             HSSFRow myRow = (HSSFRow) rowIter.next();
             Iterator cellIter = myRow.cellIterator();
             ArrayList cellStoreVector=new ArrayList();
             String header_name = null;
             while(cellIter.hasNext())
             {
                 HSSFCell myCell = (HSSFCell) cellIter.next();
                 // if it is empty cell in  my excel file its not added to
                 // array List                            
                cellStoreVector.add(myCell); 
             }
             cellVectorHolder.add(cellStoreVector);

          } 
        }catch (Exception e)
         {
           e.printStackTrace(); 
         }
         saveToDatabase(cellVectorHolder);
        }

      public void saveToDatabase(ArrayList array)
      {
        for (int i=0;i<array.size(); i++)
        {
             ArrayList cellStoreVector=(ArrayList)array.get(i);
             for (int j=0; j < cellStoreVector.size();j++) 
             {  
                HSSFCell myCell = (HSSFCell)cellStoreVector.get(j);
                String st = myCell.toString();
                System.out.println("values "+st);
             }
     }
 }

The above code is my sample code for reading excel file and print values into console. Here I have a problem when reading blank cells and this blank cells are not displayed in the console. So please give me solution for reading blank cell and print this blank in console.

please format your code, change the title to something useful and put the discrpition of your problem from the title to the text. with this, you won't get any help as it's totaly unreadable. — oezi, Apr 18 '11 at 07:49
I have editted your formatting for this time, but please do it yourself next time. Otherwise people simply can't help you. — Vladimir Ivanov, Apr 18 '11 at 07:53
Not sure I understand - you have a void test() which is returning a cellVectorHolder object. Where is the saveToDatabase called? — lobster1234, Apr 18 '11 at 07:56

score 6 · Answer 1 · answered Apr 18 '11 at 08:01

From the HSSFRow#cellIterator JavaDoc:

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.

This means you'd have to store the current and last cell number and if you get a difference greater that 1 you have blank/undefined cells in between, i.e. something like int numBlankCells = (curCellNum - lastCellNum) - 1;

Another aproach could be:

short minColIndex = row.getFirstCellNum();
short maxColIndex = row.getLastCellNum();
for(short colIndex = minColIndex; colIndex < maxColIndex; colIndexx++) {
  HSSFCell cell = row.getCell(colIndex);
  if(cell == null) {
    //blank/undefined cell
  }
  else {
    //defined cell which still could be of type HSSFCell.CELL_TYPE_BLANK or contain an empty string
  }
}

If you set a missing cell policy the code could be even clearer - that way when the cell isn't there it'd for example get auto created as a blank cell. — Gagravarr, Apr 18 '11 at 09:23

score 1 · Answer 2 · edited Jun 13 '12 at 11:42

List cellDataList = new ArrayList();
int lineNumber = 0;   

while (rowIterator.hasNext())
{
    HSSFRow hssfRow = (HSSFRow) rowIterator.next();
    //System.out.println("Befor If");
    lineNumber++;
    if(lineNumber==1){continue;}
    //System.out.println("Out side if ");

    Iterator iterator = hssfRow.cellIterator();
    List cellTempList = new ArrayList();
    int current = 0, next = 1;

    while (iterator.hasNext())
    {
        HSSFCell hssfCell = (HSSFCell) iterator.next();
        current = hssfCell.getColumnIndex();

        if(current<next) 
        {
            System.out.println("Condition Satisfied");
        }
        else 
        {
            int loop = current-next;
            System.out.println("inside else Loop value : "+(loop));

            for(int k=0;k<loop+1;k++)
            {
            System.out.println("Adding nulls");
                cellTempList.add(null);
                next = next + 1;
            }
        }

        cellTempList.add(hssfCell);
        next = next + 1;
        System.out.println("At End  next value is : "+next);
    }

    cellDataList.add(cellTempList);
}

Try this code it will works.

score 0 · Answer 3 · edited Apr 18 '11 at 08:07

0

if(myCell.cellType == NPOI.SS.UserModel.CellType.BLANK)

Have you modified or overwritten HSSFCell? If not, this should work fine.

edited Apr 18 '11 at 08:07

Andrew Thompson

168,117
40
217
433

answered Apr 18 '11 at 07:59

bastianwegge

2,435
1
24
30

How to read empty cell in excel file using poi and how to add this empty cell to array list?

3 Answers3

Linked