如何使用poi读取excel文件中的空单元格以及如何将此空单元格添加到数组列表中? [英] 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);
             }
     }
 }

上面的代码是我读取excel文件并将值打印到控制台的示例代码.在这里，我在读取空白单元格时遇到问题，并且此空白单元格未显示在控制台中.所以请给我读取空白单元格的解决方案并在控制台中打印这个空白.

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.

推荐答案

来自 HSSFRow#cellIterator JavaDoc:

From the HSSFRow#cellIterator JavaDoc:

请注意，第 4 个元素可能不是单元格 4，因为迭代器不会返回未定义(空)的单元格.对返回的单元格调用 getCellNum() 以了解它们是哪个单元格.

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.

这意味着您必须存储当前和最后一个单元格编号，如果差异大于 1，则中间有空白/未定义的单元格，即类似于 int numBlankCells = (curCellNum - lastCellNum) -1;

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;

另一种方法可能是:

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

这篇关于如何使用poi读取excel文件中的空单元格以及如何将此空单元格添加到数组列表中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！