CODEWITHSUNDEEP
javaapache-poi
veda
veda

Reputation: 31

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

    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.

Upvotes: 2

Views: 20576

Answers (3)

Praveen
Praveen

Reputation: 46

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.

Upvotes: 1

bastianwegge
bastianwegge

Reputation: 2498

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

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

Upvotes: 0

Thomas
Thomas

Reputation: 88707

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
  }
}

Upvotes: 6

Related Questions